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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
| #!python.exe
import site
#!python.exe
import site
from win32com.client import Dispatch, constants
import os
# PUT YOUR OWN PATH ===============================================
MasterFilePath = 'C:/Essai/masterfile.xls'
OutputFilePath = 'C:/Essai/OUTPUT_1/'
RecievedFilesPath = 'c:/Essai/received_files/'
ResultFilePath = 'C:/Essai/'
ResultFileName = 'Results.xls'
bExcelVisible = False
# ===============================================================
class Utility(object):
@staticmethod
def xlSmartOpen(app, fileName, filePath):
wbRet = None
for wb in app.Workbooks:
if wb.Name == fileName:
wbRet = wb
break
else:
if os.path.exists(filePath+fileName):wbRet = app.Workbooks.Open(filePath+fileName)
else:
wbRet=app.Workbooks.Add()
wbRet.SaveAs(filePath+fileName)
return wbRet
class ManipExcelFiles(object):
def __init__(self, folder, inputfilename , outputFolder,masterfile):
# Next implement that if file name = * we should take all the files in folder
self.excel = Dispatch("Excel.Application")
self.excel.Visible = bExcelVisible
# I open Masterfile
self.resultWb = self.excel.Workbooks.Open(masterfile)
self.AggregateWb = Utility.xlSmartOpen(self.excel,ResultFileName,ResultFilePath)
# I loop over input file
if inputfilename=='*.xls':
dirList=os.listdir(folder)
else:
dirList = [inputfilename]
ColumnIndic = 6
for filename in dirList:
print "Je fais "+ filename
# Je load les input file et j enleve la protection =================
self.InputFileWb = self.excel.Workbooks.Open(folder+filename)
self.InputFileWb.Worksheets('Result').Visible = True
self.InputFileWb.Worksheets('Result').Select
self.InputFileWb.Worksheets('Result').Unprotect('SOLSTICE')
self.InputFileWs = self.InputFileWb.Worksheets('Result')
# Je fais la copie =================
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()
# Je paste dans MasterFile =================
self.resultWb.Worksheets('Result').Activate()
self.resultWb.Worksheets('Result').Cells(1,1).Select()
self.excel.Selection.PasteSpecial( Paste=constants.xlPasteValues)
#self.excel.Selection.PasteSpecial( Paste=constants.xlPasteFormats)
# Je sauve MasterFile =================
self.excel.CutCopyMode = False
fileoutputname = outputFolder+self.resultWb.Worksheets('Result').Cells(2,6).Value+'.xls'
self.resultWb.SaveAs(fileoutputname)
# Je refais la copie =================
self.InputFileWs.Activate()
self.InputFileWs.Cells(1,1).Select()
self.InputFileWs.Range(self.InputFileWs.Cells(1,6),self.InputFileWs.Cells(1547,6)).Select()
self.excel.Selection.Copy()
# Je paste dans fichier aggregation =================
self.AggregateWb.Worksheets[0].Activate()
self.AggregateWb.Worksheets[0].Cells(1,ColumnIndic).Select()
self.excel.Selection.PasteSpecial( Paste=constants.xlPasteValues)
self.excel.Selection.PasteSpecial( Paste=constants.xlPasteFormats)
# J'incremente pour next paste =================
ColumnIndic = ColumnIndic + 1
# Je close input file =================
self.excel.CutCopyMode = False
self.InputFileWb.Close(SaveChanges=False)
# Loop is over je close le file result =================
self.excel.CutCopyMode = False
self.AggregateWb.Close(SaveChanges=True)
del self.excel
testfile = ManipExcelFiles(RecievedFilesPath,'*.xls',OutputFilePath,MasterFilePath)
# ===============================================================
FilePath = 'C:/Essai/'
DataProfilesfilename = 'DataProfiles.xls'
PlanPensionfilename = 'PlanPension.xls'
# ===============================================================
class ManipExcelMacro2(object):
# Start Excel and open the XLS file:
excel = Dispatch('Excel.Application')
excel.Visible = False
workbook = excel.Workbooks.Open(FilePath+PlanPensionfilename)
excel.Run('auto_open')
workbook.Close(SaveChanges=True)
workbook = excel.Workbooks.Open(FilePath+DataProfilesfilename)
excel.Run('START')
# Save as CSV:
# xlCSVWindows =0x17 # from enum XlFileFormat
# workbook.SaveAs(Filename=filepath+".csv",FileFormat=xlCSVWindows)
# Close workbook and Excel
workbook.Close(SaveChanges=True)
excel.Quit() |
