Ejemplo n.º 1
0
  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();
      }
    }
  }
Ejemplo n.º 3
0
  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;
  }
Ejemplo n.º 4
0
 /**
  * Creates the top-level drawing patriarch.
  *
  * @return The new drawing patriarch.
  */
 public Drawing createDrawingPatriarch() {
   return _sh.createDrawingPatriarch();
 }