openpyxl不能填写到合并单元格，报错

颂风侯 · 发表于 2024-7-31 16:48:28

马上注册，结交更多好友，享用更多功能^_^

您需要登录才可以下载或查看，没有账号？立即注册

×

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

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

wp231957 · 发表于 2024-8-1 08:54:24

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

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

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")
复制代码

颂风侯 · 发表于 2024-8-2 10:00:38

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

颂风侯 · 发表于 2024-8-2 10:16:43

我试了，取消合并单元格后，还是写不进去
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

颂风侯 · 发表于 2024-8-2 10:37:16

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取名字就可以了

账号		自动登录	找回密码
密码			立即注册

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

马上注册，结交更多好友，享用更多功能^_^