Created attachment 33425 [details] testFile.xls 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() + ", "); } Result: 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. Thanks, Michele
Can you also share the code that creates the strange Excel sheet?
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
Setting RETURN_NULL_AND_BLANK or RETURN_BLANK_AS_NULL has no effects. 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). Thanks
Created attachment 33430 [details] BLSintesi_VerReport.java
Created attachment 33431 [details] BLSintesi_XlsStyles.java
Created attachment 33432 [details] XlsUtil.java
(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.
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?
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.
initial attempt at a unit test for this: @Test 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() + "), "); } } wb.close(); }