POI Creates XLSX files incompletely or wrongly(the dimension tag in the XLSX XML reads only A1 after creation instead of A1:500Z)

Question

I write XLSX files through the apache POI library..(I have tried it by using POI 3.8 beta1 to beta 4). When i try to open the XLSX files through Excel 2007 it gives me an error that the sheet has unreadable content, and then excel asks me to whether it should repair it.

My problem is that my program generates a XLSX file and an another program reads it through the POI event API. When I read the dimension XML through event API I find that instead of being A1:500Z(suppose the excel has 500 rows and 26 cols) it reads only A1. I have seen that the sheet's dimensions are A1 only when the sheet is empty, but in my case the sheet has 500 rows of data. So there is a problem in the XLSX writing code which causes the dimension to be set incorrectly. ( and I think this must be the reason why excel throws the unreadable content error ).

I went through the POI mailing lists which mentioned this unreadable content error but to no avail.

The XLSX file creation code is fairly simple so I doubt there must be anything wrong in there, (and this erroneous behavior is only coming in cases where the sheet to be written has data greater the 100s of rows).

Has anybody experienced such a problem? Or is there anything wrong inherently with the POI libs (their mailing lists do show unreadable content error)?

Answer 1

Having same problem here. If you unzip xlsx and look into file 'xl/worksheets/sheet1.xml' you'll always read

<worksheet>
 <dimension ref="A1"/>
 <sheetViews>
  <sheetView ...

no matter how many rows and cells have being added.

I filed a bug on apache's bug tracker: https://issues.apache.org/bugzilla/show_bug.cgi?id=53611

[Edit]
On the bugtracker a workaround solution was posted by Ryan https://issues.apache.org/bugzilla/show_bug.cgi?id=53611#c3

CTWorksheet ctSheet = wb.getXSSFWorkbook().getSheetAt(0).getCTWorksheet();
ctSheet.getDimension().setRef("A1:D47");

So you have to keep track of the columns and rows you created and set the dimension tag for each sheet yourself.

Here's my resulting code:

private void updateDimensionRef(Sheet sheet, int columnIndex, int rowNumber) {
    ((XSSFSheet) sheet).getCTWorksheet().getDimension()
        .setRef("A1:" + CellReference.convertNumToColString(columnIndex) + rowNumber);
}

[Edit #2]

It has been fixed in version 3.16-beta1. See Changelog (Bug #53611).