| Summary: | Incorret dimension set in created workbook sheet xml file | ||
|---|---|---|---|
| Product: | POI | Reporter: | Mathias Rühle <math2306> |
| Component: | XSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | major | CC: | math2306, Ryan.Harris |
| Priority: | P1 | ||
| Version: | 3.9-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Linux | ||
| Bug Depends on: | |||
| Bug Blocks: | 61798 | ||
| Attachments: | Small java project build with maven to create example .xlsx file having incorrect dimension ref value set in workbook sheet xml file | ||
copy and paste error in source code:
it's
XSSFSheet.newSheet(){
not:
XSSFSheetworksheet.newSheet(){
Same bug described here: http://stackoverflow.com/questions/7204217/poi-creates-xlsx-files-incompletely-or-wronglythe-dimension-tag-in-the-xlsx-xml 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");
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");
(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. 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! 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. (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 Problem still exists for XSSFWorkbook in POI version 3.9 . 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 :(. In r1767096 we populate the dimension when writing out the XSSFSheet. Doing it for every change to rows/cells would likely be a performance problem so we only update this before writing out the file. (In reply to Dominik Stadler from comment #11) > In r1767096 we populate the dimension when writing out the XSSFSheet. Doing > it for every change to rows/cells would likely be a performance problem so > we only update this before writing out the file. Thanks, looking forward to the release containing this fix. (In reply to Mathias Rühle from comment #12) > (In reply to Dominik Stadler from comment #11) > > In r1767096 we populate the dimension when writing out the XSSFSheet. Doing > > it for every change to rows/cells would likely be a performance problem so > > we only update this before writing out the file. > > Thanks, looking forward to the release containing this fix. This code has a bug reported here: Bug 61798 |
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.