python处理Excel自动统计表

• 使用python库xlutils、xlrd、xlwt。

• 设计思路

1. 使用xlrd.open_workbook读取统计数据表
2. 做一个总数据的空列表，
3. 使用for循环将使用的数据以字典的形式存储在总列表中
4. 将汇总的数据通过if的条件判断分配写入不同的列表中
5. 通过xlutils套用格式，写入到自动统计表中

• python的脚本范例

from xlutils.copy import copy
import xlrd
import xlwt

xlsx = xlrd.open_workbook(r'D:\python_work\1\CourseCode\Chapter1\S1-1-3\LessonCode\7月下旬入库表.xlsx')

table = xlsx.sheet_by_index(0)
all_data = []
for i in range(1, table.nrows):
    company = table.cell_value(i, 1)
    price = table.cell_value(i, 3)
    weight = table.cell_value(i, 4)

    data = {'company': company, 'price': price, 'weight': weight}
    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 i in all_data:
    if i['company'] == '王五小麦':
        a_weight.append(i['weight'])
        a_total_price.append(i['weight'] * i['price'])
    if i['company'] == '李四粮食':
        b_weight.append(i['weight'])
        b_total_price.append(i['weight'] * i['price'])
    if i['company'] == '赵六麦子专营':
        c_weight.append(i['weight'])
        c_total_price.append(i['weight'] * i['price'])
    if i['company'] == '张三粮配':
        d_weight.append(i['weight'])
        d_total_price.append(i['weight'] * i['price'])

tem_excel = xlrd.open_workbook(r'D:\python_work\1\CourseCode\Chapter1\S1-1-3\LessonCode\7月下旬统计表.xls',formatting_info=True)
tem_sheet = tem_excel.sheet_by_index(0)

new_excel = copy(tem_excel)
new_sheet = new_excel.get_sheet(0)
dstyle = xlwt.XFStyle()

font = xlwt.Font()
font.name = '微软雅黑'
font.bold = True
font.height = 360
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

aligment = xlwt.Alignment()
aligment.horz = xlwt.Alignment.HORZ_CENTER
aligment.vert = xlwt.Alignment.HORZ_CENTER
style.alignment = aligment


new_sheet.write(2, 1, len(a_weight), style)
new_sheet.write(2, 2, round(sum(a_weight), 2), style)
new_sheet.write(2, 3, round(sum(a_total_price), 2), style)
new_sheet.write(3, 1, len(b_weight), style)
new_sheet.write(3, 2, round(sum(b_weight), 2), style)
new_sheet.write(3, 3, round(sum(b_total_price), 2), style)
new_sheet.write(4, 1, len(c_weight), style)
new_sheet.write(4, 2, round(sum(c_weight), 2), style)
new_sheet.write(4, 3, round(sum(c_total_price), 2), style)
new_sheet.write(5, 1, len(d_weight), style)
new_sheet.write(5, 2, round(sum(d_weight), 2), style)
new_sheet.write(5, 3, round(sum(d_total_price), 2), style)


new_excel.save(r'D:\python_work\统计表.xls')