How to merge several Excel sheets from different files into one file

Question

Each source file contains only one sheet. All I want is to combine these sheets into a single file. I suppose I'll have to use win32com.

Does anyone know how?

Update: the sheets to be combined have extensive conditional formatting that I'd like to keep. The following code could only combine them with all conditionally formatting information lost.

from openpyxl import load_workbook, Workbook
import os

fwb = Workbook()

wb = load_workbook('CM1.xlsx')
ws1 = wb.active
wb = load_workbook('CM2.xlsx')
ws2 = wb.active
wb = load_workbook('CM3.xlsx')
ws3 = wb.active

fwb.add_sheet(ws1)
fwb.add_sheet(ws2)
fwb.add_sheet(ws3)

fwb.save('CM.xlsx')

Solution

Thank you both! After taking your advice and trying for 5 minutes, the following worked!

import win32com.client as win32
import os

excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Add()

for f in [os.path.join(os.getcwd(), "CM1.xlsx"), os.path.join(os.getcwd(), "CM2.xlsx")]: 
    w = excel.Workbooks.Open(f) 
    w.Sheets(1).Copy(wb.Sheets(1))

wb.SaveAs(os.path.join(os.getcwd(), "CM.xlsx"))
excel.Application.Quit()

Comments

[Bjorn Stiel]

If you have Excel installed you can do something like this: import win32com.client excel = win32com.client.DispatchEx('Excel.Application') wb = excel.Workbooks.Add() for f in ['CM1.xlsx', 'CM2.xlsx', 'CM3.xlsx']: w = excel.Workbooks.Open(f) w.Sheets(1).Copy(wb.Sheets(1)) Let me know if you don't have Excel as I'm sure there's another way via openpyxl, xlrd and the likes. I'm not really familiar with any of these but am happy to have a look.

[Dogod]

Hi, I tried the code and had very weird issue, could you please advise.pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft Excel', u'\u62b1\u6b49\uff0c\u65e0\u6cd5\u627e\u5230 C:\\Users\\dogod\\Documents\\GitHub\\mmt-autobench\\autobenc‌​h\\test\\CM1.xlsx\u3‌​002\u662f\u5426\u53e‌​f\u80fd\u88ab\u79fb\‌​u52a8\u3001\u91cd\u5‌​47d\u540d\u6216\u522‌​0\u9664?', u'xlmain11.chm', 0, -2146827284), None)

[Dan Kerchner]

Which python library is the "xl" library referenced here?

[sliders_alpha]

@DanKerchner xl = win32.gencache.EnsureDispatch('Excel.Application')