protected SRow importRow(Row poiRow, SSheet sheet) {
    SRow row = sheet.getRow(poiRow.getRowNum());
    row.setHeight(UnitUtil.twipToPx(poiRow.getHeight()));
    row.setCustomHeight(poiRow.isCustomHeight());
    row.setHidden(poiRow.getZeroHeight());
    CellStyle rowStyle = poiRow.getRowStyle();
    if (rowStyle != null) {
      row.setCellStyle(importCellStyle(rowStyle));
    }

    for (Cell poiCell : poiRow) {
      importCell(poiCell, poiRow.getRowNum(), sheet);
    }

    return row;
  }
  public void testModifyArrayCells_removeRow() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells
    CellRangeAddress cra = CellRangeAddress.valueOf("B5");
    CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
    Cell scell = srange.getTopLeftCell();
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());

    Row srow = scell.getRow();
    assertSame(srow, sheet.getRow(cra.getFirstRow()));
    sheet.removeRow(srow);
    assertNull(sheet.getRow(cra.getFirstRow()));

    // re-create the removed row and cell
    scell = sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn());
    assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
    assertFalse(scell.isPartOfArrayFormulaGroup());

    // we cannot remove rows with cells included in a multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      int columnIndex = mcell.getColumnIndex();
      Row mrow = mcell.getRow();
      try {
        sheet.removeRow(mrow);
        fail("expected exception");
      } catch (IllegalStateException e) {
        String msg =
            "Row[rownum="
                + mrow.getRowNum()
                + "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Row.removeCell leaves the row
      // in the state that it was in prior to the invocation
      assertSame(mrow, sheet.getRow(mrow.getRowNum()));
      assertSame(mcell, mrow.getCell(columnIndex));
      assertTrue(mcell.isPartOfArrayFormulaGroup());
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
    }
  }
  private static InterviewInstance getInstance(Row row) throws ParseException {

    // extract the cells needed from this row
    Cell interview_id_cell = row.getCell(0, Row.CREATE_NULL_AS_BLANK);
    Cell line_cell = row.getCell(1, Row.CREATE_NULL_AS_BLANK);
    Cell speaker_cell = row.getCell(2, Row.CREATE_NULL_AS_BLANK);
    Cell comment_cell = row.getCell(3, Row.CREATE_NULL_AS_BLANK);
    ArrayList<Cell> category_cells = new ArrayList<>(5);
    for (int i = 4; i <= 8; ++i) {
      Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
      if (cell.getCellType() != Cell.CELL_TYPE_BLANK) category_cells.add(cell);
    }
    Cell sentiment_cell = row.getCell(9, Row.CREATE_NULL_AS_BLANK);

    // verify that all the required information is present
    verifyNumericCell(interview_id_cell, "Interview ID field");
    verifyNumericCell(line_cell, "Line field");
    verifySpeakerCell(speaker_cell);
    verifyCellNotEmpty(comment_cell, "Comment cell");

    if (category_cells.isEmpty())
      throw new ParseException("No category labels given for samples in row " + row.getRowNum(), 0);

    Iterator<Cell> row_iter = category_cells.iterator();
    ArrayList<Category> categories = new ArrayList<>();
    while (row_iter.hasNext()) {
      Cell category_cell = row_iter.next();
      verifyCategoryCell(category_cell);
      categories.add(Category.getCategory(category_cell.getStringCellValue()));
    }

    // convert the cell into an interviewInstance
    InterviewInstance instance =
        new InterviewInstance(
            (int) interview_id_cell.getNumericCellValue(),
            (int) line_cell.getNumericCellValue(),
            Speaker.getSpeaker(speaker_cell.getStringCellValue()),
            comment_cell.getStringCellValue(),
            categories);

    return instance;
  }
Exemplo n.º 4
0
  /**
   * Compute width of a single cell
   *
   * @param cell the cell whose width is to be calculated
   * @param defaultCharWidth the width of a single character
   * @param formatter formatter used to prepare the text to be measured
   * @param useMergedCells whether to use merged cells
   * @return the width in pixels
   */
  public static double getCellWidth(
      Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) {

    Sheet sheet = cell.getSheet();
    Workbook wb = sheet.getWorkbook();
    Row row = cell.getRow();
    int column = cell.getColumnIndex();

    int colspan = 1;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
      CellRangeAddress region = sheet.getMergedRegion(i);
      if (containsCell(region, row.getRowNum(), column)) {
        if (!useMergedCells) {
          // If we're not using merged cells, skip this one and move on to the next.
          return -1;
        }
        cell = row.getCell(region.getFirstColumn());
        colspan = 1 + region.getLastColumn() - region.getFirstColumn();
      }
    }

    CellStyle style = cell.getCellStyle();
    int cellType = cell.getCellType();

    // for formula cells we compute the cell width for the cached formula result
    if (cellType == Cell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType();

    Font font = wb.getFontAt(style.getFontIndex());

    AttributedString str;
    TextLayout layout;

    double width = -1;
    if (cellType == Cell.CELL_TYPE_STRING) {
      RichTextString rt = cell.getRichStringCellValue();
      String[] lines = rt.getString().split("\\n");
      for (int i = 0; i < lines.length; i++) {
        String txt = lines[i] + defaultChar;

        str = new AttributedString(txt);
        copyAttributes(font, str, 0, txt.length());

        if (rt.numFormattingRuns() > 0) {
          // TODO: support rich text fragments
        }

        layout = new TextLayout(str.getIterator(), fontRenderContext);
        if (style.getRotation() != 0) {
          /*
           * Transform the text using a scale so that it's height is increased by a multiple of the leading,
           * and then rotate the text before computing the bounds. The scale results in some whitespace around
           * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
           * is added by the standard Excel autosize.
           */
          AffineTransform trans = new AffineTransform();
          trans.concatenate(
              AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
          trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
          width =
              Math.max(
                  width,
                  ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        } else {
          width =
              Math.max(
                  width,
                  ((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        }
      }
    } else {
      String sval = null;
      if (cellType == Cell.CELL_TYPE_NUMERIC) {
        // Try to get it formatted to look the same as excel
        try {
          sval = formatter.formatCellValue(cell, dummyEvaluator);
        } catch (Exception e) {
          sval = String.valueOf(cell.getNumericCellValue());
        }
      } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
        sval = String.valueOf(cell.getBooleanCellValue()).toUpperCase();
      }
      if (sval != null) {
        String txt = sval + defaultChar;
        str = new AttributedString(txt);
        copyAttributes(font, str, 0, txt.length());

        layout = new TextLayout(str.getIterator(), fontRenderContext);
        if (style.getRotation() != 0) {
          /*
           * Transform the text using a scale so that it's height is increased by a multiple of the leading,
           * and then rotate the text before computing the bounds. The scale results in some whitespace around
           * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
           * is added by the standard Excel autosize.
           */
          AffineTransform trans = new AffineTransform();
          trans.concatenate(
              AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
          trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
          width =
              Math.max(
                  width,
                  ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        } else {
          width =
              Math.max(
                  width,
                  ((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
                      + cell.getCellStyle().getIndention());
        }
      }
    }
    return width;
  }
Exemplo n.º 5
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;
  }