load_workbook slow on files with many custom properties
load_workbook
takes a long time (~20 seconds) to read a file with many custom properties (~10k).
The following test exposes the issue:
def test_write_and_read_custom_doc_props(datadir, tmp_path):
"""Writes custom properties into a file, save the file into a temporary path
and then re-read and assert that the custom properties are the ones that
were written before."""
# Read workbook and assert that it doesn't have any custom property
datadir.join("reader").chdir()
wb = load_workbook('example_vba_and_no_custom_doc_props.xlsm')
assert len(wb.custom_doc_props) == 0
# Write custom properties
n_properties = 100
custom_props = {f"PropName{i}": i for i in range(n_properties)}
for name, value in custom_props.items():
wb.custom_doc_props.append(IntProperty(name=name, value=value))
tmp_path_xlsx = tmp_path.with_suffix('.xlsx')
wb.save(tmp_path_xlsx)
# Load workbook and validate custom properties
wb = load_workbook(tmp_path_xlsx)
for prop in wb.custom_doc_props:
assert prop.value == custom_props[prop.name]
If n_properties
is set to 100, we don't notice any slowdown, but if we increase it to 10000 it takes seconds to run.
The following change in the method append
of CustomPropertyList
makes the code run 4 to 5 times faster:
def append(self, prop):
if prop.name in self.names:
raise ValueError(f"Property with name {prop.name} already exists")
self.props.append(prop)
Edited by Rafael Santos