06-16 借助win32com 向excel插入图片，捎带排版

因为我现在每天的第一项工作，就是整理出压力测试的报告，自动化工具会在每天早上准时把各种日志和趋势图发到我的指定邮箱。然后我要把44张png图片拍到excel中。44张图片分为两类，一类CPU性能，一类各种MEM性能。以前每人考虑过用脚本来做，所以都是手工排版的，我昨天第一天接触，想到了win32com应该可以做到插入图片。

我计算好，每张图片原始插入后的原始大小是（446,177），然后我打算所有图片排成2列。第一列全是CPU的，第二列是对应的MEN的。

代码不是很难理解，但我觉得可能写的有点粗糙 ，呵呵

 

 1 #!/usr/bin/env python
 2 #coding=utf-8
 3 #需要先修改Pics的位置。生成的excel位于c:\test.xlsx.（word2007,如果要2003的，把filename后缀改成xls即可）
 4 from win32com.client import Dispatch
 5 import win32com.client
 6 import os
 7 
 8 def creatExcel(filename):
 9     """creat a new excel to edit"""
10     excel = win32com.client.Dispatch("Excel.Application")        
11     d1 = excel.Workbooks.Add()      
12     sheet = d1.Sheets(1)   
13     d1.SaveAs(filename)   
14     excel.Quit()
15     
16 
17 class easyExcel:
18       """A utility to make it easier to get at Excel.    Remembering
19       to save the data is your problem, as is    error handling.
20       Operates on one workbook at a time."""
21 
22       def __init__(self, filename=None):
23           self.xlApp = win32com.client.Dispatch('Excel.Application')
24           if filename:
25               self.filename = filename
26               self.xlBook = self.xlApp.Workbooks.Open(filename)
27           else:
28               self.xlBook = self.xlApp.Workbooks.Add()
29               self.filename = ''  
30     
31       def save(self, newfilename=None):
32           if newfilename:
33               self.filename = newfilename
34               self.xlBook.SaveAs(newfilename)
35           else:
36               self.xlBook.Save()    
37 
38       def close(self):
39           self.xlBook.Close(SaveChanges=0)
40           del self.xlApp
41 
42       def getCell(self, sheet, row, col):
43           "Get value of one cell"
44           sht = self.xlBook.Worksheets(sheet)
45           return sht.Cells(row, col).Value
46 
47       def setCell(self, sheet, row, col, value):
48           "set value of one cell"
49           sht = self.xlBook.Worksheets(sheet)
50           sht.Cells(row, col).Value = value
51 
52       def getRange(self, sheet, row1, col1, row2, col2):
53           "return a 2d array (i.e. tuple of tuples)"
54           sht = self.xlBook.Worksheets(sheet)
55           return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value
56 
57       def addPicture(self, sheet, pictureName, Left, Top, Width, Height):
58           "Insert a picture in sheet"
59           sht = self.xlBook.Worksheets(sheet)
60           sht.Shapes.AddPicture(pictureName, 1, 1, Left, Top, Width, Height)
61   
62       def cpSheet(self, before):
63           "copy sheet"
64           shts = self.xlBook.Worksheets
65           shts(1).Copy(None,shts(1))
66 
67 if __name__ == "__main__":
68       dic = 'c:\\temp'
69       i = 0
70       j = 0
71       list = os.listdir(dic)
72       dict = {0:list[0],1:list[11],2:list[22],3:list[33],4:list[39],5:list[40],6:list[41],7:list[42],8:list[43],9:list[1],10:list[2],\
73                11:list[3],12:list[4],13:list[5],14:list[6],15:list[7],16:list[8],17:list[9],18:list[10],19:list[12],20:list[13],21:list[14],\
74               22:list[15],23:list[16],24:list[17],25:list[18],26:list[19],27:list[20],28:list[21],29:list[23],30:list[24],31:list[25],32:list[26],33:list[27],34:list[28],\
75               35:list[29],36:list[30],37:list[31],38:list[32],39:list[34],40:list[35],41:list[36],42:list[37],43:list[38]}
76       filename = 'c:\\test.xlsx'
77       creatExcel(filename)
78       xls = easyExcel(filename)
79       for odd in range(0,44,2):
80         PNFILE_odd = dic+'\\'+dict[odd]
81         xls.addPicture('Sheet1', PNFILE_odd, 0,i*177,446,177)
82         i += 1
83       for even in range(1,45,2):
84         PNFILE_even = dic+'\\'+dict[even]
85         xls.addPicture('Sheet1', PNFILE_even, 460,j*177,446,177)
86         j += 1
87       xls.cpSheet('Sheet1')
88       xls.save()
89       xls.close()
90 
91