A clear article using Python to batch generate target Excel tables based on original Excel tables

Hello everyone, I am a Python advanced user.

1. Foreword

A few days ago, when I was helping fans solve problems, I encountered a simple little request. I will share it with you here. When I encounter it again later, you can Get inspired here.

2. Clarification of requirements

Fans’ questions come from actual needs. The picture below is the original data. Data in another table needs to be filled in at 1-3:

If it is a normal operation, you must click into the Excel file, then copy each cell, then paste it into a new file, then save it, and then rename it.

This is definitely possible, but what if there are hundreds of folders that need to be copied? What about thousands of files? It will definitely require a lot of time and energy. It is estimated that it may not be completed in one day.

Here we use Python for batch implementation!

3. Implementation process

Here is a feasible code for everyone. The idea is also very simple. The openpyxl library implements it. The code is as follows:

import openpyxl

workbook1 = openpyxl.load_workbook("模板.xlsx")
worksheet1 = workbook1.worksheets[0]
print(worksheet1['C4'].value)# 金额
print(worksheet1['D4'].value)# 公司
print(worksheet1['F4'].value)# 编号

workbook2 = openpyxl.load_workbook("订单.xlsx")
worksheet2 = workbook2[0]
print(worksheet2['C3'].value)# 城市
print(worksheet2['D3'].value)# 编号
print(worksheet2['CU3'].value)# 金额
print(worksheet2['DM3'].value)# 公司

print(f"正在处理订单：{worksheet2['C3'].value}...")
worksheet1['C4'].value = worksheet2['CU3'].value
worksheet1['D4'].value = f"{worksheet2['DM3'].value}分公司"
worksheet1['F4'].value = worksheet2['D3'].value
new_file_name = f"（{worksheet2['C3'].value} {worksheet2['D3'].value}）"
workbook1.save(new_file_name + '.xlsx')
print(f"订单：{worksheet2['C3'].value}处理完成")

After the code is run, the corresponding data in the Excel file can be replaced. But this is just a replacement of a single file.

If you want to replace in batches, you need to add a for loop, as shown below:

for i in range(len(worksheet.row)):
print(f"正在第{i}行，处理订单：{worksheet2[f'C{i}'].value}...")
worksheet1['C4'].value = worksheet2[f'CU{i}'].value
worksheet1['D4'].value = f"{worksheet2[f'DM{i}'].value}分公司"
worksheet1['F4'].value = worksheet2[f'D{i}'].value
new_file_name = f"（{worksheet2[f'C{i}'].value} {worksheet2[f'D{i}'].value}）"
workbook1.save(new_file_name + '.xlsx')
time.sleep(3)
print(f"订单：{worksheet2[f'C{i}'].value}处理完成")

3. Summary

Hello everyone, I'm Pippi. This article mainly reviews a practical case of Python automated office work. This case can be applied to file processing in actual work. You can also slightly improve it and use it in your own actual work, and draw inferences from one example. Through this case, you should have learned a lot. I believe there should be other better methods. You are welcome to leave a message in the message area.

The above is the detailed content of A clear article using Python to batch generate target Excel tables based on original Excel tables. For more information, please follow other related articles on the PHP Chinese website!