1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
| from win32com.client import Dispatch, constants
import os
class Utility(object):
@staticmethod
def xlSmartOpen(app, filePath, fileName):
wbRet = None
for wb in app.Workbooks:
if wb.Name == fileName:
wbRet = wb
break
else:
if filePath=='': # mean that I am opening Result File
if os.path.exists(fileName):wbRet = app.Workbooks.Open(fileName)
else:
wbRet=app.Workbooks.Add()
wbRet.SaveAs(fileName)
else:
wbRet = app.Workbooks.Open(filePath+fileName)
return wbRet
class ManipExcelFiles(object):
def __init__(self, folder, xlSim , xlResult):
# Next implement that if file name = * we should take all the files in folder
self.excel = Dispatch("Excel.Application")
self.excel.Visible = False
# I open Result File
self.resultWb = Utility.xlSmartOpen(self.excel, '', xlResult)
# I loop over input file
if xlSim=='*.xls':
dirList=os.listdir(folder)
else:
dirList = [xlSim]
ColumnIndic = 1
for filename in dirList:
self.InputFileWb = Utility.xlSmartOpen(self.excel, folder, filename)
self.InputFileWs = self.InputFileWb.Worksheets('Inputs and results')
self.InputFileWs.Activate()
self.InputFileWs.Cells(1,1).Select()
self.InputFileWs.Range(self.InputFileWs.Cells(1,1),self.InputFileWs.Cells(1547,6)).Select()
self.excel.Selection.Copy()
self.resultWb.Worksheets[0].Activate()
self.resultWb.Worksheets[0].Cells(1,ColumnIndic).Select()
self.excel.Selection.PasteSpecial( Paste=constants.xlPasteValues)
#self.excel.Selection.PasteSpecial( Paste=constants.xlPasteFormats)
self.excel.CutCopyMode = False
self.InputFileWb.Close(SaveChanges=False)
ColumnIndic = ColumnIndic + 6
def shut(self):
self.excel.CutCopyMode = False
self.resultWb.Close(SaveChanges=True)
del self.excel
testfile = ManipExcelFiles('C:/essai/OUTPUT_1/','*.xls','C:/Essai/result.xls')
testfile.shut() |
Partager