Bug 58838 - Some cells are null when they aren't
Summary: Some cells are null when they aren't
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.13-FINAL
Hardware: PC Windows NT
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2016-01-12 08:22 UTC by Michele
Modified: 2016-02-15 08:47 UTC (History)
0 users

testFile.xls (151.50 KB, application/vnd.ms-excel)
2016-01-12 08:22 UTC, Michele
BLSintesi_VerReport.java (29.40 KB, text/plain)
2016-01-13 09:07 UTC, Michele
BLSintesi_XlsStyles.java (9.68 KB, text/plain)
2016-01-13 09:07 UTC, Michele
XlsUtil.java (6.84 KB, text/plain)
2016-01-13 09:08 UTC, Michele

Note You need to log in before you can comment on or make changes to this bug.
Description Michele 2016-01-12 08:22:16 UTC
Created attachment 33425 [details]

Hi all,
when I try to read some values into the xls (as attach) I obtain that a lot of cells result as null when they aren't.

Example code: print all the cells in the first row that contain a value (not null)

HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(pathToTestFileXls));
HSSFSheet sheet = workbook.getSheet("InitCharge");
int lastCol = CellReference.convertColStringToIndex("BC");
for (int c=0; c <= lastCol; c++) {
    HSSFCell cell = sheet.getRow(1).getCell(c);
    if (cell != null)
        System.out.print(new CellReference(1, c).formatAsString() + ", ");

A2, B2, AA2, AD2,

This is very strange! Only few cells doesn't contain a value!
Even stranger: open the xls with Excel, do NOTHING, click save, re-execute the same code, result:
A2, B2, Z2, AA2, AB2, AD2, AE2, AG2, AH2, AI2, AJ2, AK2, AL2, AM2, AN2, AO2, AP2, AQ2, AR2, AS2, AT2, AU2, AV2, AW2, AX2, AY2, AZ2, BA2, BB2, BC2, 

The xls used as input was generated previously with POI.

Comment 1 Dominik Stadler 2016-01-12 16:33:13 UTC
Can you also share the code that creates the strange Excel sheet?
Comment 2 Javen O'Neal 2016-01-12 17:47:57 UTC
Could you also try this with explicitly setting the CellCreationPolicy[1] to RETURN_NULL_AND_BLANK or RETURN_BLANK_AS_NULL before calling getCell?

[1] https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/class-use/Row.MissingCellPolicy.html
Comment 3 Michele 2016-01-13 09:06:47 UTC

The code "that creates the strange Excel sheet" is quite complex, I add it as attachment (BLSintesi_VerReport.java).

One of the cell that I need to read is writed in xls in BLSintesi_VerReport.java:174

I add as attachment also other two classes used to write the xls file (XlsUtil.java, BLSummaryReportXlsStyles.java).

Comment 4 Michele 2016-01-13 09:07:35 UTC
Created attachment 33430 [details]
Comment 5 Michele 2016-01-13 09:07:57 UTC
Created attachment 33431 [details]
Comment 6 Michele 2016-01-13 09:08:21 UTC
Created attachment 33432 [details]
Comment 7 Michele 2016-01-13 09:09:41 UTC
(In reply to Michele from comment #3)
> I add as attachment also other two classes used to write the xls file
> (XlsUtil.java, BLSummaryReportXlsStyles.java).

Sorry, the correct file is BLSintesi_XlsStyles.java and not BLSummaryReportXlsStyles.java.
Comment 8 Dominik Stadler 2016-01-13 20:10:09 UTC
Hmm, not easy to reproduce with all the SQL statements and other stuff, any chance of a more reduced test-case which can be run without any of your infrastructure?
Comment 9 Michele 2016-01-14 07:40:34 UTC
Unluckyly, I don't know why some cell results null and I am not able to create a small program for reproduce the same problem.

But if I had said I get the report from another program/office/collegue or the report was created with a tool different from POI instead create it by myself with POI?
The problem remains the same.

I don't understand why I can't read some cells when these cells have correctly a value (or a formula) and I can see these values using Excel. So the values exist (and they are saved) somewhere into the file and POI isn't able to read it, I don't know why.
Comment 10 Dominik Stadler 2016-02-15 08:47:25 UTC
initial attempt at a unit test for this:

    public void testBug58838() throws IOException {
        Workbook wb = HSSFTestDataSamples.openSampleWorkbook("58838.xls");

        Sheet sheet = wb.getSheet("InitCharge");
        Row row = sheet.getRow(1);

        //int lastCol = CellReference.convertColStringToIndex("BC");
        for (int c=0; c <= row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null) {
                System.out.print(new CellReference(1, c).formatAsString() + ", ");
            } else {
                System.out.print("(" + new CellReference(1, c).formatAsString() + "), ");