Batch modification of excel by Python
Because in the last article, I used xlrd and xlutils to modify excel. After running, the table format and pictures were all gone. My little brother was too busy and had to study it himself.
In the past two days, I checked the library of Python to modify excel, and I finally chose xlwings, which is very simple to use
All codes are as follows:
# -*- coding: utf-8 -*- import os import xlwings as xw def editExlXL(app, originPath, fileItem): #Open existing workbooks (support relative path and absolute path) wb = app.books.open(originPath + '/' + fileItem) #Get the first sheet sht = wb.sheets # print(sht, 'sheet') #Modify the value of A1 # sht.range('a1').value = '' for sheet in wb.sheets: for picture in sheet.pictures: height = picture.height top = picture.top #Delete the picture at the specified location if (top < 10 and height < 30): picture.delete() wb.save('./data/' + fileItem) #Close the workbook wb.close() def mkdir(path): folder = os.path.exists(path) if not folder: os.makedirs(path) print('--- folder mk ---') else: print('--- folder exists ---') def getFileList(path): return os.listdir(path) def editAll(): #New Workbook (open only, no new) app = xw.App(visible=True, add_book=False) # wb = app.books.add() originPath = './origin' fileList = getFileList(originPath) print(fileList) #Clear data before running # if os.path.exists('./data'): # os.removedirs("./data") #Create data folder mkdir('./data') for fileItem in fileList: editExlXL(app, originPath, fileItem) #Exit Excel app.quit() editAll()
- Put the file you need to modify into the origin folder. If you don’t have to create it manually, then follow the following method
pip install xlwings python excel_opt.py
You can also refer to other articles in my Python learning column
xlwings API Document
To borrow my little brother’s mantra: methods are more than difficulties.