|Summary:||[POI 3.5 Beta 3] MS Excel 2007 open workbook (saved by XSSFWorkbook ) with errors|
|Component:||XSSF||Assignee:||POI Developers List <dev>|
Excel 2007 prompts error
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.