How to write in merged cell in Excel using `openpyxl` library?

Question

I am using openpyxl library to write in existing Excel file in separate cells.

How do I write some text in Excel merged cell?

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

when cells are merged:

CODE:

        wb = openpyxl.load_workbook(filename=src)
        for row in df_short.itertuples():
            ws = wb[row.sheet]
            try:
                cell = 'N'+str(row.id)
                ws[cell] = '=HYPERLINK("%s","#%s")' % (row.txt_path, row.txt)

You may try first unmerging the cells, then setting the value, then merging them back together? https://openpyxl.readthedocs.io/en/latest/usage.html#merge-unmerge-cells — David Zemens, Aug 01 '19 at 15:22
Alternatively, try referring to the single top/left-most cell like `ws.cells(row=1, column=1).value = ...` (obviously, replace with the correct row/column values). — David Zemens, Aug 01 '19 at 15:24
Good idea! Ho to check in advance if particular cell is merged? — Alexander Borochkin, Aug 01 '19 at 15:24
If you always write to the top/left `cell`, I don't think you need to bother checking whether it's merged. But you can see some suggestions here: https://stackoverflow.com/questions/39574991/how-to-detect-merged-cells-in-excel-with-openpyxl/42823355 — David Zemens, Aug 01 '19 at 15:32

score 1 · Answer 1 · answered May 25 '20 at 11:01

Use the following code where ws is the sheet object.

    ws.cell(cells).value = 'Whatever you want it to be'

replace cells with the top-left cell of the merged block. I usually keep this as rows and columns. So B1 would be represented as row = 1, column = 2.

After the value =

Put any string or integer of what you want to place inside the cell.

score -1 · Answer 2 · answered Jan 06 '20 at 19:59

-1

I had this exact error and solution for conda environment within python3.5 was

conda install -c conda-forge openpyxl=2.5.14 -y

Similarly for pip3 environments:

pip3 install openpyxl==2.5.14 -y

answered Jan 06 '20 at 19:59

moader808

49
6

score -2 · Answer 3 · answered Nov 14 '19 at 15:40

If you are open to using another excel library, your issue is not encountered with xlwings which lets Python control excel. Differences between xlwings vs openpyxl Reading Excel Workbooks. Note xlwings requires Excel to be installed on your machine while openpyxl does not.

The below sets the value of already merged cell A1 with no problems.

import xlwings as xw

wb = xw.Book('myproject.xlsm')
sh = wb.sheets[0]
sh.range('A1').value = 'hello'

Result:

How to write in merged cell in Excel using `openpyxl` library?

3 Answers3