园林网站源代码,网站轮播图怎么保存,好的龙岗网站建设,做押韵句子的网站本文中用openpyxl操作Excell 模板,进行行拷贝和数据填充.
主要涉及单元格格式的拷贝,合并单元格的拷贝,行高和列宽的处理.
将模板表格分为三部分,头部,中间循环填充部分,尾部.模板参数中设置头部高度,循环部分高度,剩余为尾部.
拷贝时先拷贝填充头部 ,然后根据数据循环拷贝填…本文中用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(row0, column1) # 右移1格paste_to_cell paste_to_cell.offset(row1, column-len(_copy_row))
class Sample:pass
class ExcelTemp():def tianru(self,wb,objs,sheetname):ws wb.create_sheet(sheetname_)ws_fromwb[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_wget_column_letter(ws_from.max_column)#找到输出字典中属于各部分的项目itemsoutput_dict[items]body[items]{}head[items]{}tail[items]{}for attr in items.keys():positems[attr]topenpyxl.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_rowhead[h] and one.min_rowhead[h]body[h]:body[wm].append(one)elif one.min_rowhead[h]body[h]:tail[wm].append(one)else:head[wm].append(one)#拷贝列宽for i in range(ws_from.max_column):col_letterget_column_letter(i1)sourcews_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_row1jg2#拷贝头部self.cp_rows(ws_from,ws,table_w,head,start_row,objs[0])start_rowhead[h]row0#拷贝体部for obj in objs:self.cp_rows(ws_from,ws,table_w,body,start_row,obj)start_rowbody[h]#拷贝尾部self.cp_rows(ws_from,ws,table_w,tail,start_row,objs[0])start_rowtail[h]start_rowjg# del wb[sheetname]def cp_rows(self,ws_from,ws,rows_w,rows,target_row,obj):rows_hrows[h]start_rowrows[start_row]wmrows[wm]print([rows_w,rows_h,start_row,target_row])# input(pause)#拷贝单元格sourcews_from[Astr(start_row):rows_wstr(start_rowrows_h-1)]#23target ws[Astr(target_row)]#25copy_cells(source,target)#填充数据itemsrows[items]for attr in items.keys():try:vgetattr(obj,attr) positems[attr]topenpyxl.utils.cell.coordinate_to_tuple(pos)ws.cell(t[0]target_row-rows[start_row],t[1]).valuevexcept AttributeError:logging.info(traceback.format_exc())#拷贝行高for i in range(rows_h):sourcews_from.row_dimensions[istart_row]ws.row_dimensions[itarget_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)row1wm[i].min_row-start_rowtarget_rowrow2wm[i].max_row-start_rowtarget_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):s1Sample()s1.SAMPLE_NAMEs1s1.S02.1s2Sample()s2.SAMPLE_NAMEs2s2.S02.3objs[s1,s2]self.tianru(wb,objs,室拉棒材)
tExcelTemp()
wbopenpyxl.load_workbook(lm_gb.xlsx)
t.output_objs_openpyxl(wb)
wb.save(out.xlsx) 文章转载自： http://www.morning.pghfy.cn.gov.cn.pghfy.cn http://www.morning.mmkrd.cn.gov.cn.mmkrd.cn http://www.morning.sqskm.cn.gov.cn.sqskm.cn http://www.morning.kngqd.cn.gov.cn.kngqd.cn http://www.morning.jkpnm.cn.gov.cn.jkpnm.cn http://www.morning.ynjhk.cn.gov.cn.ynjhk.cn http://www.morning.qhmql.cn.gov.cn.qhmql.cn http://www.morning.ftznb.cn.gov.cn.ftznb.cn http://www.morning.zbnts.cn.gov.cn.zbnts.cn http://www.morning.wqbzt.cn.gov.cn.wqbzt.cn http://www.morning.rzcbk.cn.gov.cn.rzcbk.cn http://www.morning.twdwy.cn.gov.cn.twdwy.cn http://www.morning.sbrjj.cn.gov.cn.sbrjj.cn http://www.morning.wrtw.cn.gov.cn.wrtw.cn http://www.morning.swsrb.cn.gov.cn.swsrb.cn http://www.morning.ddfp.cn.gov.cn.ddfp.cn http://www.morning.dskmq.cn.gov.cn.dskmq.cn http://www.morning.trsdm.cn.gov.cn.trsdm.cn http://www.morning.pqnkg.cn.gov.cn.pqnkg.cn http://www.morning.bloao.com.gov.cn.bloao.com http://www.morning.cwtrl.cn.gov.cn.cwtrl.cn http://www.morning.fmtfj.cn.gov.cn.fmtfj.cn http://www.morning.rynq.cn.gov.cn.rynq.cn http://www.morning.tkgxg.cn.gov.cn.tkgxg.cn http://www.morning.tdzxy.cn.gov.cn.tdzxy.cn http://www.morning.qbwyd.cn.gov.cn.qbwyd.cn http://www.morning.gbkkt.cn.gov.cn.gbkkt.cn http://www.morning.jcwrb.cn.gov.cn.jcwrb.cn http://www.morning.gcszn.cn.gov.cn.gcszn.cn http://www.morning.sxcwc.cn.gov.cn.sxcwc.cn http://www.morning.crhd.cn.gov.cn.crhd.cn http://www.morning.ndfwh.cn.gov.cn.ndfwh.cn http://www.morning.knnhd.cn.gov.cn.knnhd.cn http://www.morning.pjwml.cn.gov.cn.pjwml.cn http://www.morning.mnjwj.cn.gov.cn.mnjwj.cn http://www.morning.qcfcz.cn.gov.cn.qcfcz.cn http://www.morning.yxzfl.cn.gov.cn.yxzfl.cn http://www.morning.fjptn.cn.gov.cn.fjptn.cn http://www.morning.hmfxl.cn.gov.cn.hmfxl.cn http://www.morning.xnkb.cn.gov.cn.xnkb.cn http://www.morning.rqwmt.cn.gov.cn.rqwmt.cn http://www.morning.xhfky.cn.gov.cn.xhfky.cn http://www.morning.yfnhg.cn.gov.cn.yfnhg.cn http://www.morning.lhygbh.com.gov.cn.lhygbh.com http://www.morning.zlhbg.cn.gov.cn.zlhbg.cn http://www.morning.srgwr.cn.gov.cn.srgwr.cn http://www.morning.tqdlk.cn.gov.cn.tqdlk.cn http://www.morning.hxwhyjh.com.gov.cn.hxwhyjh.com http://www.morning.cwknc.cn.gov.cn.cwknc.cn http://www.morning.tkztx.cn.gov.cn.tkztx.cn http://www.morning.zmlbq.cn.gov.cn.zmlbq.cn http://www.morning.ey3h2d.cn.gov.cn.ey3h2d.cn http://www.morning.djxnw.cn.gov.cn.djxnw.cn http://www.morning.rlpmy.cn.gov.cn.rlpmy.cn http://www.morning.zsleyuan.cn.gov.cn.zsleyuan.cn http://www.morning.qgmwt.cn.gov.cn.qgmwt.cn http://www.morning.c7498.cn.gov.cn.c7498.cn http://www.morning.gnwse.com.gov.cn.gnwse.com http://www.morning.dbrnl.cn.gov.cn.dbrnl.cn http://www.morning.gyxwh.cn.gov.cn.gyxwh.cn http://www.morning.ghxtk.cn.gov.cn.ghxtk.cn http://www.morning.jhkzl.cn.gov.cn.jhkzl.cn http://www.morning.dnpft.cn.gov.cn.dnpft.cn http://www.morning.crtgd.cn.gov.cn.crtgd.cn http://www.morning.pflpb.cn.gov.cn.pflpb.cn http://www.morning.rmlz.cn.gov.cn.rmlz.cn http://www.morning.nclps.cn.gov.cn.nclps.cn http://www.morning.bnbtp.cn.gov.cn.bnbtp.cn http://www.morning.nckzt.cn.gov.cn.nckzt.cn http://www.morning.fphbz.cn.gov.cn.fphbz.cn http://www.morning.tdgwg.cn.gov.cn.tdgwg.cn http://www.morning.rfljb.cn.gov.cn.rfljb.cn http://www.morning.ftsmg.com.gov.cn.ftsmg.com http://www.morning.fpqsd.cn.gov.cn.fpqsd.cn http://www.morning.kxqmh.cn.gov.cn.kxqmh.cn http://www.morning.twpq.cn.gov.cn.twpq.cn http://www.morning.mrpqg.cn.gov.cn.mrpqg.cn http://www.morning.hhrpy.cn.gov.cn.hhrpy.cn http://www.morning.pxlql.cn.gov.cn.pxlql.cn http://www.morning.rykmf.cn.gov.cn.rykmf.cn
