4

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')
pnuts
  • 58,317
  • 11
  • 87
  • 139
Tian He
  • 272
  • 1
  • 5
  • 14
  • 2
    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. – Bjoern Stiel Mar 07 '14 at 13:25

2 Answers2

2

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()
Tian He
  • 272
  • 1
  • 5
  • 14
  • 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\\autobench\\test\\CM1.xlsx\u3002\u662f\u5426\u53ef\u80fd\u88ab\u79fb\u52a8\u3001\u91cd\u547d\u540d\u6216\u5220\u9664?', u'xlmain11.chm', 0, -2146827284), None) – Dogod Jun 01 '18 at 04:56
-1

this will paste sheet 1 ot to_copy to sheet 1 of emptyWorkbook

empty_wb = xl.Workbooks.Open(util.getTestRessourcePath("emptyWorkbook.xlsx"))
tocopy_wb = xl.Workbooks.Open(util.getTestRessourcePath("toCopy.xls"))

tocopy_wb.Sheets(1).Cells.Copy()
empty_wb.Sheets(1).Paste(empty_wb.Sheets(1).Range("A1"))
sliders_alpha
  • 2,276
  • 4
  • 33
  • 52