public CellStyle buildDimensionCellStyle(Sheet sheet) {
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);

    String headerBGColor = (String) this.getProperty(PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR);
    logger.debug("Header background color : " + headerBGColor);
    short backgroundColorIndex =
        headerBGColor != null
            ? IndexedColors.valueOf(headerBGColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR).getIndex();
    cellStyle.setFillForegroundColor(backgroundColorIndex);

    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);

    String bordeBorderColor = (String) this.getProperty(PROPERTY_HEADER_BORDER_COLOR);
    logger.debug("Header border color : " + bordeBorderColor);
    short borderColorIndex =
        bordeBorderColor != null
            ? IndexedColors.valueOf(bordeBorderColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_HEADER_BORDER_COLOR).getIndex();

    cellStyle.setLeftBorderColor(borderColorIndex);
    cellStyle.setRightBorderColor(borderColorIndex);
    cellStyle.setBottomBorderColor(borderColorIndex);
    cellStyle.setTopBorderColor(borderColorIndex);

    Font font = sheet.getWorkbook().createFont();

    Short headerFontSize = (Short) this.getProperty(PROPERTY_HEADER_FONT_SIZE);
    logger.debug("Header font size : " + headerFontSize);
    short headerFontSizeShort =
        headerFontSize != null ? headerFontSize.shortValue() : DEFAULT_HEADER_FONT_SIZE;
    font.setFontHeightInPoints(headerFontSizeShort);

    String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
    logger.debug("Font name : " + fontName);
    fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
    font.setFontName(fontName);

    String color = (String) this.getProperty(PROPERTY_DIMENSION_NAME_COLOR);
    logger.debug("Dimension color : " + color);
    short colorIndex =
        bordeBorderColor != null
            ? IndexedColors.valueOf(color).getIndex()
            : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_COLOR).getIndex();
    font.setColor(colorIndex);

    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setItalic(true);
    cellStyle.setFont(font);
    return cellStyle;
  }
Esempio n. 2
0
 private Font cloneFont(CellStyle cellstyle) {
   Font newFont = spreadsheet.getWorkbook().createFont();
   Font originalFont = spreadsheet.getWorkbook().getFontAt(cellstyle.getFontIndex());
   if (originalFont != null) {
     newFont.setBold(originalFont.getBold());
     newFont.setItalic(originalFont.getItalic());
     newFont.setFontHeight(originalFont.getFontHeight());
     newFont.setUnderline(originalFont.getUnderline());
     newFont.setStrikeout(originalFont.getStrikeout());
     // This cast an only be done when using .xlsx files
     XSSFFont originalXFont = (XSSFFont) originalFont;
     XSSFFont newXFont = (XSSFFont) newFont;
     newXFont.setColor(originalXFont.getXSSFColor());
   }
   return newFont;
 }
Esempio 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;
  }