网上搜到的都是说改openpyxl的worksheet.py源码,
加了这几句:
min_col,min_row,max_col,max_row=cr.bounds
for row in range(min_row,max_row+1):
for col in range(min_col,max_col+1):
if col==min_col and row ==min_row:
continue
del self._cells[(row,col)]
我改了,发现没有效果,后来经过研究,可以通过以下两种办法来解决。
我的问题是需要写入第14,15列,然后每一行都有可能是合并过的,如果是合并过行,按照原先的直接赋值就会报错。
办法一:
判断第13列是否为None或者为""即可
filePath="E:\\test\\download\\交易明细表-20240530165233.xlsx"
# 用openpyxl读取数据
# 打开工作簿,整个xlsx文件是一个workbook,然后包含多个sheet
workbook = openpyxl.load_workbook(filePath)
worksheet = workbook['sheet1']
rowCount = list(worksheet.rows).__len__()
print(f"rowCount={rowCount}")
rowIndex = 1
while rowIndex < rowCount:
rowIndex+=1
print(f"开始循环抽查交易明细第{rowIndex}行")
expressUrl="testurl"
expressStatus="teststatus"
# 物流查询网站,excel从1开始
cell13 = worksheet.cell(row=rowIndex, column=13)
orderNo=cell13.value
#必须判断前一行是否为None或者为"",否则因为合并过会导致写入失败
# AttributeError : 'MergedCell' object attribute 'value' is read-only
if orderNo:
cell14 = worksheet.cell(row=rowIndex, column=14)
cell14.value=expressUrl+str(rowIndex)
cell15 = worksheet.cell(row=rowIndex, column=15)
cell15.value=expressStatus+str(rowIndex)
workbook.save(filePath)
workbook.close()
办法二:
先取消所有的合并,数据写入后再合并
merged_cell_range = list(worksheet.merged_cells.ranges)
merged_cell_N = [cell for cell in merged_cell_range if cell.coord[0] == 'N']
cellCount=merged_cell_N.__len__()
for merged_cell in merged_cell_N:
min_col, min_row, max_col, max_row = merged_cell.bounds # 获取合并单元格的边界
# 取消合并
worksheet.unmerge_cells(str(merged_cell))
循环写入值之后,
cell14 = worksheet.cell(row=rowIndex, column=14)
cell14.value=expressUrl+str(rowIndex)
cell15 = worksheet.cell(row=rowIndex, column=15)
cell15.value=expressStatus+str(rowIndex)
再合并
for merged_cell in merged_cell_N:
min_col, min_row, max_col, max_row = merged_cell.bounds # 获取合并单元格的边界
# 重新合并
worksheet.merge_cells(start_row=min_row, start_column=min_col, end_row=max_row, end_column=max_col)
最后再保存
workbook.save(filePath) workbook.close()