python 操作 excel
1. Use comtypes to control the Excel
1 ''' 2 Created on 2013-5-18 3 4 @author: Administrator 5 ''' 6 # -*- coding: utf-8 -*- 7 from comtypes.client import CreateObject 8 class Excel(object): 9 def __init__(self,filename = None): 10 self.xlapp = CreateObject("Excel.Application") 11 if filename: 12 self.filename = filename 13 self.xlbook = self.xlapp.Workbooks.Open(filename) 14 self.sheet = '' 15 else: 16 self.xlbook = self.xlapp.Workbooks.Add() 17 self.filename = '' 18 self.sheet = '' 19 def sheet_by_name(self,sheetname): 20 self.sheet = self.xlbook.Worksheets(sheetname) 21 return self.sheet 22 23 def save(self,newfilename = None): 24 if newfilename: 25 self.filename = newfilename 26 self.xlbook.SaveAs(newfilename) 27 else: 28 self.xlbook.Save() 29 30 def nrows(self,sheet): 31 sht = self.xlbook.Worksheets(sheet) 32 return sht.UsedRange.Rows.Count 33 34 def close(self): 35 self.xlbook.Close(0) 36 self.xlapp.Quit() 37 38 class Sheet(object): 39 def __init__(self, sheet): 40 self.sheet = sheet 41 42 def getCell(self,row,col): 43 return self.sheet.Cells.Item[row,col].Value[()] 44 45 def setCell(self,row,col,value): 46 self.sheet.Cells.Item[row,col].Value[()] = value 47 48 def nrows(self): 49 return self.sheet.UsedRange.Rows.Count 50 51 def main(): 52 try: 53 excel = Excel(r'C:\result.xlsx') 54 sheet = Sheet(excel.sheet_by_name("PCM")) 55 print sheet.getCell(16,6) 56 sheet.setCell(16,11,"pass") 57 excel.save() 58 print sheet.getCell(16,11) 59 except IOError as e: 60 print e 61 finally: 62 excel.close() 63 64 if __name__ == '__main__': 65 main()
2. Use pywin32 to control the Excel
1 ''' 2 Created on 2013-5-18 3 4 @author: Administrator 5 ''' 6 # -*- coding: utf-8 -*- 7 import pywintypes 8 import win32com.client 9 from win32com.client import Dispatch 10 11 class EasyExcel(object): 12 def __init__(self,filename = None): 13 self.xlapp = win32com.client.Dispatch("Excel.Application") 14 if filename: 15 self.filename = filename 16 self.xlbook = self.xlapp.workbooks.open(filename) 17 self.sheet = '' 18 else: 19 self.xlbook = self.xlapp.workbooks.Add() 20 self.filename = '' 21 self.sheet = '' 22 23 def sheet_by_name(self,sheetname): 24 self.sheet = self.xlbook.Worksheets(sheetname) 25 return self.sheet 26 27 def save(self,newfilename = None): 28 if newfilename: 29 self.filename = newfilename 30 self.xlbook.SaveAs(newfilename) 31 else: 32 self.xlbook.Save() 33 34 def nrows(self,sheet): 35 sht = self.xlbook.Worksheets(sheet) 36 return sht.UsedRange.Rows.Count 37 38 def close(self): 39 #del self.sheet 40 self.xlbook.Close(SaveChanges = 0) 41 del self.xlapp 42 43 class Sheet(object): 44 def __init__(self, sheet): 45 self.sheet = sheet 46 47 def getCell(self,row,col): 48 return self.sheet.Cells(row,col).Value 49 50 def setCell(self,row,col,value): 51 self.sheet.Cells(row,col).Value = value 52 53 def nrows(self): 54 return self.sheet.UsedRange.Rows.Count 55 56 def main(): 57 try: 58 excel = EasyExcel(r'D:\python\testresult\result.xlsx') 59 sheet = Sheet(excel.sheet_by_name("PCM")) 60 print sheet.getCell(16,6) 61 sheet.setCell(16,11,"pass") 62 excel.save() 63 print sheet.getCell(16,11) 64 65 except pywintypes.com_error as e: 66 print e 67 finally: 68 excel.close() 69 70 if __name__ == '__main__': 71 main()
