python做报表汇总_Python_汇总生成统计报表

1 importxlrd2 importxlwt3 from xlutils.copy importcopy4

5 objWb = xlrd.open_workbook(r'C:\Users\IBM\Desktop\新建文件夹\S1-3\7月下旬入库表.xlsx')6 objSht =objWb.sheet_by_index(0)7 #定义列表，存储读取的内容

8 All_Data =[]9 for Nrow in range(1, objSht.nrows): #nrows 返还sheet行数

10 Company = objSht.cell(Nrow, 1).value11 Price = objSht.cell(Nrow, 3).value12 Weight = objSht.cell(Nrow, 4).value13 Data = {'Company': Company, 'Weight': Weight, 'Price': Price}14 All_Data.append(Data)15 #张三

16 A_weight =[]17 A_total_price =[]18 #李四

19 B_weight =[]20 B_total_price =[]21 #王五

22 C_weight =[]23 C_total_price =[]24 #赵六

25 D_weight =[]26 D_total_price =[]27

28 for D inAll_Data:29 #print(D)

30 if D['Company'] == '张三粮配':31 A_weight.append(D['Weight'])32 A_total_price.append(D['Price'])33 if D['Company'] == '李四粮食':34 B_weight.append(D['Weight'])35 B_total_price.append(D['Price'])36 if D['Company'] == '王五小麦':37 C_weight.append(D['Weight'])38 C_total_price.append(D['Price'])39 if D['Company'] == '赵六麦子专营':40 D_weight.append(D['Weight'])41 D_total_price.append(D['Price'])42 #格式

43 Style =xlwt.XFStyle()44 #格式.字体

45 Font =xlwt.Font()46 Font.name = '微软雅黑'

47 Font.bold =True48 Font.height = 18 * 20

49 Style.font =Font50 #格式.边框

51 Borders =xlwt.Borders()52 Borders.top =xlwt.Borders.THIN53 Borders.bottom =xlwt.Borders.THIN54 Borders.left =xlwt.Borders.THIN55 Borders.right =xlwt.Borders.THIN56 Style.borders =Borders57 #格式.对齐

58 Alignment =xlwt.Alignment()59 Alignment.horz =xlwt.Alignment.HORZ_CENTER60 Alignment.Vert =xlwt.Alignment.VERT_CENTER61 Style.alignment =Alignment62 #格式

63

64 Tem_wb = xlrd.open_workbook(r'C:\Users\IBM\Desktop\新建文件夹\S1-3\7月下旬统计表模板.xls',formatting_info=True)65 Tem_sheet =Tem_wb.sheet_by_index(0)66

67 Result_wb =copy(Tem_wb)68 Result_sheet =Result_wb.get_sheet(0)69 #print(A_weight)

70 Result_sheet.write(2, 1, len(A_weight), Style)71 Result_sheet.write(2, 2, round(sum(A_weight), 2), Style)72 Result_sheet.write(2, 3, round(sum(A_total_price), 2), Style)73

74 Result_sheet.write(3, 1, len(B_weight), Style)75 Result_sheet.write(3, 2, round(sum(B_weight), 2), Style)76 Result_sheet.write(3, 3, round(sum(B_total_price), 2), Style)77

78 Result_sheet.write(4, 1, len(C_weight), Style)79 Result_sheet.write(4, 2, round(sum(C_weight), 2), Style)80 Result_sheet.write(4, 3, round(sum(C_total_price), 2), Style)81

82 Result_sheet.write(5, 1, len(D_weight), Style)83 Result_sheet.write(5, 2, round(sum(D_weight), 2), Style)84 Result_sheet.write(5, 3, round(sum(D_total_price), 2), Style)85

86 Result_wb.save(r'C:\Users\IBM\Desktop\新建文件夹\S1-3\7月下旬统计.xls')