Fails to load Pivot without a dxfId

Issue #1134 resolved
Ulrich Luttner
created an issue

I came across xlsx files with PivotTables that couldn't be opened for writing.

load_workbook fails:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
C:\anaconda3\lib\site-packages\openpyxl\descriptors\base.py in _convert(expected_type, value)
     56         try:
---> 57             value = expected_type(value)
     58         except:

TypeError: int() argument must be a string, a bytes-like object or a number, not 'NoneType'

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)
C:\anaconda3\lib\site-packages\openpyxl\reader\excel.py in load_workbook(filename, read_only, keep_vba, data_only, guess_types, keep_links)
    284                     src = archive.read(pivot_path)
    285                     tree = fromstring(src)
--> 286                     pivot = TableDefinition.from_tree(tree)
    287                     pivot.cache = pivot_caches[pivot.cacheId]
    288                     ws.add_pivot(pivot)

C:\anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     78             if hasattr(desc, 'from_tree'):
     79                 #descriptor manages conversion
---> 80                 obj = desc.from_tree(el)
     81             else:
     82                 if hasattr(desc.expected_type, "from_tree"):

C:\anaconda3\lib\site-packages\openpyxl\descriptors\sequence.py in from_tree(self, node)
     84
     85     def from_tree(self, node):
---> 86         return [self.expected_type.from_tree(el) for el in node]
     87
     88

C:\anaconda3\lib\site-packages\openpyxl\descriptors\sequence.py in <listcomp>(.0)
     84
     85     def from_tree(self, node):
---> 86         return [self.expected_type.from_tree(el) for el in node]
     87
     88

C:\anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     98                 attrib[tag] = obj
     99
--> 100         return cls(**attrib)
    101
    102

C:\anaconda3\lib\site-packages\openpyxl\pivot\table.py in __init__(self, action, dxfId, pivotArea, extLst)
    501                 ):
    502         self.action = action
--> 503         self.dxfId = dxfId
    504         self.pivotArea = pivotArea
    505         self.extLst = extLst

C:\anaconda3\lib\site-packages\openpyxl\descriptors\base.py in __set__(self, instance, value)
     67         if ((self.allow_none and value is not None)
     68             or not self.allow_none):
---> 69             value = _convert(self.expected_type, value)
     70         super(Convertible, self).__set__(instance, value)
     71

C:\anaconda3\lib\site-packages\openpyxl\descriptors\base.py in _convert(expected_type, value)
     57             value = expected_type(value)
     58         except:
---> 59             raise TypeError('expected ' + str(expected_type))
     60     return value
     61

TypeError: expected <class 'int'>

It's pretty much the same problem as described for tableStyleElement in issue #902:

I changed in pivot/table.py:

dxfId = Integer()

to

dxfId = Integer(allow_none=True)

and gave it a try.

Seemed to work, I could write my files with the Pivots just fine.

I don't know if this may have other implications, though.

Would be good to have this fixed. Thanks ever so much, also for your cool library.

Comments (5)

  1. CharlieC

    Thanks for the report. Just checked the specification and this is indeed an optional field. Do you have a sample file (or just a sample pivot table for the tests? Haven't come across one so far.

  2. Ulrich Luttner reporter

    I can't give you what I was working on because it contains confidential data.

    Just tried to create a similar file with no luck so far. I'll see what I can do with the original data.

    Does Bitbucket have some kind of personal messaging?

  3. CharlieC

    Bitbucket used to have that but got rid of it years ago. I actually only need the PivotTable file in the XLSX file, and from that actually only the part with the format element in it. There's unlikely to be anything sensitive in that.

    If you like you can submit a PR based on the 2.5 branch containing relevant tests.

  4. Log in to comment