Automate Excel Data Entry with Python: Batch Processing Script Explained

Introduction

Hello, I’m Pi Pi. A fan needed a way to automatically fill target Excel tables with data from source tables to avoid tedious and error‑prone copy‑pasting.

The original table looks like this:

The desired target table looks like this:

Implementation

The following Python code uses pandas to read the source file, extract fields such as name, ID, age, and date, and write them into a new Excel file. It also iterates over all .xls files in the directory, processing each one in turn.

# -*- coding: utf-8 -*-
__author__ = 'Jason.Fan'

import pandas as pd
import re
import os
import glob

rawXls = '模板.xls'   # source template
resXls = 'res.xls'   # result file

rerule = r"(\d{4}-\d{1,2}-\d{1,2})"
resDict = {}

class SheetInfo:
    name = ''
    ID = ''
    age = ''
    date = ''

def main_sub(rawXls, resXls):
    df = pd.read_excel(rawXls)
    SheetInfo.name = df.columns[1]
    SheetInfo.ID = df.iloc[0, 1]
    SheetInfo.age = df.iloc[1, 1]
    SheetInfo.date = re.findall(rerule, df.iloc[1, 2])[0]
    
    print(SheetInfo.name, SheetInfo.ID, SheetInfo.age, SheetInfo.date)
    
    resDict['日期'] = SheetInfo.date
    resDict['姓名'] = SheetInfo.name
    resDict['ID'] = SheetInfo.ID
    resDict['年龄'] = SheetInfo.age
    
    ndf = df.iloc[4:, :]
    ndf.columns = range(6)
    for idx, v in ndf.iterrows():
        print(v[0], v[2], v[3])
        # core processing here
    
    finalDF = pd.DataFrame.from_dict(resDict, orient='index').T
    finalDF.to_excel(resXls, index=None)


def main():
    for fi in glob.glob('*.xls'):
        if '_res' in os.path.basename(fi):
            continue
        main_sub(fi, f"{os.path.basename(fi)}_res.xls")

if __name__ == '__main__':
    main()

Running this script processes every Excel file in the folder, producing the expected output files automatically.

Conclusion

The script provides a practical solution for batch‑processing Excel data with Python, extracting key information and generating new spreadsheets without manual intervention, thereby improving efficiency and reducing errors.