/** * 测试单元格样式 * * @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); }
public void initStyles() { // HSSFDataFormat format = workbook.createDataFormat(); redStyle = workbook.createCellStyle(); setBorder(redStyle, 1); redStyle.setFillForegroundColor(HSSFColor.RED.index); yellowStyle = workbook.createCellStyle(); setBorder(yellowStyle, 1); yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index); blueStyle = workbook.createCellStyle(); setBorder(blueStyle, 1); blueStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index); whiteStyle = workbook.createCellStyle(); setBorder(whiteStyle, 1); whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index); normalStyle = workbook.createCellStyle(); setBorder(normalStyle, 0); // normalStyle.setFillForegroundColor(HSSFColor.WHITE.index); normalPStyle = workbook.createCellStyle(); setBorder(normalPStyle, 0); normalPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%")); // normalPStyle.setFillForegroundColor(HSSFColor.WHITE.index); headerStyle = workbook.createCellStyle(); setBorder(headerStyle, 0); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(font); redPStyle = workbook.createCellStyle(); setBorder(redPStyle, 1); redPStyle.setFillForegroundColor(HSSFColor.RED.index); // redPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); redPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%")); yellowPStyle = workbook.createCellStyle(); setBorder(yellowPStyle, 1); yellowPStyle.setFillForegroundColor(HSSFColor.YELLOW.index); // yellowPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); yellowPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%")); _fonts = new HashMap<String, HSSFFont>(2); HSSFFont t13y_red = workbook.createFont(); t13y_red.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); t13y_red.setColor(HSSFFont.COLOR_RED); _fonts.put("t13yr", t13y_red); HSSFFont t13y_blue = workbook.createFont(); t13y_blue.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); t13y_blue.setColor(HSSFColor.BLUE_GREY.index); _fonts.put("t13yb", t13y_blue); }
/** * 生成文件 * * @throws IOException */ public byte[] run() throws IOException { // 初始化参数 initParameter(); // 实例化decorator initDecorator(); // 初始化 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); sheet.setDefaultColumnWidth((short) 20); // 设置默认宽度 try { wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16); } catch (Exception ex) { logger.error("生成表单时出错:怀疑是表单名导致。说明:poi对中文的支持不好,可能会有问题(仅表单名)如中文?--中文字加英文标点的情况"); } baseCellStyle = wb.createCellStyle(); baseCellStyle.setAlignment(align); baseCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); dateCellStyle = wb.createCellStyle(); dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat)); // 创建标题行 HSSFRow row = sheet.createRow((short) 0); // 创建标题 for (int i = 0; i < nameList.size(); i++) { HSSFCell cell = row.createCell((short) i); writeCell(cell, (String) nameList.get(i)); } // 创建数据 for (int i = 0; i < dataList.size(); i++) { row = sheet.createRow((short) i + 1); for (int j = 0; j < fieldList.size(); j++) { HSSFCell cell = row.createCell((short) j); Object value = getValue(dataList.get(i), (String) fieldList.get(j)); writeCell(cell, value); } } // 写入文件 // FileOutputStream fileOut = new FileOutputStream(fileName); ByteArrayOutputStream out = new ByteArrayOutputStream(); wb.write(out); return out.toByteArray(); // fileOut.close(); }
private CoverageResults() { fileName = "Coverage-results.xls"; workbook = new HSSFWorkbook(); allElementsSheet = workbook.createSheet("All Elements"); packagesSheet = workbook.createSheet("Packages"); boundClassesSheet = workbook.createSheet("Bound Classes"); allClassesSheet = workbook.createSheet("All Classes"); boundMethodsSheet = workbook.createSheet("Bound Methods"); allMethodsSheet = workbook.createSheet("All Methods"); normalFont = workbook.createFont(); normalFont.setFontName(HSSFFont.FONT_ARIAL); normalFont.setFontHeightInPoints((short) 10); boldFont = workbook.createFont(); boldFont.setFontName(HSSFFont.FONT_ARIAL); boldFont.setFontHeightInPoints((short) 10); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); centerAlignmentCellStyle = workbook.createCellStyle(); centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); leftAlignmentCellStyle = workbook.createCellStyle(); leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); rightAlignmentCellStyle = workbook.createCellStyle(); rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); dataFormatCellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); }
public void applyTo(HSSFCellStyle style, HSSFWorkbook workbook) { style.setAlignment(this.alignment); style.setBorderBottom(this.borderBottom); style.setBorderLeft(this.borderLeft); style.setBorderRight(this.borderRight); style.setBorderTop(this.borderTop); style.setBottomBorderColor(this.bottomBorderColor); style.setDataFormat(this.dataFormat); style.setFillPattern(this.fillPattern); style.setFillForegroundColor(this.fillForegroundColor); style.setFillBackgroundColor(this.fillBackgroundColor); style.setFont(workbook.getFontAt(this.fontIndex)); style.setHidden(this.hidden); style.setIndention(this.indention); style.setLeftBorderColor(this.leftBorderColor); style.setLocked(this.locked); style.setRightBorderColor(this.rightBorderColor); style.setRotation(this.rotation); style.setTopBorderColor(this.topBorderColor); style.setVerticalAlignment(this.verticalAlignment); style.setWrapText(this.wrapText); }
/** * 摘要: @说明:Excel样式 @创建:作者:yxy 创建时间:2011-8-17 * * @param workBook * @param cellStyle 样式模型 * @return @修改历史: [序号](yxy 2011-8-17)<修改说明> */ public static HSSFCellStyle getNewStyle(HSSFWorkbook workBook, CellStyle cellStyle) { HSSFCellStyle style = workBook.createCellStyle(); // 对齐方式 style.setAlignment(cellStyle.getAlignment()); style.setVerticalAlignment(cellStyle.getVAlignment()); // 设置背景颜色 // 最好的设置Pattern style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 单元格背景的显示模式 style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式. // 设置边框 style.setBorderBottom(cellStyle.getBorderBottom()); // 下边框 style.setBorderLeft(cellStyle.getBorderLeft()); // 左边框 style.setBorderTop(cellStyle.getBorderTop()); // 上边框 style.setBorderRight(cellStyle.getBorderRight()); // 右边框 // 设置边框颜色 style.setBottomBorderColor(cellStyle.getBottomBorderColor()); style.setTopBorderColor(cellStyle.getTopBorderColor()); style.setLeftBorderColor(cellStyle.getLeftBorderColor()); style.setRightBorderColor(cellStyle.getRightBorderColor()); // 设置自动换行 style.setWrapText(cellStyle.getWrapText()); style.setHidden(cellStyle.getHidden()); // 数据格式 style.setDataFormat(cellStyle.getDataFormate()); style.setLocked(cellStyle.getLocked()); // 文本旋转 请注意,这里的Rotation取值是从-90到90,而不是0-180度 style.setRotation(cellStyle.getRotation()); // 文本缩进 style.setIndention(cellStyle.getIndention()); return style; }
public void setCell(int index, Calendar value) { HSSFCell cell = this.row.createCell((short) index); // cell.setEncoding(XLS_ENCODING); cell.setCellValue(value.getTime()); // for new cell style HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); cell.setCellStyle(cellStyle); }
private static Map readStyles(NodeList styleList, Map fonts, HSSFWorkbook workbook) throws ExcelTransformerException { if (LOG.isLoggable(Level.FINE)) { LOG.entering( SimpleExcelRenderer.class.getName(), "readStyles", String.valueOf(styleList.getLength())); } Map results = new HashMap(); for (int i = 0; i < styleList.getLength(); i++) { Element styleNode = (Element) styleList.item(i); String name = styleNode.getAttribute("name"); HSSFCellStyle style = workbook.createCellStyle(); NodeList children = styleNode.getChildNodes(); for (int j = 0; j < children.getLength(); j++) { Node child = children.item(j); if (child.getNodeType() == Node.ELEMENT_NODE) { Element childE = (Element) child; String value = XmlUtils.getElementText(childE); if (childE.getNodeName().equals("font")) { HSSFFont font = (HSSFFont) fonts.get(value); if (font == null) { throw new ExcelTransformerException("Unable to locate referenced font: " + value); } style.setFont(font); } else if (childE.getNodeName().equals("builtinformat")) { style.setDataFormat(HSSFDataFormat.getBuiltinFormat(value)); } else if (childE.getNodeName().equals("dataformat")) { style.setDataFormat(workbook.createDataFormat().getFormat(value)); } } } results.put(name, style); } if (LOG.isLoggable(Level.FINE)) { LOG.exiting(SimpleExcelRenderer.class.getName(), "readStyles"); } return results; }
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); }
@Test public void testReadCell() throws Exception { when(mockCell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC); final HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); final HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); final Date currentDate = new Date(); cellStyle.setDataFormat( hssfWorkbook.getCreationHelper().createDataFormat().getFormat("m/d/yyyy h:mm")); when(mockCell.getCellStyle()).thenReturn(cellStyle); when(mockCell.getDateCellValue()).thenReturn(currentDate); final Calendar calendar = calendarConverter.readCell(mockCell); assertNotNull(calendar); assertEquals(currentDate.getTime(), calendar.getTimeInMillis()); }
// 创建长字符串样式 public static HSSFCellStyle createLongStringStyle(HSSFWorkbook wb) { HSSFCellStyle normalStyle = wb.createCellStyle(); // normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // HSSFCell.CELL_TYPE_STRING // normalStyle.set normalStyle.setAlignment(align); // normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); normalStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // normalStyle.setWrapText(true); return normalStyle; }
/** * 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); }
/** * Fills the report with content * * @param worksheet * @param startRowIndex starting row offset * @param startColIndex starting column offset * @param datasource the data source */ public static void fillReport( HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Expense> datasource) { // Row offset startRowIndex += 2; // Create cell style for the body HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle(); bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER); bodyCellStyle.setWrapText(true); HSSFCellStyle numericStyle = worksheet.getWorkbook().createCellStyle(); numericStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); // Create body for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) { // Create a new row HSSFRow row = worksheet.createRow((short) i + 1); HSSFCell cell0 = row.createCell(startColIndex + 0); cell0.setCellValue(datasource.get(i - 2).getMember().getUsername()); cell0.setCellStyle(bodyCellStyle); HSSFCell cell1 = row.createCell(startColIndex + 1); cell1.setCellValue(datasource.get(i - 2).getExpensetype().getCode()); cell1.setCellStyle(bodyCellStyle); HSSFCell cell2 = row.createCell(startColIndex + 2); cell2.setCellValue(datasource.get(i - 2).getDocumentDate()); HSSFCell cell3 = row.createCell(startColIndex + 3); cell3.setCellValue(datasource.get(i - 2).getCompany()); cell3.setCellStyle(bodyCellStyle); HSSFCell cell4 = row.createCell(startColIndex + 4); cell4.setCellValue(datasource.get(i - 2).getDescription()); cell4.setCellStyle(bodyCellStyle); HSSFCell cell5 = row.createCell(startColIndex + 5); cell5.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell5.setCellValue(datasource.get(i - 2).getAmount().doubleValue()); cell5.setCellStyle(numericStyle); } }
public void testTest1() throws Exception { HSSFWorkbook _wb = new HSSFWorkbook(); HSSFSheet _s = _wb.createSheet("waterlevel"); for (int i = 0; i < HSSFDataFormat.getNumberOfBuiltinBuiltinFormats(); i++) { System.out.println(HSSFDataFormat.getBuiltinFormat((short) i)); } HSSFCellStyle _dateCellStyle = _wb.createCellStyle(); _dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFRow _rowHeader = _s.createRow(0); HSSFCell _c = _rowHeader.createCell(0); _c.setCellStyle(_dateCellStyle); _c.setCellValue(new Date()); FileOutputStream _stream = new FileOutputStream(new File("d:\\tttt.xls")); _wb.write(_stream); }
public static HSSFCellStyle getNewStyles( HSSFWorkbook workBook, CellStyle cellStyle, HSSFFont font) { HSSFCellStyle style = workBook.createCellStyle(); // 对齐方式 style.setAlignment(cellStyle.getAlignment()); style.setVerticalAlignment(cellStyle.getVAlignment()); // 设置自动换行 style.setWrapText(cellStyle.getWrapText()); style.setHidden(cellStyle.getHidden()); // 数据格式 style.setDataFormat(cellStyle.getDataFormate()); style.setLocked(cellStyle.getLocked()); // 文本旋转 请注意,这里的Rotation取值是从-90到90,而不是0-180度 style.setRotation(cellStyle.getRotation()); // 文本缩进 style.setIndention(cellStyle.getIndention()); // 设置字体 style.setFont(font); return style; }
/** * Создает новый стиль в документе Excel который полностью копирует свойства некоторого исходного * стиля этого же документа. Данный метод используется для того чтобы в последствии поменять в * новом стиле один или несколько свойств не затрагивая при этом свойства исходного стиля. * * @param wb документ Excel. * @param src стиль ячейки взятый в качестве шаблона. * @return созданная копия исходного стиля в этом же документе. */ public static HSSFCellStyle copyStyle(final HSSFWorkbook wb, final HSSFCellStyle src) { final HSSFCellStyle dst = wb.createCellStyle(); dst.setAlignment(src.getAlignment()); dst.setBorderBottom(src.getBorderBottom()); dst.setBorderLeft(src.getBorderLeft()); dst.setBorderRight(src.getBorderRight()); dst.setBorderTop(src.getBorderTop()); dst.setBottomBorderColor(src.getBottomBorderColor()); dst.setDataFormat(src.getDataFormat()); dst.setFillForegroundColor(src.getFillForegroundColor()); dst.setFillBackgroundColor(src.getFillBackgroundColor()); dst.setFillPattern(src.getFillPattern()); dst.setFont(src.getFont(wb)); dst.setHidden(src.getHidden()); dst.setIndention(src.getIndention()); dst.setLeftBorderColor(src.getLeftBorderColor()); dst.setLocked(src.getLocked()); dst.setRightBorderColor(src.getRightBorderColor()); dst.setRotation(src.getRotation()); dst.setTopBorderColor(src.getTopBorderColor()); dst.setVerticalAlignment(src.getVerticalAlignment()); dst.setWrapText(src.getWrapText()); return dst; }
/** Create a library of cell styles */ private HashMap<String, CellStyle> createStyles(HSSFWorkbook wb) { HashMap<String, CellStyle> styles = new HashMap<String, CellStyle>(); HSSFCellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 10); monthFont.setColor(IndexedColors.WHITE.getIndex()); // header in first sheet style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); // header in second sheet style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); setBorder(style, 1); style.setFont(monthFont); styles.put("header1", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); setBorder(style, 1, 0); style.setFont(monthFont); styles.put("header1_b", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); setBorder(style, 1, 1); style.setFont(monthFont); styles.put("header1_t", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); setBorder(style, 1, 2); style.setFont(monthFont); styles.put("header1_l", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); setBorder(style, 1, 3); style.setFont(monthFont); styles.put("header1_r", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); setBorder(style, 1, 4); style.setFont(monthFont); styles.put("header1_a", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
@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; }