/** Create a library of cell styles */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle 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) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); 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); 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; }
private static Map createStyles(Workbook wb) { Map styles = new HashMap(); Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Trebuchet MS"); CellStyle style = wb.createCellStyle(); style.setFont(titleFont); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); styles.put("title", style); Font itemFont = wb.createFont(); itemFont.setFontHeightInPoints((short) 9); itemFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setAlignment((short) 1); style.setFont(itemFont); styles.put("item_left", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); styles.put("item_right", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat( wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); styles.put("input_$", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0.000%")); styles.put("input_%", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); styles.put("input_i", style); style = wb.createCellStyle(); style.setAlignment((short) 2); style.setFont(itemFont); style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy")); styles.put("input_d", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00")); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern((short) 1); styles.put("formula_$", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern((short) 1); styles.put("formula_i", style); return styles; }
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) { File excelFile = new File("export_parts.xls"); // Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); // Create a blank sheet XSSFSheet sheet = workbook.createSheet("Parts Data"); String header = StringUtils.join(queryResult.getQuery().getSelects(), ";"); String[] columns = header.split(";"); Map<Integer, String[]> data = new HashMap<>(); String[] headerFormatted = createXLSHeaderRow(header, columns, locale); data.put(1, headerFormatted); Map<Integer, String[]> commentsData = new HashMap<>(); String[] headerComments = createXLSHeaderRowComments(header, columns); commentsData.put(1, headerComments); List<String> selects = queryResult.getQuery().getSelects(); int i = 1; for (QueryResultRow row : queryResult.getRows()) { i++; data.put(i, createXLSRow(selects, row, baseURL)); commentsData.put(i, createXLSRowComments(selects, row)); } // Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = sheet.createRow(rownum++); String[] objArr = data.get(key); int cellnum = 0; for (String obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellValue(obj); } CreationHelper factory = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); String[] commentsObjArr = commentsData.get(key); cellnum = 0; for (String commentsObj : commentsObjArr) { if (commentsObj.length() > 0) { Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 1); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(commentsObj); comment.setString(str); // Assign the comment to the cell cell.setCellComment(comment); } cellnum++; } } // Define header style Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 10); headerFont.setFontName("Courier New"); headerFont.setItalic(true); headerFont.setColor(IndexedColors.WHITE.getIndex()); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // Set header style for (int j = 0; j < columns.length; j++) { Cell cell = sheet.getRow(0).getCell(j); cell.setCellStyle(headerStyle); if (cell.getCellComment() != null) { String comment = cell.getCellComment().getString().toString(); if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER) || comment.equals(QueryField.PART_MASTER_NUMBER)) { for (int k = 0; k < queryResult.getRows().size(); k++) { Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j) : sheet.getRow(k + 1).createCell(j); grayCell.setCellStyle(headerStyle); } } } } try { // Write the workbook in file system FileOutputStream out = new FileOutputStream(excelFile); workbook.write(out); out.close(); } catch (Exception e) { LOGGER.log(Level.FINEST, null, e); } return excelFile; }