python合并多个excel表不同列,如何在python中将不同的excel文件合并到一个具有不同工作表名称的工作簿中...

I have two excel workbooks.

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()

Doing this gives me a workbook, but with only 2 sheets. First sheet of the first workbook and second sheet of second workbook.

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()