@Override public void setDateTimePattern(String dateTimePattern) { this.dateTimePattern = dateTimePattern; DateTimeConverter dtConverter = (DateTimeConverter) converters.lookup(Date.class); dtConverter.setDateTimePattern(dateTimePattern); dateCellStyle = workbook.createCellStyle(); CreationHelper helper = workbook.getCreationHelper(); dateCellStyle.setDataFormat(helper.createDataFormat().getFormat(dateTimePattern)); }
private void makeTemporalCell(Cell retVal, Date cellObj, String format) { CreationHelper creationHelper = workbook.getCreationHelper(); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format)); retVal.setCellStyle(cellStyle); if (null != cellObj) { Calendar calendar = Calendar.getInstance(); calendar.setTime((Date) cellObj); retVal.setCellValue(calendar); } }
/** * ハイパーリンクの設定 * * @param sheet シート * @param nRow 対象行番号 * @param nColumn 対象列番号 * @param value ハイパーリンクテキスト * @param url ハイパーリンク先URL */ public static void setHyperLink( XSSFSheet sheet, int nRow, int nColumn, String value, String url) { assert sheet != null; XSSFWorkbook workbook = sheet.getWorkbook(); CreationHelper helper = workbook.getCreationHelper(); Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL); hyperlink.setAddress(url); XSSFRow row = getRowAnyway(sheet, nRow); XSSFCell cell = getCellAnyway(row, nColumn); cell.setCellValue(value); cell.setHyperlink(hyperlink); // ハイパーリンクテキストの装飾 XSSFFont font = workbook.createFont(); XSSFCellStyle style = workbook.createCellStyle(); // font.setColor(new XSSFColor(new Color(0, 0, 255))); font.setUnderline(XSSFFont.U_SINGLE); style.setFont(font); cell.setCellStyle(style); }
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; }