Mailing-List: contact dev-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Developers List" <dev@poi.apache.org>
From: bugzilla@apache.org
To: dev@poi.apache.org
Subject: [Bug 60258] New: XLSX files created in POI unreadable in Excel 2013+
 due to AbsolutePath in workbook
Date: Fri, 14 Oct 2016 18:40:32 +0000
Message-ID: <bug-60258-47293@https.bz.apache.org/bugzilla/>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit
Auto-Submitted: auto-generated
MIME-Version: 1.0
archived-at: Fri, 14 Oct 2016 18:40:43 -0000

https://bz.apache.org/bugzilla/show_bug.cgi?id=60258

            Bug ID: 60258
           Summary: XLSX files created in POI unreadable in Excel 2013+
                    due to AbsolutePath in workbook
           Product: POI
           Version: 3.15-FINAL
          Hardware: PC
            Status: NEW
          Severity: major
          Priority: P2
         Component: XSSF
          Assignee: dev@poi.apache.org
          Reporter: tywdev@gmail.com

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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org