Bug 57523

Summary: Some cells reporting as blank when they aren't
Product: POI Reporter: cowardlydragon <carlemueller>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: normal    
Priority: P2    
Version: 3.10-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Mac OS X 10.4   
Attachments: XSSF some nonblank cells reporting as blank

Description cowardlydragon 2015-02-02 18:05:24 UTC
Created attachment 32424 [details]
XSSF some nonblank cells reporting as blank

My code just exports the Attributes tab of the attached spreadsheet.

The problematic cells are:

Attribute Master tab

Row 16 

Columns N and P (values 500 and 10) reported as blank.


There are some others

When I open the XLS in LibreOffice, the affected rows are SLIGHTLY thicker vertically than others. Strange.

code:

  public List<String[]> extractList(XSSFSheet tab, Integer columnWidthLimit, boolean padToWidth, boolean skipHeaderRow)
  {
    List<String[]> rows = new ArrayList<>();
    Iterator<Row> rowIterator = tab.iterator();

    boolean first = true;
    while (rowIterator.hasNext())
    {
      if (skipHeaderRow && first)
      {
        first = false;
        continue;
      }

      Row row = rowIterator.next();
      Iterator<Cell> cellIterator = row.cellIterator();
      int cellCount = 0;
      List<String> rowData = new ArrayList<>();
      while (cellIterator.hasNext())
      {
        if (columnWidthLimit != null && cellCount >= columnWidthLimit)
        {
          break;
        }
        Cell cell = cellIterator.next();
        if (cellCount == 5)
        {
          System.out.println("" + rowData.get(4));
        }
        if (cellCount > 5)
        {
          System.out.print("::" + cellCount + "::" + cell.getCellType() + "::");
        }
        switch (cell.getCellType())
        {
          case Cell.CELL_TYPE_BOOLEAN:
            rowData.add("" + cell.getBooleanCellValue());
            break;
          case Cell.CELL_TYPE_NUMERIC:
            rowData.add("" + cell.getNumericCellValue());
            break;
          case Cell.CELL_TYPE_STRING:
            rowData.add("" + cell.getStringCellValue());
            break;
          case Cell.CELL_TYPE_BLANK:
            rowData.add("");
            break;
          case Cell.CELL_TYPE_FORMULA:
            rowData.add("#FORMULA");
            break;
          case Cell.CELL_TYPE_ERROR:
            rowData.add("#ERROR");
            break;
          default:
            rowData.add("#UNKNOWN");
            break;
        }
        if (cellCount > 5)
        {
          System.out.println(rowData.get(rowData.size() - 1));
        }

        cellCount++;
      }
      if (padToWidth && columnWidthLimit != null)
      {
        if (cellCount < columnWidthLimit)
        {
          for (int i = cellCount; i < columnWidthLimit; i++)
          {
            rowData.add("");
          }
        }
      }
      rows.add(rowData.toArray(new String[rowData.size()]));
    }
    return rows;
  }
}
Comment 1 Dominik Stadler 2015-02-11 15:56:55 UTC
Hmm, the code you pasted is very complicated and I am not sure how it shows the problem.

When I do a very simple check, I can read those values just fine:

    @Test
    public void bug57523() {
        Workbook wb = XSSFTestDataSamples.openSampleWorkbook("57523.xlsx");
        Sheet sheet = wb.getSheetAt(1);

        Row row = sheet.getRow(1);
        Cell cellN = row.getCell(CellReference.convertColStringToIndex("N"));
        assertEquals(500.0, cellN.getNumericCellValue(), 0.00001);
        
        Cell cellP = row.getCell(CellReference.convertColStringToIndex("P"));
        assertEquals(10.0, cellP.getNumericCellValue(), 0.00001);
    }


Based on that I am closing this as WORKSFORME for now, please work on your code to produce a simplified test-case which allows us to reproduce the problem more easily and then please reopen this bug.
Comment 2 cowardlydragon 2015-03-02 17:46:41 UTC
You only tested rowindex ONE

My bug report indicates the SIXTEENTH (maybe 15 if indexing from 0th position) row exhibits the issue.

... I'll make a better test case for you
Comment 3 cowardlydragon 2015-03-02 17:47:30 UTC
Oh, thanks for looking though.
Comment 4 cowardlydragon 2015-03-02 19:02:56 UTC
I'm going to doublecheck for stupidity and off-by-one errors
Comment 5 Javen O'Neal 2016-06-20 06:31:40 UTC
Moving back to WORKSFORME. See Dominik's unit test in r1749267.

Please reopen if you provide a simplified test-case that could allow us to reproduce a problem. The while loop and two rowIterator consumers are likely to cause an indexing problem. You should try rewriting your code using the for-each row iterator "for (Row row : sheet) { }" for simpler code.