In Jupyter Notebook, I am attempting to run the following code to open an existing XLSX file with pivot tables.
from openpyxl import load_workbook
wb=load_workbook('TEMPLATE.xlsx')
This ends up throwing a value error, to do with pivot tables:
ValueError Traceback (most recent call last) in ----> 1 wb=load_workbook('TEMPLATE.xlsx')
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\reader\excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links) 315 reader = ExcelReader(filename, read_only, keep_vba, 316 data_only, keep_links) --> 317 reader.read() 318 return reader.wb
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\reader\excel.py in read(self) 280 self.read_theme() 281 apply_stylesheet(self.archive, self.wb) --> 282 self.read_worksheets() 283 self.parser.assign_names() 284 if not self.read_only:
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\reader\excel.py in read_worksheets(self) 266 src = self.archive.read(pivot_path) 267 tree = fromstring(src) --> 268 pivot = TableDefinition.from_tree(tree) 269 pivot.cache = self.parser.pivot_caches[pivot.cacheId] 270 ws.add_pivot(pivot)
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node) 81 if hasattr(desc, 'from_tree'): 82 #descriptor manages conversion ---> 83 obj = desc.from_tree(el) 84 else: 85 if hasattr(desc.expected_type, "from_tree"):
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\descriptors\sequence.py in from_tree(self, node) 83 84 def from_tree(self, node): ---> 85 return [self.expected_type.from_tree(el) for el in node] 86 87
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\descriptors\sequence.py in (.0) 83 84 def from_tree(self, node): ---> 85 return [self.expected_type.from_tree(el) for el in node] 86 87
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node) 101 attrib[tag] = obj 102 --> 103 return cls(**attrib) 104 105
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\pivot\table.py in init(self, scope, type, priority, pivotAreas, extLst) 479 extLst=None, 480 ): --> 481 self.scope = scope 482 self.type = type 483 self.priority = priority
~\AppData\Roaming\Python\Python38\site-packages\openpyxl\descriptors\base.py in set(self, instance, value) 126 def set(self, instance, value): 127 if value not in self.values: --> 128 raise ValueError(self.doc) 129 super(Set, self).set(instance, value) 130
ValueError: Value must be one of {'data', 'selection', 'field'}
Has anyone encountered this before or found a workaround? I cannot open the file in read-only mode because I need to edit a sheet which is the source for the data tables. However, opening in read-only mode allows me to open the file.
