3

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)?

rirhs
  • 297
  • 2
  • 4
  • 9

3 Answers3

3

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).

Mathias
  • 75
  • 8
0

I'm not aware of any open bugs in POI relating to incorrect dimensions records. If you're able to produce a small testcase that generates a simple xlsx file using XSSF with the problem, I'd strongly suggest you open a new bug in the POI Bugzilla so it can get tracked and fixed.

The most common reasons for unreadable content on the mailing list is either people using old versions of the library (where there was a bug that is now fixed), or people trying to craft their own xlsx files largely by hand (using the BigGridDemo style approach). The latter is normally solved by switching to the new SXSSF way of doing streaming writing.

(Excel will normally give you an xml file with details of the error when it fixes a problematic file up, without knowing what it said it's hard to be sure exactly what's wrong)

Gagravarr
  • 47,320
  • 10
  • 111
  • 156
0

I have the same problem. The strange thing is that Open Office open this files without any errors (but losing grouping for some strange reason). I disabled the grouping in the code and everything works fine. So it looks like it is Apache POI rows grouping bug.

MaSEL
  • 505
  • 1
  • 5
  • 20