使用python处理Excel，Excel中一行数据生产一个Excel文件
应用场景：比如说收到一个文件，文件里面有几百个用户，需要按照一定的格式每一个用户生成一个Excel文件
需要生成这样结果：
每个文件格式如下：
代码如下：
# -*- coding: utf-8 -*- """ Created on Tue Apr 28 15:16:14 2020 @author: Admin """ #导入模块 import pandas as pd import numpy as np import xlrd #读Excel import xlwt #将内容写进Excel #打开excel文件1 workbook = xlrd.open_workbook(r'F:\\python\\test\\bb_6c.xlsx') sheet = workbook.sheet_by_index(0) rows = [sheet.row_values(row,0,14) for row in range(sheet.nrows)] name_lists = {} for r in rows: #因为我们是根据姓名来切分，姓名在第二列。所以这里是r[1] if r[1] not in name_lists: name_lists[r[1]] = [] name_lists[r[1]].append(r) #打开Excel文件2 workbook =xlrd.open_workbook(r'F:\\python\\test\\bb_6p.xlsx') sheet1= workbook.sheet_by_index(0) rows1 = [sheet1.row_values(row,0,12) for row in range(sheet1.nrows)] name_lists1 = {} for r in rows1: #因为我们是根据姓名来切分，姓名在第二列。所以这里是r[0] if r[0] not in name_lists1: name_lists1[r[0]] = [] name_lists1[r[0]].append(r) #写进Excel里面 for k, k2 in zip(name_lists,name_lists1): wb = xlwt.Workbook() #新建sheet ws = wb.add_sheet(k) #设置列宽度 ,其中第一列要宽一些 ，其他的小一些 ws.col(0).width=8000 for i in range(1,14): ws.col(i).width=3333 #设置加粗 font = xlwt.Font() font.bold = True #tyle_ziti = xlwt.easyxf('font: bold on') #设置背景颜色黄色 pattern = xlwt.Pattern() # Create the Pattern pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12 pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... #设置居中 alignment = xlwt.Alignment() # Create Alignment alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED #设置边框 borders = xlwt.Borders() # Create Borders borders.left = xlwt.Borders.THIN # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D. borders.right = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN borders.left_colour = 0x08 borders.right_colour = 0x08 borders.top_colour = 0x08 borders.bottom_colour = 0x08 style1 = xlwt.XFStyle() style2 = xlwt.XFStyle() style3 = xlwt.XFStyle() style4 = xlwt.XFStyle() style5 = xlwt.XFStyle() style7 = xlwt.XFStyle() #1.边框，居中 加粗 style1.borders=borders style1.alignment=alignment style1.font=font # 2.边框，居中 style2.borders=borders style2.alignment=alignment #3.边框，居中，黄色 style3.borders=borders style3.alignment=alignment style3.pattern=pattern #4.边框，居中 加粗，黄色 style4.borders=borders style4.alignment=alignment style4.pattern=pattern style4.font=font #5，边框 style5.borders=borders # style7.borders=borders style7.alignment=alignment style7.num_format_str="0.00%" #6.合并，换行,底部对齐，设置行高 style6 = xlwt.XFStyle() tall_style = xlwt.easyxf('font:height 1200;') # 36pt,类型小初的字号 first_row = ws.row(3) first_row.set_style(tall_style) alignment1 = xlwt.Alignment() alignment1.horz = xlwt.Alignment.HORZ_LEFT alignment1.vert = xlwt.Alignment.VERT_BOTTOM style6.alignment=alignment1 style6.alignment.wrap = 1 #设置加粗并设置黄底 #style_ziti_h = xlwt.easyxf('pattern: pattern solid, fore_colour yellow; font: bold on') #设置黄底 #style_h = xlwt.easyxf('pattern: pattern solid, fore_colour yellow') # write_merge函数参数 start_row，end_row，start_col，end_col #这里先写入表头 ws.write_merge(0, 0, 0, 13, '欠款明细',style1) #需要合并write_merge(0, 0, 0, 13,style_ziti),还需要加粗 ws.write(1, 0, '合同号',style1) #需要加粗 ws.write(1, 1, '姓名',style1) ws.write(1, 2, '贷款本金',style1) ws.write(1, 3, '首次还款日',style1) ws.write(1, 4, '分期期数',style1) ws.write(1, 5, '已还期数',style1) ws.write(1, 6, '未还期数',style1) ws.write(1, 7, '每期期款',style1) ws.write(1, 8, '未还本金',style1) ws.write(1, 9, '月贷款利率%',style1) ws.write(1, 10, '未还利息',style1) ws.write(1, 11, '未还本息总和',style1) ws.write(1, 12, '利息损失',style1) ws.write(1, 13, '欠款总额',style1) #第二行的都需要加粗 row_idx = 2 for i in name_lists[k]: col_idx=0 for j in i : ws.write(row_idx,col_idx,j,style3) #需要黄体 col_idx=col_idx+1 row_idx=row_idx+1 ws.write_merge(3, 3,0,13,'欠款计算说明：\n欠款总额=未还本息总和+利息损失；\n未还本息总和=等额本息还款法计算出的每期期款金额*未还期数；\n利息损失=以代偿金额（即应还未还的贷款本息和）为基数，按照银行同期贷款利率4.35%的标准，自完成代偿之日起，计算至实际清偿之日止。' ,style6 ) ws.write_merge(4, 4, 0, 3, '附：等额本息还款法计算公式：',style5) #需要合并write_merge(7, 0, 0, 3) ws.write_merge(5, 5, 0, 3, '输入区',style5) #需要合并write_merge(8, 0, 0, 3) ws.write(6, 0, '本金',style2) ws.write(6, 1, '分期数（月）',style2) ws.write(6, 2, '月利率',style2) ws.write(6, 3, '年化',style2) ws.write(7, 0, xlwt.Formula('c3'),style2) ws.write(7, 1, xlwt.Formula('e3'),style2) ws.write(7, 2, xlwt.Formula('j3'),style7) ws.write(7, 3, xlwt.Formula('j3*12'),style7) ws.write(9, 0, '合同号',style4) ws.write(9, 1, '期数',style4) #第13行需要黄体加粗 ws.write(9, 2, '每月还款额',style4) ws.write(9, 3, '本月应还本金',style4) ws.write(9, 4, '本月应还利息',style4) ws.write(9, 5, '已还本息',style4) ws.write(9, 6, '未还本息',style4) ws.write(9, 7, '应还款日',style4) ws.write(9, 8, '完成代偿日',style4) ws.write(9, 9, '利息暂计至',style4) ws.write(9, 10, '计息天数',style4) ws.write(9, 11, '利息损失',style4) row_bb=10 for gg in name_lists1[k2]: col_bb=0 for hh in gg: ws.write(row_bb,col_bb,hh,style2) col_bb=col_bb+1 row_bb=row_bb+1 ws.write_merge(16, 16, 0, 5, '未还本息合计',style4) #需要合并write_merge(16, 0, 0, 4) ws.write(16, 6, xlwt.Formula('l3'),style4) ws.write_merge(16, 16,7,10, '利息损失小计',style4) ws.write(16, 11, xlwt.Formula('m3'),style4) wb.save('F:\\python\\test\\欠款明细-'+k+'.xls') #如果要固定存在某个文件夹，则可以这样save(r'f:\python_to_excel_xlwt\cell_width.xls')
