org.apache.poi.hssf.usermodel.HSSFDataFormat Java Examples

@Override
public void completeCell(Sheet sheet, Row row, Cell cell, ExcelField excelField, Field field, int index,
                         int colIndex, boolean isHead) {
    if (isHead) {
        if (index == 0) {
            CellStyle cellStyle = this.workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setWrapText(true);
            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(excelField.format()));
            sheet.setDefaultColumnStyle(colIndex, cellStyle);
            sheet.setColumnWidth(colIndex, excelField.width());
        }
        this.setHeadStyle(row, cell, excelField, field, index, colIndex);
        return;
    }
    this.setBodyStyle(row, cell, excelField, field, index, colIndex);
}
 

/**
 * Returns the format string, eg $##.##, for the given number format index.
 * 
 * @param formatIndex the format index
 * 
 * @return the format string
 */
public String getFormatString(int formatIndex) {
	String format = null;
	if (formatIndex >= HSSFDataFormat.getNumberOfBuiltinBuiltinFormats()) {
		FormatRecord tfr = _customFormatRecords.get(Integer.valueOf(formatIndex));
		if (tfr == null) {
			logger.log( POILogger.ERROR, "Requested format at index " + formatIndex
					+ ", but it wasn't found");
		} else {
			format = tfr.getFormatString();
		}
	} else {
		format = HSSFDataFormat.getBuiltinFormat((short) formatIndex);
	}
	return format;
}
 

private static final void writeCell(final Cell cell, final Object value) {
    if (value instanceof Number) {
        cell.setCellType(CellType.NUMERIC);
        cell.setCellValue(Number.class.cast(value).doubleValue());
    } else if (value instanceof Date) {
        final CellStyle style = cell.getSheet().getWorkbook().createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        cell.setCellStyle(style);
        cell.setCellType(CellType.NUMERIC);
        cell.setCellValue(Date.class.cast(value));
    } else if (value instanceof Boolean) {
        cell.setCellType(CellType.BOOLEAN);
    } else {
        cell.setCellType(CellType.STRING);
        cell.setCellValue(value != null ? String.valueOf(value) : "");
    }
}
 

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);
}
 

/**
 * Instantiates a new Poi cell style.
 *
 * @param workBook the work book
 */
public PoiCellStyle(PoiWorkBook workBook) {

    this.stringCs = this.getDefaultExcelCellStyle(workBook);

    this.numberCs = this.getDefaultExcelCellStyle(workBook);
    this.numberCs.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
    this.numberCs.setAlignment(HorizontalAlignment.RIGHT);

    this.dateCs = this.getDefaultExcelCellStyle(workBook);
    this.dateCs.setDataFormat(DATE_FORMAT_VALUE);
}
 

/**
 * 设置单元格
 * 
 * @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日期的显示格式
}
 

/**
 * 设置单元格
 * 
 * @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浮点数的显示格式
}
 

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;
}
 

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;
}
 

protected short getBuiltinFormat(String formatStr) {
	short format = HSSFDataFormat.getBuiltinFormat(formatStr);
	return format;
}
 

protected CellStyle buildCellStyle(CellContextData cellContext){
		FieldModel field = cellContext.getFieldModel();
		
		String styleString = getStyle(field);
		String fontString = getFont(field);
		if(ExcelUtils.isBlank(field.getDataFormat()) && ExcelUtils.isBlank(styleString) && ExcelUtils.isBlank(fontString)){
			return null;
		}
		
		if(!ExcelUtils.isBlank(styleString) && styleString.startsWith("#")){
			styleString = (String)cellContext.parseValue(styleString);
		}

		if(!ExcelUtils.isBlank(field.getDataFormat())){
			styleString += ";dataFormat:"+HSSFDataFormat.getBuiltinFormat(field.getDataFormat());
		}
		
		if(!ExcelUtils.isBlank(fontString) && fontString.startsWith("#")){
			fontString = (String)cellContext.parseValue(fontString);
		}
		
		String key = styleString + fontString;
		CellStyle cstyle = this.styleCache.get(key);
		if(cstyle!=null){
//			System.out.println("get style from cache");
			return cstyle;
		}
		
		cstyle = this.generator.getWorkbook().createCellStyle();
		BeanWrapper bw = ExcelUtils.newBeanWrapper(cstyle);
		
		Map<String, String> styleMap = this.generator.getPropertyStringParser().parseStyle(styleString);
		try {
			for(Entry<String, String> entry : styleMap.entrySet()){
				/*if(isStaticField(CellStyle.class, entry.getValue())){
					Object styleValue = ReflectUtils.getStaticFieldValue(CellStyle.class, getStaticField(entry.getValue()));
					ReflectUtils.setProperty(cstyle, entry.getKey(), styleValue);
				}else{
					bw.setPropertyValue(entry.getKey(), entry.getValue());
				}*/
				bw.setPropertyValue(entry.getKey(), getStyleValue(entry.getValue()));
			}
		} catch (Exception e) {
			throw ExcelUtils.wrapAsUnCheckedException(cellContext.getLocation()+" buildCellStyle error: " + e.getMessage(), e);
		}
		
		Font font = buildCellFont(cellContext, fontString);
		if(font!=null){
			cstyle.setFont(font);
		}
		
		this.styleCache.putIfAbsent(key, cstyle);
		
		return cstyle;
	}