protected SCell importCell(Cell poiCell, int row, SSheet sheet) {

    SCell cell = sheet.getCell(row, poiCell.getColumnIndex());
    cell.setCellStyle(importCellStyle(poiCell.getCellStyle()));

    switch (poiCell.getCellType()) {
      case Cell.CELL_TYPE_NUMERIC:
        cell.setNumberValue(poiCell.getNumericCellValue());
        break;
      case Cell.CELL_TYPE_STRING:
        RichTextString poiRichTextString = poiCell.getRichStringCellValue();
        if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) {
          SRichText richText = cell.setupRichTextValue();
          importRichText(poiCell, poiRichTextString, richText);
        } else {
          cell.setStringValue(poiCell.getStringCellValue());
        }
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        cell.setBooleanValue(poiCell.getBooleanCellValue());
        break;
      case Cell.CELL_TYPE_FORMULA:
        cell.setFormulaValue(poiCell.getCellFormula());
        // ZSS-873
        if (isImportCache() && !poiCell.isCalcOnLoad() && !mustCalc(cell)) {
          ValueEval val = null;
          switch (poiCell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
              val = new NumberEval(poiCell.getNumericCellValue());
              break;
            case Cell.CELL_TYPE_STRING:
              RichTextString poiRichTextString0 = poiCell.getRichStringCellValue();
              if (poiRichTextString0 != null && poiRichTextString0.numFormattingRuns() > 0) {
                SRichText richText = new RichTextImpl();
                importRichText(poiCell, poiRichTextString0, richText);
                val = new StringEval(richText.getText());
              } else {
                val = new StringEval(poiCell.getStringCellValue());
              }
              break;
            case Cell.CELL_TYPE_BOOLEAN:
              val = BoolEval.valueOf(poiCell.getBooleanCellValue());
              break;
            case Cell.CELL_TYPE_ERROR:
              val = ErrorEval.valueOf(poiCell.getErrorCellValue());
              break;
            case Cell.CELL_TYPE_BLANK:
            default:
              // do nothing
          }
          if (val != null) {
            ((AbstractCellAdv) cell).setFormulaResultValue(val);
          }
        }
        break;
      case Cell.CELL_TYPE_ERROR:
        cell.setErrorValue(PoiEnumConversion.toErrorCode(poiCell.getErrorCellValue()));
        break;
      case Cell.CELL_TYPE_BLANK:
        // do nothing because spreadsheet model auto creates blank cells
      default:
        // TODO log: leave an unknown cell type as a blank cell.
        break;
    }

    Hyperlink poiHyperlink = poiCell.getHyperlink();
    if (poiHyperlink != null) {
      String addr = poiHyperlink.getAddress();
      String label = poiHyperlink.getLabel();
      SHyperlink hyperlink =
          cell.setupHyperlink(
              PoiEnumConversion.toHyperlinkType(poiHyperlink.getType()),
              addr == null ? "" : addr,
              label == null ? "" : label);
      cell.setHyperlink(hyperlink);
    }

    Comment poiComment = poiCell.getCellComment();
    if (poiComment != null) {
      SComment comment = cell.setupComment();
      comment.setAuthor(poiComment.getAuthor());
      comment.setVisible(poiComment.isVisible());
      RichTextString poiRichTextString = poiComment.getString();
      if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) {
        importRichText(poiCell, poiComment.getString(), comment.setupRichText());
      } else {
        comment.setText(poiComment.toString());
      }
    }

    return cell;
  }
Пример #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;
  }