Python Excel批量案例如何批量操作表格

wen python案例 2026-06-30 123

本文目录导读：

环境准备
批量读取Excel文件
批量处理单个Excel文件中的多个工作表
批量写入Excel文件
批量数据清洗和处理
高级批量操作
实用综合案例
性能优化建议

我来为您详细介绍Python批量操作Excel表格的常用方法和案例。

环境准备

首先安装必要的库：

pip install openpyxl pandas xlsxwriter

批量读取Excel文件

案例1：批量读取多个Excel文件并合并

import pandas as pd
import os
from glob import glob
# 方法1：读取指定文件夹下所有Excel文件
def batch_read_excel(folder_path):
    # 获取文件夹下所有xlsx文件
    excel_files = glob(os.path.join(folder_path, "*.xlsx"))
    all_data = []
    for file in excel_files:
        # 读取每个Excel文件
        df = pd.read_excel(file)
        # 添加文件名作为来源标识
        df['来源文件'] = os.path.basename(file)
        all_data.append(df)
    # 合并所有数据
    combined_df = pd.concat(all_data, ignore_index=True)
    return combined_df
# 使用示例
folder_path = "./excel_files/"
combined_data = batch_read_excel(folder_path)
print(f"合并后的数据形状: {combined_data.shape}")
print(combined_data.head())

批量处理单个Excel文件中的多个工作表

案例2：批量处理一个Excel文件中的所有sheet

import pandas as pd
def process_all_sheets(file_path):
    # 读取所有sheet
    excel_file = pd.ExcelFile(file_path)
    sheet_names = excel_file.sheet_names
    processed_data = {}
    for sheet_name in sheet_names:
        # 读取每个sheet
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        # 进行数据处理（示例：计算数值列的平均值）
        numeric_cols = df.select_dtypes(include=['float64', 'int64']).columns
        if len(numeric_cols) > 0:
            df[f'{numeric_cols[0]}_平均值'] = df[numeric_cols[0]].mean()
        processed_data[sheet_name] = df
    return processed_data
# 使用示例
result = process_all_sheets("销售数据.xlsx")
for sheet, data in result.items():
    print(f"Sheet: {sheet}, 形状: {data.shape}")

批量写入Excel文件

案例3：批量创建多个Excel文件

import pandas as pd
import numpy as np
from openpyxl import Workbook
import os
def batch_create_excel(output_folder, num_files=5):
    # 确保输出文件夹存在
    os.makedirs(output_folder, exist_ok=True)
    for i in range(1, num_files + 1):
        # 生成示例数据
        data = {
            '姓名': [f'员工{j}' for j in range(1, 11)],
            '部门': np.random.choice(['销售', '技术', '财务', '人事'], 10),
            '销售额': np.random.randint(10000, 50000, 10),
            '提成': np.random.randint(1000, 5000, 10)
        }
        df = pd.DataFrame(data)
        # 保存为Excel文件
        file_name = f"报表_{i}.xlsx"
        file_path = os.path.join(output_folder, file_name)
        df.to_excel(file_path, index=False, sheet_name=f'Sheet{i}')
        print(f"已创建: {file_name}")
# 使用示例
batch_create_excel("./output_reports/", 10)

批量数据清洗和处理

案例4：批量清洗多个Excel文件

import pandas as pd
import os
from glob import glob
def batch_clean_excel(input_folder, output_folder):
    # 创建输出文件夹
    os.makedirs(output_folder, exist_ok=True)
    # 获取所有Excel文件
    excel_files = glob(os.path.join(input_folder, "*.xlsx"))
    for file_path in excel_files:
        try:
            # 读取数据
            df = pd.read_excel(file_path)
            # 数据清洗操作
            # 1. 删除空行
            df = df.dropna(how='all')
            # 2. 填充数值列的缺失值
            numeric_cols = df.select_dtypes(include=['float64', 'int64']).columns
            df[numeric_cols] = df[numeric_cols].fillna(0)
            # 3. 删除重复行
            df = df.drop_duplicates()
            # 4. 去除字符串前后的空格
            string_cols = df.select_dtypes(include=['object']).columns
            for col in string_cols:
                df[col] = df[col].str.strip()
            # 保存清洗后的数据
            output_path = os.path.join(output_folder, os.path.basename(file_path))
            df.to_excel(output_path, index=False)
            print(f"已清洗: {os.path.basename(file_path)}")
        except Exception as e:
            print(f"处理 {file_path} 时出错: {e}")
# 使用示例
batch_clean_excel("./raw_data/", "./cleaned_data/")

高级批量操作

案例5：批量修改Excel格式和样式

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
import os
from glob import glob
def batch_format_excel(folder_path):
    # 定义样式
    header_font = Font(bold=True, color="FFFFFF", size=12)
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    data_font = Font(size=11)
    center_align = Alignment(horizontal='center', vertical='center')
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    # 获取所有Excel文件
    excel_files = glob(os.path.join(folder_path, "*.xlsx"))
    for file_path in excel_files:
        try:
            # 加载工作簿
            wb = load_workbook(file_path)
            for sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
                # 格式化表头
                for cell in ws[1]:
                    cell.font = header_font
                    cell.fill = header_fill
                    cell.alignment = center_align
                    cell.border = thin_border
                # 格式化数据区域
                for row in ws.iter_rows(min_row=2, max_row=ws.max_row, max_col=ws.max_column):
                    for cell in row:
                        cell.font = data_font
                        cell.alignment = center_align
                        cell.border = thin_border
                # 自动调整列宽
                for col in ws.columns:
                    max_length = 0
                    column = col[0].column_letter
                    for cell in col:
                        try:
                            if len(str(cell.value)) > max_length:
                                max_length = len(str(cell.value))
                        except:
                            pass
                    adjusted_width = min(max_length + 2, 50)
                    ws.column_dimensions[column].width = adjusted_width
            # 保存修改
            wb.save(file_path)
            print(f"已格式化: {os.path.basename(file_path)}")
        except Exception as e:
            print(f"格式化 {file_path} 时出错: {e}")
# 使用示例
batch_format_excel("./formatted_excel/")

实用综合案例

案例6：批量报表生成系统

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import os
class BatchReportGenerator:
    def __init__(self, output_folder):
        self.output_folder = output_folder
        os.makedirs(output_folder, exist_ok=True)
    def generate_sales_report(self, months_data):
        """生成销售报表"""
        for month, num_records in months_data.items():
            # 生成模拟销售数据
            data = {
                '日期': [datetime(2024, month, day) for day in range(1, num_records + 1)],
                '产品': np.random.choice(['产品A', '产品B', '产品C', '产品D'], num_records),
                '销售员': np.random.choice(['张三', '李四', '王五', '赵六'], num_records),
                '数量': np.random.randint(10, 100, num_records),
                '单价': np.random.randint(50, 200, num_records),
                '金额': [0] * num_records
            }
            df = pd.DataFrame(data)
            df['金额'] = df['数量'] * df['单价']
            # 生成统计信息
            summary = {
                '总销售额': df['金额'].sum(),
                '平均销售额': df['金额'].mean(),
                '最高销售额': df['金额'].max(),
                '销售记录数': len(df)
            }
            # 保存到Excel
            file_name = f"2024年{month}月销售报表.xlsx"
            file_path = os.path.join(self.output_folder, file_name)
            with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
                df.to_excel(writer, sheet_name='销售明细', index=False)
                pd.DataFrame([summary]).to_excel(writer, sheet_name='统计摘要', index=False)
            print(f"已生成: {file_name}")
    def merge_reports(self):
        """合并所有报表"""
        excel_files = glob(os.path.join(self.output_folder, "*.xlsx"))
        all_data = []
        for file_path in excel_files:
            df = pd.read_excel(file_path, sheet_name='销售明细')
            file_name = os.path.basename(file_path)
            df['来源月份'] = file_name.replace('.xlsx', '')
            all_data.append(df)
        if all_data:
            merged_df = pd.concat(all_data, ignore_index=True)
            merged_path = os.path.join(self.output_folder, "年度销售汇总.xlsx")
            merged_df.to_excel(merged_path, index=False)
            print(f"已生成年度汇总: {merged_path}")
# 使用示例
generator = BatchReportGenerator("./sales_reports/")
months_data = {i: 30 for i in range(1, 7)}  # 1-6月，每月30条记录
generator.generate_sales_report(months_data)
generator.merge_reports()

性能优化建议

import pandas as pd
from concurrent.futures import ThreadPoolExecutor
import os
def parallel_process_excel(files, process_func, max_workers=4):
    """并行处理多个Excel文件"""
    with ThreadPoolExecutor(max_workers=max_workers) as executor:
        results = list(executor.map(process_func, files))
    return results
def process_single_file(file_path):
    """处理单个文件的函数"""
    df = pd.read_excel(file_path)
    # 处理逻辑
    return df
# 批量处理示例
folder_path = "./data/"
excel_files = glob(os.path.join(folder_path, "*.xlsx"))
results = parallel_process_excel(excel_files, process_single_file)

这些示例涵盖了Python批量操作Excel的常见场景,您可以根据实际需求进行调整和组合使用。

本文地址： https://www.bmjup.com/post/12364.html