Excel表格汇总问题

Question

有多个工作簿，每个工作簿有sheet1、sheet2等多个工作表，如何快速汇总到一个工作簿多个工作表上

Answer 1

这是我刚写过的多个工作表汇总到一个工作簿里，原文件只有两列：商户、销售额
仅供参考：

import pandas as pd
df = pd.read_excel('d:\\done.xlsx', sheet_name=None)
dic = {}
for sheetname in df.keys():
    for lines in range(df[sheetname].shape[0]):
        k = df[sheetname].loc[lines][0]
        v = df[sheetname].loc[lines][1]
        v = 0 if pd.isnull(v) else v
        dic[k] = dic.get(k,[])+[(v,sheetname)]
 
header = list(df.keys())
toxlsx = [['']+header]

print(' ', end='')
for i in range(len(header)):
    print(f'{header[i]:>9}',end='')

print('\n'+'-'*(9*len(header)+4))

for k,v in dic.items():
    print(f'{k:<4}',end='')
    i = -1
    t = [k]
    for n in v:
        i += 1
        while n[1]!=header[i]:
            i += 1
            print(f'{0:>9}', end='')
            t.append(0)
        print(f'{n[0]:>9}', end='')
        t.append(n[0])
    if i<len(header):
        print(f'{0:>9}'*(len(header)-i-1))
        t.extend([0]*(len(header)-i-1))
    toxlsx.append(t)

df = pd.DataFrame(toxlsx)
df.to_excel('newxls.xlsx',index=False,header=False)