Java Code Examples for org.apache.poi.hssf.usermodel.HSSFCellStyle#setDataFormat()

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFCellStyle#setDataFormat() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: POIUtils.java    From ermasterr with Apache License 2.0 7 votes vote down vote up 
public static HSSFCellStyle copyCellStyle(final HSSFWorkbook workbook, final HSSFCellStyle style) {

        final HSSFCellStyle newCellStyle = workbook.createCellStyle();

        newCellStyle.setAlignment(style.getAlignment());
        newCellStyle.setBorderBottom(style.getBorderBottom());
        newCellStyle.setBorderLeft(style.getBorderLeft());
        newCellStyle.setBorderRight(style.getBorderRight());
        newCellStyle.setBorderTop(style.getBorderTop());
        newCellStyle.setBottomBorderColor(style.getBottomBorderColor());
        newCellStyle.setDataFormat(style.getDataFormat());
        newCellStyle.setFillBackgroundColor(style.getFillBackgroundColor());
        newCellStyle.setFillForegroundColor(style.getFillForegroundColor());
        newCellStyle.setFillPattern(style.getFillPattern());
        newCellStyle.setHidden(style.getHidden());
        newCellStyle.setIndention(style.getIndention());
        newCellStyle.setLeftBorderColor(style.getLeftBorderColor());
        newCellStyle.setLocked(style.getLocked());
        newCellStyle.setRightBorderColor(style.getRightBorderColor());
        newCellStyle.setRotation(style.getRotation());
        newCellStyle.setTopBorderColor(style.getTopBorderColor());
        newCellStyle.setVerticalAlignment(style.getVerticalAlignment());
        newCellStyle.setWrapText(style.getWrapText());

        final HSSFFont font = workbook.getFontAt(style.getFontIndex());
        newCellStyle.setFont(font);

        return newCellStyle;
    }
Example 2
Source File: XlsDataSetWriter.java    From Leo with Apache License 2.0 6 votes vote down vote up 
protected void setNumericCell(HSSFCell cell, BigDecimal value, HSSFWorkbook workbook)
{
    if(logger.isDebugEnabled())
        logger.debug("setNumericCell(cell={}, value={}, workbook={}) - start", 
            new Object[] {cell, value, workbook} );

    cell.setCellValue( ((BigDecimal)value).doubleValue() );

    HSSFDataFormat df = workbook.createDataFormat();
    int scale = ((BigDecimal)value).scale();
    short format;
    if(scale <= 0){
        format = df.getFormat("####");
    }
    else {
        String zeros = createZeros(((BigDecimal)value).scale());
        format = df.getFormat("####." + zeros);
    }
    if(logger.isDebugEnabled())
        logger.debug("Using format '{}' for value '{}'.", String.valueOf(format), value);
    
    HSSFCellStyle cellStyleNumber = workbook.createCellStyle();
    cellStyleNumber.setDataFormat(format);
    cell.setCellStyle(cellStyleNumber);
}
Example 3
Source File: XLSExportUtil.java    From DWSurvey with GNU Affero General Public License v3.0 5 votes vote down vote up 
/**
 * 设置单元格
 * 
 * @param index
 *            列号
 * @param value
 *            单元格填充值
 */
@SuppressWarnings("deprecation")
public void setCell(int index, Calendar value) {
	HSSFCell cell = this.row.createCell((short) index);
	cell.setCellValue(value.getTime());
	HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
	cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
	cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
}
Example 4
Source File: XLSExportUtil.java    From DWSurvey with GNU Affero General Public License v3.0 5 votes vote down vote up 
/**
 * 设置单元格
 * 
 * @param index
 *            列号
 * @param value
 *            单元格填充值
 */
public void setCell(int index, double value) {
	HSSFCell cell = this.row.createCell((short) index);
	cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
	cell.setCellValue(value);
	HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
	HSSFDataFormat format = workbook.createDataFormat();
	cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
	cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
}
Example 5
Source File: XslStyleHelper.java    From yarg with Apache License 2.0 5 votes vote down vote up 
public static void cloneStyleRelations(HSSFCellStyle source, HSSFCellStyle target) {
    //First we need to clone the extended format record
    getFormatFromStyle(target).cloneStyleFrom(getFormatFromStyle(source));
    //Handle matching things if we cross workbooks
    InternalWorkbook sourceWorkbook = getWorkbookFromStyle(source);
    InternalWorkbook targetWorkbook = getWorkbookFromStyle(target);
    if (targetWorkbook != sourceWorkbook) {
        //Then we need to clone the format string, and update the format record for this
        short fmt = targetWorkbook.getFormat(source.getDataFormatString(), true);
        target.setDataFormat(fmt);
    }
}
Example 6
Source File: XlsDataSetWriter.java    From Leo with Apache License 2.0 5 votes vote down vote up 
protected static HSSFCellStyle createDateCellStyle(HSSFWorkbook workbook) {
    HSSFDataFormat format = workbook.createDataFormat();
    short dateFormatCode = format.getFormat(DATE_FORMAT_AS_NUMBER_DBUNIT);
    HSSFCellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormatCode);
    return dateCellStyle;
}
Example 7
Source File: POIUtils.java    From ermaster-b with Apache License 2.0 5 votes vote down vote up 
public static HSSFCellStyle copyCellStyle(HSSFWorkbook workbook,
		HSSFCellStyle style) {

	HSSFCellStyle newCellStyle = workbook.createCellStyle();

	newCellStyle.setAlignment(style.getAlignment());
	newCellStyle.setBorderBottom(style.getBorderBottom());
	newCellStyle.setBorderLeft(style.getBorderLeft());
	newCellStyle.setBorderRight(style.getBorderRight());
	newCellStyle.setBorderTop(style.getBorderTop());
	newCellStyle.setBottomBorderColor(style.getBottomBorderColor());
	newCellStyle.setDataFormat(style.getDataFormat());
	newCellStyle.setFillBackgroundColor(style.getFillBackgroundColor());
	newCellStyle.setFillForegroundColor(style.getFillForegroundColor());
	newCellStyle.setFillPattern(style.getFillPattern());
	newCellStyle.setHidden(style.getHidden());
	newCellStyle.setIndention(style.getIndention());
	newCellStyle.setLeftBorderColor(style.getLeftBorderColor());
	newCellStyle.setLocked(style.getLocked());
	newCellStyle.setRightBorderColor(style.getRightBorderColor());
	newCellStyle.setRotation(style.getRotation());
	newCellStyle.setTopBorderColor(style.getTopBorderColor());
	newCellStyle.setVerticalAlignment(style.getVerticalAlignment());
	newCellStyle.setWrapText(style.getWrapText());

	HSSFFont font = workbook.getFontAt(style.getFontIndex());
	newCellStyle.setFont(font);

	return newCellStyle;
}
Example 8
Source File: ExportEventsImpl.java    From neoscada with Eclipse Public License 1.0 4 votes vote down vote up 
private IStatus storeExcel ( final File file, final List<Event> events, final List<Field> columns, final IProgressMonitor monitor ) throws IOException
{
    final HSSFWorkbook workbook = new HSSFWorkbook ();

    final HSSFDataFormat dateFormat = workbook.createDataFormat ();
    final HSSFCellStyle dateCellStyle = workbook.createCellStyle ();
    dateCellStyle.setDataFormat ( dateFormat.getFormat ( "YYYY-MM-DD hh:mm:ss.000" ) );

    try
    {
        monitor.beginTask ( Messages.ExportImpl_Progress_ExportingEvents, events.size () + 3 + columns.size () );

        try
        {
            monitor.subTask ( Messages.ExportImpl_Progress_CreateWorkbook );
            monitor.worked ( 1 );

            final HSSFSheet sheet = createSheet ( events, workbook, columns );
            monitor.worked ( 1 );

            monitor.setTaskName ( Messages.ExportImpl_Progress_ExportEvents );

            for ( int i = 0; i < events.size (); i++ )
            {
                final HSSFRow row = sheet.createRow ( i + 1 );

                final Event e = events.get ( i );
                for ( int j = 0; j < columns.size (); j++ )
                {
                    final Field field = columns.get ( j );
                    final ExcelCell cell = new ExcelCell ( row, j, dateCellStyle );
                    field.render ( e, cell );
                }
                monitor.worked ( 1 );
                if ( monitor.isCanceled () )
                {
                    return Status.CANCEL_STATUS;
                }
            }

            sheet.setRepeatingRows ( new CellRangeAddress ( 0, 1, -1, -1 ) );

            monitor.setTaskName ( "Auto sizing" );
            for ( int i = 0; i < columns.size (); i++ )
            {
                monitor.subTask ( String.format ( "Auto sizing column: %s", columns.get ( i ).getHeader () ) );
                sheet.autoSizeColumn ( i );
                monitor.worked ( 1 );

                if ( monitor.isCanceled () )
                {
                    return Status.CANCEL_STATUS;
                }
            }

        }
        finally
        {
            monitor.subTask ( Messages.ExportImpl_Progress_CloseFile );
            if ( workbook != null )
            {
                makeDocInfo ( workbook );

                final FileOutputStream stream = new FileOutputStream ( file );
                workbook.write ( stream );
                stream.close ();
            }
            monitor.worked ( 1 );
        }
    }
    finally
    {
        monitor.done ();
    }

    return Status.OK_STATUS;
}