Automate Excel Data Transfer with Python and openpyxl: Step-by-Step Guide

Introduction

The author encountered a simple requirement while helping a fan and decided to share a Python solution for batch processing Excel files.

Requirement Clarification

The task is to fill cells 1‑3 in a target spreadsheet with data from another spreadsheet, as shown in the original data image.

Solution Overview

Using the openpyxl library, the script loads the template workbook and the order workbook, reads specific cells, and writes the values into the template. The single‑file version works for one pair of files.

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}处理完成")

To handle multiple files, a for loop iterates over rows, applying the same logic and saving each new workbook.

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}处理完成")

Conclusion

The article showcases a practical Python automation case for Excel file handling, encouraging readers to adapt and extend the script for their own workflows.