[已解决]openpyxl不能填写到合并单元格，报错

颂风侯

D:\PYTHON_NEW\pythonProject\.venv\Scripts\python.exe "D:\PYTHON_NEW\pythonProject\提取python excel\11增加单元格数据  测试生成管线对应的工艺卡的填充数据.py"
Traceback (most recent call last):
  File "D:\PYTHON_NEW\pythonProject\提取python excel\11增加单元格数据  测试生成管线对应的工艺卡的填充数据.py", line 86, in <module>
    copy_template_to_new_file(pipeline_number, template_file, target_folder)
  File "D:\PYTHON_NEW\pythonProject\提取python excel\11增加单元格数据  测试生成管线对应的工艺卡的填充数据.py", line 59, in copy_template_to_new_file
    sheet3['A8'] = 11
    ~~~~~~^^^^^^
  File "D:\PYTHON_NEW\pythonProject\.venv\Lib\site-packages\openpyxl\worksheet\worksheet.py", line 317, in __setitem__
    self[key].value = value
    ^^^^^^^^^^^^^^^
AttributeError: 'MergedCell' object attribute 'value' is read-only

Process finished with exit code 1

--------------
对合并单元格，不允许编辑数据。

最佳答案

月排行榜 / 总排行榜

Twilight6

2024-8-1 17:31:56

可以对左上角赋值，这样就不会报错了~

import openpyxl

def write_xlsx(xlsx_file):
    workbook = openpyxl.load_workbook(xlsx_file)
    sheet = workbook['Sheet1']

    # 获取合并单元格信息
    merged_cells = sheet.merged_cells.ranges

    # 检查单元格是否为合并单元格，并对左上角单元格赋值
    cell_address = 'F5'
    if any([cell_address in merged_cell for merged_cell in merged_cells]):
        # 获取合并区域
        for merged_cell in merged_cells:
            if cell_address in merged_cell:
                # 对左上角单元格赋值
                top_left_cell = merged_cell.min_row, merged_cell.min_col
                sheet.cell(row=top_left_cell[0], column=top_left_cell[1], value="是合并的单元格，测试赋值，嘿嘿~")
                break
    else:
        sheet[cell_address] = "不是合并的单元格"

    output_path = f"test_output.xlsx"
    workbook.save(output_path)

write_xlsx("test.xlsx")

跳转到最佳答案楼层

wp231957

网上找到的比较靠谱的办法 是先取消合并单元格   写入之后  再合并

还有一种是修改openpyxl的源代码  取消只读属性，不过这个貌似不怎么好弄

Twilight6

可以对左上角赋值，这样就不会报错了~

import openpyxl

def write_xlsx(xlsx_file):
    workbook = openpyxl.load_workbook(xlsx_file)
    sheet = workbook['Sheet1']

    # 获取合并单元格信息
    merged_cells = sheet.merged_cells.ranges

    # 检查单元格是否为合并单元格，并对左上角单元格赋值
    cell_address = 'F5'
    if any([cell_address in merged_cell for merged_cell in merged_cells]):
        # 获取合并区域
        for merged_cell in merged_cells:
            if cell_address in merged_cell:
                # 对左上角单元格赋值
                top_left_cell = merged_cell.min_row, merged_cell.min_col
                sheet.cell(row=top_left_cell[0], column=top_left_cell[1], value="是合并的单元格，测试赋值，嘿嘿~")
                break
    else:
        sheet[cell_address] = "不是合并的单元格"

    output_path = f"test_output.xlsx"
    workbook.save(output_path)

write_xlsx("test.xlsx")

颂风侯

大佬这个想法我马上去试试

颂风侯

我试了，取消合并单元格后，还是写不进去
D:\PYTHON_NEW\pythonProject\.venv\Scripts\python.exe "D:\PYTHON_NEW\pythonProject\提取python excel\11增加单元格数据  测试生成管线对应的工艺卡的填充数据.py"
Traceback (most recent call last):
  File "D:\PYTHON_NEW\pythonProject\提取python excel\11增加单元格数据  测试生成管线对应的工艺卡的填充数据.py", line 87, in <module>
    copy_template_to_new_file(pipeline_number, template_file, target_folder)
  File "D:\PYTHON_NEW\pythonProject\提取python excel\11增加单元格数据  测试生成管线对应的工艺卡的填充数据.py", line 60, in copy_template_to_new_file
    sheet3['C8'] = 11
    ~~~~~~^^^^^^
  File "D:\PYTHON_NEW\pythonProject\.venv\Lib\site-packages\openpyxl\worksheet\worksheet.py", line 317, in __setitem__
    self[key].value = value
    ^^^^^^^^^^^^^^^
AttributeError: 'MergedCell' object attribute 'value' is read-only

Process finished with exit code 1

颂风侯

 workbook_new = load_workbook(new_file_path)

          for sheet3 in workbook_new:
              #sheet_name.cell(8,1,"好的")
              #sheet_name.cell(9,1,"不好")
              if 'H' in sheet3.title:
                #sheet3['C8'] = "11"
                #sheet3['C9'] = "12"
                sheet3.cell(8,3).value = "985"
                sheet3.cell(9, 3).value = "211"

          workbook_new.save(new_file_path)

代码通过了，我估计之前的错误是因为我把SHEETNAME IN WS.SHEETNAMES的方法取得名字和命名单元格的方法WS.CELL(I,J).VALUE混用了导致报错
改为sheet in wb  sheet.title取名字就可以了