想把一个完整的worksheet复制到新的文件中,但所有合并单元格都只剩下左上角的边框,其它边框丢失了,怎样才能修补回来?
版本python3.8+openpyxl3.0.3
试过论谈里的修改workbook.py或者patch_worksheet()打补丁都无效
def add_sheet_with_format(targetbk, sourcesheet):
ws = sourcesheet
ws2 = targetbk.create_sheet()
max_row=ws.max_row #最大行数
max_column=ws.max_column #最大列数
m_list=ws.merged_cells #合并单元格的位置信息,可迭代对象(单个是一个'openpyxl.worksheet.cell_range.CellRange'对象),print后就是excel坐标信息
mergedcellqty=len(ws.merged_cells.ranges)
if mergedcellqty>0 :
cr = []
for m_area in m_list:
# 合并单元格的起始行坐标、终止行坐标。。。。,
r1, r2, c1, c2 = m_area.min_row, m_area.max_row, m_area.min_col, m_area.max_col
# 纵向合并单元格的位置信息提取出
if r2 - r1 >= 0:
cr.append((r1, r2, c1, c2))
#print('符合条件%s' % str(m_area))
for r in cr:
ws2.merge_cells(start_row=r[0], end_row=r[1], start_column=r[2], end_column=r[3])
for m in range(1,max_row + 1):
ws2.row_dimensions[m].height = ws.row_dimensions[m].height
for n in range(1,1 + max_column):
if n<27 :
c=chr(n+64).upper() #ASCII字符,chr(65)='A'
else:
if n < 677 :
c=chr(divmod(n,26)[0]+64)+chr(divmod(n,26)[1]+64)
else:
c=chr(divmod(n,676)[0]+64) + chr(divmod(divmod(n,676)[1],26)[0]+64) + chr(divmod(divmod(n,676)[1],26)[1]+64)
i='%s%d'%(c,m) #单元格编号
if m == 1 :
ws2.column_dimensions[c].width = ws.column_dimensions[c].width
try:
cell1=ws[i] #获取data单元格数据
ws2[i].value=cell1.value #赋值到ws2单元格
if cell1.has_style: #拷贝格式
ws2[i].font = copy(cell1.font)
ws2[i].border = copy(cell1.border)
ws2[i].fill = copy(cell1.fill)
ws2[i].number_format = copy(cell1.number_format)
ws2[i].protection = copy(cell1.protection)
ws2[i].alignment = copy(cell1.alignment)
except AttributeError as e:
print("cell(%s) is %s" % (i,e))
continue
原文件
复制后的文件