Bug 53611 - Incorret dimension set in created workbook sheet xml file
Incorret dimension set in created workbook sheet xml file
Status: NEW
Product: POI
Classification: Unclassified
Component: XSSF
3.9
PC Linux
: P1 major with 7 votes (vote)
: ---
Assigned To: POI Developers List
:
Depends on:
Blocks:
  Show dependency tree
 
Reported: 2012-07-27 12:45 UTC by Mathias Rühle
Modified: 2014-05-27 14:19 UTC (History)
2 users (show)



Attachments
Small java project build with maven to create example .xlsx file having incorrect dimension ref value set in workbook sheet xml file (20.00 KB, application/zip)
2012-07-27 12:45 UTC, Mathias Rühle
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mathias Rühle 2012-07-27 12:45:17 UTC
Created attachment 29120 [details]
Small java project build with maven to create example .xlsx file having incorrect dimension ref value set in workbook sheet xml file

After creating and writing an XSSFWorkbook with some rows and cells to a file the sheet XML file included in .xlsx archive has an incorrect value for attribute 'ref' of tag 'dimension':

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


It's always set to 'A1' no matter how many rows and cells are actually in the sheet. The dimension is only initialized on creation of worksheet in class XSSFSheet:

XSSFSheetworksheet.newSheet(){
 ...
 worksheet.addNewDimension().setRef("A1");
 ...
}

I included a example java app showing the. Build it with maven:

cd poi-bug
mvn package
java -jar target/poi-bug-0.1-SNAPSHOT-jar-with-dependencies.jar

After successful execution the project folder contains the file 'poi-bug.xlsx'. Unzip file and open 'xl/worksheets/sheet1.xml' containing the dimension tag.
Comment 1 Mathias Rühle 2012-07-27 12:52:19 UTC
copy and paste error in source code:

it's 

XSSFSheet.newSheet(){

not:
XSSFSheetworksheet.newSheet(){
Comment 3 Ryan 2012-09-18 16:46:42 UTC
I have found a workaround to this bug.  You can set the dimension reference yourself.  I am using SXSSF in my application, but the workaround is possible for either SXSSF or XSSF.

It should also be an easy bug to fix for POI.

Here is the code snippet: (wb is a SXSSFWorkbook)

CTWorksheet ctSheet = wb.getXSSFWorkbook().getSheetAt(0).getCTWorksheet();
ctSheet.getDimension().setRef("A1:D47");
Comment 4 Mahesh 2012-09-26 13:02:46 UTC
Getting following error for POI 3.7-
"Excel found unreadable content in 'out.xlsx'. Do you want to recover the contents of this workbook ? If you trust the source of this workbook, click Yes."

I have tried searching the fix for it, please paste the fix if anyone knows.

I have tried following remedy, but in vain-
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Q");			
sheet.getCTWorksheet().getDimension().setRef("A1:K47");
Comment 5 Ryan 2012-09-26 16:48:49 UTC
(In reply to comment #4)
> Getting following error for POI 3.7-
> "Excel found unreadable content in 'out.xlsx'. Do you want to recover the
> contents of this workbook ? If you trust the source of this workbook, click
> Yes."
> 
> I have tried searching the fix for it, please paste the fix if anyone knows.
> 
> I have tried following remedy, but in vain-
> XSSFWorkbook wb = new XSSFWorkbook();
> XSSFSheet sheet = wb.createSheet("Q");			
> sheet.getCTWorksheet().getDimension().setRef("A1:K47");

Mahesh - remove setting the reference from your code, generate an .xlsx file, and make sure that the file is readable by Excel.  In other words, did your file generation work until you added the reference change?  Incorrect references will not cause the file to be unreadable.
Comment 6 Mahesh 2012-09-27 06:08:07 UTC
Thanks! Ryan for your reply,

I am getting this error even I remove this reference code. I have added it to see if its work for this issue.

I am generating report and have kept all the columns names less than 31 character including sheet name.

Your help will be much appreciated!
Comment 7 Ryan 2012-09-27 15:49:51 UTC
Then your problem has nothing to do with this bug, and you just have bad Excel generation code.  This bug has nothing to do with unreadable content.
Comment 8 Ryan 2012-09-27 15:53:07 UTC
(In reply to comment #6)
> Thanks! Ryan for your reply,
> 
> I am getting this error even I remove this reference code. I have added it
> to see if its work for this issue.
> 
> I am generating report and have kept all the columns names less than 31
> character including sheet name.
> 
> Your help will be much appreciated!

Also, make sure you let your program finish writing.  If you don't check the progress or notify the user when it is done, then the excel generation could still be occurring.  I made this mistake by trying to open it too fast before the program was finished.  You can use task manager to see when it stops using CPU and then its done.  My program takes around 1 min 15 sec to write a .xlsx file that is 200,000 rows by 41 columns
Comment 9 Mathias Rühle 2013-04-08 12:34:11 UTC
Problem still exists for XSSFWorkbook in POI version 3.9 .
Comment 10 Mathias Rühle 2013-04-08 12:41:49 UTC
Thanks, Ryan for the workaround. Works. Now IBM SPSS Statistics imports all columns and rows without manual help. Hope they will fix it soon in POI. XSSFSheet.getCTWorksheet is annotated with @Internal marking this as possibly being removed in future releases :(.