Batch modification of Excel files in Python

Time:2020-9-19
Batch modification of excel by Python

In the morning, I suddenly received a little sister’s help. How to use the program to delete the pictures in Excel in batch? As a front-end dog, I feel a little cluttered. After all, this is not my specialty. Miss, python should be OK. Google it quickly.

It has been more than two years since I started learning Python to give up. I quickly made a list of questions in my heart

List of problems and subsequent solutions found

**Read

#Using xlrd to read excel file
wb = open_workbook(path + '/' + name)

**Get each sheet

#Get the sheet list of the current file
sheetList = wb.sheets()
...
for sheet in sheetList:
...

**Modify worksheet

#You can use xlutils to modify the worksheet. In fact, you can also use xlwt,
#I don't use it. The reason is: the demo is used in the data, although the demo doesn't run through
from xlutils.copy import copy
...
wb = open_workbook(path + '/' + name)
...
#Copy the original file, because the original file can only be read, can not write data, so copy to get a file that can write data
newwb = copy(wb)
...
for row in sheet.get_rows():
    #Traverse each row and change the value to 0 when the value of column 8 is less than 12
    if row[0].value < 12:
        newsheet.write(index, 0, 0)
...

**Preservation

newwb.save('./data/' + name)
  • Excel file under file

**Get file list

import os


os.listdir(path)

Well, the functions are as follows:
Batch modification of Excel files in Python

All codes are as follows:

# -*- coding: utf-8 -*-
from xlrd import open_workbook
from xlutils.copy import copy
import os


def editExl(path, name):
    if os.path.exists('/data'):
        os.removedirs("/data")
    #The absolute path of the file to be read is placed in brackets, and the relative path can also be used 
    Wei os.getcwd () returns the absolute path of the current. Py file
    # print(os.getcwd(), 'lujing')
    wb = open_workbook(path + '/' + name)
    #Gets the first form of the read file
    # sheet = wb.sheet_by_index(0)
    #Gets the number of rows in the form
    # s = sheet.nrows


    #Get the sheet list of the current file
    sheetList = wb.sheets()
    # print('sheetList', sheetList)


    #Copy the original file, because the original file can only be read, can not write data, so copy to get a file that can write data
    newwb = copy(wb)
    sheetIndex = 0
    for sheet in sheetList:


        #Get the first form of a writable file
        newsheet = newwb.get_sheet(sheetIndex)
        # print(newsheet, newsheet.get_rows())
        index = 0
        try:
            for row in sheet.get_rows():
                #Traverse each row and change the value to 0 when the value of column 8 is less than 12
                # print(row)
                # print(row[0].value, '000000000000000')
                if row[0].value < 12:
                    # print('here', index)
                    newsheet.write(index, 0, 0)
                    # print('after here')
                index = index + 1
        except:
            print("aaa")
        sheetIndex = sheetIndex + 1


    mkdir('./data')
    newwb.save('./data/' + name)


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():
    originPath = './origin'
    fileList = getFileList(originPath)
    # print(fileList)
    for fileItem in fileList:
        editExl(originPath, fileItem)
editAll()
Defects:

You can only modify the contents of the cell, but the picture can’t be read. After the modification, all the pictures in the table are lost. But also almost meet the needs of my little sister, I will let my little brother come to optimize.

Operation mode
  • Put the files you need to modify into the origin folder, if you don’t have to create them manually
  • Install Python

Those who see my article should be big guys. You don’t need to post it. You can also see other articles in my Python learning column
Just to mention one point, if it is windows, after the installation is completed, if you run Python prompt under CMD

Python is not an internal command XXXXXX

For example, find the python installation directory and add it to the environment variable (and Pip’s directory)
Let’s take an example

Pthon installation directory: D: software / Python 27
PIP installation directory: D: software / Python 27 / scripts

Batch modification of Excel files in Python

Click OK > Restart CMD > CD excel_ opt.py Folder where

Batch modification of Excel files in Python

Real process

Although the article is written like this, but the actual programming process is like this

  • Read excel file
  • Modify the data for the first sheet
  • Save excel file
  • Read all Excel files under the folder
  • Traverse each excel worksheet and modify it
Wordy time

In fact, the whole process didn’t take a lot of time. The realization of basic functions had a certain connection with my previous entry to give up, although the door was not entered at that time. I feel it’s useful to learn more. Maybe I can use it one day. [manual]

I don’t know whether to meet the needs of my little sister, but I really don’t have time to optimize today. After all, I’m still stuck in my work and taking care of my baby.

First of all, I will take time to study and supplement some of the above knowledge points.

Recommended Today

Analysis of super comprehensive MySQL statement locking (Part 1)

A series of articles: Analysis of super comprehensive MySQL statement locking (Part 1) Analysis of super comprehensive MySQL statement locking (Part 2) Analysis of super comprehensive MySQL statement locking (Part 2) Preparation in advance Build a system to store heroes of the Three KingdomsheroTable: CREATE TABLE hero ( number INT, name VARCHAR(100), country varchar(100), PRIMARY […]