本文中用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 get_tempate_name(self,record):
return "室拉棒材"
def equal_first(self,a,b):
# keys=[""]
# for k in keys:
# if dict1.get(k) != dict2.get(k):
# return False
if a.hth==b.hth:
return True
else:
return False
# r=random.random()
# if r>0.5:
# return True
# else:
# return False
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"]
items=output_dict["items"]
items_body={}
items_head={}
items_tail={}
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"]:
items_body[attr]=items[attr]
elif t[0]>head["h"]+body["h"]:
items_tail[attr]=items[attr]
else:
items_head[attr]=items[attr]
head["items"]=items_head
body["items"]=items_body
tail["items"]=items_tail
wm = list(ws_from.merged_cells)
print(wm,dir(wm[0]))
wm_head=[]
wm_body=[]
wm_tail=[]
for one in wm:
if one.min_row>head["h"] and one.min_row<=head["h"]+body["h"]:
wm_body.append(one)
elif one.min_row>head["h"]+body["h"]:
wm_tail.append(one)
else:
wm_head.append(one)
head["wm"]=wm_head
body["wm"]=wm_body
tail["wm"]=wm_tail
source_table_h=ws_from.max_row
tail["h"]=source_table_h-head["h"]-body["h"]
table_w=get_column_letter(ws_from.max_column)
#treat col width
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
head["start_row"]=1
target_row=10
body_start_row=head["h"]+1
body["start_row"]=body_start_row
tail["start_row"]=head["h"]+body["h"]+1
print("head",head,body,tail)
start_row=1
jg=2
# input("pause")
# self.cp_rows(ws_from,ws,table_w,head,target_row)
self.cp_rows(ws_from,ws,table_w,head,start_row,objs[0])
start_row+=head["h"]
row=0
for record in objs:
self.cp_rows(ws_from,ws,table_w,body,start_row,record)
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,table_w,head,target_row,record):
table_h=head["h"]
start_row=head["start_row"]
wm=head["wm"]
print([table_w,table_h,start_row,target_row])
# input("pause")
source=ws_from['A'+str(start_row):table_w+str(start_row+table_h-1)]#23
target = ws['A'+str(target_row)]#25
copy_cells(source,target)
items=head["items"]
for attr in items.keys():
try:
v=getattr(record,attr)
pos=items[attr]
t=openpyxl.utils.cell.coordinate_to_tuple(pos)
ws.cell(t[0]+target_row-head["start_row"],t[1]).value=v
except AttributeError:
logging.info(traceback.format_exc())
for i in range(table_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()
objs=[]
wb=openpyxl.load_workbook("lm_gb.xlsx")
t.output_objs_openpyxl(wb)
wb.save("out.xlsx")