python openpyxl 带格式复制表格的实现_Python

有合并单元格的，先把合并单元格复制过去，合并单元格用wm=list(zip(wbsheet.merged_cells))得出合并单元格列表，把其中的(<CellRange A1：A4>,) 替换成为A1:A4格式

再从新表中合并单元格

再用.has_style: #拷贝格式测试是否有格式，再复制格式和数据

其中：

font(字体类)：字号、字体颜色、下划线等

fill(填充类)：颜色等

border(边框类)：设置单元格边框

alignment(位置类)：对齐方式

number_format(格式类)：数据格式

protection(保护类)：写保护

				?

									import os                           #找文件目录

									import win32com.client as win32     #操作excel文件

									from tqdm import tqdm               #进度条显示

									from openpyxl import load_workbook # 读取时导入这个

									from openpyxl.styles import Font, Alignment #设置单元格格式

									from openpyxl.utils import get_column_letter, column_index_from_string

									from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

									from copy import copy

									path=input('输入整理前原始路径： ')

									if path=="":

									    path=os.getcwd()

									xlsx_lists=[]

									xls_lists=[]

									for file in os.listdir(path):

									 filename=os.path.join(path,file)

									 if os.path.isfile(filename):    #是目录

									  if filename.endswith(".xls"):

									    xls_lists.append(filename)

									  if filename.endswith(".xlsx"):

									    xlsx_lists.append(filename)

									source_file='原始数据.xlsx'

									if os.path.exists(os.path.join(os.getcwd(),source_file)):

									    os.remove(os.path.join(os.getcwd(),source_file))

									choose="1"

									excel = win32.gencache.EnsureDispatch('Excel.Application')

									# while choose not in "1|2":

									#  choose =input("xls转为xlsx：1 xlsx转为xls：2 ")

									if choose=="1":

									    with tqdm(total=len(xls_lists),desc='写文件数 ',leave=True,unit='个',unit_scale=True,mininterval=0.5,bar_format=None) as pbar:

									        for xls_list in xls_lists:

									            pbar.update(1)

									            wb = excel.Workbooks.Open(xls_list)

									            wb.SaveAs(xls_list+"x", FileFormat = 51) #FileFormat = 51 is for .xlsx extension

									            wb.Close()        #FileFormat = 56 is for .xls extension

									        pbar.close()

									else:

									    with tqdm(total=len(xls_lists),desc='写文件数 ',leave=True,unit='个',unit_scale=True,mininterval=0.5,bar_format=None) as pbar:

									        for xlsx_list in xlsx_lists:

									            pbar.update(1)

									            wb = excel.Workbooks.Open(xlsx_list)

									            wb.SaveAs(xlsx_list[0:len(xlsx_list)-1], FileFormat = 56) #FileFormat = 51 is for .xlsx extension

									            wb.Close() 

									        pbar.close()

									excel.Application.Quit()

									tag_file='拆分后表.xlsx'

									totaldata=pd.DataFrame()

									writer=pd.ExcelWriter(tag_file)

									totaldata.to_excel(writer, 'sheet')

									writer.save()

									book = load_workbook(tag_file)   #能写入已存在表中

									wb = load_workbook('原始数据.xlsx')

									for sheet in wb.sheetnames:

									    print(sheet)

									    wbsheet=wb[sheet]

									    for num in range(3):

									        name=wbsheet.cell(1,num*15+10).value

									        wbsheet_new = book.create_sheet(name,0)

									        wm=list(wbsheet.merged_cells) #开始处理合并单元格形式为“(<CellRange A1：A4>,)，替换掉(<CellRange 和 >,)' 找到合并单元格

									        #print (list(wm))

									        if len(wm)>0 :

									            for i in range(0,len(wm)):

									                cell2=str(wm[i]).replace('(<CellRange ','').replace('>,)','')

									                #print("MergeCell : %s" % cell2)

									                wbsheet_new.merge_cells(cell2)

									        for rows in range(40):

									            wbsheet_new.row_dimensions[rows+1].height = wbsheet.row_dimensions[rows+1].height 

									            for col in range(14):

									                wbsheet_new.column_dimensions[get_column_letter(col+1)].width = wbsheet.column_dimensions[get_column_letter(col+1)].width

									                wbsheet_new.cell(row=rows+1,column=col+1,value=wbsheet.cell(rows+1,num*15+col+1).value)

									                if wbsheet.cell(rows+1,num*15+col+1).has_style: #拷贝格式

									                    wbsheet_new.cell(row=rows+1,column=col+1).font = copy(wbsheet.cell(rows+1,num*15+col+1).font)

									                    wbsheet_new.cell(row=rows+1,column=col+1).border = copy(wbsheet.cell(rows+1,num*15+col+1).border)

									                    wbsheet_new.cell(row=rows+1,column=col+1).fill = copy(wbsheet.cell(rows+1,num*15+col+1).fill)

									                    wbsheet_new.cell(row=rows+1,column=col+1).number_format = copy(wbsheet.cell(rows+1,num*15+col+1).number_format)

									                    wbsheet_new.cell(row=rows+1,column=col+1).protection = copy(wbsheet.cell(rows+1,num*15+col+1).protection)

									                    wbsheet_new.cell(row=rows+1,column=col+1).alignment = copy(wbsheet.cell(rows+1,num*15+col+1).alignment)

									wb.close()

									book.save('拆分后表.xlsx')

									book.close()

上例中，因为要把一个表拆分为三个，所以要循环三次

补充：python-excel 之带有格式及合并单元格样式的表格复制

代码如下：

				?

									from openpyxl import load_workbook

									def copy_excel(totle_excel,totle_sheetname,down_excel,down_sheetname):

									 down = load_workbook(down_excel)

									 totle = load_workbook(totle_excel)

									 totle_sheet = totle[totle_sheetname]

									 down_sheet = down[down_sheetname]

									 # 两个for循环遍历整个excel的单元格内容

									 for i, row in enumerate(down_sheet.iter_rows()):

									  for j, cell in enumerate(row):

									   totle_sheet.cell(row=i + 1, column=j + 1, value=cell.value)

									 totle.save(totle_excel)