Bug 57699 - Support Strict OOXML files
Summary: Support Strict OOXML files
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.12-dev
Hardware: PC Linux
: P2 enhancement with 14 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
: 57914 63847 (view as bug list)
Depends on:
Blocks:
 
Reported: 2015-03-13 11:43 UTC by Nick Burch
Modified: 2022-03-15 12:30 UTC (History)
8 users (show)



Attachments
spam (377.25 KB, application/binary)
2018-06-26 06:48 UTC, tmasocha06
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Burch 2015-03-13 11:43:49 UTC
Office 2013 has added the option to save as "strict" ooxml files, which as reported in http://stackoverflow.com/questions/29023542/how-to-parse-strict-xlsx-file-in-java have a different core type

In r1666410 some sample strict xlsx files have been added, support is needed to support them (for reading at least)
Comment 1 Nick Burch 2015-03-13 18:01:59 UTC
It looks like some namespace munging is going to be required to properly support this. After making changes to ExtractorFactory and POIXMLDocumentPart to handle the differing core relationship type, it now fails at the xmlbeans level:

org.apache.xmlbeans.XmlException: error: The document is not a workbook@http://schemas.openxmlformats.org/spreadsheetml/2006/main: document element namespace mismatch expected "http://schemas.openxmlformats.org/spreadsheetml/2006/main" got "http://purl.oclc.org/ooxml/spreadsheetml/main"
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:399)

Caused by: org.apache.xmlbeans.XmlException: error: The document is not a workbook@http://schemas.openxmlformats.org/spreadsheetml/2006/main: document element namespace mismatch expected "http://schemas.openxmlformats.org/spreadsheetml/2006/main" got "http://purl.oclc.org/ooxml/spreadsheetml/main"
	at org.apache.xmlbeans.impl.store.Locale.verifyDocumentType(Locale.java:459)
	at org.apache.xmlbeans.impl.store.Locale.autoTypeDocument(Locale.java:364)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1280)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1264)
	at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
	at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument$Factory.parse(Unknown Source)

The purl namespace crops up in most of the xml files at least somewhere, so a general mapping solution is probably required if we want to take this further
Comment 2 Dominik Stadler 2016-02-27 10:24:56 UTC
*** Bug 57914 has been marked as a duplicate of this bug. ***
Comment 3 PJ Fanning 2016-07-17 12:58:18 UTC
http://pyxb.sourceforge.net/PyXB-1.2.2/bundles.html has a list of namespace URLs that could be used in a mapping class.
Comment 4 PJ Fanning 2016-07-17 13:28:16 UTC
Without spending much time on this, I have been unable to track down the XSDs with the purl namespaces (OOXML Strict). From accounts, they should be very similar to the OOXML Transitional schemas other than the namespaces.
2 approaches pop to mind.
1. In poi-ooxml-schemas, we could create XmlBeans for the OOXML Strict namespaces by using modified versions of the OOXML Transitional schemas.
2. support a transformation of the XML in input docs so that the OOXML Strict namespaces are replaced by OOXML transitional equivalents.
Comment 5 PJ Fanning 2016-07-17 15:51:17 UTC
I have added some basic prototype code to convert Strict OOXML files to https://github.com/pjfanning/ooxml-strict-converter - there is still a lot of work to do but I'm just posting it here if anyone wants to review what I'm doing.
Comment 6 Javen O'Neal 2016-07-18 00:34:47 UTC
Looks good so far.

In the interest of wanting to start committing this early so that we can update our unit tests to handle XSSF Strict:
* Are we planning on having XSSFWorkbook transparently handle strict workbooks or will be have a different class for that?
Will this be in the o.a.p.xssf.usermodel package or are we going to package it in o.a.p.xssf.extractor or create o.a.p.xssf.strict?

In the long term, I would like for POI to be able to read and write strict files without having to downconvert to non-strict. This probably affects how we go about packaging this--making it more than a distant examples or static utility converter class.
Comment 7 Dominik Stadler 2016-07-18 08:19:07 UTC
FYI, there is also a converter provided by Microsoft: https://www.microsoft.com/en-us/download/details.aspx?id=38828, could come in handy when doing development work on this topic.
Comment 8 PJ Fanning 2016-07-18 22:46:44 UTC
Hi Javen,
I can understand that we will want to be able to save POI documents using Strict OOXML but my focus for now is just on the down-porting to Transitional OOXML to allow parsing.
For now, I'm looking at a standalone utility to down-port but this could be plugged into XSSFWorkbook and XSSF extractor under the hood. They could either do some pre-processing of the input doc to determine if it is Strict OOXML and the down-port to a temp file and then read from the temp file.
My prototype code is working now for the SimpleStrict.xlsx in the POI test data folder.
I'll see about testing with more input files.
Comment 9 tmasocha06 2018-06-26 06:48:32 UTC
Created attachment 35988 [details]
spam
Comment 10 Piotr Wilkin 2019-05-20 14:26:49 UTC
Over two years have passed - has there been any work done on this / any milestone?
Comment 11 Dominik Stadler 2019-05-20 14:56:34 UTC
No, it seems none of the contributors needs it urgently enough to warrant spending time on it.

As this is a purely community supported project without commercial backing, your best bet to get progress on this will be to provide patches/time yourself if you can contribute in any way.
Comment 12 Piotr Wilkin 2019-05-20 15:25:04 UTC
Yeah, which is why I was asking :> there were some partial results done by some people, I'll see if something can be done.
Comment 13 sm462x 2019-06-30 18:44:56 UTC
I am interested in working on this issue. Will be willing to work with some if somebody is already is working on it otherwise I can take it independently.
Comment 14 Andreas Beeker 2019-10-15 06:05:32 UTC
*** Bug 63847 has been marked as a duplicate of this bug. ***
Comment 15 PJ Fanning 2021-02-13 21:48:39 UTC
There is some basic support in excel-streaming-reader, documented at https://github.com/pjfanning/excel-streaming-reader#ooxml-strict-format
Comment 16 Sebastian Böhm 2021-03-20 06:11:24 UTC
Good Morning, I like to contribute to the support of strict OOXML files (as I want to get rid of my workaround scripts). What is the current state? Are there already some changes/progress in any branch? If not, I would try to use the information from the comments here and start working on that.
Comment 17 PJ Fanning 2021-03-20 15:30:07 UTC
Hi Sebastian - supporting Strict OOXML format is a big undertaking and my impression is that this format has not really taken off (there aren't too many people asking about it). If you read my comment from 2021-02-13, you'll see that there is some support already but it is limited.

I would encourage you to spell out your plan because we may not necessarily want to support the strict format in poi-ooxml lib and might want to add a new jar dedicated to strict format - because we could end up bloating poi-ooxml by trying to support both formats in one jar.
Comment 18 Asbjørn S. 2022-03-15 10:43:04 UTC
Hello

I am in contact with MS about adding OOXML Strict conformance validation to their OOXML Validator (the respository is however hosted personally by an MS developer and it might in fact not be official software but the Open XML SDK links to it).

See the following correspondences for context:

https://github.com/mikeebowen/ooxml-validator-vscode/discussions/13
https://github.com/mikeebowen/ooxml-validator-vscode/issues/14

We are also very interested in having Apache POI support Strict data manipulation and validation.

For instance the prototype archival software Spreadsheet Complexity Analyser uses Apache POI to analyse spreadsheet documents and it could be improved by adding support for OOXML Strict conformance validation.

See repository:
https://github.com/RvanVeenendaal/Spreadsheet-Complexity-Analyser
Comment 19 PJ Fanning 2022-03-15 11:09:47 UTC
Thanks Asbjorn. Could you read https://poi.apache.org/devel/guidelines.html and in particular, the piece about Microsoft NDAs? If you have any non-public info - possibly even private emails with a Microsoft employee - we will be unable to accept any contributions from you.
Comment 20 PJ Fanning 2022-03-15 11:36:42 UTC
Asbjorn - supporting strict ooxml strict format is a massive task. poi-ooxml uses generated classes based on the OOXML Transitional XSDs. Generating the same classes for OOXML Strict XSDs and then having the all the XSSF classes or variants of them work with the OOXML Strict generated classes would be months of work and noone is volunteering to do it.

The best I can offer is https://github.com/pjfanning/excel-streaming-reader can read OOXML Strict files because it does not use the XSDs - but its API support is more limited than POI.

I do not have Excel installed but use https://onedrive.live.com/ to work with excel files. This hosted version of Excel doesn't even offer the ability to save in OOXML Strict format - only supports OOXML Transitional. As far as I can see OOXML Strict files are rare compared to OOXML Transitional files.
Comment 21 Asbjørn S. 2022-03-15 12:11:48 UTC
(In reply to PJ Fanning from comment #19)
> Thanks Asbjorn. Could you read https://poi.apache.org/devel/guidelines.html
> and in particular, the piece about Microsoft NDAs? If you have any
> non-public info - possibly even private emails with a Microsoft employee -
> we will be unable to accept any contributions from you.

Hello
Thanks for informing me.

I have not signed any NDA and all mentioned contact in my previous post has happened through their public repositories with MS developers.

Secondly, I am in verbal contact with one of their non-dev employees regarding our question of getting public information about what are their plans for future adoption and support of OOXML Strict. This is relevant for us to know, because we are a public archive and most documents in our government agencies are created in OOXML, and we want to know what are their future plans, before we potentially accept submission of OOXML documents into our archives. I hope you understand the relevancy for us to know this, and we also accept that any such knowledge should only be given to us, if it is suitable for public domain. I have no private e-mails answering this question and I have not received any verbal reply yet, but I have a future meeting in the calendar and I will make sure to mention, that I only want to receive information available to public. I will therefore share any information given, if anyone are interested.

I am very happy you inform me of this clause, so I will be aware of never signing any NDA with MS in the future. I hope our honesty, intentions and your guidance are sufficient for continuing conversation.
Comment 22 Asbjørn S. 2022-03-15 12:30:17 UTC
(In reply to PJ Fanning from comment #20)
> Asbjorn - supporting strict ooxml strict format is a massive task. poi-ooxml
> uses generated classes based on the OOXML Transitional XSDs. Generating the
> same classes for OOXML Strict XSDs and then having the all the XSSF classes
> or variants of them work with the OOXML Strict generated classes would be
> months of work and noone is volunteering to do it.
> 
> The best I can offer is https://github.com/pjfanning/excel-streaming-reader
> can read OOXML Strict files because it does not use the XSDs - but its API
> support is more limited than POI.
> 
> I do not have Excel installed but use https://onedrive.live.com/ to work
> with excel files. This hosted version of Excel doesn't even offer the
> ability to save in OOXML Strict format - only supports OOXML Transitional.
> As far as I can see OOXML Strict files are rare compared to OOXML
> Transitional files.


The lack of software support for conversion to and validation of OOXML Strict, might inform us to adopt OOXML Transitional for long-term preservation of archived spreadsheets, but it is not ideal, because we expect OOXML Transitional to be replaced by OOXML Strict in the future, hence we will have to migrate any already submitted OOXML Transitional spreadsheets. The question is really, will OOXML Strict ever replace OOXML Transitional!? As you say, OOXML Strict files are very rare today and software support both from MS and open-source community is insufficient.

Having Apache POI support conversion and validation would have tremendous impact from an archival point of view. I understand it is a huge task and no one is voluntering. I wanted to share our usercase, however, so the community will know demand exists, however marginal it might be.