public void testAccessMethods() throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFDrawing drawing = sheet.createDrawingPatriarch(); XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30); XSSFChart chart = drawing.createChart(anchor); XSSFCategoryAxis axis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); axis.setCrosses(AxisCrosses.AUTO_ZERO); assertEquals(axis.getCrosses(), AxisCrosses.AUTO_ZERO); assertEquals(chart.getAxis().size(), 1); }
protected void newPage() { addRegions(xlsSheet, regions, wb); if (hasTemplate()) { xlsSheet = wb.getSheetAt(bean.getReportLayout().getTemplateSheet() - 1); } else { // for a group name inside sheet name when we create a page we do not have the group name yet // so we will have to set the name of the previous sheet (see also finishExport where we set // the name of the last sheet(s) String sheetName = replaceSheetNameParam(bean.getReportLayout().getSheetNames()); if ((sheetName == null) || sheetName.isEmpty()) { sheetName = String.valueOf(page); } if (sheetNameContainsGroup(bean.getReportLayout().getSheetNames()) && (page > 1)) { // current group is for previous sheet page xlsSheet = wb.createSheet(String.valueOf(page)); wb.setSheetName(page - 2, sheetName); } else { xlsSheet = wb.createSheet(sheetName); } } xlsSheet.setMargin( Sheet.LeftMargin, getInches(bean.getReportLayout().getPagePadding().getLeft())); xlsSheet.setMargin( Sheet.RightMargin, getInches(bean.getReportLayout().getPagePadding().getRight())); xlsSheet.setMargin( Sheet.TopMargin, getInches(bean.getReportLayout().getPagePadding().getTop())); xlsSheet.setMargin( Sheet.BottomMargin, getInches(bean.getReportLayout().getPagePadding().getBottom())); if (bean.getReportLayout().getOrientation() == LANDSCAPE) { xlsSheet.getPrintSetup().setLandscape(true); } setPaperSize(); patriarch = xlsSheet.createDrawingPatriarch(); buildHeader(); buildFooter(); page++; // first page header is written by ResultExporter if (bean.getReportLayout().isHeaderOnEveryPage() && (page > 2)) { try { printHeaderBand(); } catch (QueryException e) { e.printStackTrace(); } } }
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; }
/** * Creates the top-level drawing patriarch. * * @return The new drawing patriarch. */ public Drawing createDrawingPatriarch() { return _sh.createDrawingPatriarch(); }