如何在python中将不同的excel文件合并到一个具有不同工作表名称的工作簿中
问题描述:
我有两本Excel工作簿.
I have two excel workbooks.
一个带有3张纸,另一个只有一张.我正在尝试将这两部分合并为一本工作簿. 此工作簿应有4张纸.
One with 3 sheets and the other with only one sheet. I am trying to combine these two into one workbook. This workbook should have 4 sheets.
from pandas import ExcelWriter
writer = ExcelWriter("Sample.xlsx")
for filename in glob.glob("*.xlsx"):
df_excel = pd.read_excel(filename)
(_, f_name) = os.path.split(filename)
(f_short_name, _) = os.path.splitext(f_name)
df_excel.to_excel(writer, f_short_name, index=False)
writer.save()
这样做可以给我一个工作簿,但是只有2张纸.第一本工作簿的第一页和第二本工作簿的第二页.
Doing this gives me a workbook, but with only 2 sheets. First sheet of the first workbook and second sheet of second workbook.
如何在一个工作簿中获取所有4张纸?
How to get all the 4 sheets in one workbook?
答
您必须遍历工作表名称.请参见以下代码:
You have to loop through the sheet names. See the below code:
from pandas import ExcelWriter
import glob
import os
import pandas as pd
writer = ExcelWriter("output.xlsx")
for filename in glob.glob("*.xlsx"):
excel_file = pd.ExcelFile(filename)
(_, f_name) = os.path.split(filename)
(f_short_name, _) = os.path.splitext(f_name)
for sheet_name in excel_file.sheet_names:
df_excel = pd.read_excel(filename, sheet_name=sheet_name)
df_excel.to_excel(writer, f_short_name+'_'+sheet_name, index=False)
writer.save()