PYTHON操作EXCEL

# -*- coding: utf-8 -*- from win32com.client import Dispatch import win32com.client class easyExcel: """A utility to make it easier to get at Excel. Remembering to save the data is your problem, as is error handling. Operates on one workbook at a time.""" def __init__(self, filename=None): self.xlApp = win32com.client.Dispatch('Excel.Application') self.xlApp.Visible=1 if filename: self.filename = filename self.xlBook = self.xlApp.Workbooks.Open(filename) else: self.xlBook = self.xlApp.Workbooks.Add() self.filename = '' def save(self, newfilename=None): if newfilename: self.filename = newfilename self.xlBook.SaveAs(newfilename) else: self.xlBook.Save() def close(self): self.xlBook.Close(SaveChanges=0) del self.xlApp def getCell(self, sheet, row, col): "Get value of one cell" sht = self.xlBook.Worksheets(sheet) return sht.Cells(row, col).Value def setCell(self, sheet, row, col, value): "set value of one cell" sht = self.xlBook.Worksheets(sheet) sht.Cells(row, col).Value = value def getRange(self, sheet, row1, col1, row2, col2): "return a 2d array (i.e. tuple of tuples)" sht = self.xlBook.Worksheets(sheet) return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value def getList(self,sheet,row1,col1,row2,col2): sht = self.xlBook.Worksheets(sheet) list = [] for row in range(row1,row2+1): for col in range(col1,col2+1): list.append(self.getCell(sheet,row,col)) return list def addPicture(self, sheet, pictureName, Left, Top, Width, Height): "Insert a picture in sheet" sht = self.xlBook.Worksheets(sheet) sht.Shapes.AddPicture(pictureName, 1, 1, Left, Top, Width, Height) def cpSheet(self, before): "copy sheet" shts = self.xlBook.Worksheets shts(1).Copy(None,shts(1)) def mergecell(self,sheet,row1,col1,row2,col2): "merge cells" if row1>row2:return 'input error,row2 should be more than row1' if col1>col2:return 'input error,col2 should be more than col1' r1 = row1 c1 = col1 r2 = row2 c2 = col2 sht = self.xlBook.Worksheets(sheet) if row2 == row1:row2=row1+1 if row2>row1 : row2 = row1 + 2 col2 = col1 + 2 for i in range(row1,row2): for j in range(col1,col2): sht.Cells(i,j).Value = None mr = sht.Range(sht.Cells(r1,c1),sht.Cells(r2,c2)) mr.MergeCells=True def clearcell(self,sheet,row1,col1): "merge cells" r1 = row1 c1 = col1 sht = self.xlBook.Worksheets(sheet) sht.Cells(r1,c1).Value = None def clearallcell(self,sheet,row1,col1,row2,col2): "clear cells" if row1>row2:return 'input error,row2 should be more than row1' if col1>col2:return 'input error,col2 should be more than col1' sht = self.xlBook.Worksheets(sheet) row2 = row2 + 1 col2 = col2 + 1 for i in range(row1,row2): for j in range(col1,col2): sht.Cells(i,j).Value = None def sumcell(self,sheet,row1,col1,row2,col2,rowall): "sum cells" if row1>row2:return 'input error,row2 should be more than row1' if col1>col2:return 'input error,col2 should be more than col1' r1 = row1 c1 = col1 r2 = row2 c2 = col2 sht = self.xlBook.Worksheets(sheet) row2=row2+1 col2 = col2 + 1 for i in range(col1,col2): val = 0 for j in range(row1,row2): if sht.Cells(j,i).Value == None: sht.Cells(j,i).Value = 0 val = val + int(sht.Cells(j,i).Value) sht.Cells(rowall,i).Value = val def copycell(self,sheet,frow1,fcol1,frow2,fcol2,erow1,ecol1,erow2,ecol2): "clear cells" diff_col=fcol1-ecol1 sht = self.xlBook.Worksheets(sheet) frow2 = frow2 + 1 fcol2 = fcol2 + 1 for i in range(frow1,frow2): for j in range(fcol1,fcol2): sht.Cells(i,j-diff_col).Value = sht.Cells(i,j).Value