利用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的資料