Bug 46207

Summary: [POI 3.5 Beta 3] MS Excel 2007 open workbook (saved by XSSFWorkbook ) with errors
Product: POI Reporter: Matthew <matthew.knl>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.5-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Excel 2007 prompts error
Original file
Result error file
Patch to fix
Result fixed file

Description Matthew 2008-11-13 19:37:48 UTC
Created attachment 22863 [details]
Excel 2007 prompts error

I have an Excel 2007 file (attachment: TestPOI1.xlsm), after it is opened and saved by following codes, it prompts errors in Excel 2007 when I open it (attachment: 2008-11-14_103611.jpg):

--
Workbook wb = new XSSFWorkbook(source);
FileOutputStream outStream = new FileOutputStream(target);
wb.write(outStream);
--

The result file is attached: TestPOI1_error.xlsm

Problems
========
I checked the file structure and I have following findings:

1. In the content of /xl/workbook.xml, it is referencing an externalReference element with id:rId4, but /xl/_rels/workbook.xml.rels doesn't contains that relationship. externalLinks relationships are missing (which can be found inside TestPOI1.xlsm)

2. In the content of /xl/worksheets/sheet1.xml, it is referencing an pageSetup element with id:rId1, but /xl/worksheets/_rels/sheet1.xml.rels rId1 is a vmlDrawing and printerSetting relationships are missing (which can be found inside TestPOI1.xlsm)

Solutions
=========

I have made a patch to solve above problem (attachment: fix_printer_setup_drawing_named_range.patch), which the result saved file (attachment: TestPOI1_fix.xlsm) can be opened in Excel 2007 successfully with errors.

I made following changes:

1. Load the external links and printer settings when opening the workbook

2. Since the external link relationship type the target URI is an external file, I made some changes on XSSFRelation to support external target mode so that it won't find the target package part (it doesn't exist in the package).

3. Save the external links and printer settings when opening the workbook

4. Other problem I found is the relationship id problem: while saving a workbook, existing codes will generate the relationship id for drawings and activex controls of a worksheet. Since the drawings and activex controls are placed inside common folder /xl/drawings/, /xl/activeX/ which are shared by different sheets, there is a chance of id conflicts, so I modified part of XSSFWorkbook.save(...)

5. Although I load and save the external links and printer settings elements, but the relationship id will be changed, so I changed XSSFSheet to enabled set the page setup id and printer setting id.

--

Please review my patch since it maybe hardcoded and doesn't following your model design concept correctly, since it is "result oriented" :P Hope it can help the POI-OOXML development.
Comment 1 Matthew 2008-11-13 19:39:17 UTC
Created attachment 22864 [details]
Original file
Comment 2 Matthew 2008-11-13 19:39:37 UTC
Created attachment 22865 [details]
Result error file
Comment 3 Matthew 2008-11-13 19:39:59 UTC
Created attachment 22866 [details]
Patch to fix
Comment 4 Matthew 2008-11-13 19:40:17 UTC
Created attachment 22867 [details]
Result fixed file
Comment 5 Yegor Kozlov 2008-11-13 22:50:29 UTC
Try the latest svn build. It works OK for me.
Read-write round trip was significantly  improved since 3.5-beta3. 

daily builds can be downloaded from http://encore.torchbox.com/poi-svn-build/

Yegor