python处理excel
需求：本人最近做一个project，从log里查找对应文字段，并将之写入excel，并画图，将图保存成jpg文件，如果手工一个个处理会很麻烦，于是，python进入了视线。本人使用python的win32com库来做，最好是先将此过程录制成excel宏，然后根据宏来编写python程序。1 # $Revision: #1 $
2 # $DateTime: 2011/10/13 14:08:58 $
3 # $Author: yabi.c.chen $
4 import win32com.client
5 import re,os,glob,Image
6 win32c = win32com.client.constants
7 class GetProjectData:
8 def __init__(self,DataTypeList = []):
9 self.app = win32com.client.Dispatch('Excel.Application')
10 self.app.Visible = True
11 self.app.DisplayAlerts = False
12 self.app.AlertBeforeOverwriting = False
13 self.DataTypeList = DataTypeList
14 if self.DataTypeList:
15 for DataType in self.DataTypeList:
16 self.DataType = DataType
17 self.IsDrawChart = False
18 if DataType == 'HMS':
19 self.State = "WATERFALL"
20 self.SheetName = ['VBAR_BY_ZONE','AFTER_ADJ_BPIC1','AFTER_ADJ_CLR','BEFORE_ADJ_BPIC2','AFTER_ADJ_BPIC2']
21 self.pat = "HMS MEASUREMENTS RESULT:.*?HMS MEASUREMENTS RESULT END"
22 self.IsDrawChart = True
23 elif DataType == 'BPI':
24 self.State = "WATERFALL"
25 self.SheetName = ['VBAR_BY_ZONE','AFTER_ADJ_BPIC1','AFTER_ADJ_BPIC2']
26 self.pat = "Hd TPIC_Avg TPIP_Avg TPIM_Avg BPI_Avg BPIP_Avg BPIM_Avg.*?EXECUTION INFO:"
27 else:
28 continue
29 try:
30 self.AddData2Xls()
31 except:
32 raise
33
34 def __del__(self):
35 self.app.Quit()
36
37 def DrawHMSChart(self,workbook,fileName,SerialNum,headCount):
38 AllJPGPath = os.path.dirname(fileName)+os.sep+'all.jpg'
39 Width = 536*(2000/536)
40 Height = 270*(len(self.SheetName)/(2000/536)+1)
41 AllJPG = Image.new('RGB',(Width,Height))
42 AllJPG.paste(0xFFFFFF,(0,0,Width,Height))
43 for index,Sheet in enumerate(workbook.Sheets):
44 workbook.Sheets(Sheet.Name).Activate()
45 workbook.Sheets(Sheet.Name).Cells(1,1).Activate()
46 chart = Sheet.ChartObjects().Add(200,100,400,200).Chart #chart = workbook.Charts.Add()
47 chart.ChartType = win32c.xlLineMarkers
48 chartTitle = SerialNum + '_' + Sheet.Name
49 chart.ApplyLayout(1)
50 chart.Axes(win32c.xlCategory).TickLabelPosition = win32c.xlLow
51 chart.ChartTitle.Text = chartTitle
52 chart.Axes(win32c.xlValue, win32c.xlPrimary).AxisTitle.Text = "HMS"
53 #chart.Axes(win32c.xlCategory).TickLabelSpacing = 5
54 chart.Axes(win32c.xlValue).MinimumScale = 0
55 chart.Axes(win32c.xlValue).MaximumScale = 8
56 chart.Axes(win32c.xlValue).MajorUnit = 0.5
57 for hd in range(headCount):
58 series = chart.SeriesCollection().NewSeries()
59 chart.SeriesCollection(hd+1).Name = "Hd %s" % hd
60 chart.SeriesCollection(hd+1).Values = "='%s'!$C$%d:$C$%d" % (Sheet.Name,2+24*hd,25+24*hd)
61 chart.Location(Where = win32c.xlLocationAsObject,Name = Sheet.Name)
62 #Save Chart as picture to local
63 JPGFile = os.path.dirname(fileName)+os.sep+chartTitle+'.jpg'
64 Sheet.ChartObjects(1).Chart.Export(JPGFile,'jpg')
65 if index < 3:
66 AllJPG.paste(Image.open(JPGFile),(index*536,0))
67 else:
68 AllJPG.paste(Image.open(JPGFile),((index-3)*536,270))
69 AllJPG.save(AllJPGPath)
70 workbook.Save()
71
72 def AddData2Xls(self):
73 filelist = glob.glob("*.txt")
74 for myfile in filelist:
75 SN = myfile[:8]
76 myfolder = os.path.splitext(os.path.basename(myfile))
77 if not os.path.isdir(myfolder):
78 os.mkdir(myfolder)
79 rawdata = self.GetRawData(myfile)
80 workbook = self.app.Workbooks.Add()
81 for index,data in enumerate(rawdata):
82 worksheet = workbook.Sheets.Add(workbook.Sheets)
83 worksheet.Name = self.SheetName
84 Data = []
85 rowIndex = 0
86 startIndex = int(1 and self.DataType != 'BPI' or 0)
87 for linedata in data.splitlines():
88 rowIndex +=1
89 line = linedata.strip().split('\t')
90 for colIndex in range(len(line)):
91 try:
92 line = float(line)
93 except:
94 pass
95 Data.append(line)
96 HD =int(Data[-1].strip()[-1]) + 1 #Get Head count
97 worksheet.Range(worksheet.Cells(1,1),worksheet.Cells(rowIndex,len(line))).Value = Data
98 for sh in workbook.Sheets:
99 if sh.Name not in self.SheetName: #Delete Sheet1,Sheet2 and Sheet3
100 workbook.Sheets(sh.Name).Delete()
101 filename = os.getcwd()+os.sep+myfolder+os.sep+myfolder+'_'+self.DataType+'.xls'
102 workbook.SaveAs(filename)
103 if self.IsDrawChart and self.DataType == 'HMS':
104 self.DrawHMSChart(workbook,filename,SN,HD)
105 workbook.Close()
106
107 def GetRawData(self,Filename):
108 f = open(Filename,'r')
109 rawdata = str(f.read())
110 #rawdata = rawdata
111 rawdatapat = '- '*20 + self.State + ' : BEGIN ' + ' -'*20 + '(.*?)' + '- '*20 + self.State + ' : COMPLETE' + ' -'*20
112 rawdata = re.findall(rawdatapat,rawdata,re.DOTALL)
113 data = re.findall(self.pat,rawdata[-1],re.DOTALL)
114 f.close()
115 return data
116
117 if __name__ == '__main__':
118 DataTypeList = ['HMS','BPI']
119 GetProjectData(DataTypeList)
页:
[1]