python带格式复制Excel和带格式写入
python带格式复制Excel, 带格式写入, 排序， 去重，调整线型
操作方法
- 01
# -*- coding: utf-8 -*- import xlwt import pandas as pd import numpy as np from xlrd import open_workbook from xlutils.copy import copy data = 'all.xls' muban = 'muban.xls' for mon in range( 201501, 201513 ): allData = pd.read_excel( data, sheetname = str( mon ), skiprows = [ 0, 1 ] ) allData = allData.iloc[ : -2, : 10 ] # 除去表头表尾 allData.sort_values( by = [ '单据号码', '物料编码' ], inplace = True ) for dj in allData[ '单据号码' ].drop_duplicates(): newFile = dj + '.xls' thisData = allData[ allData[ '单据号码' ] == dj ] date = thisData[ '发生日期' ].iloc[ 0 ] rb = open_workbook(muban,formatting_info=True) rs = rb.sheet_by_index(0) wb = copy(rb) s=wb.get_sheet(0) s.set_name(dj) font = xlwt.Font() font.name = 'Arial Narrow' font.color = 'red' font.size = 10 style = xlwt.XFStyle() style.font = font s.write(2,4,date, style) s.write(2,7,dj, style) borders = xlwt.Borders() borders.left = xlwt.Borders.DOUBLE for row in range( len( thisData ) ): for col in range( len( thisData.columns ) - 2 ): content = thisData.iloc[ row, col ] content = "" if type(content)==type(1.3) and np.isnan(content) else content if col == 0: style = xlwt.XFStyle() style.borders = borders s.write(5+row,1+col, content, style ) else: s.write(5+row,1+col, content ) borders = xlwt.Borders() borders.bottom = xlwt.Borders.DOUBLE style = xlwt.XFStyle() style.borders = borders s.write(106,6,xlwt.Formula("sum(G6:G106)"), style ) s.write(106,7,xlwt.Formula("sum(H6:H106)"), style ) wb.save(str( mon ) + "_" + newFile )