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

本文目录导读：

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

我来为您详细介绍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的常见场景,您可以根据实际需求进行调整和组合使用。