Python notes: Working with Excel files using Pywin32

Source: Internet
Author: User
Tags assert echo message glob

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.