pandasexecl2.py

import pandas as pd
import traceback

excel_path = 'toy_execl.xlsx'

sheet = pd.read_excel(excel_path, sheet_name='余额表-对账表汇总 (2)')

shdict = {}

for index, row in sheet.iterrows():
    name = '{mc}|{zh}|{fd}'.format(mc=str(row['代理名称']), zh=str(row['账户名']), fd=str(row['充值返点']))
    cz = round(float(row['账户币充值']), 3)
    xh = round(float(row['账户币消耗']), 3)
    tk = round(float(row['账户币退款']), 3)
    if shdict.get(name, ''):
        shdict[name][0] += cz
        shdict[name][1] += xh
        shdict[name][2] += tk
    else:
        shdict[name] = [cz,xh,tk]

resdict = {'代理名称':[],
           '账户名':[],
           '充值返点':[],
           '余额值':[]}

for k,v in shdict.items():
    try:
        res = v[0] - v[1] - v[2]
        res = round(res, 3)
        i = k.split('|')
        resdict['代理名称'].append(i[0])
        resdict['账户名'].append([i[1]])
        resdict['充值返点'].append(i[2])
        if not isinstance(res, float):
            res = 0.0

        resdict['余额值'].append(str(res))
    except:
        traceback.print_exc()
        print(k)


df = pd.DataFrame(resdict)
writer = pd.ExcelWriter('output.xlsx') #构建写入者
df.to_excel(writer,'Sheet1') #将内容写入到 execl中 名为Sheet1的表中
writer.save()