How to Automate Excel Data Consolidation with Python: Openpyxl & Pandas Guide

Introduction

Hello, I'm Pipi. This article shares a Python automation task that was asked in a group, focusing on consolidating multiple Excel sheets.

Implementation

First solution uses openpyxl to load workbooks, create a summary sheet, and append rows from sheets whose name ends with “汇总”.

import openpyxl

def append_rows(sheet, rows):
    for row in rows:
        sheet.append(row)

wb = openpyxl.load_workbook('数据测试.xlsx', data_only=True)
sheet_names = wb.sheetnames
new_sheet = wb.create_sheet("全部汇总", 0)
new_sheet.append(['姓名','分数'])
for sheet in sheet_names:
    if sheet.endswith('汇总'):
        book = wb[sheet]
        rows = book.iter_rows(values_only=True, min_row=2, max_col=2)
        append_rows(new_sheet, rows)
wb.save('汇总数据.xlsx')

The second solution leverages pandas to read all summary sheets, concatenate them, and write the combined result.

file = '各种汇总.xlsx'
xlsx_file = pd.ExcelFile(file)
sheet_names = [s for s in xlsx_file.sheet_names if '汇总' in s]
dfs = pd.read_excel(file, sheet_name=sheet_names, usecols=[0,1])
df = pd.concat(dfs).reset_index(drop=True)
df.to_excel('汇总表.xlsx')
print(df)

Optionally, the DataFrame can be sorted by the “成绩” column in descending order:

df = df.sort_values(by='成绩', ascending=False, ignore_index=True)

Conclusion

The provided code demonstrates how to automate Excel data aggregation and ranking using Python, offering two practical approaches with openpyxl and pandas.