Bug 55384 - Setting a precalculated String value on a formula cell clears out the cell using SXSSF
Summary: Setting a precalculated String value on a formula cell clears out the cell us...
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-08-07 20:58 UTC by Adrian K
Modified: 2016-07-27 18:17 UTC (History)
1 user (show)



Attachments
The generated spreadsheet showing the missing information in cell B11 (3.39 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-08-07 20:58 UTC, Adrian K
Details
HSSF spreadsheet generated using new code; shows NO bug (4.50 KB, application/vnd.ms-excel)
2013-08-15 15:45 UTC, Adrian K
Details
XSSF spreadsheet generated using new code; shows NO bug (3.40 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-08-15 15:45 UTC, Adrian K
Details
SXSSF spreadsheet generated using new code; SHOWS BUG (3.39 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-08-15 15:46 UTC, Adrian K
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Adrian K 2013-08-07 20:58:14 UTC
Created attachment 30713 [details]
The generated spreadsheet showing the missing information in cell B11

Trying to set a precalculated String value on a formula cell fails totally, leaving absolutely no information in that cell in the generated spreadsheet.  This is counter to what is written in the API for SXSSFCell (and for that matter, the Cell interface as well), which states: "Note, this method only sets the formula string and does not calculate the formula value. To set the precalculated value use setCellValue(double) or setCellValue(String)"



Here is a snippet of code that demonstrates this incorrect behavior.  This code will generate a spreadsheet with 11 rows, the last one (row #11) being sums of their respective columns.  In column A (cell A11), no precalculated value is set.  In column B (cell B11), a precalculated value is set as a String and here is where we see the failure, with absolutely no information existing in the generated spreadsheet.  In column C (cell C11), a precalculated value is set as an integer, and that performs as expected.
I've also attached a copy of the generated spreadsheet to this report.


  SXSSFWorkbook wb = new SXSSFWorkbook(100);
  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);
    }
  }
  org.apache.poi.ss.usermodel.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
  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);

  FileOutputStream out = new   FileOutputStream("/temp/sxssf.xlsx");
  wb.write(out);
  out.close();


Please also note that I have not had a chance to test this outside of the SXSSF component, so I'm unsure as to whether or not this bug is isolated to SXSSF or exists in the general case.

Thanks!
Comment 1 Nick Burch 2013-08-08 15:27:10 UTC
Any chance you could re-write this as a general unit test across all implementations? Probably something starting with

for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook, new SXSSFWorkbook() }) {
  ....

You'd need to use one hssf/xssf to read back afterwards to check, but otherwise it ought to be possible to make a generic test to check how it behaves across all versions
Comment 2 Adrian K 2013-08-08 15:34:18 UTC
>Any chance you could re-write this as a general unit test across all implementations?



Sure, I'll do that sometime within the next few days.  We'll see just how slow work is ;)
Comment 3 Adrian K 2013-08-15 15:43:35 UTC
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();
    }
  }
Comment 4 Adrian K 2013-08-15 15:45:37 UTC
Created attachment 30732 [details]
HSSF spreadsheet generated using new code; shows NO bug
Comment 5 Adrian K 2013-08-15 15:45:54 UTC
Created attachment 30733 [details]
XSSF spreadsheet generated using new code; shows NO bug
Comment 6 Adrian K 2013-08-15 15:46:16 UTC
Created attachment 30734 [details]
SXSSF spreadsheet generated using new code; SHOWS BUG
Comment 7 David North 2015-08-18 14:57:13 UTC
I've come across this too; it looks like not all of the setCellValue methods on SXSSFCell are taking appropriate care to avoid wiping any previously set formula. Will try and progress this via a test.
Comment 8 Dominik Stadler 2016-07-27 18:17:32 UTC
I have applied a fix for this via r1754328 by copying over the formula value when a new formula-cell-type is set.