Python筆記：使用pywin32處理excel檔案

標籤：python   excel   pywin32   

由於前端同事需要批量的對excel檔案做特殊處理，刪除指定行，故寫了此指令碼，同時配合config.ini方便不熟悉py的同事使用

#!/usr/bin/env python#-*- coding:utf-8 -*-########################################################  用於大量刪除excel的指定行                          ##  適用於所有office，前提需要安裝pywin32和office軟體  ########################################################import osimport sysimport timeimport globimport shutilimport stringimport os.pathimport tracebackimport ConfigParserimport win32com.clientSPATH = ""            #需處理的excel檔案目錄DPATH = ""            #處理後的excel存放目錄SKIP_FILE_LIST = []   #需要跳過的檔案清單MAX_SHEET_INDEX = 1   #每個excel檔案的前幾個表需要處理DELETE_ROW_LIST = []  #需要刪除的行號def dealPath(pathname=''):'''deal with windows file path'''if pathname:pathname = pathname.strip()if pathname:pathname = r'%s'%pathnamepathname = string.replace(pathname, r'/', '\\')pathname = os.path.abspath(pathname)if pathname.find(":\\") == -1:pathname = os.path.join(os.getcwd(), pathname)return pathnameclass EasyExcel(object):'''class of easy to deal with excel'''def __init__(self):'''initial excel application'''self.m_filename = ''self.m_exists = Falseself.m_excel = win32com.client.DispatchEx('Excel.Application') #也可以用Dispatch，前者開啟新進程，後者會複用進程中的excel進程self.m_excel.DisplayAlerts = False                             #覆蓋同名檔案時不彈出確認框def open(self, filename=''):'''open excel file'''if getattr(self, 'm_book', False):self.m_book.Close()self.m_filename = dealPath(filename) or ''self.m_exists = os.path.isfile(self.m_filename)if not self.m_filename or not self.m_exists:self.m_book = self.m_excel.Workbooks.Add()else:self.m_book = self.m_excel.Workbooks.Open(self.m_filename)def reset(self):'''reset'''self.m_excel = Noneself.m_book = Noneself.m_filename = ''def save(self, newfile=''):'''save the excel content'''assert type(newfile) is str, 'filename must be type string'newfile = dealPath(newfile) or self.m_filenameif not newfile or (self.m_exists and newfile == self.m_filename):self.m_book.Save()returnpathname = os.path.dirname(newfile)if not os.path.isdir(pathname):os.makedirs(pathname)self.m_filename = newfileself.m_book.SaveAs(newfile)def close(self):'''close the application'''self.m_book.Close(SaveChanges=1)self.m_excel.Quit()time.sleep(2)self.reset()def addSheet(self, sheetname=None):'''add new sheet, the name of sheet can be modify,but the workbook can't '''sht = self.m_book.Worksheets.Add()sht.Name = sheetname if sheetname else sht.Namereturn shtdef getSheet(self, sheet=1):'''get the sheet object by the sheet index'''assert sheet > 0, 'the sheet index must bigger then 0'return self.m_book.Worksheets(sheet)def getSheetByName(self, name):'''get the sheet object by the sheet name'''for i in xrange(1, self.getSheetCount()+1):sheet = self.getSheet(i)if name == sheet.Name:return sheetreturn Nonedef getCell(self, sheet=1, row=1, col=1):'''get the cell object'''assert row>0 and col>0, 'the row and column index must bigger then 0'return self.getSheet(sheet).Cells(row, col)def getRow(self, sheet=1, row=1):'''get the row object'''assert row>0, 'the row index must bigger then 0'return self.getSheet(sheet).Rows(row)def getCol(self, sheet, col):'''get the column object'''assert col>0, 'the column index must bigger then 0'return self.getSheet(sheet).Columns(col)def getRange(self, sheet, row1, col1, row2, col2):'''get the range object'''sht = self.getSheet(sheet)return sht.Range(self.getCell(sheet, row1, col1), self.getCell(sheet, row2, col2))def getCellValue(self, sheet, row, col):'''Get value of one cell'''return self.getCell(sheet,row, col).Valuedef setCellValue(self, sheet, row, col, value):'''set value of one cell'''self.getCell(sheet, row, col).Value = valuedef getRowValue(self, sheet, row):'''get the row values'''return self.getRow(sheet, row).Valuedef setRowValue(self, sheet, row, values):'''set the row values'''self.getRow(sheet, row).Value = valuesdef getColValue(self, sheet, col):'''get the row values'''return self.getCol(sheet, col).Valuedef setColValue(self, sheet, col, values):'''set the row values'''self.getCol(sheet, col).Value = valuesdef getRangeValue(self, sheet, row1, col1, row2, col2):'''return a tuples of tuple)'''return self.getRange(sheet, row1, col1, row2, col2).Valuedef setRangeValue(self, sheet, row1, col1, data):'''set the range values'''row2 = row1 + len(data) - 1col2 = col1 + len(data[0]) - 1range = self.getRange(sheet, row1, col1, row2, col2)range.Clear()range.Value = datadef getSheetCount(self):'''get the number of sheet'''return self.m_book.Worksheets.Countdef getMaxRow(self, sheet):'''get the max row number, not the count of used row number'''return self.getSheet(sheet).Rows.Countdef getMaxCol(self, sheet):'''get the max col number, not the count of used col number'''return self.getSheet(sheet).Columns.Countdef clearCell(self, sheet, row, col):'''clear the content of the cell'''self.getCell(sheet,row,col).Clear()def deleteCell(self, sheet, row, col):'''delete the cell'''self.getCell(sheet, row, col).Delete()def clearRow(self, sheet, row):'''clear the content of the row'''self.getRow(sheet, row).Clear()def deleteRow(self, sheet, row):'''delete the row'''self.getRow(sheet, row).Delete()def clearCol(self, sheet, col):'''clear the col'''self.getCol(sheet, col).Clear()def deleteCol(self, sheet, col):'''delete the col'''self.getCol(sheet, col).Delete()def clearSheet(self, sheet):'''clear the hole sheet'''self.getSheet(sheet).Clear()def deleteSheet(self, sheet):'''delete the hole sheet'''self.getSheet(sheet).Delete()def deleteRows(self, sheet, fromRow, count=1):'''delete count rows of the sheet'''maxRow = self.getMaxRow(sheet)maxCol = self.getMaxCol(sheet)endRow = fromRow+count-1if fromRow > maxRow or endRow < 1:returnself.getRange(sheet, fromRow, 1, endRow, maxCol).Delete()def deleteCols(self, sheet, fromCol, count=1):'''delete count cols of the sheet'''maxRow = self.getMaxRow(sheet)maxCol = self.getMaxCol(sheet)endCol = fromCol + count - 1if fromCol > maxCol or endCol < 1:returnself.getRange(sheet, 1, fromCol, maxRow, endCol).Delete()def echo(msg):'''echo message'''print msgdef dealSingle(excel, sfile, dfile):'''deal with single excel file'''echo("deal with %s"%sfile)basefile = os.path.basename(sfile)excel.open(sfile)sheetcount = excel.getSheetCount()if not (basefile in SKIP_FILE_LIST or file in SKIP_FILE_LIST):for sheet in range(1, sheetcount+1):if sheet > MAX_SHEET_INDEX: continuereduce = 0for row in DELETE_ROW_LIST:excel.deleteRow(sheet, row-reduce)reduce += 1#excel.deleteRows(sheet, 2, 2)excel.save(dfile)def dealExcel(spath, dpath):'''deal with excel files'''start = time.time()#check source path exists or not spath = dealPath(spath)if not os.path.isdir(spath):echo("No this directory :%s"%spath)return#check destination path exists or notdpath = dealPath(dpath)if not os.path.isdir(dpath):os.makedirs(dpath)shutil.rmtree(dpath)#list the excel filefilelist = glob.glob(os.path.join(spath, '*.xlsx'))if not filelist:echo('The path of %s has no excel file'%spath)return#deal with excel fileexcel = EasyExcel()for file in filelist:basefile = os.path.basename(file)destfile = os.path.join(dpath, basefile)dealSingle(excel, file, destfile)echo('Use time:%s'%(time.time()-start))excel.close()def loadConfig(configfile='./config.ini'):'''parse config file'''global SPATHglobal DPATHglobal SKIP_FILE_LISTglobal MAX_SHEET_INDEXglobal DELETE_ROW_LISTfile = dealPath(configfile)if not os.path.isfile(file):echo('Can not find the config.ini')return Falseparser = ConfigParser.ConfigParser()parser.read(file)SPATH = parser.get('pathconfig', 'spath').strip()DPATH = parser.get('pathconfig', 'dpath').strip()filelist = parser.get('otherconfig', 'filelist').strip()index = parser.get('otherconfig', 'maxindex').strip()rowlist = parser.get('otherconfig', 'deleterows').strip()if filelist:SKIP_FILE_LIST = filelist.split(";")if rowlist:DELETE_ROW_LIST = map(int, rowlist.split(";"))MAX_SHEET_INDEX = int(index) if index else MAX_SHEET_INDEXdef main():'''main function'''loadConfig()if SPATH and DPATH and MAX_SHEET_INDEX:dealExcel(SPATH, DPATH)raw_input("Please press any key to exit!")if __name__=="__main__":main()

config.ini檔案如下：

[pathconfig]#;spath表示需要處理的excel檔案目錄spath=./tests#;dpath表示處理後的excel檔案目錄dpath=./dest[otherconfig]#;filelist表示不需要做特殊處理的excel檔案清單,以英文分號分隔filelist=#;maxindex表示需要處理每個excel檔案的前幾張表maxindex=1#;deleterows表示需要刪除的阿拉伯數字行號，用英文分號分隔deleterows=2;3

Python筆記：使用pywin32處理excel檔案