Bug 60258 - XLSX files created in POI unreadable in Excel 2013+ due to AbsolutePath in workbook
Summary: XLSX files created in POI unreadable in Excel 2013+ due to AbsolutePath in wo...
Status: NEEDINFO
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-10-14 18:40 UTC by Mr. West
Modified: 2017-11-11 07:18 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Mr. West 2016-10-14 18:40:32 UTC
I'm creating spreadsheets in Apache POI 3.15 (I was using 3.9 but upgraded once I saw that CT_FileVersion was rendered useless) for use on Excel 2013 using the XSSF format and I receive the "unreadable content" message when opening the workbook. After allowing Excel to find the issues, I went and saw that it discovered that the workbook had (1) an author, (2) Absolute Path.

The author issue was easily resolved by modifying the CoreProps but the Absolute Path issue is another headache. So Excel 2013 (and up) are using a newer schema (http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac Schema) that includes the CT_AbsolutePath attribute that does not exist in the schemas used by POI.

Upon further investigation, I let Excel fix the issue and compared the workbook.xml files of a "broken" spreadsheet and a fixed one. The difference is that Excel 2013 adds two namespaces (one with an attribute/value) and an element with 3 attributes/values.

xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15"
xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"
<fileVersion appName="xl" lastEdited="6" lowestEdited="6">

The x15 and fileVersion items come into play with this explanation:

"Workbook.xml contains information about the Excel file such as worksheet names, window height and width parameters, and a bit of other information.  For the most part, this XML file appears to be similar across files created using Excel 2007, 2010, and 2013, however, there is one key difference: the "x15ac:absPath" element.  The "x15ac:absPath" element is a child element of "mc:Choice" (which is a child element of "mc:AlternateContent") and contains an attribute called "url" that corresponds to the last saved location of the spreadsheet."

AND 

"If you know that a spreadsheet was created using Excel 2013 but are unable to find the last saved location metadata, it's possible that the spreadsheet was last saved in a version of Excel other than 2013.  This can be verified through the "fileVersion" element, which is the first child element of "workbook".  The "fileVersion" element includes an attribute called "lastEdited" and, according to Microsoft documentation, the "lastEdited" attribute "specifies the version of the application that last saved the workbook". Interestingly, the value specified in the "lastEdited" attribute is not consistent with the application version of Excel (i.e. 2007=12.x, 2010=14.x, etc.).  Instead, this value is a single-digit numeral corresponding to a particular version of Excel.  I've ran some quick tests using 2007, 2010, and 2013 and summarized the corresponding fileVersion values for each Excel version in the table below."

FYI: the value "6" on lastEdited and lowestEdited refers to Excel 2013.

I have been trying to edit the xml before the download of the spreadsheet since I'm using JDeveloper 11g and am sending it through a HttpServletResponse outputstream but that's proving to be ...not possible for me :). ANY assistance with this would be awesome.

Thanks.
Comment 1 Dominik Stadler 2017-11-11 07:18:47 UTC
Sorry for the late response, but can you provide some sample code that allows us to reproduce the problem?