private void writeSheet( List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) { // Set column widths HSSFSheet sheet = wb.createSheet(worksheetName); sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000")); sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000")); // header style HSSFCellStyle headerStyle; HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFont(headerFont); headerStyle.setWrapText(true); // header row HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(30); HSSFCell headerCell0 = headerRow.createCell((short) 0); HSSFCell headerCell1 = headerRow.createCell((short) 1); headerCell0.setCellStyle(headerStyle); setText(headerCell0, "Layer Name"); headerCell1.setCellStyle(headerStyle); setText(headerCell1, "Message"); int counter = 1; for (Map<String, String> valueMap : valueMaps) { HSSFRow dataRow = sheet.createRow(counter); String layer = valueMap.get("layer"); String status = valueMap.get("status"); status = HtmlUtils.htmlUnescape(status); HSSFCell currentCell0 = dataRow.createCell((short) 0); HSSFCell currentCell1 = dataRow.createCell((short) 1); setText(currentCell0, layer); setText(currentCell1, status); counter++; } }
private HSSFFont matchFont(Font font) { HSSFColor hssfColor = workbook .getCustomPalette() .findColor( (byte) foreground.getRed(), (byte) foreground.getGreen(), (byte) foreground.getBlue()); if (hssfColor == null) hssfColor = workbook .getCustomPalette() .findSimilarColor( (byte) foreground.getRed(), (byte) foreground.getGreen(), (byte) foreground.getBlue()); boolean bold = (font.getStyle() & Font.BOLD) != 0; boolean italic = (font.getStyle() & Font.ITALIC) != 0; HSSFFont hssfFont = workbook.findFont( bold ? HSSFFont.BOLDWEIGHT_BOLD : 0, hssfColor.getIndex(), (short) (font.getSize() * 20), font.getName(), italic, false, (short) 0, (byte) 0); if (hssfFont == null) { hssfFont = workbook.createFont(); hssfFont.setBoldweight(bold ? HSSFFont.BOLDWEIGHT_BOLD : 0); hssfFont.setColor(hssfColor.getIndex()); hssfFont.setFontHeight((short) (font.getSize() * 20)); hssfFont.setFontName(font.getName()); hssfFont.setItalic(italic); hssfFont.setStrikeout(false); hssfFont.setTypeOffset((short) 0); hssfFont.setUnderline((byte) 0); } return hssfFont; }
/** * runs a xls file where the user insert a row within a worksheet where two fields are set: CODE, * NAME. * * @param document: Document to modify the name and code field. */ public void postProcessXLS(Object document) { HSSFWorkbook book = (HSSFWorkbook) document; HSSFSheet sheet = book.getSheetAt(0); // Se toma hoja del libro HSSFRow row; HSSFCellStyle cellStyle = book.createCellStyle(); HSSFFont font = book.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); row = sheet.createRow(0); // Se crea una fila dentro de la hoja createCell(cellStyle, row, 0, "CODIGO"); // "100">#{rowX.column1}</p:column> createCell( cellStyle, row, 1, "NOMBRE"); // "100">#{rowX.column23}</p:column> countriesList = countriesFacade.findAll(); for (int i = 0; i < countriesList.size(); i++) { row = sheet.createRow(i + 1); createCell(row, 0, countriesList.get(i).getIdCountry().toString()); // CODIGO createCell(row, 1, countriesList.get(i).getName()); // NOMBRE } }
public void testDoesNoHarmIfNothingToDo() { HSSFWorkbook wb = new HSSFWorkbook(); // New files start with 4 built in fonts, and 21 built in styles assertEquals(4, wb.getNumberOfFonts()); assertEquals(21, wb.getNumCellStyles()); // Create a test font and style, and use them HSSFFont f = wb.createFont(); f.setFontName("Testing"); HSSFCellStyle s = wb.createCellStyle(); s.setFont(f); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow(0); row.createCell(0).setCellStyle(s); // Should have one more than the default of each assertEquals(5, wb.getNumberOfFonts()); assertEquals(22, wb.getNumCellStyles()); // Optimise fonts HSSFOptimiser.optimiseFonts(wb); assertEquals(5, wb.getNumberOfFonts()); assertEquals(22, wb.getNumCellStyles()); assertEquals(f, s.getFont(wb)); // Optimise styles HSSFOptimiser.optimiseCellStyles(wb); assertEquals(5, wb.getNumberOfFonts()); assertEquals(22, wb.getNumCellStyles()); assertEquals(f, s.getFont(wb)); }
public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack) throws RuntimeException, IOException { HSSFWorkbook workbook = new HSSFWorkbook(); // create a sheet with specified name HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName()); for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) { sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256); } // create a title for sheet title sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1)); HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont titleFont = workbook.createFont(); // set font titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleFont.setFontName("宋体"); titleFont.setFontHeight((short) (240)); titleFont.setColor(HSSFColor.AUTOMATIC.index); titleCellStyle.setFont(titleFont); titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow title = sheet.createRow(0); title.setHeight((short) 300); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = title.createCell(i); cell.setCellValue(xlscaCallBack.getTitle()); cell.setCellStyle(titleCellStyle); } HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headerFont = workbook.createFont(); // set font headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontName("幼圆"); headerFont.setColor(HSSFColor.AUTOMATIC.index); headerCellStyle.setFont(headerFont); headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow header = sheet.createRow(1); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header HSSFCell cell = header.createCell(i); cell.setCellValue(xlscaCallBack.getHeaders()[i]); cell.setCellStyle(headerCellStyle); } HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont rowFont = workbook.createFont(); // set font rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); rowFont.setFontName("幼圆"); rowFont.setColor(HSSFColor.AUTOMATIC.index); rowCellStyle.setFont(rowFont); rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < lists.size(); i++) { HSSFRow row = sheet.createRow(i + 2); String values[] = xlscaCallBack.getValue(lists.get(i)); for (int j = 0; j < values.length; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(values[j]); cell.setCellStyle(rowCellStyle); } } HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont bottomFont = workbook.createFont(); // set font bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); bottomFont.setFontName("幼圆"); bottomFont.setColor(HSSFColor.AUTOMATIC.index); bottomCellStyle.setFont(bottomFont); bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); sheet.addMergedRegion( new CellRangeAddress( lists.size() + 2, lists.size() + 2, 0, xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls HSSFRow bottomRow = sheet.createRow(lists.size() + 2); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = bottomRow.createCell(i); SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); cell.setCellValue("共计导出 " + lists.size() + " 条记录 ,导出日期:" + sf.format(new Date())); cell.setCellStyle(bottomCellStyle); } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("create Excel failed due to some unkonw reasion~"); } }
public void testOptimiseFonts() { HSSFWorkbook wb = new HSSFWorkbook(); // Add 6 fonts, some duplicates HSSFFont f1 = wb.createFont(); f1.setFontHeight((short) 11); f1.setFontName("Testing"); HSSFFont f2 = wb.createFont(); f2.setFontHeight((short) 22); f2.setFontName("Also Testing"); HSSFFont f3 = wb.createFont(); f3.setFontHeight((short) 33); f3.setFontName("Unique"); HSSFFont f4 = wb.createFont(); f4.setFontHeight((short) 11); f4.setFontName("Testing"); HSSFFont f5 = wb.createFont(); f5.setFontHeight((short) 22); f5.setFontName("Also Testing"); HSSFFont f6 = wb.createFont(); f6.setFontHeight((short) 66); f6.setFontName("Also Unique"); // Use all three of the four in cell styles assertEquals(21, wb.getNumCellStyles()); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setFont(f1); assertEquals(5, cs1.getFontIndex()); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setFont(f4); assertEquals(8, cs2.getFontIndex()); HSSFCellStyle cs3 = wb.createCellStyle(); cs3.setFont(f5); assertEquals(9, cs3.getFontIndex()); HSSFCellStyle cs4 = wb.createCellStyle(); cs4.setFont(f6); assertEquals(10, cs4.getFontIndex()); assertEquals(25, wb.getNumCellStyles()); // And three in rich text HSSFSheet s = wb.createSheet(); HSSFRow r = s.createRow(0); HSSFRichTextString rtr1 = new HSSFRichTextString("Test"); rtr1.applyFont(0, 2, f1); rtr1.applyFont(3, 4, f2); r.createCell(0).setCellValue(rtr1); HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest"); rtr2.applyFont(0, 2, f3); rtr2.applyFont(3, 5, f5); rtr2.applyFont(6, 8, f6); r.createCell(1).setCellValue(rtr2); // Check what we have now assertEquals(10, wb.getNumberOfFonts()); assertEquals(25, wb.getNumCellStyles()); // Optimise HSSFOptimiser.optimiseFonts(wb); // Check font count assertEquals(8, wb.getNumberOfFonts()); assertEquals(25, wb.getNumCellStyles()); // Check font use in cell styles assertEquals(5, cs1.getFontIndex()); assertEquals(5, cs2.getFontIndex()); // duplicate of 1 assertEquals(6, cs3.getFontIndex()); // duplicate of 2 assertEquals(8, cs4.getFontIndex()); // two have gone // And in rich text // RTR 1 had f1 and f2, unchanged assertEquals(5, r.getCell(0).getRichStringCellValue().getFontAtIndex(0)); assertEquals(5, r.getCell(0).getRichStringCellValue().getFontAtIndex(1)); assertEquals(6, r.getCell(0).getRichStringCellValue().getFontAtIndex(3)); assertEquals(6, r.getCell(0).getRichStringCellValue().getFontAtIndex(4)); // RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down) assertEquals(7, r.getCell(1).getRichStringCellValue().getFontAtIndex(0)); assertEquals(7, r.getCell(1).getRichStringCellValue().getFontAtIndex(1)); assertEquals(6, r.getCell(1).getRichStringCellValue().getFontAtIndex(3)); assertEquals(6, r.getCell(1).getRichStringCellValue().getFontAtIndex(4)); assertEquals(8, r.getCell(1).getRichStringCellValue().getFontAtIndex(6)); assertEquals(8, r.getCell(1).getRichStringCellValue().getFontAtIndex(7)); }
public void testOptimiseStyles() { HSSFWorkbook wb = new HSSFWorkbook(); // Two fonts assertEquals(4, wb.getNumberOfFonts()); HSSFFont f1 = wb.createFont(); f1.setFontHeight((short) 11); f1.setFontName("Testing"); HSSFFont f2 = wb.createFont(); f2.setFontHeight((short) 22); f2.setFontName("Also Testing"); assertEquals(6, wb.getNumberOfFonts()); // Several styles assertEquals(21, wb.getNumCellStyles()); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setFont(f1); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setFont(f2); HSSFCellStyle cs3 = wb.createCellStyle(); cs3.setFont(f1); HSSFCellStyle cs4 = wb.createCellStyle(); cs4.setFont(f1); cs4.setAlignment((short) 22); HSSFCellStyle cs5 = wb.createCellStyle(); cs5.setFont(f2); cs5.setAlignment((short) 111); HSSFCellStyle cs6 = wb.createCellStyle(); cs6.setFont(f2); assertEquals(27, wb.getNumCellStyles()); // Use them HSSFSheet s = wb.createSheet(); HSSFRow r = s.createRow(0); r.createCell(0).setCellStyle(cs1); r.createCell(1).setCellStyle(cs2); r.createCell(2).setCellStyle(cs3); r.createCell(3).setCellStyle(cs4); r.createCell(4).setCellStyle(cs5); r.createCell(5).setCellStyle(cs6); r.createCell(6).setCellStyle(cs1); r.createCell(7).setCellStyle(cs2); assertEquals(21, r.getCell(0).getCellValueRecord().getXFIndex()); assertEquals(26, r.getCell(5).getCellValueRecord().getXFIndex()); assertEquals(21, r.getCell(6).getCellValueRecord().getXFIndex()); // Optimise HSSFOptimiser.optimiseCellStyles(wb); // Check assertEquals(6, wb.getNumberOfFonts()); assertEquals(25, wb.getNumCellStyles()); // cs1 -> 21 assertEquals(21, r.getCell(0).getCellValueRecord().getXFIndex()); // cs2 -> 22 assertEquals(22, r.getCell(1).getCellValueRecord().getXFIndex()); assertEquals(22, r.getCell(1).getCellStyle().getFont(wb).getFontHeight()); // cs3 = cs1 -> 21 assertEquals(21, r.getCell(2).getCellValueRecord().getXFIndex()); // cs4 --> 24 -> 23 assertEquals(23, r.getCell(3).getCellValueRecord().getXFIndex()); // cs5 --> 25 -> 24 assertEquals(24, r.getCell(4).getCellValueRecord().getXFIndex()); // cs6 = cs2 -> 22 assertEquals(22, r.getCell(5).getCellValueRecord().getXFIndex()); // cs1 -> 21 assertEquals(21, r.getCell(6).getCellValueRecord().getXFIndex()); // cs2 -> 22 assertEquals(22, r.getCell(7).getCellValueRecord().getXFIndex()); // Add a new duplicate, and two that aren't used HSSFCellStyle csD = wb.createCellStyle(); csD.setFont(f1); r.createCell(8).setCellStyle(csD); HSSFFont f3 = wb.createFont(); f3.setFontHeight((short) 23); f3.setFontName("Testing 3"); HSSFFont f4 = wb.createFont(); f4.setFontHeight((short) 24); f4.setFontName("Testing 4"); HSSFCellStyle csU1 = wb.createCellStyle(); csU1.setFont(f3); HSSFCellStyle csU2 = wb.createCellStyle(); csU2.setFont(f4); // Check before the optimise assertEquals(8, wb.getNumberOfFonts()); assertEquals(28, wb.getNumCellStyles()); // Optimise, should remove the two un-used ones and the one duplicate HSSFOptimiser.optimiseCellStyles(wb); // Check assertEquals(8, wb.getNumberOfFonts()); assertEquals(25, wb.getNumCellStyles()); // csD -> cs1 -> 21 assertEquals(21, r.getCell(8).getCellValueRecord().getXFIndex()); }