openpyxl处理Excel模板,带格式拷贝行和数据填入

本文中用openpyxl操作Excell 模板,进行行拷贝和数据填充.

主要涉及单元格格式的拷贝,合并单元格的拷贝,行高和列宽的处理.

将模板表格分为三部分,头部,中间循环填充部分,尾部.模板参数中设置头部高度,循环部分高度,剩余为尾部.

拷贝时先拷贝填充头部 ,然后根据数据循环拷贝填充中间部分,最后拷贝填充尾部.

import os
import openpyxl
import logging
from openpyxl.utils import get_column_letter
import traceback
import copy
def copy_cells(copy_from, paste_to_cell):"""复制粘贴某个区域:param copy_from 复制源:param paste_to_cell 粘贴的左上角"""# 记录边缘值for _copy_row in copy_from:  # 循环每一行print(dir(_copy_row),_copy_row)for source_cell in _copy_row:  # 循环每一列# paste_to_cell.value = _row_cell.value# paste_to_cell._style = deepcopy(source_cell._style)  # 复制样式paste_to_cell._value = source_cell._valuepaste_to_cell.data_type = source_cell.data_typeif source_cell.has_style:paste_to_cell._style = copy.copy(source_cell._style)if source_cell.hyperlink:paste_to_cell._hyperlink = copy.copy(source_cell.hyperlink)if source_cell.comment:paste_to_cell.comment = copy.copy(source_cell.comment)paste_to_cell = paste_to_cell.offset(row=0, column=1)  # 右移1格paste_to_cell = paste_to_cell.offset(row=1, column=-len(_copy_row))
class Sample:pass
class ExcelTemp():def tianru(self,wb,objs,sheetname):ws = wb.create_sheet(sheetname+"_")ws_from=wb[sheetname]output_dict={"head": 14,"body": 4,"items": {"SAMPLE_NAME": "A15","sampleid": "B15","SAMPLE_CODE": "D15","d0": "E15","L0": "H15","S0": "F15","Lu": "K15",}}head={}body={}tail={}#记录各部分的高度head["h"]=output_dict["head"]body["h"]=output_dict["body"]tail["h"]=ws_from.max_row-head["h"]-body["h"]table_w=get_column_letter(ws_from.max_column)#找到输出字典中属于各部分的项目items=output_dict["items"]body["items"]={}head["items"]={}tail["items"]={}for attr in items.keys():pos=items[attr]t=openpyxl.utils.cell.coordinate_to_tuple(pos)if t[0]>head["h"] and t[0]<=head["h"]+body["h"]:body["items"][attr]=items[attr]elif t[0]>head["h"]+body["h"]:tail["items"][attr]=items[attr]else:head["items"][attr]=items[attr]#找到源表格属于各部分的合并单元格wm = list(ws_from.merged_cells)print(wm,dir(wm[0]))head["wm"]=[]body["wm"]=[]tail["wm"]=[]for one in wm:if one.min_row>head["h"] and one.min_row<=head["h"]+body["h"]:body["wm"].append(one)elif one.min_row>head["h"]+body["h"]:tail["wm"].append(one)else:head["wm"].append(one)#拷贝列宽for i in range(ws_from.max_column):col_letter=get_column_letter(i+1)source=ws_from.column_dimensions[col_letter]ws.column_dimensions[col_letter]=copy.copy(source)#记录各部分的起始行head["start_row"]=1body["start_row"]=head["h"]+1tail["start_row"]=head["h"]+body["h"]+1print("head",head,body,tail)start_row=1jg=2#拷贝头部self.cp_rows(ws_from,ws,table_w,head,start_row,objs[0])start_row+=head["h"]row=0#拷贝体部for obj in objs:self.cp_rows(ws_from,ws,table_w,body,start_row,obj)start_row+=body["h"]#拷贝尾部self.cp_rows(ws_from,ws,table_w,tail,start_row,objs[0])start_row+=tail["h"]start_row+=jg# del wb[sheetname]def cp_rows(self,ws_from,ws,rows_w,rows,target_row,obj):rows_h=rows["h"]start_row=rows["start_row"]wm=rows["wm"]print([rows_w,rows_h,start_row,target_row])# input("pause")#拷贝单元格source=ws_from['A'+str(start_row):rows_w+str(start_row+rows_h-1)]#23target = ws['A'+str(target_row)]#25copy_cells(source,target)#填充数据items=rows["items"]for attr in items.keys():try:v=getattr(obj,attr)    pos=items[attr]t=openpyxl.utils.cell.coordinate_to_tuple(pos)ws.cell(t[0]+target_row-rows["start_row"],t[1]).value=vexcept AttributeError:logging.info(traceback.format_exc())#拷贝行高for i in range(rows_h):source=ws_from.row_dimensions[i+start_row]ws.row_dimensions[i+target_row]=copy.copy(source)#拷贝合并单元格for i in range(0, len(wm)):print(wm[i],dir(wm[i]),type(wm[i]))print(str(wm[i]))# print(wm[i].start_cell.row,wm[i].start_cell.column)# print(wm[i].min_row,wm[i].max_row,wm[i].min_col,wm[i].max_col)row1=wm[i].min_row-start_row+target_rowrow2=wm[i].max_row-start_row+target_rowcell2 =get_column_letter(wm[i].min_col)+str(row1)+":"+ get_column_letter(wm[i].max_col)+str(row2)print(cell2)ws.merge_cells(cell2)def output_objs_openpyxl(self,wb):s1=Sample()s1.SAMPLE_NAME="s1"s1.S0=2.1s2=Sample()s2.SAMPLE_NAME="s2"s2.S0=2.3objs=[s1,s2]self.tianru(wb,objs,"室拉棒材")
t=ExcelTemp()
wb=openpyxl.load_workbook("lm_gb.xlsx")
t.output_objs_openpyxl(wb)
wb.save("out.xlsx")