Batch modification of excel by python

In the morning, I suddenly got help from my little sister. How to use the program to delete the pictures in excel in batches? As a front-end dog, I felt a little frustrated. After all, this is not my specialty. Miss said python should be OK. google it now.

It has been more than two years since I started to learn python and gave up. I quickly made a list of questions

List of problems and solutions found later

• Single excel file

* 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 sheet

# In fact, xlutils can also be used to modify the worksheet,
# I didn't use it, because: on the data, demo uses this one, although demo doesn't work
from xlutils.copy import copy
...
wb = open_workbook(path + '/' + name)
...
# Copy the original file. Because the original file can only be read and cannot be written to data, copy to get a file that can be written to data
newwb = copy(wb)
...
for row in sheet.get_rows():
    # Traverse each row. When the value of 8 columns is less than 12, change the value to 0
    if row[0].value < 12:
        newsheet.write(index, 0, 0)
...

* * preservation

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

• excel files under files

**Get file list

import os


os.listdir(path)

Well, the functions are as follows:

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")
    # Put the absolute path and relative path of the file to be read in parentheses 
    # os.getcwd() returns the absolute path of the current. py file
    # print(os.getcwd(), 'lujing')
    wb = open_workbook(path + '/' + name)
    # Get the first form of the read file
    # sheet = wb.sheet_by_index(0)
    # Get the number of rows in this 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 and cannot be written to data, copy to get a file that can be written to 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. When the value of 8 columns is less than 12, change the value to 0
                # 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:

Only the cell content can be modified, and the picture can't be read yet. After the modification, all the pictures in the table are lost. But almost meet the needs of my little sister. I'll let my little brother come to optimize when I'm free.

Operation mode

• Put the files you need to modify into the origin folder. If you don't have to work hard to create them manually
• Install python

I think all of you who read this article are big guys. You don't have to post it. You can also read other articles in my python learning column
Just to mention, if it is windows, after the installation is completed, if you run python under cmd, you will be prompted

python is not an internal command XXXXXXX

For example, find the installation directory of python and add it to the environment variable (as well as the directory of pip)
Let's give you an example

Installation directory of Python: D: \ software \ Python 27
 pip installation directory: D: \ software \ Python 27 \ scripts

Click OK - > Restart CMD - > CD excel? Opt.py

Real process

Although the article is written in this way, the actual programming process is as follows

• Read excel file
• Modify the data of the first worksheet
• Save excel file
• Read all excel files in the folder
• Traverse the worksheet under each excel and modify it

Wordy time

In fact, the whole process didn't take a lot of time, and the realization of basic functions had a certain connection with my previous entry to give up, although at that time the door also entered. So, it's still useful to learn more. Maybe it can be used some day.

I don't know if I can meet the needs of my little sister, but I really don't have time to optimize today. After all, I'm still deeply involved in work and taking baby.

Record it first, and then I will research and supplement some knowledge points above.