利用win32com处理excle,这样可以不管文件是老的xls还是新的xlsx格式,非常方便。
类的源码如下:
import win32com.client as win32 class easyExcel: #初始化读取文件的部分 def __init__(self, filename=None): self.xlApp = win32.Dispatch('Excel.Application') self.xlApp.Visible = False self.xlApp.DisplayAlerts = False if filename: self.filename = filename self.xlBook = self.xlApp.Workbooks.Open(filename) else: self.xlBook = self.xlApp.Workbooks.Add() self.filename = '' #获取sheet的名字,以列表返回 def getSheetsName(self): sheetList = [] sheetCount = self.xlApp.Worksheets.Count; print(sheetCount) for i in range(1,sheetCount+1): sheetList.append(self.xlBook.Sheets(i).Name) return sheetList #获取某个sheet的最大行数 def getMaxRows(self, sheet): sht = self.xlBook.Worksheets(sheet) return sht.UsedRange.Rows.Count #获取某个sheet的最大列数 def getMaxCols(self, sheet): sht = self.xlBook.Worksheets(sheet) return sht.UsedRange.Columns.Count #获取某个sheet的某行某列的数据,行列全是数字,从1开始 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 setRange(self, sheet, leftCol, topRow, data): """insert a 2d array starting at given location. Works out the size needed for itself""" bottomRow = topRow + len(data) - 1 rightCol = leftCol + len(data[0]) - 1 sht = self.xlBook.Worksheets(sheet) sht.Range( sht.Cells(topRow, leftCol), sht.Cells(bottomRow, rightCol) ).Value = data def getContiguousRange(self, sheet, row, col): """Tracks down and across from top left cell until it encounters blank cells; returns the non-blank range. Looks at first row and column; blanks at bottom or right are OK and return None witin the array""" sht = self.xlBook.Worksheets(sheet) # find the bottom row bottom = row while sht.Cells(bottom + 1, col).Value not in [None, '']: bottom = bottom + 1 # right column right = col while sht.Cells(row, right + 1).Value not in [None, '']: right = right + 1 return sht.Range(sht.Cells(row, col), sht.Cells(bottom, right)).Value #删除某行 或者删除第几行到第几行 1 '1:3' or 'A:C' def deleteRows(self, sheet, row_num): sht = self.xlBook.Worksheets(sheet) sht.Rows(row_num).Delete() #删除某列 或者删除第几列到第几列 1 '1:3' or 'A:C' def deleteRows(self, sheet, col_num): sht = self.xlBook.Worksheets(sheet) sht.Columns(col_num).Delete() 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
2、使用方法
#从写好的类导入方法 from doExcel import easyExcel #读取excle excel = easyExcel(r'D:\code\transExcle\testdata\test.xlsx') #获取Sheet1 第9行2列内的数据 print(excel.getCell('Sheet1', 9, 2)) #修改数据 excel.setCell('Sheet1',9,2,"newdata") #保存文件 excel.save(r'D:\code\transExcle\testdata\out.xlsx') #关闭文件 excel.close()
其他方法请自行看源码(第一段代码)可以完美的处理excle的数据