load_workbook takes ages with lots of pivot tables

Hi,

Thanks for maintaining this handy library 👏

Context

Recently, I came across an issue with one of my Excel file built like so:

• Worksheet 1 : A big table (~10000 rows, 25 columns)
• Worksheet 2 : Lots of pivot tables (~40) to get various statistics about the big table on worksheet 1
• Worksheet 3 : Some charts made of of pivot tables from worksheet 2

Basically I want to populate data in the big table thanks to openpyxl, then open the workbook in Excel and use pivot tables and charts in other documents.

However, in that situation, openpyxl takes ages to load the workbook:

workbook = load_workbook(filename='workbook.xlsm')

(I gave up waiting after 15 minutes)

Why it happens

I'm not familiar with the codebase, but investigating where the code takes time to run, I tracked down the issue to the following:

• Creating a pivot table generates a cache in the workbook, e.g. pivotCacheRecords1.xml
• When openpyxl loads the workbook, it loads the same pivotCacheRecords XML once per pivot table
  • More specifically get_rel(archive, deps, id=None, cls=None) from packaging/relationship.py is called once per pivot table
  • For each pivot table based on the same source table, the same pivotCacheRecords XML document is parsed again (unnecessarily?)

Since I have lots of pivot tables and the XML size is like ~2MB, this takes ages to load.

Proof of concept

Example workbook

I made a sample workbook that demonstrates the issue using data from sample-csv-files:

big-workbook.xlsx

poc.py

#!/usr/bin/env python3

import time
from openpyxl import load_workbook

EXCEL_FILE='big-workbook.xlsx'
print('Loading Workbook: ' + EXCEL_FILE + ' - This may take a while')
load_start = time.time()
workbook = load_workbook(filename=EXCEL_FILE)
load_end = time.time()
print('Loaded Workbook: ' + EXCEL_FILE + ' - took ' + str(int(load_end - load_start)) + ' seconds')

$ ./poc.py
Loading Workbook: big-workbook.xlsx - This may take a while
Loaded Workbook: big-workbook.xlsx - took 335 seconds

(This is an example, my actual workbook did not load at all, I gave up after 15 minutes / 900 seconds)

Example Fix (quick and dirty)

As an exercice, I added an object cache in get_rel to skip parsing already loaded XML documents:

--- a/openpyxl/packaging/relationship.py.orig
+++ b/openpyxl/packaging/relationship.py.patch
@@ -147,8 +147,22 @@ def get_dependents(archive, filename):
             r.target = posixpath.normpath(pth)
     return rels
 
+_rel_cache = {}
+import builtins
 
 def get_rel(archive, deps, id=None, cls=None):
+
+    # Skip rebuilding data we already have, just return the data!
+    cache_key = (str(builtins.id(archive)) + '_' + str(archive) +
+                '_' + str(builtins.id(deps)) + '_' + str(deps) +
+                '_' + str(builtins.id(id)) + '_' + str(id) +
+                '_' + str(builtins.id(cls)) + '_' + str(cls)).replace("\n", '_')
+    if cache_key in _rel_cache:
+        return _rel_cache[cache_key]
+
     """
     Get related object based on id or rel_type
     """
@@ -173,4 +187,7 @@ def get_rel(archive, deps, id=None, cls=None):
     except KeyError:
         obj.deps = []
 
+    # Save object in cache
+    _rel_cache[cache_key] = obj
+
     return obj

The sample workbook loads 10 times faster with this quick and dirty patch:

$ ./poc.py
Loading Workbook: big-workbook.xlsx - This may take a while
Loaded Workbook: big-workbook.xlsx - took 31 seconds

(My actual workbook also loads in ~30 seconds with this quick and dirty fix: out of 179 calls to get_rel, only 19 different XML documents were loaded, which makes 160 cache hits where get_rel skipped loading the same XML document and just returned the existing object)

Although it works for me, I think this should be solved in a more elegant way I can't come up alone since I'm so unfamiliar with the code.

What do you think?