一个修改EXCEL表格中单元格数据的例子

""" Make sure the filename is in the form of a abs path, or a relative path when the spreadsheet file or its sub dir is in the same location with easyExcel.py Row & Col index are 1 based for default """ import win32com.client import os import time import string import win32api class ArguErr:pass #Constants """I have no idea of how to use the original constants class in the gen_py package, so just create a mini one below :-( """ class constants: xlRed =win32api.RGB(255,0,0) xlGreen =win32api.RGB(0,255,0) xlBlue =win32api.RGB(0,0,255) xlMagenta =win32api.RGB(255,0,255) xlHairline =0x1 # from enum XlBorderWeight xlMedium =-4138 # from enum XlBorderWeight xlThick =0x4 # from enum XlBorderWeight xlThin =0x2 # from enum XlBorderWeight xlContinuous =0x1 # from enum XlLineStyle xlDash =-4115 # from enum XlLineStyle xlDashDot =0x4 # from enum XlLineStyle xlDashDotDot =0x5 # from enum XlLineStyle xlDot =-4118 # from enum XlLineStyle xlDouble =-4119 # from enum XlLineStyle xlLineStyleNone =-4142 # from enum XlLineStyle xlSlantDashDot =0xd # from enum XlLineStyle xlDiagonalDown =0x5 # from enum XlBordersIndex xlDiagonalUp =0x6 # from enum XlBordersIndex xlEdgeBottom =0x9 # from enum XlBordersIndex xlEdgeLeft =0x7 # from enum XlBordersIndex xlEdgeRight =0xa # from enum XlBordersIndex xlEdgeTop =0x8 # from enum XlBordersIndex xlInsideHorizontal =0xc # from enum XlBordersIndex xlInsideVertical =0xb # from enum XlBordersIndex xlPasteAll =-4104 # from enum XlPasteType xlPasteAllExceptBorders =0x7 # from enum XlPasteType xlPasteColumnWidths =0x8 # from enum XlPasteType xlPasteComments =-4144 # from enum XlPasteType xlPasteFormats =-4122 # from enum XlPasteType xlPasteFormulas =-4123 # from enum XlPasteType xlPasteFormulasAndNumberFormats=0xb # from enum XlPasteType xlPasteValidation =0x6 # from enum XlPasteType xlPasteValues =-4163 # from enum XlPasteType xlPasteValuesAndNumberFormats =0xc # from enum XlPasteType 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') if filename: if os.path.isabs(filename): self.filename = filename else: self.filename = os.path.abspath(filename) assert os.path.isfile(self.filename), "No such file exists in your located path!" self.xlBook = self.xlApp.Workbooks.Open(self.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 show(self): self.xlApp.Visible = 1 def hide(self): self.xlApp.Visible = 0 # # now for the helper methods # def setRangeVal(self, sheet, rangeStr, Val): """supply a simply way to access the large indexed cells """ sht = self.xlBook.Worksheets(sheet) sht.Range(rangeStr).Value = Val def getRangeVal(self, sheet, rangeStr): """supply a simply way to access the large indexed cells """ sht = self.xlBook.Worksheets(sheet) return sht.Range(rangeStr).Value def getSheetsCount(self): return self.xlBook.Worksheets.Count def setBorderStyle(self, sheet, range, style): """ range can be any form of the legal Excel format! xlHairline =0x1 # from enum XlBorderWeight xlMedium =-4138 # from enum XlBorderWeight xlThick =0x4 # from enum XlBorderWeight xlThin =0x2 # from enum XlBorderWeight xlContinuous =0x1 # from enum XlLineStyle xlDash =-4115 # from enum XlLineStyle xlDashDot =0x4 # from enum XlLineStyle xlDashDotDot =0x5 # from enum XlLineStyle xlDot =-4118 # from enum XlLineStyle xlDouble =-4119 # from enum XlLineStyle xlLineStyleNone =-4142 # from enum XlLineStyle xlSlantDashDot =0xd # from enum XlLineStyle xlDiagonalDown =0x5 # from enum XlBordersIndex xlDiagonalUp =0x6 # from enum XlBordersIndex xlEdgeBottom =0x9 # from enum XlBordersIndex xlEdgeLeft =0x7 # from enum XlBordersIndex xlEdgeRight =0xa # from enum XlBordersIndex xlEdgeTop =0x8 # from enum XlBordersIndex xlInsideHorizontal =0xc # from enum XlBordersIndex xlInsideVertical =0xb # from enum XlBordersIndex """ if len(style) == 3: linestyle, color, weight = style sht = self.xlBook.Worksheets(sheet) sht.Range(range).Borders.LineStyle = linestyle sht.Range(range).Borders.Weight = weight sht.Range(range).Borders.Color = color else: borderindex, linestyle, color, weight = style sht = self.xlBook.Worksheets(sheet) sht.Range(range).Borders(borderindex).LineStyle = linestyle sht.Range(range).Borders(borderindex).Weight = weight sht.Range(range).Borders(borderindex).Color = color def setRowFont(self, sheet, row, font): fontname, size, bold, italic = font sht = self.xlBook.Worksheets(sheet) sht.Range("%s:%s"%(row,row)).Font.Name = fontname sht.Range("%s:%s"%(row,row)).Font.Size = size sht.Range("%s:%s"%(row,row)).Font.Bold = bold sht.Range("%s:%s"%(row,row)).Font.Italic = italic def pasteRow(self, sheet, row, pastevalues = 1): """ xlPasteAll =-4104 # from enum XlPasteType xlPasteAllExceptBorders =0x7 # from enum XlPasteType xlPasteColumnWidths =0x8 # from enum XlPasteType xlPasteComments =-4144 # from enum XlPasteType xlPasteFormats =-4122 # from enum XlPasteType xlPasteFormulas =-4123 # from enum XlPasteType xlPasteFormulasAndNumberFormats=0xb # from enum XlPasteType xlPasteValidation =0x6 # from enum XlPasteType xlPasteValues =-4163 # from enum XlPasteType xlPasteValuesAndNumberFormats =0xc # from enum XlPasteType """ sht = self.xlBook.Worksheets(sheet) #sht.Range(sht.Cells(row,1), sht.Ce