将多个sheet合并成一个sheet导出，将多个dataframe导出至多个sheet

最新推荐文章于 2025-11-02 16:02:06 发布

原创

最新推荐文章于 2025-11-02 16:02:06 发布 · 930 阅读

1 ·

CC 4.0 BY-SA版权

# -*- coding: utf-8 -*-
"""
Created on Mon Jan 20 13:02:27 2020

@author: twx253
"""

import xlrd
import pandas as pd
from pandas import DataFrame
from openpyxl import load_workbook

excel_name = 'C:/Users/twx253/Desktop/many_sheets.xlsx'
wb = xlrd.open_workbook(excel_name)
sheets = wb.sheet_names() # 获取workbook中所有的表格
# 循环遍历所有sheet
alldata = DataFrame()
for i in range(len(sheets)):
df = pd.read_excel(excel_name, sheet_name=i, index=False, encoding='utf8')
print(df.shape)
alldata = alldata.append(df)

#指定合并后的数据放在哪个excel里
writer = pd.ExcelWriter('C:/Users/twx253/Desktop/many_sheets.xlsx',engine='openpyxl')
book = load_workbook(writer.path)
writer.book = book

#指定合并后的数据放在哪个sheet里
alldata.to_excel(excel_writer=writer,sheet_name="ALLDATA",index=False)
writer.save()
writer.close()
print('处理完成