CODEWITHSUNDEEP
pythonopenpyxl
Sean_M
Sean_M

Reputation: 1

Can't overwrite cell values using openpyxl ['MergedCell' is read-only]

Firstly iterating over spreadsheets in a dir, then trying to iterate over cells in column D, overwriting their values from a list. D1 = list[0], D2 = list[1] and so on.

from openpyxl import Workbook

i = 0
for copy in os.listdir(dir):
    wscopy = openpyxl.load_workbook(os.path.join(dir, copy))
    for cell in wscopy.active['D']:
        cell.value = converted_numbers[i]
        i += 1
    wb.save(copy)

Issue seems to be that some cell attributes are 'cell' while others are 'mergedcell', the latter of which cannot be overwritten. Full error:

"AttributeError: 'MergedCell' object attribute 'value' is read-only"

Any suggestions?

Upvotes: 0

Views: 710

Answers (1)

Charlie Clark
Charlie Clark

Reputation: 19537

Excel relies on MergedCells for formatting borders and this introduces dangerous ambiguity into worksheets because the cells could contain real values even though they're supposed to be merged.

To solve this in openpyxl we created MergedCells, which will preserve any border formats (no other formatting requires them) but which cannot be altered. The exception is intended to help the user from any unexpected side effects, which would be the case here.

You can easily work around this with a try and except clause:

try:
    cell.value = = converted_numbers[i]
except AttributeError:
    if isinstance(cell, MergedCell):
        warn(f"Failed trying to assign a value to MergedCell {cell.coodinates}")

Upvotes: 1

Related Questions