有没有一种方法在Python中将Excel工作簿合并成一个主工作簿?

huangapple go评论87阅读模式
英文:

Is there a way to combine Excel workbooks into one Master workbook within python?

问题

我想知道是否可以在不将所有的Excel工作簿合并到一个主工作簿的单个工作表中的情况下实现这一点。

(更确切地说)

一个主工作簿,包含所有从属工作簿的工作表。

这张图片展示了我想做的事情

也许有一种方法可以在Python中使用VBA脚本来实现这一点?

英文:

I would like to know if this is possible without merging all Excel workbooks into a single Worksheet within a Master workbook

(rather)

A Master workbook, containing the sheets of all the slave workbooks

This picture shows what I'd like to do

Perhaps is there a way to use VBA script within python to do this?

答案1

得分: 2

是的,可以使用Python和Pandas来实现这个目标。您可以按表格打开多个文件,然后将它们简单地写入单个输出文档。更多信息,请参阅此处的文档

您需要根据您的用例更改变量名称,但这是一个通用的想法:

mon_sheet = pd.read_excel('file_1_path.xlsx', sheetname='Mon')
tue_sheet = pd.read_excel('file_1_path.xlsx', sheetname='Tue')
wed_sheet = pd.read_excel('file_1_path.xlsx', sheetname='Wed')
thu_sheet = pd.read_excel('file_2_path.xlsx', sheetname='Thu')
fri_sheet = pd.read_excel('file_2_path.xlsx', sheetname='Fri')
sat_sheet = pd.read_excel('file_3_path.xlsx', sheetname='Sat')
sun_sheet = pd.read_excel('file_3_path.xlsx', sheetname='Sun')

with pd.ExcelWriter('output.xlsx') as writer:
    mon_sheet.to_excel(writer, sheet_name='Mon')
    tue_sheet.to_excel(writer, sheet_name='Tue')
    wed_sheet.to_excel(writer, sheet_name='Wed')
    thu_sheet.to_excel(writer, sheet_name='Thu')
    fri_sheet.to_excel(writer, sheet_name='Fri')
    sat_sheet.to_excel(writer, sheet_name='Sat')
    sun_sheet.to_excel(writer, sheet_name='Sun')

希望这有所帮助!

英文:

Yes, this is achievable using python and pandas. You can open multiple files by sheet and simply write them to a single output document. See the documentation here for more information.

You'll need to change the variable names to fit your use case, but here is a generic idea:

mon_sheet = pd.read_excel('file_1_path.xlsx', sheetname = 'Mon')
tue_sheet = pd.read_excel('file_1_path.xlsx', sheetname = 'Tue')
wed_sheet = pd.read_excel('file_1_path.xlsx', sheetname = 'Wed')
thu_sheet = pd.read_excel('file_2_path.xlsx', sheetname = 'Thu')
fri_sheet = pd.read_excel('file_2_path.xlsx', sheetname = 'Fri')
sat_sheet = pd.read_excel('file_3_path.xlsx', sheetname = 'Sat')
sun_sheet = pd.read_excel('file_3_path.xlsx', sheetname = 'Sun')

with pd.ExcelWriter('output.xlsx') as writer: 
	mon_sheet.to_excel(writer, sheet_name = 'Mon')
	tue_sheet.to_excel(writer, sheet_name = 'Tue')
	wed_sheet.to_excel(writer, sheet_name = 'Wed')
	thu_sheet.to_excel(writer, sheet_name = 'Thu')
	fri_sheet.to_excel(writer, sheet_name = 'Fri')
	sat_sheet.to_excel(writer, sheet_name = 'Sat')
	sun_sheet.to_excel(writer, sheet_name = 'Sun')

huangapple
  • 本文由 发表于 2020年1月4日 01:38:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/59582955.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定