2.5.x load_workbook Errors on PivotTable Style When Style Has No Name

Issue #915 resolved
Brian Ritz
created an issue

Not all Pivot Table styles have the name attribute required by the class PivotTableStyle in openpyxl / openpyxl / pivot / table.py. One such style is the "Plain" style of pivottable that is the very first choice on the far left when choosing styles in design tab on mac.

When I added allow_none=True on the name attribute, the workbook loaded successfully.

I've attached a toy example of a workbook that has a plain style that throws the following error on load_workbook:

In [104]: d = load_workbook("/data/pivottablewithstyle.xlsx")
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-104-9bb3e4269703> in <module>()
----> 1 d = load_workbook("/data/pivottablewithstyle.xlsx")

/usr/local/lib/python3.6/site-packages/openpyxl/reader/excel.py in load_workbook(filename, read_only, keep_vba, data_only, guess_types, keep_links)
    280                     src = archive.read(pivot_path)
    281                     tree = fromstring(src)
--> 282                     pivot = TableDefinition.from_tree(tree)
    283                     pivot.cache = pivot_caches[pivot.cacheId]
    284                     ws.add_pivot(pivot)

/usr/local/lib/python3.6/site-packages/openpyxl/descriptors/serialisable.py in from_tree(cls, node)
     81                 if hasattr(desc.expected_type, "from_tree"):
     82                     #complex type
---> 83                     obj = desc.expected_type.from_tree(el)
     84                 else:
     85                     #primitive

/usr/local/lib/python3.6/site-packages/openpyxl/descriptors/serialisable.py in from_tree(cls, node)
     97                 attrib[tag] = obj
     98 
---> 99         return cls(**attrib)
    100 
    101 

/usr/local/lib/python3.6/site-packages/openpyxl/pivot/table.py in __init__(self, name, showRowHeaders, showColHeaders, showRowStripes, showColStripes, showLastColumn)
    190                  showLastColumn=None,
    191                 ):
--> 192         self.name = name
    193         self.showRowHeaders = showRowHeaders
    194         self.showColHeaders = showColHeaders

/usr/local/lib/python3.6/site-packages/openpyxl/descriptors/base.py in __set__(self, instance, value)
     42             if (not self.allow_none
     43                 or (self.allow_none and value is not None)):
---> 44                 raise TypeError('expected ' + str(self.expected_type))
     45         super(Typed, self).__set__(instance, value)
     46 

TypeError: expected <class 'str'>

Comments (4)

  1. CharlieC

    Thanks for the report. It's true that the element is optional (the OOXML schema is inconsistent when flagging required elements but it turns out that all attributes are optional unless it says otherwise).

  2. CharlieC

    FWIW this is what the schema for the element looks like. Why one of the elements is explicitly optional is anyone's guess!

    <xsd:complexType name="CT_PivotTableStyle">
       <xsd:attribute name="name" type="xsd:string"/> 
       <xsd:attribute name="showRowHeaders" type="xsd:boolean"/>
       <xsd:attribute name="showColHeaders" type="xsd:boolean"/>
       <xsd:attribute name="showRowStripes" type="xsd:boolean"/>
       <xsd:attribute name="showColStripes" type="xsd:boolean"/>
       <xsd:attribute name="showLastColumn" type="xsd:boolean" use="optional"/>
    </xsd:complexType>
    
  3. Log in to comment