本文中用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._value
            paste_to_cell.data_type = source_cell.data_type

            if 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"]=1
        body["start_row"]=head["h"]+1
        tail["start_row"]=head["h"]+body["h"]+1
        print("head",head,body,tail)
        start_row=1
        jg=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)]#23
        target = ws['A'+str(target_row)]#25
        copy_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=v
                except 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_row
            row2=wm[i].max_row-start_row+target_row
            cell2 =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.1
        s2=Sample()
        s2.SAMPLE_NAME="s2"
        s2.S0=2.3
        objs=[s1,s2]
        self.tianru(wb,objs,"室拉棒材")
t=ExcelTemp()
wb=openpyxl.load_workbook("lm_gb.xlsx")
t.output_objs_openpyxl(wb)
wb.save("out.xlsx")