Error when trying to use module load_workbook from openpyxl

Question

I'm attempting to open an xlsx-file using load_workbook from the module openpyxl. The code I have is:

import os
from openpyxl import load_workbook

def edit_workbook():
    path = r'C:\123 ABC\Excel documents'
    filename = 'filename.xlsx'

    os.path.join(path, filename)
    workbook = load_workbook(os.path.join(path, filename))

    ## Error is on the line above.

The complete error message I get is:

Traceback (most recent call last):

  File "<ipython-input-12-22dfdfc4e5e1>", line 1, in <module>
    workbook = load_workbook(os.path.join(path, filename))

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\reader\excel.py", line 214, in load_workbook
    apply_stylesheet(archive, wb) # bind styles to workbook

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py", line 176, in apply_stylesheet
    stylesheet = Stylesheet.from_tree(node)

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py", line 99, in from_tree
    return super(Stylesheet, cls).from_tree(node)

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 79, in from_tree
    obj = desc.expected_type.from_tree(el)

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 79, in from_tree
    obj = desc.expected_type.from_tree(el)

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 79, in from_tree
    obj = desc.expected_type.from_tree(el)

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 92, in from_tree
    return cls(**attrib)

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\styles\table.py", line 37, in __init__
    self.dxfId = dxfId

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\descriptors\base.py", line 69, in __set__
    value = _convert(self.expected_type, value)

  File "C:\Users\Stewie\Anaconda3\lib\site-packages\openpyxl\descriptors\base.py", line 59, in _convert
    raise TypeError('expected ' + str(expected_type))

TypeError: expected <class 'int'>

Anyone know what this can be?

Code should work, however the error here seems to indicate an issue with a Table in one of your xlsx file. The stylesheet seems to be of a format not recognized by openpyxl. Did you try to remove superfluous styling/elements from your files? — 301_Moved_Permanently, Sep 11 '17 at 08:50
There are **a lot** of elements in the workbook, including many figures. Unfortunately I can't remove anything. Do you know if there's another way module I can use to edit one cell in many sheets and save it as `xlsx`? As you already know, `xlwt` only saves as `xls`. — Stewie Griffin, Sep 11 '17 at 08:56
Are you able to load the file into Excel ok? You could try making a minor change in Excel and resaving. — Martin Evans, Sep 11 '17 at 08:58
I can open it in Excel, and with `xlrd` / `xlwt`. There are 200 files, so I don't want to do it all manually. — Stewie Griffin, Sep 11 '17 at 08:59
Re-reading your comment about all the fluff around the actual data, did you read `openpyxl` warning: "[openpyxl does currently not read all possible items in an Excel file so images and charts will be lost from existing files if they are opened and saved with the same name.](http://openpyxl.readthedocs.io/en/default/usage.html#read-an-existing-workbook)". Does it really suit your needs then? — 301_Moved_Permanently, Sep 11 '17 at 10:01
No, and no. I didn't read it, and it most certainly doesn't meet my need after all. Darn... Thanks for commenting! — Stewie Griffin, Sep 11 '17 at 10:27
Starting with openpyxl 2.5 charts are be preserved. The error is related to some styling on the table but without a file it's very difficult so more. — Charlie Clark, Sep 11 '17 at 13:59

score 2 · Answer 1 · answered Sep 17 '20 at 14:39

2

I received the same error. In my case, the Excel workbook has no charts, no filters, no formulas, no VBA. Only data. This workbook was generated by some third party software.

Turns out that the workbook was corrupted. I found that out when trying to save it after some very minor change. Once I resolved the corruption (by letting Excel save it to a different name as it requested), the openpyxl error disappeared.

answered Sep 17 '20 at 14:39

Marc Meketon

2,463
1
24
21

Hi there @Mark Meketon did you manage to do it automatically, I have more than 3.000 files and with all of them I'm getting this error. Or did you have to do it manually? I found an intermediare solution, but I have to change my libraries version, so it won't be too good to deploy to a server. – Luís Henrique Martins May 17 '22 at 14:40

Error when trying to use module load_workbook from openpyxl

1 Answers1

Linked