Beispiel #1
0
  /** 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;
  }
Beispiel #2
0
 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;
  }