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

The author needed to automatically fill a target Excel table with data from a source table, eliminating tedious copy‑paste and reducing errors.

Original table:

Target table:

Implementation

The following Python script uses pandas to read the template Excel file, extracts specific fields (name, ID, age, date) with regular expressions, builds a result dictionary, and writes the data to a new Excel file.

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

import pandas as pd
import re
import os

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

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

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

def main():
    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)
    os.system(resXls)

if __name__ == '__main__':
    main()

Running the script generates the expected output for a single table. For handling many tables, a follow‑up article will discuss batch processing.

Conclusion

The article demonstrates a practical Python automation solution for Excel data handling, providing a reusable script that can be adapted to similar tasks.