.xlsm spread sheet returns errors

Issue #516 resolved
S R created an issue

I have a complex excel spreadsheet and I have the following python script

w = px.load_workbook('file1.xlsm', data_only=True)
p = w.get_sheet_by_name(name = 'forecasting')

a = p.cell('J11').value
b = p.cell('J12').value

print a
print b

and when it runs it throws up the following error

Traceback (most recent call last):
File "Test2.py", line 5, in <module>
w = px.load_workbook('file1.xlsm', data_only=True)
File "C:\Python27\lib\site-packages\openpyxl\reader\excel.py", line 152, in load_workbook
raise InvalidFileException(unicode(e))
openpyxl.utils.exceptions.InvalidFileException: ''

If it is of any help. the file throws up the following error when i use xlrd

Traceback (most recent call last):
File "Test1.py", line 5, in <module>
book = xlrd.open_workbook("file1.xlsm")
File "C:\Python27\lib\site-packages\xlrd\__init__.py", line 422, in open_workbook
File "C:\Python27\lib\site-packages\xlrd\xlsx.py", line 765, in open_workbook_2007_xml
x12book.process_stream(zflo, 'Workbook')
File "C:\Python27\lib\site-packages\xlrd\xlsx.py", line 254, in process_stream
meth(self, elem)
File "C:\Python27\lib\site-packages\xlrd\xlsx.py", line 359, in do_sheet
reltype = self.relid2reltype[rid]
KeyError: ''

Can you please let me know how to solve the issue?

Comments (19)

  1. CharlieC

    We need information about the traceback but it looks as if the file isn't an Excel file. Can you supply a test file?

  2. S R reporter

    Due to sensitivity, I have deleted the attachment. Can I please have an email address to send the attachment to? Thanks

  3. Hamza Khchine

    You must have received a new mail from bitbucket talking about a personal message from me. You must found a link to your bitbucket inbox.

  4. Hamza Khchine

    There is no sheet with the name "forecasting" and the file contains a three veryhidden sheet which are generated with an other tool than Ms Excel.

  5. CharlieC

    No, I don't think that's the problem.

    the veryHidden sheets don't seem to be plugged into the relationships:

        <sheet name="Module3" sheetId="12" state="veryHidden" r:id=""/>
        <sheet name="Module1" sheetId="13" state="veryHidden" r:id=""/>
        <sheet name="Module2" sheetId="14" state="veryHidden" r:id=""/>

    It doesn't make any difference whether these are hidden or not. If you change the attribute then these are just empty worksheets but I suspect they have something to do with the the macros in use.

    It's fairly easy to patch openpyxl so that the file can be read but these sheets would be lost if it were saved. Does that matter? The customXML stuff is also going to be lost.

  6. CharlieC

    This is now effectively resolved. Note the file supplied for testing will not roundtrip safely and will lost data.

    A full resolution will handle the intention of including non-extant worksheets (they are not in the manifest). Excel seems to takes this as an instruction to create them.

  7. S R reporter

    The worksheet that I am reading from is "4CASTING RAP" and that is not a hidden sheet. I had mistakenly changed the name when posting the issue.

    p = w.get_sheet_by_name(name = 'forecasting') 

    Does that change the issue?

    I am sorry, but I am not sure what you mean in the last comment Charlie Clark. Can you please explain what I need to do to read the required cells?

  8. CharlieC

    The note is because the bug isn't totally resolved. With the new code you'll have no trouble reading the cells but if you save the workbook it's possible it wouldn't work properly in Excel. VBA is awful.

    To use the code you'll need to checkout the 2.3 branch of openpyxl. This can be done in a one-liner with pip. Otherwise you'll have to wait for the next beta to be released.

  9. HPM2BP


    I have the exact same issue. However in my case I can't modify the package. We are running the code on a Jenkins server with several other scripts using Openpyxl, so we can't risk downgrading the version and ruining other jobs. Is there any possible solution?

    The exact errors I get are the following:

    C:\WINDOWS\system32>python C:\Users\HPM2BP\Desktop\SWexporter.py C:\temp\VW_MQB_BCM_SDP_work.xlsx C:\\Temp
    Traceback (most recent call last):
      File "C:\Program Files\Python 3.5\lib\site-packages\openpyxl-2.0.4-py3.5.egg\openpyxl\reader\excel.py", line 165, in load_workbook
        _load_workbook(wb, archive, filename, use_iterators, keep_vba)
      File "C:\Program Files\Python 3.5\lib\site-packages\openpyxl-2.0.4-py3.5.egg\openpyxl\reader\excel.py", line 221, in _load_workbook
        for sheet in detect_worksheets(archive):
      File "C:\Program Files\Python 3.5\lib\site-packages\openpyxl-2.0.4-py3.5.egg\openpyxl\reader\workbook.py", line 139, in detect_worksheets
        rel = rels[rId]
    KeyError: ''
    During handling of the above exception, another exception occurred:
    Traceback (most recent call last):
      File "C:\Users\HPM2BP\Desktop\SWexporter.py", line 23, in <module>
        wb = load_workbook(filePathToPAD, data_only=True, use_iterators=True)
      File "C:\Program Files\Python 3.5\lib\site-packages\openpyxl-2.0.4-py3.5.egg\openpyxl\reader\excel.py", line 168, in load_workbook
        raise InvalidFileException(unicode(e))
    openpyxl.exceptions.InvalidFileException: ''
  10. CharlieC

    Then you have a problem unless you can disable macros. These files do not conform to the specification.

  11. Log in to comment