python 读取excel(ZT)
import win32com.clientimport osimport timeclass Excel: def __init__(self, filename=None, configuration = None): self.xApp = win32com.client.Dispatch(' Excel.Application ') self.Config = configuration #start info. #reading configuation file print "Setting standard font: %s" % self.Config['FONT'] self.xApp.StandardFont = self.Config['FONT'] print "Setting standard size: %s" % str(TextSize) self.xApp.StandardFontSize = TextSize #check configuration file if os.path.exists( self.Config['DATAPATH'] ) : pass else: print "Error: The given data path doesn't exist" self.close() # name of report if filename: self.Filename = filename try: self.xBook = self.xApp.Workbooks.Open(filename) except: print "Error: Open file %s failed" % filename else: self.xBook = self.xApp.Workbooks.Add() reportname = self.Config['PROJECT']+ '_R'+ self.Config['VERSION'] + '_' + time.strftime("%Y%m%d") + '_IssueReport' self.Filename = os.path.join( self.Config['PATH'], reportname) Name = self.Filename + '.xls' # if the file exists, rename new file as format oldname(number) try: if os.path.exists ( Name ): number = 2 # test new name while 1: Name = self.Filename + "(" + str(number) + ").xls" if os.path.exists ( Name ): number += 1 continue else : break self.Filename = self.Filename + "(" + str(number) + ").xls" print "Given name has been used, rename new report to %s" % self.Filename else: self.Filename += '.xls' print "Report name: %s" % self.Filename self.xBook.SaveAs( self.Filename ) print "Report file was created" except: print "Error: creating file failed" def save(self, newfilename=None): print "Saving report to %s" % self.Config['PATH'] if newfilename: self.Filename = newfilename self.xBook.SaveAs(newfilename) else: self.xBook.Save() print "Report saved" def close(self): self.xBook.Close(SaveChanges=0) # close error msg del self.xApp def show(self): print "Show generated report" self.xApp.Visible = 1 def delSheet(self, sheet): sht = self.xBook.Worksheets(sheet) try: sht.Delete() print "sheet %s is deleted" % sheet except: print "Error: Delete sheet %s failed" % sheet def hide(self): self.xApp.Visible = 0 # get value from cell def getCell(self,sheet, row, col): "Get value of one cell" sht = self.xBook.Worksheets(sheet) return sht.Cells (row, col).Value # set value for cell def setCell(self, sheet, row, col, value=None, formula=None): sht = self.xBook.Worksheets(sheet) if formula : sht.Cells(row, col).Formula = formula else: sht.Cells(row, col).Value = value # return tuple def getRange(self, sheet, row1, col1, row2, col2): "return a 2d array ( i.e. tuple of tuples)" sht = self.xBook.Worksheets(sheet) return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value # set tuple for range def setRange(self, sheet, topRow, leftCol, data): ''' set range data from a tuple ''' if type( data ) == types.TupleType: bottomRow = topRow + len(data) - 1 rightCol = leftCol + len(data) - 1 length = len(data) else: bottomRow = topRow rightCol = leftCol length = 1 sht = self.xBook.Worksheets(sheet) if( length > MaxAccept): times = length/MaxAccept; i = 0; # Multiple * MaxAccept data while( i < times ): sht.Range( sht.Cells (topRow + i*MaxAccept, leftCol), sht.Cells(topRow + (i+1)*MaxAccept - 1, rightCol) ).Value = data i += 1 # additional data besides Multiple MaxAccept sht.Range( sht.Cells(topRow + times * MaxAccept, leftCol), sht.Cells(bottomRow, rightCol) ).Value = data else: sht.Range( sht.Cells(topRow, leftCol), sht.Cells(bottomRow, rightCol) ).Value = data # rename a work sheet def setSheetName(self, sheet, name): ''' Rename a sheet ''' sht = self.xBook.Worksheets(sheet) #logging print "Rename sheet %s to %s" % ( sheet , name ) sht.Name = name
页:
[1]