pandas 将多个DataFrame.to_excel写入不同的Sheet

话不多说直接上代码：

 

 

path = r"C:\Users\Administrator\Desktop"
if not os.path.exists(path):
    os.mkdir(path)
path1 = path + r'\test.xlsx'

book = xlrd.open_workbook(r'C:\Users\Administrator\Desktop\多个sheet的测试数据表.xlsx')
with pd.ExcelWriter(path1) as writer:
    sheet_name = book.sheet_names()
    for i in sheet_name:
        new_data = get_data(i)
        print(new_data)
        new_data.to_excel(writer, sheet_name=i)
    writer.save()

其中get_data是我读测试表返回的dataframe的函数 ，大致就是把每个sheet文件变成dataframe返回出来

def get_data(i):
    table = book.sheet_by_name(u"%s" % (i))
    data_list = []
    for i in range(table.nrows - 1):
        commpany_name = table.col(0)[i + 1].value
        fund_name = table.col(1)[i + 1].value
        amt_rank = table.col(2)[i + 1].value
        data_list.append((commpany_name, fund_name, amt_rank))
    data = pd.DataFrame(data_list, columns=["commpany_name", "fund_name", "manager_name"], dtype=object)
    return new_data