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
- Single excel file
#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: ...
#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.value < 12: newsheet.write(index, 0, 0) ...
newwb.save('./data/' + name)
- Excel file under file
**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") #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.value, '000000000000000') if row.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()
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.
- 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
Click OK > Restart CMD > CD excel_ opt.py Folder where
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
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.