Can't read multi-sheet file in macOS 'Numbers'

Issue #677 invalid
AltPez created an issue

Since upgrading to openpyxl 2.4.0, my generated xlsx files aren't fully readable in Mac OS X 'Numbers' and 'Preview' apps.

See attached program 'zz00.py'. This program generates output file 'zz00.xlsx'. When I open 'zz00.xlsx' with 'Microsoft Excel 2008 for Mac' and LibreOffice, I can see the data okay. However, when I open the same file with my Mac's built-in Numbers or Preview apps, I can't see any data.

Based on my experience with my full-sized program, I believe this problem did not exist in openpyxl 2.3.5.

Environment:

  • Mac OS X 10.11.6

  • Built-in Python 2.7.10

  • IDE: Visual Studio Code with Python extension, executing native Python 2.7.10

  • openpyxl 2.4.0

Comments (23)

  1. CharlieC

    What makes you think this is a bug with openpyxl and not with Numbers? I've fixed stuff before that Numbers couldn't deal with but I don't have a copy any more so I can't test it. The file you supplied is valid according to the Microsoft OOXML validator.

  2. AltPez reporter

    I agree it's arguably a bug in the Mac software if it can't interpret a file that has passed the validator.

    Update: I just reverted to openpyxl 2.3.5 and confirmed that 'zz00.py' produces Numbers-compatible output. I'll try to upload this.

    So the 2.4.0 release does introduce a regression in compatibility. Would-be-nice if openpyxl would continue to support this part of the spreadsheet-consuming world.

  3. CharlieC

    This is not a regression if we're following the specification. I suspect the problem that Numbers has may be related to some minor changes in packaging which we will not be backing out so the only solution may be to hope that Apple does their job. But it could be something entirely different. Without an error message there is little we can do about this. I suggest you contact Apple for additional information.

  4. CharlieC

    So, how do you think we should go about this? Have you been in touch with Apple about getting more information?

  5. AltPez reporter

    We have a few complications regarding reporting to Apple:

    • Apple just released new versions of Numbers for macOS and iOS. Ideally I should test against those latest updates. However, I don't expect to personally update my macOS/iOS installs for another 1-2 months.

    • I did check this on Numbers running in iCloud, which should have the same compatibility as the latest macOS/iOS releases. I confirmed a problem there. I filed a bug report with Apple. [ID 28409683, but this won't be visible outside me+Apple.]

    • Unfortunately, Apple is notoriously opaque about bug fixes & feature improvements. I think it'll be impossible to learn their priority & scheduling on this bug.

    • As with any large company, they'll have slow progress on non-emergency bugs. I s'pect the fastest possible fix will be six months from now. Most likely it'll be much longer than that.

    I suppose you could mark this as "suspended" or "will-not-fix" until I can further confirm the problem on the latest macOS/iOS. But I hope you'll consider other success metrics for openpyxl besides strict spec compliance. In every product I've ever worked on, there's always been consideration for de-facto standards/constraints and real-world user-base usage patterns.

  6. AltPez reporter

    Compatibility update for 'zz00.xlsx' generated by openpyxl 2.4.0:

    • The file is compatible with Google Sheets.

    • The file is not compatible with Numbers 2.5.4. on iOS 9.3.5.

    • The file is not compatible with Numbers running in iCloud/browser. Presumably this version has the same compatibility as the latest Numbers for macOS 10.12 and iOS 10.

  7. Thomas Deniau

    This is because you are producing relationships in workbook.xml.rels that look like this:

    <Relationship Id="rId2" Target="/xl/worksheets/sheet2.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" />
    

    If you open the file and re-save it with Excel you get this instead:

    <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml"/>
    

    Note that you made the Target an absolute path, while Excel uses relative paths.

    the OpenXML spec part 2 (Open Packaging Conventions) says this of the Target attribute

    The package implementer shall require the Target attribute to be a URI reference pointing to a target resource. The URI reference shall be a URI or a relative reference.

    So this use of an absolute path is invalid. Changing it to worksheets/sheet2.xml (and same for the other relationships) makes the file viewable again in Quick Look and Numbers.

  8. CharlieC

    As noted above I feared this would be the reason. Applications that target Numbers for MacOS / IOS should stick with 2.3.5 as the absolute paths are going to stay.

  9. Thomas Deniau

    Why should they stay if the spec says it should either be a full URI or a relative reference and doesn't include the possibility of an absolute reference? I might be missing some context here.

  10. CharlieC
    1. The specification is full of holes where the narrative section contains a description that is not part of the XML schema. :-/

    2. We need the path for every element in the package in at least three different places so absolute paths are by far the easiest to manage through normalisation.

  11. CharlieC

    I've been in touch with OOXML Working Group on this and they confirm that the use of absolute paths in relative references conforms to the the OPC specification:

    """ What do you mean by a relative reference? In RFC 3986, a relative reference is either an absolute-path reference (e.g., /foo/bar) or a relative-path reference (e.g., foo/bar). OPC already allows both. """

    This is definitely a bug in Apple's software. I have already filed a bug report and will update it accordingly.

  12. AltPez reporter

    I can confirm that Numbers 3.0 in iOS 10 (latest) loads the problematic file successfully. Apple says that Numbers 4.0 in macOS 10.12 Sierra also loads the file correctly. So Apple doesn't universally fail with this file.

    On the other hand: Numbers-in-iCloud can't open the file. Preview 9.0 in macOS 10.12 crashed trying to open the file (I did this in a retail store which had older Numbers installed).

    I'm willing to close this report if the team wishes. Or we can keep it alive to track Apple's progress or accumulate more compatibility info.

  13. CharlieC

    @AltPez Thanks for the info.

    So it looks like Apple have started to look at this. I guess we can't expect the fixes to be backported but most users tend to upgrade OS versions, hardware permitting, within a couple of months of release.

  14. CharlieC

    Problem is with Apple's programs / libraries, though the specification is unhelpful in this area and due to be updated as a result.

  15. CharlieC

    In a related issue I filed a bug with Apple about quicklook not being able to preview XLSX files produced by openpyxl. Apple has been in touch to say that the bug should be fixed in MacOS 10.13 (High Sierra). Would be great if someone could confirm this.

  16. Log in to comment