Created attachment 24020 [details] sample file generated by POI that is unreadable by Excel 2008 SP2 After upgrading to Excel 2008 SP2 for Mac, Excel is no longer able to read xlsx files generated by poi. I am including an email thread with Microsoft relating to this. I'm not sure if this is a POI issue, or a Microsoft one, but I wanted to document it here. --Leif ===== EMAIL THREAD START ==== Pat- Thanks for the response. The solution of using windows excel won’t work for us, as we have a batch system that generates literally thousands of excel files on a weekly basis that get distributed to users across our organization. The batch system runs on linux-based servers. This “sudden” breaking of excel is really an issue for us, especially with the automatic updating of excel. A quick and speedy resolution of this problem would help much. If there are any more details on the specifics of the contents of the xlsx file that Excel 2008 SP2 cannot read, it might be possible to change our excel generator to tweak the xlsx files we generate. I compared the contents of the “fixed” version ( windows->xlsb->xlsx) to the “incompatible” version by unzipping and comparing the contents of the embedded xml files. But there are may differences. Do you know what it is about the sample I sent you that Excel Mac doesn’t like? Thanks much, --Leif On 7/22/09 3:52 PM, "Patrick McMillan (MACBU)" <patmcmil@microsoft.com> wrote: Hi Leif, There’s some explanation for the cause of this at this link: http://support.microsoft.com/kb/972141. Basically, files that were generated in specific ways in particular versions of Windows Excel (for example, by opening a text file) and then saved to the .xlsx format will have this problem. We’re working on a solution to this problem, but in the meantime, if you have access to Windows Excel, you should be able to “fix” affected files by opening them in Windows Excel, saving out as .xlsb, and then saving them again as .xlsx. At that point, the files should open fine in Mac Excel again. Please let me know if that works for you. Thanks, Pat On 7/22/09 2:06 PM, "Leif Nelson" wrote: Hi Pat- I saw your posting on the Excel forum ( http://www.officeformac.com/ms/ProductForums/Excel/4544 ), and thought I could send you one of the files that I cannot open. For our business systems, we generate xlsx files programmatically with Java using apache POI and their new xlsx support. We have been happily using this for several months now with awesome results on Mac and Windows platforms for distributing our business data. Well, after the SP2 update, ALL of the files we generate are now unable to be opened by Excel 2008 for Mac. This is a huge issue for us. I have generated the simplest excel file possible. It works with Excel 12.1.9 and Excel 2007 for windows. For completeness, here’s the java code I’m using to generate the xlsx file: Thanks much, Leif Nelson package org.apache.poi.xssf.util; 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(); } } } ===== EMAIL THREAD END ======
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