Because front-end colleagues need to batch check excel Special treatment of documents , Delete specified row , So I wrote this script . At the same time config.ini I'm not familiar with py My colleagues use

#!/usr/bin/env python
#-*- coding:utf-8 -*- #######################################################
# For batch deletion excel Specified line for #
# Apply to all office. The premise is to install pywin32 and office Software #
####################################################### import os
import sys
import time
import glob
import shutil
import string
import os.path
import traceback
import ConfigParser
import win32com.client SPATH = "" # What needs to be dealt with excel File folder
DPATH = "" # After processing the excel Storage folder SKIP_FILE_LIST = [] # List of files to skip
MAX_SHEET_INDEX = 1 # every last excel The first few tables of the file need to be processed
DELETE_ROW_LIST = [] # Line number to delete def dealPath(pathname=''):
'''deal with windows file path'''
if pathname:
pathname = pathname.strip()
if pathname:
pathname = r'%s'%pathname
pathname = string.replace(pathname, r'/', '\\')
pathname = os.path.abspath(pathname)
if pathname.find(":\\") == -1:
pathname = os.path.join(os.getcwd(), pathname)
return pathname class EasyExcel(object):
'''class of easy to deal with excel''' def __init__(self):
'''initial excel application'''
self.m_filename = ''
self.m_exists = False
self.m_excel = win32com.client.DispatchEx('Excel.Application') # It can also be used Dispatch, The former starts a new process , The latter reuses the in-process excel process
self.m_excel.DisplayAlerts = False # The confirmation box will not pop up when the file with the same name is overlapped 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 = None
self.m_book = None
self.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_filename
if not newfile or (self.m_exists and newfile == self.m_filename):
self.m_book.Save()
return
pathname = os.path.dirname(newfile)
if not os.path.isdir(pathname):
os.makedirs(pathname)
self.m_filename = newfile
self.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.Name
return sht def 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 sheet
return None def 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).Value def setCellValue(self, sheet, row, col, value):
'''set value of one cell'''
self.getCell(sheet, row, col).Value = value def getRowValue(self, sheet, row):
'''get the row values'''
return self.getRow(sheet, row).Value def setRowValue(self, sheet, row, values):
'''set the row values'''
self.getRow(sheet, row).Value = values def getColValue(self, sheet, col):
'''get the row values'''
return self.getCol(sheet, col).Value def setColValue(self, sheet, col, values):
'''set the row values'''
self.getCol(sheet, col).Value = values def getRangeValue(self, sheet, row1, col1, row2, col2):
'''return a tuples of tuple)'''
return self.getRange(sheet, row1, col1, row2, col2).Value def setRangeValue(self, sheet, row1, col1, data):
'''set the range values'''
row2 = row1 + len(data) - 1
col2 = col1 + len(data[0]) - 1
range = self.getRange(sheet, row1, col1, row2, col2)
range.Clear()
range.Value = data def getSheetCount(self):
'''get the number of sheet'''
return self.m_book.Worksheets.Count def getMaxRow(self, sheet):
'''get the max row number, not the count of used row number'''
return self.getSheet(sheet).Rows.Count def getMaxCol(self, sheet):
'''get the max col number, not the count of used col number'''
return self.getSheet(sheet).Columns.Count def 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-1
if fromRow > maxRow or endRow < 1:
return
self.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 - 1
if fromCol > maxCol or endCol < 1:
return
self.getRange(sheet, 1, fromCol, maxRow, endCol).Delete() def echo(msg):
'''echo message'''
print msg def 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:
continue
reduce = 0
for 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 not
dpath = dealPath(dpath)
if not os.path.isdir(dpath):
os.makedirs(dpath)
shutil.rmtree(dpath)
#list the excel file
filelist = glob.glob(os.path.join(spath, '*.xlsx'))
if not filelist:
echo('The path of %s has no excel file'%spath)
return
#deal with excel file
excel = 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 SPATH
global DPATH
global SKIP_FILE_LIST
global MAX_SHEET_INDEX
global DELETE_ROW_LIST file = dealPath(configfile)
if not os.path.isfile(file):
echo('Can not find the config.ini')
return False
parser = 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_INDEX def 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 Files such as the following :

[pathconfig]
#;spath It means to deal with excel File folder
spath=./tests
#;dpath After processing excel File folder
dpath=./dest [otherconfig]
#;filelist No special treatment is needed excel File list , Separated by semicolons
filelist=
#;maxindex It means to deal with every excel The first tables of the document
maxindex=1
#;deleterows Indicates the Arabic numeral line number to be deleted , Separate with semicolons
deleterows=2;3

Python note : Use pywin32 Handle excel More articles about the document

  1. python Use xlrd Module reading and writing Excel Method of file

    This article gives an example of python Use xlrd Module reading and writing Excel Method of file . Share with you for your reference . As follows : One . install xlrd modular To python Download from the official website http://pypi.python.org/pypi ...

  2. use Python Of pandas Frame operation Excel Data in files

    use Python Of pandas Frame operation Excel Data in files Purpose of this paper , Is to show you how to use pandas To perform some common Excel Mission . Some examples are trivial , But I think showing these simple things and those you can do in their ...

  3. Use pywin32 Handle excel file

    #!/usr/bin/env python #-*- coding:utf-8 -*- ####################################################### ...

  4. Python A little experiment —— read &amp; Write Excel The contents of the document

    install xlrd Module and xlwt modular Read Excel File content requires additional modules -- \(xlrd\), You can find it on the official website :https://pypi.python.org/pypi/xlrd#download ...

  5. python Interface automation 21- download excel file (Content-Type:octets/stream)

    Preface Content-Type The type is octets/stream, This is usually a file type , For example, sometimes you need to export excel data , download excel How to use this kind of scene python To implement? ? Grasp the download interface 1. Download scenarios like ...

  6. be based on Python Interface automation for - Reading and writing excel file

    introduction Use python Interface test data is often needed for interface test . Assertion interface function . Verify interface response status, etc , If a large number of interface test case scripts write the interface test case data in the script file , This way, the entire interface test case script code will look ...

  7. Record python Interface automation testing -- Operate excel The method of file encapsulation ( Item 5 )

    How to operate it has been supplemented excel file , How to get excel Of documents sheet object . Row number . Encapsulation of cell data , Convenient to call later handle_excel.py# coding:utf-8 import ...

  8. Python Use xlwt modular operation Excel file

    export Excel file     1.  Use xlwt modular import xlwt import xlwt    # Import xlwt # Create a new one excel file file = xlwt.Workbook() # ...

  9. Python Operation of automatic office Excel file

    Module import import openpyxl Read Excel file open Excel file workbook = openpyxl.load_workbook("test.xlsx") Output ...

Random recommendation

  1. Springmvc jar Package introduction

    spring.jar It's a single... With a full release jar package ,spring.jar In addition to spring-mock.jar All other than what is contained in jar The contents of the package , Because it's only used in a development environment spring-m ...

  2. Unity Command line arguments

    Usually ,Unity You can start by double clicking the icon on the desktop , It can also be started by entering the command line ( for example ,MacOS Terminal or Windows Of CMD window ), In this way, commands and messages are received at startup . We can make some gadgets and Uni ...

  3. asp Life cycle

    note : If you don't , Welcome to point out . When a user visits a page , It's actually sending http Request to server , and http The request is actually socket Send specific specifications ( agreement ) Words of ( message ). And then the server's http.sys The kernel module accepts please ...

  4. adb Port occupied

    The program cannot be executed ,kill Drop it in the task manager adb service , There's still something wrong with reconnecting the device It turns out that it might be adb Port occupied see adb Which port is used :C:\Users\wanglin>adb nodaemon serv ...

  5. getSystemService Detailed explanation

     android Running in the background of many service, They are activated when the system starts up SystemServer Turn on , Support the normal operation of the system , such as MountService Monitor whether there is SD Card installation and removal ,ClipboardServi ...

  6. The finger of the sword offer Interview questions 4 Replace blank space (java)

    notes : utilize java in stringBuilder,append,length Method is very convenient to solve the string problem /* * The finger of the sword offer Replace blank space * xsf * */ /* Functions that start replacing spaces ,length Is the original number ...

  7. Oracle Enable scott user

    First, check whether the current database has scott user select username,account_status from dba_users where username like '%SCOTT%'; If ...

  8. Android And webview Detailed explanation

    Outline of the article One .webview Basic introduction 1. What is? webview2. Why use webview3.webview Basic operation Two .webview Advanced use 1.WebView state 2. Resource loading 3.WebView load ...

  9. SWPU News backstage login page

    Final rendering : <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <ti ...

  10. ORACLE Of rownum Explain the usage

    If you choose to set foot , One day you will fall in love with this road . Let's talk about today ORACLE About China ROWNUM Usage of : One . A brief introduction ROWNUM What is it? , What can be used for . answer :ROWNUM It's a sequence , Will be based on sql Sentence automatically adds a ...