1.3 数据汇总案例

import xlrd
from xlutils.copy import copy

def read_data():
    wb = xlrd.open_workbook('./办公自动化/files/orders.xls')
    sh = wb.sheet_by_index(0)
    fen_type = {}
    count_price = []

    for r in range(1, sh.nrows):
        count = sh.cell_value(r, 3) * sh.cell_value(r, 4)
        count_price.append(count)
        key = sh.cell_value(r, 0)
        if fen_type.get(key):
            fen_type[key] += count
        else:
            fen_type[key] = count
    # print(count_price)
    # print(fen_type)

    return fen_type, count_price   # 商品分类的总价值，每个单品的总价值

def save(fen_type, count_price):
    wb = xlrd.open_workbook('./办公自动化/files/orders.xls')
    sh_t = wb.sheet_by_index(0)

    wb2 = copy(wb)
    sh = wb2.get_sheet(0)

    for r in range(1, sh_t.nrows):
        sh.write(r, sh_t.ncols + 1, count_price[r-1])

    sh2 = wb2.add_sheet('汇总数据')

    for i, key in enumerate(fen_type.keys()):
        sh2.write(i, 0, key)
        sh2.write(i, 1, fen_type.get(key))

    wb2.save('./办公自动化/files/orders.xls')

if __name__ == '__main__':
    f, c = read_data()
    save(f, c)