Example #1
0
  public void setComment(int rowIndex, int columnIndex, int rows, int cols, String text) {
    HSSFCell cell = getCell(rowIndex, columnIndex);
    if (cell.getCellComment() != null) {
      cell.removeCellComment();
    }
    ClientAnchor anchor = getCreationHelper().createClientAnchor();

    anchor.setCol1(columnIndex + 2);
    anchor.setCol2(columnIndex + 2 + cols);
    anchor.setRow1(rowIndex + 1);
    anchor.setRow2(rowIndex + 1 + rows);

    RichTextString str = getCreationHelper().createRichTextString(text);
    HSSFFont font = createFont();
    font.setFontHeightInPoints((short) 10);
    str.applyFont(font);

    Comment comment = getDrawing().createCellComment(anchor);
    comment.setString(str);

    cell.setCellComment(comment);
  }
Example #2
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;
  }