CODEWITHSUNDEEP
pythonpython-3.xexcelvbaopenpyxl
Nikita
Nikita

Reputation: 475

openpyxl: ValueError: Value must be one of {'selection', 'data', 'field'}

There is an xlsm file which I need to open and edit and extract data as per the macros enabled in the sheet.

But I'm not able to open the file itself. I tried:

wb = openpyxl.load_workbook("workbook.xlsm",read_only=False,keep_vba=True)

Error Occuerred:

Traceback (most recent call last):
  File "C:/Users/Downloads/Projects/Project1/Trial4.py", line 7, in <module>
    wb = openpyxl.load_workbook("workbook.xlsm",read_only=False,keep_vba=True)
  File "C:\Users\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.7_qbz5n2kfra8p0\LocalCache\local-packages\Python37\site-packages\openpyxl\reader\excel.py", line 317, in load_workbook
    reader.read()
  File "C:\Users\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.7_qbz5n2kfra8p0\LocalCache\local-packages\Python37\site-packages\openpyxl\reader\excel.py", line 282, in read
    self.read_worksheets()
  File "C:\Users\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.7_qbz5n2kfra8p0\LocalCache\local-packages\Python37\site-packages\openpyxl\reader\excel.py", line 268, in read_worksheets
    pivot = TableDefinition.from_tree(tree)
  File "C:\Users\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.7_qbz5n2kfra8p0\LocalCache\local-packages\Python37\site-packages\openpyxl\descriptors\serialisable.py", line 83, in from_tree
    obj = desc.from_tree(el)
  File "C:\Users\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.7_qbz5n2kfra8p0\LocalCache\local-packages\Python37\site-packages\openpyxl\descriptors\sequence.py", line 85, in from_tree
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\Users\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.7_qbz5n2kfra8p0\LocalCache\local-packages\Python37\site-packages\openpyxl\descriptors\sequence.py", line 85, in <listcomp>
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\Users\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.7_qbz5n2kfra8p0\LocalCache\local-packages\Python37\site-packages\openpyxl\descriptors\serialisable.py", line 103, in from_tree
    return cls(**attrib)
  File "C:\Users\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.7_qbz5n2kfra8p0\LocalCache\local-packages\Python37\site-packages\openpyxl\pivot\table.py", line 481, in __init__
    self.scope = scope
  File "C:\Users\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.7_qbz5n2kfra8p0\LocalCache\local-packages\Python37\site-packages\openpyxl\descriptors\base.py", line 128, in __set__
    raise ValueError(self.__doc__)
ValueError: Value must be one of {'selection', 'data', 'field'}

Can somebody provide a solution to this problem. TIA

Upvotes: 1

Views: 3530

Answers (3)

Alex Larrain
Alex Larrain

Reputation: 31

the issue is fixed with openpyxl versions >= 3.1.2. Upgrade and retry, it should solve the issue.

pip install openpyxl --upgrade

Upvotes: 2

Adam Reichert
Adam Reichert

Reputation: 11

In addition to Romain's answer, this issue seems to come from when conditional formatting is applied to specific cells that are part of a pivot table.

Ex. Have a pivot table with values from $A$2:$B$5. Setting a rule specifically on $A$2:$B$5 will cause an error, but if you set your conditional formatting rule to be applied to the more general 'All cells showing [values], for [Row Labels] and [Column Labels]' it won't throw an error.

Upvotes: 1

Romain Lesoin
Romain Lesoin

Reputation: 51

I had the same problem not finding a solution on internet, so I deconstructed openpyxl and I finally found.

it would appear that a piece of code is missing to manage the "ConditionalFormat", so I removed it all from the source excel.

and that seems to have solved the problem.

Upvotes: 5

Related Questions