[Bug 55384] Setting a precalculated String value on a formula cell clears out the cell

[bugzilla]

https://issues.apache.org/bugzilla/show_bug.cgi?id=55384

--- Comment #3 from Adrian K <akoks...@gmail.com> ---
Ok, so I finally got around to writing this as a general unit test and it looks
like this bug is limited to SXSSF.

Here's a snippet of code that demonstrates the failure in SXSSF, while showing
that XSSF and HSSF work as one would expect.  I'm not too familiar with using
JUnit assertions, etc, so please excuse my code if it's blatantly wrong.

I've also attached the three spreadsheets that this code will generate.


  Workbook [] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook(),
new SXSSFWorkbook() };
  String fileBase = "/temp/";
  for (Workbook wb : wbs) {

    Sheet sh = wb.createSheet();
    for(int rownum = 0; rownum < 10; rownum++){
      org.apache.poi.ss.usermodel.Row row = sh.createRow(rownum);
      for(int cellnum = 0; cellnum < 3; cellnum++){
        Cell cell = row.createCell(cellnum);
        cell.setCellValue(rownum + cellnum);
      }
    }
    Row row = sh.createRow(10);
    // setting no precalculated value works just fine.
    Cell cell1 = row.createCell(0);
    cell1.setCellFormula("SUM(A1:A10)");

    // but setting a precalculated STRING value fails totally in SXSSF
    Cell cell2 = row.createCell(1);
    cell2.setCellFormula("SUM(B1:B10)");
    cell2.setCellValue("55");

    // setting a precalculated int value works as expected
    Cell cell3 = row.createCell(2);
    cell3.setCellFormula("SUM(C1:C10)");
    cell3.setCellValue(65);

    String name = wb.getClass().getCanonicalName();
    String ext = (wb instanceof HSSFWorkbook) ? ".xls" : ".xlsx";
    try {
      FileOutputStream output = new  FileOutputStream(fileBase + name + ext);
      wb.write(output);
      output.close();
    } catch (Exception ignored) {}
  }

  for (Workbook wb : wbs) {
    String name = wb.getClass().getCanonicalName();
    String ext = (wb instanceof HSSFWorkbook) ? ".xls" : ".xlsx";
    FileInputStream fis = null;
    int cellIdx = 0;
    try {
      fis = new FileInputStream(fileBase + name + ext);

      Workbook readFile = WorkbookFactory.create(fis);
      Sheet sheet = readFile.getSheetAt(0);
      Row row = sheet.getRow(sheet.getLastRowNum());

      for (Cell cell : row) {
        cellIdx++;
        String cellValue = null;
        switch (cell.getCellType()) {
          case Cell.CELL_TYPE_STRING:
            cellValue = cell.getRichStringCellValue().getString();
            break;
          case Cell.CELL_TYPE_FORMULA:
            cellValue = cell.getCellFormula();
            break;
        }
        cellValue = cellValue.isEmpty() ? null : cellValue;
        Assert.assertNotNull(cellValue);
      }
    } catch (AssertionFailedError e) {
      System.out.println("!!!!!!!!");
      System.out.println("Assertion Error on %s at cellIdx %d", name, cellIdx);
    } catch (Exception ignored) {
      // log exe
    } finally {
      if (fis != null)
        fis.close();
    }
  }

-- 
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org