Batch modification of Excel file xlwings in python (2)

Time:2020-8-18
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.

xlwings

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[0]

    # 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()
Operation mode
  • 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

Wordy time

To borrow my little brother’s mantra: methods are more than difficulties.