Automate Excel with Python: Add Sheet Names and Merge Files in Seconds

Introduction

In a recent Python community question a user needed to add a column with the workbook name to each sheet and then merge multiple Excel files. Manually handling dozens of files is tedious, so an automated solution using Python and pandas is presented.

Solution Overview

The approach opens every Excel file in a folder, concatenates all its sheets, adds a column named “表名” containing the file name (or file‑name‑sheet‑name), and finally concatenates all data frames into a single workbook.

Code Example 1 – Add File Name Column

# coding: utf-8
# Add a column with the Excel file name to each sheet
from pathlib import Path
import pandas as pd

path = Path(r'E:\PythonCrawler\python_crawler-master\MergeExcelSheet\file\777')
excel_list = [(i.stem, pd.concat(pd.read_excel(i, sheet_name=None))) for i in path.glob('*.xls*')]
data_list = []
for name, data in excel_list:
    print(name)
    print(data)
    data['表名'] = name
    data_list.append(data)
result = pd.concat(data_list, ignore_index=True)
result.to_excel(path.joinpath('add_file_name_column.xlsx'), index=False, encoding='utf-8')
print('Addition and merge completed!')

Code Example 2 – Add File and Sheet Name Column

# coding: utf-8
# Add a column with the Excel file name and sheet name
from pathlib import Path
import pandas as pd

path = Path(r'E:\PythonCrawler\python_crawler-master\MergeExcelSheet\file\777')
excel_list = [(i.stem, pd.concat(pd.read_excel(i, sheet_name=None))) for i in path.glob('*.xls*')]
data_list = []
for name, data in excel_list:
    print(name)
    print(data)
    data['表名'] = name
    data.reset_index(level=0, inplace=True)  # reset level_0 index
    data_list.append(data)
result = pd.concat(data_list, ignore_index=True)
result['表名'] = result['表名'] + '-' + result['level_0']
del result['level_0']
result.to_excel(path.joinpath('add_file_sheet_name.xlsx'), index=False, encoding='utf-8')
print('Addition and merge completed!')

Running the scripts generates new Excel files that contain the added “表名” column and a combined workbook, as shown in the screenshots below.

Conclusion

The provided Python‑pandas solution quickly adds file (and optionally sheet) identifiers to each row and merges all workbooks, saving the user considerable time compared to manual processing.

Source code is also available on GitHub: https://github.com/cassieeric/Python-office-automation