Java Code Examples for org.apache.poi.ss.usermodel.CellStyle#setDataFormat()

/**
 * Sets the format properties of the given style based on the given map.
 *
 * @param style cell style
 * @param workbook parent workbook
 * @param properties map of format properties (String -> Object)
 * @see #getFormatProperties(CellStyle)
 */
private static void setFormatProperties(CellStyle style, Workbook workbook, Map<String, Object> properties) {
    style.setAlignment(getHorizontalAlignment(properties, ALIGNMENT));
    style.setVerticalAlignment(getVerticalAlignment(properties, VERTICAL_ALIGNMENT));
    style.setBorderBottom(getBorderStyle(properties, BORDER_BOTTOM));
    style.setBorderLeft(getBorderStyle(properties, BORDER_LEFT));
    style.setBorderRight(getBorderStyle(properties, BORDER_RIGHT));
    style.setBorderTop(getBorderStyle(properties, BORDER_TOP));
    style.setBottomBorderColor(getShort(properties, BOTTOM_BORDER_COLOR));
    style.setDataFormat(getShort(properties, DATA_FORMAT));
    style.setFillPattern(getFillPattern(properties, FILL_PATTERN));
    style.setFillForegroundColor(getShort(properties, FILL_FOREGROUND_COLOR));
    style.setFillBackgroundColor(getShort(properties, FILL_BACKGROUND_COLOR));
    style.setFont(workbook.getFontAt(getShort(properties, FONT)));
    style.setHidden(getBoolean(properties, HIDDEN));
    style.setIndention(getShort(properties, INDENTION));
    style.setLeftBorderColor(getShort(properties, LEFT_BORDER_COLOR));
    style.setLocked(getBoolean(properties, LOCKED));
    style.setRightBorderColor(getShort(properties, RIGHT_BORDER_COLOR));
    style.setRotation(getShort(properties, ROTATION));
    style.setTopBorderColor(getShort(properties, TOP_BORDER_COLOR));
    style.setWrapText(getBoolean(properties, WRAP_TEXT));
}
 

@Override
protected boolean putContentInCell(E currentobject, Cell cell, String context) {
	@SuppressWarnings("unchecked")
	DataObjectField<?, E> field = currentobject.payload.lookupSimpleFieldOnName(name);
	if (field == null)
		throw new RuntimeException("field " + name + " could not be looked-up on " + currentobject.getName());
	if (!(field instanceof DecimalDataObjectField))
		throw new RuntimeException("Expected field " + name
				+ " would be of type DecimalDataObjectField but in reality, it is " + field.getClass().toString());
	DecimalDataObjectField<
			?> decimalfield = (DecimalDataObjectField<?>) currentobject.payload.lookupSimpleFieldOnName(name);
	BigDecimal decimal = decimalfield.getValue();
	if (decimal != null) {
		decimal = decimal.multiply(this.multiplyatexport);
		cell.setCellValue(decimal.doubleValue());
	}
	if (this.decimalparser.getSpecialTreatment() == DecimalParser.SPECIAL_TREATMENT_MULTIPLY_BY_100) {
		logger.finest("special treatment for cell percentage");
		CellStyle percentagecellstyle = FlatFileExtractor.createBorderedStyle(cell.getSheet().getWorkbook());
		percentagecellstyle.setDataFormat(cell.getSheet().getWorkbook().createDataFormat().getFormat("0.0%"));
		cell.setCellStyle(percentagecellstyle);
		return true;
	}
	return false;

}
 

private CellStyle getDecimalNumberFormat(int j, Sheet sheet, CreationHelper createHelper, CellStyle dCellStyle) {

		if (decimalFormats.get(j) != null)
			return decimalFormats.get(j);
		String decimals = "";
		for (int i = 0; i < j; i++) {
			decimals += "0";
		}

		CellStyle cellStyleDoub = this.buildCellStyle(sheet); // cellStyleDoub is the default cell style for doubles
		cellStyleDoub.cloneStyleFrom(dCellStyle);
		DataFormat df = createHelper.createDataFormat();
		String format = "#,##0";
		if (decimals.length() > 0) {
			format += "." + decimals;
		}
		cellStyleDoub.setDataFormat(df.getFormat(format));

		decimalFormats.put(j, cellStyleDoub);
		return cellStyleDoub;
	}
 

/**
 * 日期样式 yyyy/MM/dd
 *
 * @return CellStyle
 */
public CellStyle getDate8Style() {

    if (buildInStyleMap.containsKey(DATE_8_STYLE_KEY)) {
        return buildInStyleMap.get(DATE_8_STYLE_KEY);
    }

    CellStyle date8Style = workbook.createCellStyle();//年月日样式
    if (!buildInFormatMap.containsKey(DATA_FORMAT_KEY)) {
        DataFormat dataFormat = workbook.createDataFormat();
        buildInFormatMap.put(DATA_FORMAT_KEY, dataFormat);
    }
    date8Style.setDataFormat(buildInFormatMap.get(DATA_FORMAT_KEY).getFormat("yyyy/MM/dd"));
    this.setCommonStyle(date8Style);
    buildInStyleMap.put(DATE_8_STYLE_KEY, date8Style);
    return date8Style;
}
 

@Override
public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
	CellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	style.setFillForegroundColor((short) 41); // 填充的背景颜色
	style.setFillPattern(CellStyle.SOLID_FOREGROUND); // 填充图案
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setDataFormat(STRING_FORMAT);
	if (isWarp) {
		style.setWrapText(true);
	}
	return style;
}
 

/**
 * 导出
 * 
 * @param templatePath
 *            模板地址，自定义比较好看 classPath下的路径
 * @param colums
 *            导出的数据列 反射用
 * @param clazz
 * @param data
 * @param out
 * @param startRow
 *            从几行开始写数据
 * @return
 * @throws IOException
 * @throws IllegalAccessException
 * @throws IllegalArgumentException
 */
public static <T> void doExport(String templatePath, String[] columns, Class<T> clazz, List<T> data,
		OutputStream out, int startRow) throws IOException, IllegalArgumentException, IllegalAccessException {
	ClassPathResource classPathResource = new ClassPathResource(templatePath);
	InputStream inputStream = classPathResource.getInputStream();
	Workbook workbook = new XSSFWorkbook(inputStream);
	Sheet sheet = workbook.getSheetAt(0);
	int size = data.size() + startRow;
	int cellNum = sheet.getRow(0).getPhysicalNumberOfCells();
	for (int i = startRow; i < size; i++) {
		Row row = sheet.createRow(i);
		for (int j = 0; j < cellNum; j++) {
			Cell cell = row.createCell(j);
			Field field = ReflectionUtils.findField(clazz, columns[j]);
			if (null == field) {
				throw new ServiceException(columns[j] + " 配置错误");
			}
			ReflectionUtils.makeAccessible(field);
			Object obj = data.get(i - startRow);
			if (field.getType().getSimpleName().equalsIgnoreCase("double")) {
				cell.setCellValue((Double) field.get(obj));
			} else if (field.getType().getSimpleName().equalsIgnoreCase("date")) {
				CellStyle style = workbook.createCellStyle();
				style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
				cell.setCellStyle(style);
				cell.setCellValue((Date) field.get(obj));
			} else {
				cell.setCellValue(field.get(obj) == null ? "" : "" + field.get(obj));
			}
		}
	}
	workbook.write(out);
	workbook.close();
}
 

@Override
public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
	CellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	style.setFillForegroundColor((short) 41); // 填充的背景颜色
	style.setFillPattern(CellStyle.SOLID_FOREGROUND); // 填充图案
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setDataFormat(STRING_FORMAT);
	if (isWarp) {
		style.setWrapText(true);
	}
	return style;
}
 

@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
    CellStyle style = workbook.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setDataFormat(STRING_FORMAT);
    if (isWarp) {
        style.setWrapText(true);
    }
    return style;
}
 

@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderLeft((short) 1); // 左边框
    style.setBorderRight((short) 1); // 右边框
    style.setBorderBottom((short) 1);
    style.setBorderTop((short) 1);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setDataFormat(STRING_FORMAT);
    if (isWarp) {
        style.setWrapText(true);
    }
    return style;
}
 

@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
	CellStyle style = workbook.createCellStyle();
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setDataFormat(STRING_FORMAT);
	if (isWarp) {
		style.setWrapText(true);
	}
	return style;
}
 

/**
 * 导出Excel的例子
 * 因为类上面注解是@Controller，此方法需要@ResponseBody注解；如果类是RestController，则不需要ResponseBody
 * @return
 * @throws Exception
 */
@ResponseBody
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() throws Exception{
    logger.trace("exportExcel");
    HttpHeaders responseHeaders = new HttpHeaders();
    responseHeaders.setContentDispositionFormData("attachment",new String("导出的文件名.xlsx".getBytes(), "ISO8859-1"));
    responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);

    //中文文件名需要用iso8859-1编码
    InputStream templateIs = this.getClass().getResourceAsStream("/excel-templates/templet.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(templateIs);
    XSSFSheet sheet = workbook.getSheetAt(0);

    List<SampleItem> list = getDataList();

    CellStyle cellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd"));

    for (int i=0; i<list.size(); i++) {
        SampleItem si = list.get(i);

        XSSFRow row = sheet.createRow(i + 1);

        Cell cell1 = row.createCell(0);
        cell1.setCellValue(si.getDate());
        cell1.setCellStyle(cellStyle);

        Cell cell2 = row.createCell(1);
        cell2.setCellValue(si.getName());

        Cell cell3 = row.createCell(2);
        cell3.setCellValue(si.getScore());
    }

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    workbook.write(bos);
    workbook.close();
    return new ResponseEntity<byte[]>(bos.toByteArray(), responseHeaders, HttpStatus.OK);
}
 

@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderLeft((short) 1); // 左边框
    style.setBorderRight((short) 1); // 右边框
    style.setBorderBottom((short) 1);
    style.setBorderTop((short) 1);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setDataFormat(STRING_FORMAT);
    if (isWarp) {
        style.setWrapText(true);
    }
    return style;
}
 

@Override
public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
    CellStyle style = workbook.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setDataFormat(STRING_FORMAT);
    if (isWarp) {
        style.setWrapText(true);
    }
    return style;
}
 

@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
	CellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setDataFormat(STRING_FORMAT);
	if (isWarp) {
		style.setWrapText(true);
	}
	return style;
}
 

/**
 * Set specified cell format
 *
 * @param excelFieldFormat the specified format
 * @param cell             the cell to set up format
 */
private void setDataFormat( String excelFieldFormat, Cell cell ) {
  if ( log.isDebug() ) {
    logDebug( BaseMessages.getString( PKG, "ExcelWriterTransform.Log.SetDataFormat", excelFieldFormat, CellReference.convertNumToColString( cell.getColumnIndex() ), cell.getRowIndex() ) );
  }

  DataFormat format = data.wb.createDataFormat();
  short formatIndex = format.getFormat( excelFieldFormat );
  CellStyle style = data.wb.createCellStyle();
  style.cloneStyleFrom( cell.getCellStyle() );
  style.setDataFormat( formatIndex );
  cell.setCellStyle( style );
}
 

@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
	CellStyle style = workbook.createCellStyle();
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setDataFormat(STRING_FORMAT);
	if (isWarp) {
		style.setWrapText(true);
	}
	return style;
}
 

private Map<String, CellStyle> createHSSFCellStyles(Workbook wb, int[] contextBgColor, int[] contextFontColor, int contextFontSize, int contextFontAlign, int[] headerBgColor,
		int[] headerFontColor, int headerFontSize, int headerAlign) {
	Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

	HSSFWorkbook workbook = (HSSFWorkbook) wb;
	HSSFPalette palette = workbook.getCustomPalette();
	palette.setColorAtIndex((short) 11, (byte) contextBgColor[0], (byte) contextBgColor[1], (byte) contextBgColor[2]);
	palette.setColorAtIndex((short) 12, (byte) contextFontColor[0], (byte) contextFontColor[1], (byte) contextFontColor[2]);
	palette.setColorAtIndex((short) 13, (byte) headerBgColor[0], (byte) headerBgColor[1], (byte) headerBgColor[2]);
	palette.setColorAtIndex((short) 14, (byte) headerFontColor[0], (byte) headerFontColor[1], (byte) headerFontColor[2]);

	HSSFFont headerFont = workbook.createFont();
	headerFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	headerFont.setFontName("宋体");
	headerFont.setColor((short) 14);
	headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	headerFont.setFontHeightInPoints((short) headerFontSize);
	CellStyle headerStyle = this.createBorderCellStyle(workbook, true);

	headerStyle.setFont(headerFont);
	headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	headerStyle.setFillForegroundColor((short) 13);
	this.setCellStyleAligment(headerStyle, headerAlign);
	headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	styles.put(GridStyleType.headerStyle.name(), headerStyle);

	HSSFFont dataFont = workbook.createFont();
	dataFont.setColor((short) 12);
	dataFont.setFontHeightInPoints((short) contextFontSize);
	dataFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	dataFont.setFontName("宋体");

	CellStyle dataAlignLeftStyle = this.createBorderCellStyle(workbook, true);
	dataAlignLeftStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignLeftStyle.setFillForegroundColor((short) 11);
	dataAlignLeftStyle.setFont(dataFont);
	dataAlignLeftStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignLeftStyle.setWrapText(true);
	dataAlignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);
	styles.put(GridStyleType.dataAlignLeftStyle.name(), dataAlignLeftStyle);

	CellStyle dataAlignCenterStyle = this.createBorderCellStyle(workbook, true);
	dataAlignCenterStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignCenterStyle.setFillForegroundColor((short) 11);
	dataAlignCenterStyle.setFont(dataFont);
	dataAlignCenterStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignCenterStyle.setWrapText(true);
	dataAlignCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
	styles.put(GridStyleType.dataAlignCenterStyle.name(), dataAlignCenterStyle);

	CellStyle dataAlignRightStyle = this.createBorderCellStyle(workbook, true);
	dataAlignRightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignRightStyle.setFillForegroundColor((short) 11);
	dataAlignRightStyle.setFont(dataFont);
	dataAlignRightStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignRightStyle.setWrapText(true);
	dataAlignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);
	styles.put(GridStyleType.dataAlignRightStyle.name(), dataAlignRightStyle);

	CellStyle dateStyle = this.createBorderCellStyle(workbook, true);
	CreationHelper helper = workbook.getCreationHelper();
	dateStyle.setDataFormat(helper.createDataFormat().getFormat("m/d/yy h:mm"));
	dateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dateStyle.setFillForegroundColor((short) 11);
	dateStyle.setFont(dataFont);
	dateStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	this.setCellStyleAligment(dateStyle, contextFontAlign);
	styles.put(GridStyleType.dateStyle.name(), dateStyle);

	return styles;
}
 

/**
 *
 * @param sheet
 *            ...
 * @param workbook
 *            ...
 * @param createHelper
 *            ...
 * @param beginRowHeaderData
 *            header's vertical offset. Expressed in number of rows
 * @param beginColumnHeaderData
 *            header's horizontal offset. Expressed in number of columns
 *
 * @return ...
 */
private CellStyle[] fillSheetHeader(Sheet sheet, Workbook workbook, CreationHelper createHelper, int beginRowHeaderData, int beginColumnHeaderData) {

	CellStyle[] cellTypes;

	logger.trace("IN");

	try {

		IMetaData dataStoreMetaData = dataStore.getMetaData();
		int colnumCount = dataStoreMetaData.getFieldCount();

		Row headerRow = sheet.getRow(beginRowHeaderData);
		CellStyle headerCellStyle = buildHeaderCellStyle(sheet);

		cellTypes = new CellStyle[colnumCount];
		for (int j = 0; j < colnumCount; j++) {
			Cell cell = headerRow.createCell(j + beginColumnHeaderData);
			cell.setCellType(getCellTypeString());
			String fieldName = dataStoreMetaData.getFieldAlias(j);
			IFieldMetaData fieldMetaData = dataStoreMetaData.getFieldMeta(j);
			String format = (String) fieldMetaData.getProperty("format");
			String alias = fieldMetaData.getAlias();
			String scaleFactorHeader = (String) fieldMetaData.getProperty(ADDITIONAL_DATA_FIELDS_OPTIONS_SCALE_FACTOR);

			String header;
			if (extractedFields != null && j < extractedFields.size() && extractedFields.get(j) != null) {
				Field field = (Field) extractedFields.get(j);
				fieldName = field.getAlias();
				if (field.getPattern() != null) {
					format = field.getPattern();
				}
			}
			CellStyle aCellStyle = this.buildCellStyle(sheet);
			if (format != null) {
				short formatInt = this.getBuiltinFormat(format);
				aCellStyle.setDataFormat(formatInt);
				cellTypes[j] = aCellStyle;
			}

			if (alias != null && !alias.equals("")) {
				header = alias;
			} else {
				header = fieldName;
			}

			header = MeasureScaleFactorOption.getScaledName(header, scaleFactorHeader, locale);
			cell.setCellValue(createHelper.createRichTextString(header));

			cell.setCellStyle(headerCellStyle);

		}

	} catch (Throwable t) {
		throw new SpagoBIRuntimeException("An unexpected error occured while filling sheet header", t);
	} finally {
		logger.trace("OUT");
	}

	return cellTypes;
}
 

private Map<String, CellStyle> createStyles(Workbook wb){
    Map<String, CellStyle> styles = new HashMap<>();
    CellStyle style;

    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short)12);
    titleFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(titleFont);
    style.setWrapText(false);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
    styles.put("header", style);

    Font cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short)10);
    cellFont.setBold(true);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat( BuiltinFormats.getBuiltinFormat( 3 )));
    styles.put("integer_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4)));
    styles.put("decimal_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat( (short) BuiltinFormats.getBuiltinFormat("text") );
    styles.put(TEXT_CELL, style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat( DateFormatConverter.convert( Locale.getDefault(), dateFormatPattern )));
    styles.put("date_cell", style);
    return styles;
}
 

/**
 * creates a style to show dates in the provided format
 * 
 * @param wb               workbook
 * @param simpledateformat format of the date
 * @return the style to use
 */
public static CellStyle createDateStyle(Workbook wb, String simpledateformat) {
	CellStyle style = createBorderedStyle(wb);
	CreationHelper createHelper = wb.getCreationHelper();
	style.setDataFormat(createHelper.createDataFormat().getFormat(simpledateformat));
	return style;
}