Inability to parse sheets with pivot tables using external data sources

Created originally on Bitbucket by gbronner (Gregory Bronner)

I'm testing 2.5-a2, and trying to get the pivot tables to work. I ran

#!python

load_workbook

on a spreadsheet (sorry -- can't supply a copy!) that uses external data sources for pivot caches.

I modified the code to print out the source of the offending pivot cache, as well as the exception chain.

The xml definition of the pivot cache was [snipped data excluded]:

#!xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<pivotCacheDefinition xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http:// chemas.openxmlformats.org/officeDocument/2006/relationships" saveData="0" refreshedBy="Ruffin, Dare M (F D)" refreshedDate="42852.493215046299" createdVersion="4" refreshedVersion="4" minRefreshableVersion="3" recordCount="0" supportSubquery="1" supportAdvancedDrill="1"><cacheSource type="external" connectionId=" "/><cacheFields count="46">



<map measureGroup="0" dimension="11"/><map measureGroup="0" dimension="12"/><map measureGroup="0" dimension="13"/></maps><extLst><ext uri="{725AE2AE-9491-48be-B2B4-4EB974FC3084}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"><x14:pivotCacheDefinition supportSubqueryNonVisual="1" supportSubqueryCalcMem="1" supportAddCalcMems="1"/></ext></extLst></pivotCacheDefinition>

The stack trace was:

#!python

File "test_generate_spreadsheet2.py", line 20, in <module>
    openpyxl.load_workbook(os.getcwd() + "/../templates/packet_demo.xlsm")
  File ".../openpyxl-2.5.0a2/openpyxl/reader/excel.py", line 223, in load_workbook
    pivot_caches = parser.pivot_caches
  File ".../openpyxl-2.5.0a2/openpyxl/packaging/workbook.py", line 123, in pivot_caches
    cache = get_rel(self.archive, self.rels, id=c.id, cls=CacheDefinition)
  File ".../openpyxl-2.5.0a2/openpyxl/packaging/relationship.py", line 159, in get_rel
    obj = cls.from_tree(tree)
  File ".../openpyxl-2.5.0a2/openpyxl/descriptors/serialisable.py", line 83, in from_tree
    obj = desc.expected_type.from_tree(el)
  File ".../openpyxl-2.5.0a2/openpyxl/descriptors/serialisable.py", line 99, in from_tree
    return cls(**attrib)
  File ".../openpyxl-2.5.0a2/openpyxl/pivot/cache.py", line 919, in __init__
    self.worksheetSource = worksheetSource
  File ".../openpyxl-2.5.0a2/openpyxl/descriptors/base.py", line 44, in __set__
    raise TypeError('expected ' + str(self.expected_type) + " got " + str(type(value)))
TypeError: expected <class 'openpyxl.pivot.cache.WorksheetSource'> got <type 'NoneType'>

The issue seems to be that the expectation is that the data from the pivot cache comes from an Worksheet. Sometimes it doesn't, but the parser is both unable to parse the actual source of the data, and to ignore it.

This represents a regression from the current release, as previous versions (2.4.5) simply ignored these pivot tables, and parsed the file successfully.