/**
* 测试单元格样式
*
* @author David
* @param wb
* @param cell
* @param td
*/
private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
Attribute typeAttr = td.getAttribute("type");
String type = typeAttr.getValue();
HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle cellStyle = wb.createCellStyle();
if ("NUMERIC".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Attribute formatAttr = td.getAttribute("format");
String formatValue = formatAttr.getValue();
formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00";
cellStyle.setDataFormat(format.getFormat(formatValue));
} else if ("STRING".equalsIgnoreCase(type)) {
cell.setCellValue("");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellStyle.setDataFormat(format.getFormat("@"));
} else if ("DATE".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
} else if ("ENUM".equalsIgnoreCase(type)) {
CellRangeAddressList regions =
new CellRangeAddressList(
cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
Attribute enumAttr = td.getAttribute("format");
String enumValue = enumAttr.getValue();
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
// 数据有效性对象
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
wb.getSheetAt(0).addValidationData(dataValidation);
}
cell.setCellStyle(cellStyle);
}
public void createExcelFile(String filename) throws Exception {
out = new FileOutputStream(filename);
wb = new HSSFWorkbook();
ws = this.wb.createSheet();
cs1 = this.wb.createCellStyle();
cs2 = this.wb.createCellStyle();
cs3 = this.wb.createCellStyle();
df = this.wb.createDataFormat();
f1 = this.wb.createFont();
f2 = this.wb.createFont();
f1.setFontHeightInPoints((short) 10);
f1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setFontHeightInPoints((short) 13);
f2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setColor(HSSFFont.COLOR_RED);
cs1.setFont(f1);
cs1.setDataFormat(df.getFormat("text"));
cs2.setFont(f2);
cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
cs3.setFont(f1);
cs3.setDataFormat(df.getFormat("#,##0.0"));
wb.setSheetName(0, "QryDetail", HSSFWorkbook.ENCODING_UTF_16);
}
private void writeHeaderRow(Entity entity, HSSFSheet sheet) {
HSSFRow headerRow = sheet.createRow(0);
int colnum = 0;
for (Map.Entry<String, Object> component : getComponents(entity)) {
String componentName = component.getKey();
headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName));
ComponentDescriptor cd = entity.descriptor().getComponent(componentName);
PrimitiveType primitiveType;
if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor)
primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType();
else
throw new UnsupportedOperationException(
"Can only export simple type attributes, "
+ "failed to export "
+ entity.type()
+ '.'
+ cd.getName());
Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class);
String formatString = null;
if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern();
else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern();
else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern();
else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern();
else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern();
if (formatString != null) {
HSSFDataFormat dataFormat = workbook.createDataFormat();
CellStyle columnStyle = workbook.createCellStyle();
columnStyle.setDataFormat(dataFormat.getFormat(formatString));
sheet.setDefaultColumnStyle(colnum, columnStyle);
}
colnum++;
}
}
private void createWorkbook() {
this.workbook = new HSSFWorkbook();
this.dateCellStyle = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
short dateFormat = format.getFormat(getDatePattern());
this.dateCellStyle.setDataFormat(dateFormat);
}
/**
* Inserts a single bulleted item into a cell.
*
* @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
* @param listItem An instance of the String class encapsulating the items text.
* @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
* cell into which the list item will be written.
*/
public void bulletedItemInCell(HSSFWorkbook workbook, String listItem, HSSFCell cell) {
// A format String must be built to ensure that the contents of the
// cell appear as a bulleted item.
HSSFDataFormat format = workbook.createDataFormat();
String formatString = InCellLists.BULLET_CHARACTER + " @";
int formatIndex = format.getFormat(formatString);
// Construct an HSSFCellStyle and set it's data formt to use the
// object created above.
HSSFCellStyle bulletStyle = workbook.createCellStyle();
bulletStyle.setDataFormat((short) formatIndex);
// Set the cells contents and style.
cell.setCellValue(new HSSFRichTextString(listItem));
cell.setCellStyle(bulletStyle);
}
public ExcelSupport() {
super();
workbook = new HSSFWorkbook();
numericCellStyle = workbook.createCellStyle();
numericData = workbook.createDataFormat();
numericCellStyle.setDataFormat(numericData.getFormat("#,##0.00"));
percentageCellStyle = workbook.createCellStyle();
percentageCellStyle.setDataFormat((short) 4);
decimalCellStyle = workbook.createCellStyle();
decimalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.0#####"));
multiLineTextStyle = workbook.createCellStyle();
multiLineTextStyle.setWrapText(true);
multiLineTextStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
genericStyle = workbook.createCellStyle();
genericStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
dollarStyle = workbook.createCellStyle();
dollarStyle.setDataFormat((short) 8);
headlineStyle = workbook.createCellStyle();
HSSFFont headlineFont = workbook.createFont();
// set font 1 to 12 point type
headlineFont.setFontHeightInPoints((short) 24);
// make it bold
// arial is the default font
headlineFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headlineStyle.setFont(headlineFont);
boldFont = workbook.createFont();
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
boldStyle = workbook.createCellStyle();
boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
boldStyle.setFont(boldFont);
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
// 取得模型数据
SqlRowSet table = (SqlRowSet) model.get("table");
String title = model.get("title").toString();
// 创建工作表和标题
HSSFSheet sheet = workbook.createSheet(title); // 创建工作区
HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象
HSSFFont title_font = workbook.createFont(); // 创建标题的字体
title_font.setFontHeightInPoints((short) 8);
title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL);
title_font.setColor((short) (HSSFFont.COLOR_RED));
HSSFCellStyle title_style = workbook.createCellStyle(); // 创建样式
title_style.setFont(title_font);
HSSFCell cell_title = row_title.createCell(1); // 创建单元格引用对象
cell_title.setCellStyle(title_style);
cell_title.setCellValue(title);
// 创建数据表头
String titles[] = {"学生姓名", "性别", "年龄", "身份证号", "出生日期", "政治面貌", "家庭电话", "家庭地址", "健康状况"};
HSSFRow row = sheet.createRow((short) 1);
HSSFCellStyle items_style = workbook.createCellStyle();
items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);
HSSFFont celltbnamefont = workbook.createFont();
celltbnamefont.setFontHeightInPoints((short) 10);
celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
items_style.setFont(celltbnamefont);
items_style.setWrapText(true);
for (int i = 0; i < titles.length; i++) {
HSSFCell cell = row.createCell(i);
if (i == 3 || i == 6 || i == 2) {
sheet.setColumnWidth(i, 5335);
} else {
sheet.setColumnWidth(i, 3335);
}
cell.setCellValue(titles[i]);
cell.setCellStyle(items_style);
}
HSSFCellStyle datestyle = workbook.createCellStyle();
HSSFDataFormat df = workbook.createDataFormat();
datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));
int i = 0;
while (table.next()) {
HSSFRow dataRow = sheet.createRow((short) (i + 2));
for (int j = 0; j < 9; j++) {
HSSFCell cell = dataRow.createCell(j);
String data = table.getString(j + 2);
cell.setCellStyle(datestyle);
cell.setCellValue(data);
}
i++;
}
}
protected Map<Short, HSSFCellStyle> applyStyles(final Report report, final HSSFWorkbook wb) {
final StylePalette palette = report.getPalette();
final Map<Short, HSSFCellStyle> styles = new HashMap<Short, HSSFCellStyle>();
if (report.getTemplate() != null) {
for (final short styleIndex : palette.getStyles().keySet()) {
final HSSFCellStyle style = wb.getCellStyleAt(styleIndex);
if (style == null)
throw new RuntimeException(
"Inconsistent report template. Style not found: " + styleIndex);
styles.put(styleIndex, style);
}
return styles;
}
if (palette.getColors().size() > PaletteRecord.STANDARD_PALETTE_SIZE)
throw new RuntimeException("too many colors on report");
final HSSFPalette pal = wb.getCustomPalette();
for (final Color color : palette.getColors().values()) {
pal.setColorAtIndex(color.getId(), color.getRed(), color.getGreen(), color.getBlue());
}
final Map<Short, HSSFFont> fonts = new HashMap<Short, HSSFFont>();
final HSSFDataFormat formatter = wb.createDataFormat();
for (final Font font : palette.getFonts().values()) {
final HSSFFont f = POIUtils.ensureFontExists(wb, font);
fonts.put(font.getId(), f);
}
for (final CellStyle style : palette.getStyles().values()) {
final short bbc =
style.getBottomBorderColor() != null ? style.getBottomBorderColor().getId() : 0;
final short fbc =
style.getFillBackgroundColor() != null ? style.getFillBackgroundColor().getId() : 0;
final short ffc =
style.getFillForegroundColor() != null ? style.getFillForegroundColor().getId() : 0;
final short lbc = style.getLeftBorderColor() != null ? style.getLeftBorderColor().getId() : 0;
final short rbc =
style.getRightBorderColor() != null ? style.getRightBorderColor().getId() : 0;
final short tbc = style.getTopBorderColor() != null ? style.getTopBorderColor().getId() : 0;
final HSSFCellStyle s = wb.createCellStyle();
s.setAlignment(style.getAlignment());
s.setBorderBottom(style.getBorderBottom());
s.setBorderLeft(style.getBorderLeft());
s.setBorderRight(style.getBorderRight());
s.setBorderTop(style.getBorderTop());
s.setBottomBorderColor(bbc);
s.setDataFormat(formatter.getFormat(style.getDataFormat()));
s.setFillBackgroundColor(fbc);
s.setFillForegroundColor(ffc);
s.setFillPattern(style.getFillPattern());
s.setHidden(style.isHidden());
s.setIndention(style.getIndention());
s.setLeftBorderColor(lbc);
s.setLocked(style.isLocked());
s.setRightBorderColor(rbc);
s.setRotation(style.getRotation());
s.setTopBorderColor(tbc);
s.setVerticalAlignment(style.getVerticalAlignment());
s.setWrapText(style.isWrapText());
s.setFont(fonts.get(style.getFont().getId()));
styles.put(style.getId(), s);
}
return styles;
}
private Map initStyles(HSSFWorkbook wb, short fontHeight) {
Map result = new HashMap();
HSSFCellStyle titleStyle = wb.createCellStyle();
HSSFCellStyle textStyle = wb.createCellStyle();
HSSFCellStyle boldStyle = wb.createCellStyle();
HSSFCellStyle numericStyle = wb.createCellStyle();
HSSFCellStyle numericStyleBold = wb.createCellStyle();
HSSFCellStyle moneyStyle = wb.createCellStyle();
HSSFCellStyle moneyStyleBold = wb.createCellStyle();
HSSFCellStyle percentStyle = wb.createCellStyle();
HSSFCellStyle percentStyleBold = wb.createCellStyle();
result.put("titleStyle", titleStyle);
result.put("textStyle", textStyle);
result.put("boldStyle", boldStyle);
result.put("numericStyle", numericStyle);
result.put("numericStyleBold", numericStyleBold);
result.put("moneyStyle", moneyStyle);
result.put("moneyStyleBold", moneyStyleBold);
result.put("percentStyle", percentStyle);
result.put("percentStyleBold", percentStyleBold);
HSSFDataFormat format = wb.createDataFormat();
// Global fonts
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setColor(HSSFColor.BLACK.index);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setFontHeightInPoints(fontHeight);
HSSFFont fontBold = wb.createFont();
fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontBold.setColor(HSSFColor.BLACK.index);
fontBold.setFontName(HSSFFont.FONT_ARIAL);
fontBold.setFontHeightInPoints(fontHeight);
// Money Style
moneyStyle.setFont(font);
moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyle.setDataFormat(format.getFormat(moneyFormat));
// Money Style Bold
moneyStyleBold.setFont(fontBold);
moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));
// Percent Style
percentStyle.setFont(font);
percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyle.setDataFormat(format.getFormat(percentFormat));
// Percent Style Bold
percentStyleBold.setFont(fontBold);
percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyleBold.setDataFormat(format.getFormat(percentFormat));
// Standard Numeric Style
numericStyle.setFont(font);
numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Standard Numeric Style Bold
numericStyleBold.setFont(fontBold);
numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Title Style
titleStyle.setFont(font);
titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Standard Text Style
textStyle.setFont(font);
textStyle.setWrapText(true);
// Standard Text Style
boldStyle.setFont(fontBold);
boldStyle.setWrapText(true);
return result;
}
