Python notes: Working with Excel files using Pywin32

Because the front-end colleague needs to do a lot of special processing of Excel file, delete the specified line, so write this script, at the same time with Config.ini convenient for colleagues unfamiliar with PY use

#!/usr/bin/env python#-*-coding:utf-8-*-######################################################## for bulk deletion of the specified line in Excel  # # applies to all office premises if you need to install PYWIN32 and office software ####################################################### #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 PA Th "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, the former opens a new process, The latter will re-use the Excel process Self.m_excel in the process. DisplayAlerts = False #覆盖同名文件时不弹出确认框def open (self, filename= "): ' Open Excel file ' if Getatt R (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 th E 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 IND Ex 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.getshe Etcount () +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 s Ht. 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 + L En (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.getsh (sheet). Rows.countdef Getmaxcol (self, sheet): "Get the max Col number, not the count of used col number" "Return Self.getsheet (SH ). 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 ' 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) m Axcol = Self.getmaxcol (sheet) Endrow = fromrow+count-1if fromrow > MaxRow or Endrow < 1:returnself.getrange (Sheet, FR Omrow, 1, Endrow, Maxcol). Delete () def deletecols (self, sheet, Fromcol, count=1): "Delete count cols of the sheet" "MaxRow = Self.getmaxrow (sheet) m Axcol = 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): I F sheet > Max_sheet_index:continuereduce = 0for row in DELETE_ROW_LIST:excel.deleteRow (sheet, row-reduce) reduce + = 1#e Xcel.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) ret Urn#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 ("Press any key to exit!") If __name__== "__main__": MaiN () 

The Config.ini file is as follows:

[Pathconfig]#;spath represents the Excel file directory that needs to be processed spath=./tests#;DP ath represents the processed Excel file directory dpath=./dest[otherconfig]#; FileList represents a list of Excel files that do not need to be specially processed, semicolon-delimited filelist=#;maxindex represent the first few tables that need to process each Excel file maxindex=1#; DeleteRows denotes the number of digits that need to be deleted, separated by semicolons deleterows=2;3

Python notes: Working with Excel files using Pywin32