Summary: | POI generated xlsx files are not compatible with Excel 2008 Mac SP2 | ||
---|---|---|---|
Product: | POI | Reporter: | Leif Nelson <lnelson> |
Component: | XSSF | Assignee: | POI Developers List <dev> |
Status: | RESOLVED FIXED | ||
Severity: | normal | ||
Priority: | P2 | ||
Version: | 3.5-dev | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | Mac OS X 10.4 | ||
Attachments: |
sample file generated by POI that is unreadable by Excel 2008 SP2
poi-test-1.xlsx: a simple file generated by POI poi-test-2.xlsx testout.xlsb - testout.xlsx opened with MS Excel 2007 and "saved as" xlsb testout-converted.xlsx - testout.xlsb opened with Mac Excel 2008 SP2 and saved as xlsx (readable by MS Excel 2008 SP2) testout-fixed.xlsx - testout.xlsx with modifications to make it openable by Excel 2008 SP2 app_xml_1.xlsx |
Description
Leif Nelson
2009-07-22 15:14:42 UTC
Hi Leif,
I have confirmed your trouble, and am very unhappy with Microsoft.
I am very concerned about this quote from the link:
> This problem occurs when Excel 2008, Word 2008, or PowerPoint 2008 has detected
> that the file that you are trying to open abides by an interpretation of the
> ISO29500 Office Open XML (OOXML) standard. Excel 2008, Word 2008, and
> PowerPoint 2008 do not support this standard.
We'll see if we can find the correct interpretation. This certainly will have the attention of the Apache POI PMC.
Regards,
Dave
I have a feeling it has to do with Bugs 47432 and 46419. If yes, then the problem is not how we generate SpreadsheetML, but how we build OPC packages. Leif, can you please check the two attached files : - poi-test-1.xlsx. This is a simple test created by POI. Mac Excel 2008 should fail to read it. OpenOffice 3.0 can't read it either. - poi-test-2.xlsx. This is a "fixed" version of poi-test-1.xlsx, readable by OpenOffice 3.0 The fix: 1. unzip a file generated by POI 2. edit /_rels/.rels and remove leading slashes from Targets: Target="/xl/workbook.xml" --> Target="xl/workbook.xml" Target="/docProps/core.xml" --> Target="docProps/core.xml" all core relationships must be fixed. 3. zip the edited files into a .xlsx file Regards, Yegor Created attachment 24023 [details]
poi-test-1.xlsx: a simple file generated by POI
Created attachment 24024 [details]
poi-test-2.xlsx
(In reply to comment #4) > Created an attachment (id=24024) [details] > poi-test-2.xlsx Hi Yegor- I tried both xlsx files (poi-test-1.xlsx and poi-test-2.xlsx) with Mac Excel 2008 SP2, and neither one can be opened. Both give the error: "Microsoft Excel cannot open the file. You may need to download the latest updates for Office for Mac. Do you want to visit the Microsoft Web site for more information?" poi-test-1.xlsx cannot be opened by OpenOffice, but poi-test-2.xlsx can be opened by OpenOffice, as you pointed out. I will attach a file that has been converted to be readable by Mac Excel 2008 SP2 by using Windows Excel 2007 to open the poi-generated xlsx file (testout.xlsx). Then saving as xlsb, then opening with Mac Excel and saving as xlsx. I will call this file testout-converted.xlsx. Thanks much! -Leif Created attachment 24025 [details]
testout.xlsb - testout.xlsx opened with MS Excel 2007 and "saved as" xlsb
Created attachment 24026 [details]
testout-converted.xlsx - testout.xlsb opened with Mac Excel 2008 SP2 and saved as xlsx (readable by MS Excel 2008 SP2)
Hi Leif, Was your original file attached here converted by the program you just attached to https://issues.apache.org/bugzilla/show_bug.cgi?id=46419 ? Regards, Dave (In reply to comment #8) > Hi Leif, > > Was your original file attached here converted by the program you just attached > to https://issues.apache.org/bugzilla/show_bug.cgi?id=46419 ? > > Regards, > Dave Dave- Nope. These are two separate issues, and I was "reminded" about the OpenOffice issue by Yegor. :-) The original file included here was generated with the code snippet in the first bug report (in the email thread). It was generated by poi-3.5beta6. I only included the program in the other bug to show the workaround I had implemented to deal with OpenOffice! The other attachments I added to this bug (testout.xlsb and testout-converted.xlsx) were created as follows: 1) testout.xlsx (created by code snippet below - poi-3.5beta6) 2) testout.xlsb (testout.xlsx (from 1) opened with Windows Excel 2007 and saved as xlsb) 3) testout-converted.xlsx (testout.xlsb (from 2) opened with Mac Excel 2008 SP2 and saved as xlsx) Hope that's more clear! Thanks, --Leif import java.io.File; import java.io.FileOutputStream; import java.io.OutputStream; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class PoiTest { public static void main(String[] args) { try { XSSFWorkbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("Hi there"); OutputStream out; out = new FileOutputStream(new File("testout.xlsx")); wb.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } } } I have found, that if I change the testout.xlsx file contents in the following ways, I can get the file to open with Mac Excel 2008 SP2: 1) Add this line to _rels/.rels <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="/docProps/app.xml"/> 2) Add this line to [Content_Types].xml <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/> 3) Add this file: docProps/app.xml <?xml version="1.0" encoding="UTF-8"?> <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"> <Application>Microsoft Macintosh Excel</Application> <DocSecurity>0</DocSecurity> <ScaleCrop>false</ScaleCrop> <HeadingPairs> <vt:vector size="2" baseType="variant"> <vt:variant> <vt:lpstr>Worksheets</vt:lpstr> </vt:variant> <vt:variant> <vt:i4>1</vt:i4> </vt:variant> </vt:vector> </HeadingPairs> <TitlesOfParts> <vt:vector size="1" baseType="lpstr"> <vt:lpstr>Sheet0</vt:lpstr> </vt:vector> </TitlesOfParts> <LinksUpToDate>false</LinksUpToDate> <SharedDoc>false</SharedDoc> <HyperlinksChanged>false</HyperlinksChanged> <AppVersion>12.0000</AppVersion> </Properties> I will attach a file modified in this way: testout-fixed.xlsx --Leif Created attachment 24028 [details]
testout-fixed.xlsx - testout.xlsx with modifications to make it openable by Excel 2008 SP2
Here is a simpler "fix" for files to make them openable in Excel 2008 SP2 1) Add this line to _rels/.rels <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="/docProps/app.xml"/> 2) Add this line to [Content_Types].xml <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/> 3) Add this file: docProps/app.xml with contents: <?xml version="1.0" encoding="UTF-8"?> <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPro psVTypes"> <Application>Microsoft Excel</Application> </Properties> In response to a query from Dave, the only entries in the app.xml file that work are: <Application>Microsoft Excel</Application> and <Application>Microsoft Macintosh Excel</Application> If I try <Application>Apache POI 3.5</Application> I get the same error as before, Microsoft Excel Mac 2008 SP2 cannot open the file. --Leif (In reply to comment #13) > In response to a query from Dave, the only entries in the app.xml file that > work are: > <Application>Microsoft Excel</Application> > and > <Application>Microsoft Macintosh Excel</Application> > > If I try > <Application>Apache POI 3.5</Application> > I get the same error as before, Microsoft Excel Mac 2008 SP2 cannot open the > file. > > --Leif Also... If I try <Application>Apache POI 3.5</Application> in app.xml, Microsoft Excel 2007 for Windows opens the file with no problem. In fact, every variation of all these files is openable by Microsoft Excel 2007 for Windows. --Leif As a workaround, I have found that you can start by reading in a minimal workbook saved out Excel 2008. Delete its worksheet and then proceed as if you just created it with new XSSFWorkbook(). Hi Leif, Please try app_xml_1.xlsx, now with extended properties (app.xml). The code to generate is as follows: XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sh = wb.createSheet("Sheet1"); sh.createRow(0).createCell(0).setCellValue("Hi There!"); POIXMLProperties.ExtendedProperties ext = wb.getProperties().getExtendedProperties(); ext.getUnderlyingProperties().setApplication("Microsoft Excel"); FileOutputStream out = new FileOutputStream("app_xml_1.xlsx"); wb.write(out); out.close(); Hope it will help. Regards, Yegor Created attachment 24030 [details]
app_xml_1.xlsx
test .xlsx file with extended properties (app.xml)
Yegor, The app_xml_1.xlsx file works fine for me. Dave (In reply to comment #18) > Yegor, > > The app_xml_1.xlsx file works fine for me. > Excellent! Now we know that Excel 2008 for Mac requires the extended property Application to be set. Valid values are either "Microsoft Excel" or "Microsoft Macintosh Excel". This fully confirms the previous Leif's observations. From common sense point of view it is absolutely ridiculous - it is just a check that the creator application is Excel. Yegor Yegor- Thanks so much! I initially tried your sample code with poi-3.5beta6 and got an exception, but when I switched to the trunk, it works well. I was able to run the sample code, generate an xlsx file and open it with Mac Excel 2008 SP2. Great news! Now, hopefully they'll fix Excel 2008 so that it'll read all of the files we have previously generated and distributed. :-) Thanks again for the quick turn-around on this! --Leif Leif, I fixed POI to generate files compatible with Excel 2008 Mac sp2. The fix was committed in r79735. I hope that Microsoft will release a fix for Excel Mac soon. Meantime, you can patch existing files with the following code: XSSFWorkbook wb = new XSSFWorkbook(path); POIXMLProperties.ExtendedProperties ext = wb.getProperties().getExtendedProperties(); ext.getUnderlyingProperties().setAppVersion("Microsoft Excel"); FileOutputStream out = new FileOutputStream(path); wb.write(out); out.close(); Yegor Actually this is r797350 - http://svn.apache.org/viewvc?view=rev&revision=797350 Fantastic! I have placed this code on our servers, and now users can open excel files with Mac Excel 2008 SP2! Thanks so much for the quick response! --Leif (In reply to comment #21) > Leif, > > I fixed POI to generate files compatible with Excel 2008 Mac sp2. The fix was > committed in r79735. > > I hope that Microsoft will release a fix for Excel Mac soon. Meantime, you can > patch existing files with the following code: > > XSSFWorkbook wb = new XSSFWorkbook(path); > > POIXMLProperties.ExtendedProperties ext = > wb.getProperties().getExtendedProperties(); > ext.getUnderlyingProperties().setAppVersion("Microsoft Excel"); > > FileOutputStream out = new FileOutputStream(path); > wb.write(out); > out.close(); > > > Yegor I believe this should be changed to: ext.getUnderlyingProperties().setApplication("Microsoft Excel"); The "AppVersion" field has something like "12.000" in it in my samples. Sorry I didn't notice this earlier, --Leif See also my additional comments on the dev@poi.apache.org mailing list archive. http://mail-archives.apache.org/mod_mbox/poi-dev/200907.mbox/%3c78E528D1-9EFB-422C-B45E-946F9D1E9D2E@jmlafferty.com%3e Blair Neumann Macintosh Business Unit, Microsoft Microsoft has released Office 2008 12.2.1 which fixes this issue. http://support.microsoft.com/kb/974170 --Leif Very good. I changed XSSF and XWPF to set the Application setting to "Apache POI". Yegor |