Another Google Sheets pivot table crash

Issue #1213 resolved
Ben Webb created an issue

Following on from https://bitbucket.org/openpyxl/openpyxl/issues/1175/google-sheets-pivot-table-causes-openpyxl, it looks like our actual data file causes a different error than the simple example.

Here's the file: https://docs.google.com/spreadsheets/d/1p-6zgIEXmLWSixDuOH_p6xhnPC64pri-acu5sL8I-OE/export?format=xlsx (also attached)

Here's the error:

Python 3.6.7 (default, Oct 22 2018, 11:32:17) 
[GCC 8.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from openpyxl import load_workbook
>>> wb = load_workbook('CoVE IATI 2.03 #2019-02-04 #pivot #published.xlsx')
Traceback (most recent call last):
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/descriptors/base.py", line 57, in _convert
    value = expected_type(value)
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:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/reader/excel.py", line 312, in load_workbook
    reader.read()
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/reader/excel.py", line 274, in read
    self.read_worksheets()
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/reader/excel.py", line 260, in read_worksheets
    pivot = TableDefinition.from_tree(tree)
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/descriptors/serialisable.py", line 84, in from_tree
    obj = desc.from_tree(el)
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/descriptors/sequence.py", line 86, in from_tree
    return [self.expected_type.from_tree(el) for el in node]
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/descriptors/sequence.py", line 86, in <listcomp>
    return [self.expected_type.from_tree(el) for el in node]
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/descriptors/serialisable.py", line 104, in from_tree
    return cls(**attrib)
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/pivot/table.py", line 572, in __init__
    self.hier = hier
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/descriptors/base.py", line 69, in __set__
    value = _convert(self.expected_type, value)
  File "/home/bjwebb/opendataservices/openpyxl/openpyxl/descriptors/base.py", line 59, in _convert
    raise TypeError('expected ' + str(expected_type))
TypeError: expected <class 'int'>
>>> 

This looks similar to the last one, so I suspect its a similar issue with google docs.

I don't need to use openpyxl's pivot table support, so one way for me to deal with these errors would be to monkeypatch to disable pivot table support.

Comments (2)

  1. CharlieC

    Thanks for the report, Benn. This is the from the schema

    <xsd:complexType name="CT_PageField">
    <xsd:sequence minOccurs="0">
        <xsd:element name="extLst" minOccurs="0" type="CT_ExtensionList"/>
    </xsd:sequence>
    <xsd:attribute name="fld" use="required" type="xsd:int"/>
    <xsd:attribute name="item" use="optional" type="xsd:unsignedInt"/>
    <xsd:attribute name="hier" type="xsd:int"/>
    <xsd:attribute name="name" type="s:ST_Xstring"/>
    <xsd:attribute name="cap" type="s:ST_Xstring"/>
    </xsd:complexType>
    

    But I and it's the hier attribute that's causing problems. As you can see it looks like it's required but I subsequently learned that attributes are optional unless otherwise marked. :-/ But easy to fix.

  2. Log in to comment