import xlrd

 import xlwt

 from xlutils.copy import copy

 objWb = xlrd.open_workbook(r'C:\Users\IBM\Desktop\新建文件夹\S1-3\7月下旬入库表.xlsx')

 objSht = objWb.sheet_by_index(0)

 # 定义列表，存储读取的内容

 All_Data = []

 for Nrow in range(1, objSht.nrows):  # nrows 返还sheet行数

     Company = objSht.cell(Nrow, 1).value

     Price = objSht.cell(Nrow, 3).value

     Weight = objSht.cell(Nrow, 4).value

     Data = {'Company': Company, 'Weight': Weight, 'Price': Price}

     All_Data.append(Data)

 # 张三

 A_weight = []

 A_total_price = []

 # 李四

 B_weight = []

 B_total_price = []

 # 王五

 C_weight = []

 C_total_price = []

 # 赵六

 D_weight = []

 D_total_price = []

 for D in All_Data:

     # print(D)

     if D['Company'] == '张三粮配':

         A_weight.append(D['Weight'])

         A_total_price.append(D['Price'])

     if D['Company'] == '李四粮食':

         B_weight.append(D['Weight'])

         B_total_price.append(D['Price'])

     if D['Company'] == '王五小麦':

         C_weight.append(D['Weight'])

         C_total_price.append(D['Price'])

     if D['Company'] == '赵六麦子专营':

         D_weight.append(D['Weight'])

         D_total_price.append(D['Price'])

 # 格式

 Style = xlwt.XFStyle()

 # 格式.字体

 Font = xlwt.Font()

 Font.name = '微软雅黑'

 Font.bold = True

 Font.height = 18 * 20

 Style.font = Font

 # 格式.边框

 Borders = xlwt.Borders()

 Borders.top = xlwt.Borders.THIN

 Borders.bottom = xlwt.Borders.THIN

 Borders.left = xlwt.Borders.THIN

 Borders.right = xlwt.Borders.THIN

 Style.borders = Borders

 # 格式.对齐

 Alignment = xlwt.Alignment()

 Alignment.horz = xlwt.Alignment.HORZ_CENTER

 Alignment.Vert = xlwt.Alignment.VERT_CENTER

 Style.alignment = Alignment

 # 格式

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

 Tem_sheet = Tem_wb.sheet_by_index(0)

 Result_wb = copy(Tem_wb)

 Result_sheet = Result_wb.get_sheet(0)

 # print(A_weight)

 Result_sheet.write(2, 1, len(A_weight), Style)

 Result_sheet.write(2, 2, round(sum(A_weight), 2), Style)

 Result_sheet.write(2, 3, round(sum(A_total_price), 2), Style)

 Result_sheet.write(3, 1, len(B_weight), Style)

 Result_sheet.write(3, 2, round(sum(B_weight), 2), Style)

 Result_sheet.write(3, 3, round(sum(B_total_price), 2), Style)

 Result_sheet.write(4, 1, len(C_weight), Style)

 Result_sheet.write(4, 2, round(sum(C_weight), 2), Style)

 Result_sheet.write(4, 3, round(sum(C_total_price), 2), Style)

 Result_sheet.write(5, 1, len(D_weight), Style)

 Result_sheet.write(5, 2, round(sum(D_weight), 2), Style)

 Result_sheet.write(5, 3, round(sum(D_total_price), 2), Style)

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