小磊哥er的办公自动化专题

Appearance

2.1.Excel表格自动化 ​

在日常办公中，Excel是最常用的数据处理工具之一。通过Python自动化Excel操作，可以大幅提高工作效率，减少重复劳动，降低人为错误。本文将介绍几种常用的Python操作Excel的方法，并提供实用的代码示例和应用场景。

组合xlrd、xlwt、xlutils实现excel读写操作 ​

这三个库是早期Python操作Excel的经典组合，各司其职：xlrd负责读取，xlwt负责写入，xlutils作为两者的桥梁。虽然现在有了更强大的库，但在一些特定场景下，这个组合仍然有其价值。

安装这些库 ​

python
pip install xlrd xlwt xlutils

读取Excel文件 ​

python
import xlrd

def read_excel_file(file_path):
    """读取Excel文件并打印内容"""
    # 打开工作簿
    workbook = xlrd.open_workbook(file_path)
    
    # 获取所有工作表名称
    sheet_names = workbook.sheet_names()
    print(f"工作表列表: {sheet_names}")
    
    # 遍历每个工作表
    for sheet_name in sheet_names:
        sheet = workbook.sheet_by_name(sheet_name)
        print(f"\n工作表: {sheet_name}, 行数: {sheet.nrows}, 列数: {sheet.ncols}")
        
        # 打印表头
        if sheet.nrows > 0:
            header = [sheet.cell_value(0, col) for col in range(sheet.ncols)]
            print(f"表头: {header}")
        
        # 打印数据（最多显示5行）
        for row in range(1, min(6, sheet.nrows)):
            row_data = [sheet.cell_value(row, col) for col in range(sheet.ncols)]
            print(f"第{row}行: {row_data}")

# 使用示例
read_excel_file("员工信息.xls")

写入Excel文件 ​

python
import xlwt

def create_excel_file(file_path):
    """创建新的Excel文件"""
    # 创建工作簿
    workbook = xlwt.Workbook(encoding='utf-8')
    
    # 添加工作表
    sheet = workbook.add_sheet('员工信息')
    
    # 定义样式
    header_style = xlwt.easyxf('font: bold on; align: horiz center')
    date_style = xlwt.easyxf(num_format_str='yyyy-mm-dd')
    
    # 写入表头
    headers = ['ID', '姓名', '部门', '入职日期', '薪资']
    for col, header in enumerate(headers):
        sheet.write(0, col, header, header_style)
    
    # 准备数据
    data = [
        [1001, '张三', '技术部', '2020-01-15', 12000],
        [1002, '李四', '市场部', '2019-05-23', 15000],
        [1003, '王五', '财务部', '2021-03-08', 13500],
        [1004, '赵六', '人事部', '2018-11-12', 14000],
    ]
    
    # 写入数据
    for row, row_data in enumerate(data, 1):
        for col, cell_value in enumerate(row_data):
            # 对日期使用特殊格式
            if col == 3:  # 入职日期列
                import datetime
                date_parts = cell_value.split('-')
                date_obj = datetime.datetime(int(date_parts[0]), int(date_parts[1]), int(date_parts[2]))
                sheet.write(row, col, date_obj, date_style)
            else:
                sheet.write(row, col, cell_value)
    
    # 保存文件
    workbook.save(file_path)
    print(f"Excel文件已创建: {file_path}")

# 使用示例
create_excel_file("新员工信息.xls")

修改现有Excel文件 ​

python
import xlrd
import xlwt
from xlutils.copy import copy

def update_excel_file(file_path, employee_id, new_salary):
    """更新指定员工的薪资信息"""
    # 打开原工作簿（只读模式）
    rb = xlrd.open_workbook(file_path, formatting_info=True)
    sheet = rb.sheet_by_index(0)
    
    # 创建一个可写的副本
    wb = copy(rb)
    w_sheet = wb.get_sheet(0)
    
    # 查找员工ID并更新薪资
    found = False
    for row in range(1, sheet.nrows):
        if int(sheet.cell_value(row, 0)) == employee_id:  # 假设ID在第一列
            w_sheet.write(row, 4, new_salary)  # 假设薪资在第五列
            found = True
            break
    
    if found:
        # 保存修改后的文件
        wb.save(file_path)
        print(f"已更新员工ID {employee_id} 的薪资为 {new_salary}")
    else:
        print(f"未找到员工ID: {employee_id}")

# 使用示例
update_excel_file("员工信息.xls", 1002, 16000)

使用openpyxl实现excel的读写修改 ​

openpyxl是目前最流行的Python Excel处理库之一，功能全面，API友好，特别适合处理较新的Excel格式（.xlsx）。

安装openpyxl ​

python
pip install openpyxl

读取Excel文件 ​

python
from openpyxl import load_workbook

def read_excel_with_openpyxl(file_path):
    """使用openpyxl读取Excel文件"""
    # 加载工作簿
    wb = load_workbook(file_path, read_only=True)
    
    # 获取所有工作表名称
    sheet_names = wb.sheetnames
    print(f"工作表列表: {sheet_names}")
    
    # 遍历每个工作表
    for sheet_name in sheet_names:
        sheet = wb[sheet_name]
        print(f"\n工作表: {sheet_name}")
        
        # 获取表格尺寸
        if not sheet.max_row:  # 对于read_only模式，需要遍历才能获取尺寸
            print("工作表为空或使用read_only模式无法直接获取尺寸")
            continue
        
        # 打印表头
        header = [cell.value for cell in next(sheet.iter_rows())]
        print(f"表头: {header}")
        
        # 打印数据（最多5行）
        row_count = 0
        for row in sheet.iter_rows(min_row=2):  # 从第二行开始
            if row_count >= 5:
                break
            row_data = [cell.value for cell in row]
            print(f"行 {row_count + 2}: {row_data}")
            row_count += 1
    
    # 关闭工作簿
    wb.close()

# 使用示例
read_excel_with_openpyxl("员工信息.xlsx")

创建新的Excel文件 ​

python
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.utils import get_column_letter
import datetime

def create_excel_with_openpyxl(file_path):
    """使用openpyxl创建格式化的Excel文件"""
    # 创建工作簿
    wb = Workbook()
    sheet = wb.active
    sheet.title = "销售数据"
    
    # 定义样式
    header_font = Font(name='Arial', size=12, bold=True, color="FFFFFF")
    header_fill = PatternFill("solid", fgColor="4F81BD")
    centered = Alignment(horizontal="center")
    
    # 写入表头
    headers = ['产品ID', '产品名称', '类别', '单价', '销售日期', '销售量', '销售额']
    for col_num, header in enumerate(headers, 1):
        cell = sheet.cell(row=1, column=col_num)
        cell.value = header
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = centered
    
    # 准备数据
    data = [
        [101, '笔记本电脑', '电子产品', 5999, datetime.date(2023, 1, 15), 10, '=D2*F2'],
        [102, '办公椅', '办公家具', 899, datetime.date(2023, 1, 16), 20, '=D3*F3'],
        [103, '打印机', '办公设备', 1299, datetime.date(2023, 1, 18), 5, '=D4*F4'],
        [104, '显示器', '电子产品', 1499, datetime.date(2023, 1, 20), 15, '=D5*F5'],
        [105, '文件柜', '办公家具', 699, datetime.date(2023, 1, 22), 8, '=D6*F6'],
    ]
    
    # 写入数据
    for row_num, row_data in enumerate(data, 2):
        for col_num, cell_value in enumerate(row_data, 1):
            cell = sheet.cell(row=row_num, column=col_num)
            cell.value = cell_value
            if col_num == 5:  # 日期列使用日期格式
                cell.number_format = 'yyyy-mm-dd'
            elif col_num == 7:  # 销售额列使用公式和货币格式
                cell.number_format = '¥#,##0.00'
    
    # 添加合计行
    total_row = len(data) + 2
    sheet.cell(row=total_row, column=1).value = "合计"
    sheet.cell(row=total_row, column=1).font = Font(bold=True)
    
    # 销售量合计
    sheet.cell(row=total_row, column=6).value = f"=SUM(F2:F{total_row-1})"
    sheet.cell(row=total_row, column=6).font = Font(bold=True)
    
    # 销售额合计
    sheet.cell(row=total_row, column=7).value = f"=SUM(G2:G{total_row-1})"
    sheet.cell(row=total_row, column=7).font = Font(bold=True)
    sheet.cell(row=total_row, column=7).number_format = '¥#,##0.00'
    
    # 调整列宽
    for col in range(1, len(headers) + 1):
        sheet.column_dimensions[get_column_letter(col)].width = 15
    
    # 保存文件
    wb.save(file_path)
    print(f"Excel文件已创建: {file_path}")

# 使用示例
create_excel_with_openpyxl("销售数据.xlsx")

处理大型Excel文件 ​

python
from openpyxl import load_workbook
import time

def process_large_excel(file_path):
    """使用read_only模式处理大型Excel文件"""
    start_time = time.time()
    
    # 使用read_only模式加载工作簿
    wb = load_workbook(file_path, read_only=True)
    sheet = wb.active
    
    # 统计数据
    row_count = 0
    sum_value = 0
    
    # 假设第5列是数值，我们要计算其总和
    for row in sheet.iter_rows(min_row=2, values_only=True):
        row_count += 1
        if len(row) >= 5 and row[4] is not None:
            try:
                sum_value += float(row[4])
            except (ValueError, TypeError):
                pass
        
        # 每处理10000行打印一次进度
        if row_count % 10000 == 0:
            print(f"已处理 {row_count} 行...")
    
    # 关闭工作簿
    wb.close()
    
    end_time = time.time()
    print(f"处理完成，共 {row_count} 行数据")
    print(f"第5列数值总和: {sum_value}")
    print(f"处理时间: {end_time - start_time:.2f} 秒")

# 使用示例（对于大型文件）
# process_large_excel("大型数据集.xlsx")

使用xlwings模块操控excel文档 ​

xlwings是一个强大的库，可以直接与Excel应用程序交互，实现自动化操作，甚至可以调用Excel的VBA函数。

安装xlwings ​

python
pip install xlwings

基本操作 ​

python
import xlwings as xw

def automate_excel_with_xlwings():
    """使用xlwings自动化Excel操作"""
    # 启动Excel应用
    app = xw.App(visible=True)  # visible=True让Excel可见，便于观察操作过程
    
    try:
        # 创建新工作簿
        wb = app.books.add()
        sheet = wb.sheets[0]
        sheet.name = "销售报表"
        
        # 添加表头
        sheet.range("A1").value = "产品"
        sheet.range("B1").value = "一季度"
        sheet.range("C1").value = "二季度"
        sheet.range("D1").value = "三季度"
        sheet.range("E1").value = "四季度"
        sheet.range("F1").value = "年度总计"
        
        # 设置表头格式
        header_range = sheet.range("A1:F1")
        header_range.color = (0, 112, 192)  # 蓝色背景
        header_range.font.color = (255, 255, 255)  # 白色文字
        header_range.font.bold = True
        
        # 添加数据
        data = [
            ["产品A", 100, 120, 140, 130],
            ["产品B", 90, 100, 110, 120],
            ["产品C", 80, 85, 90, 95],
        ]
        
        # 写入数据
        sheet.range("A2").value = data
        
        # 添加公式计算年度总计
        for i in range(len(data)):
            row = i + 2  # 数据从第2行开始
            sheet.range(f"F{row}").formula = f"=SUM(B{row}:E{row})"
        
        # 添加合计行
        total_row = len(data) + 2
        sheet.range(f"A{total_row}").value = "合计"
        sheet.range(f"A{total_row}").font.bold = True
        
        # 添加合计公式
        for col in "BCDEF":
            sheet.range(f"{col}{total_row}").formula = f"=SUM({col}2:{col}{total_row-1})"
            sheet.range(f"{col}{total_row}").font.bold = True
        
        # 添加图表
        chart = sheet.charts.add()
        chart.set_source_data(sheet.range(f"A1:E{len(data)+1}"))
        chart.chart_type = "column_clustered"
        chart.name = "季度销售图表"
        chart.top = sheet.range(f"A{total_row+2}").top
        chart.left = sheet.range("A1").left
        
        # 调整列宽
        sheet.autofit()
        
        # 保存文件
        wb.save("xlwings_销售报表.xlsx")
        print("Excel文件已创建并保存")
        
    finally:
        # 关闭工作簿和应用
        wb.close()
        app.quit()

# 使用示例
# automate_excel_with_xlwings()

与Excel VBA结合使用 ​

python
import xlwings as xw

def run_excel_macro():
    """运行Excel中的VBA宏"""
    # 打开包含宏的工作簿
    wb = xw.Book("带宏的工作簿.xlsm")
    
    try:
        # 运行名为'ProcessData'的宏
        wb.macro("ProcessData")()
        print("宏已执行完成")
        
        # 读取宏处理后的结果
        sheet = wb.sheets["结果"]
        result = sheet.range("A1:C10").value
        print("处理结果:")
        for row in result:
            print(row)
        
    finally:
        # 保存并关闭工作簿
        wb.save()
        wb.close()

# 使用示例（需要有包含'ProcessData'宏的Excel文件）
# run_excel_macro()

使用Pandas轻松处理多个excel工作薄 ​

Pandas是数据分析的利器，它提供了强大的数据结构和操作功能，特别适合处理表格数据。

安装Pandas ​

python
pip install pandas

读取Excel文件 ​

python
import pandas as pd

def read_excel_with_pandas(file_path):
    """使用pandas读取Excel文件"""
    # 读取所有工作表
    xlsx = pd.ExcelFile(file_path)
    
    # 获取所有工作表名称
    sheet_names = xlsx.sheet_names
    print(f"工作表列表: {sheet_names}")
    
    # 遍历每个工作表
    for sheet_name in sheet_names:
        # 读取工作表到DataFrame
        df = pd.read_excel(xlsx, sheet_name)
        
        print(f"\n工作表: {sheet_name}, 形状: {df.shape}")
        
        # 显示前5行数据
        print("\n数据预览:")
        print(df.head())
        
        # 显示基本统计信息
        print("\n数值列统计信息:")
        print(df.describe())

# 使用示例
read_excel_with_pandas("销售数据.xlsx")

数据处理与分析 ​

python
import pandas as pd
import matplotlib.pyplot as plt

def analyze_sales_data(file_path):
    """使用pandas分析销售数据"""
    # 读取Excel文件
    df = pd.read_excel(file_path)
    
    # 显示基本信息
    print("数据基本信息:")
    print(df.info())
    
    # 按类别分组统计
    category_stats = df.groupby('类别').agg({
        '销售量': 'sum',
        '销售额': 'sum'
    }).sort_values('销售额', ascending=False)
    
    print("\n按类别统计:")
    print(category_stats)
    
    # 按月份分析销售趋势
    df['月份'] = pd.to_datetime(df['销售日期']).dt.month
    monthly_sales = df.groupby('月份').agg({
        '销售量': 'sum',
        '销售额': 'sum'
    })
    
    print("\n按月份统计:")
    print(monthly_sales)
    
    # 创建图表
    plt.figure(figsize=(12, 5))
    
    # 销售额柱状图
    plt.subplot(1, 2, 1)
    category_stats['销售额'].plot(kind='bar', color='skyblue')
    plt.title('各类别销售额')
    plt.ylabel('销售额')
    plt.xticks(rotation=45)
    
    # 月度销售趋势图
    plt.subplot(1, 2, 2)
    monthly_sales['销售额'].plot(marker='o', color='green')
    plt.title('月度销售趋势')
    plt.xlabel('月份')
    plt.ylabel('销售额')
    
    plt.tight_layout()
    plt.savefig('销售分析.png')
    plt.close()
    
    print("\n分析图表已保存为'销售分析.png'")
    
    # 返回处理后的数据
    return df, category_stats, monthly_sales

# 使用示例
# analyze_sales_data("销售数据.xlsx")

合并多个Excel文件 ​

python
import pandas as pd
import os

def merge_excel_files(directory, output_file):
    """合并目录下的所有Excel文件"""
    # 获取目录下所有Excel文件
    excel_files = [f for f in os.listdir(directory) 
                  if f.endswith('.xlsx') or f.endswith('.xls')]
    
    if not excel_files:
        print(f"目录 {directory} 中没有找到Excel文件")
        return
    
    print(f"找到 {len(excel_files)} 个Excel文件")
    
    # 创建一个空的DataFrame列表
    dfs = []
    
    # 读取每个Excel文件
    for file in excel_files:
        file_path = os.path.join(directory, file)
        print(f"处理文件: {file}")
        
        # 读取所有工作表
        xlsx = pd.ExcelFile(file_path)
        
        for sheet_name in xlsx.sheet_names:
            # 读取工作表
            df = pd.read_excel(xlsx, sheet_name)
            
            # 添加文件名和工作表名列
            df['源文件'] = file
            df['工作表'] = sheet_name
            
            # 添加到列表
            dfs.append(df)
    
    # 合并所有DataFrame
    if dfs:
        merged_df = pd.concat(dfs, ignore_index=True)
        
        # 保存合并后的数据
        merged_df.to_excel(output_file, index=False)
        print(f"已将 {len(dfs)} 个工作表合并到 {output_file}")
        print(f"合并后的数据形状: {merged_df.shape}")
    else:
        print("没有找到有效的数据表")

# 使用示例
# merge_excel_files("excel_files", "合并数据.xlsx")

实际应用场景 ​

场景一：销售数据自动化报表 ​

python
import pandas as pd
import matplotlib.pyplot as plt
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, LineChart
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
import datetime

def generate_sales_report(sales_data_file, output_file):
    """生成销售数据分析报表"""
    # 读取销售数据
    df = pd.read_excel(sales_data_file)
    
    # 数据清洗和准备
    df['销售日期'] = pd.to_datetime(df['销售日期'])
    df['月份'] = df['销售日期'].dt.month
    df['季度'] = df['销售日期'].dt.quarter
    
    # 按产品和月份分组统计
    product_monthly = df.pivot_table(
        index='产品名称',
        columns='月份',
        values='销售额',
        aggfunc='sum',
        fill_value=0
    )
    
    # 按类别和季度分组统计
    category_quarterly = df.pivot_table(
        index='类别',
        columns='季度',
        values=['销售量', '销售额'],
        aggfunc='sum',
        fill_value=0
    )
    
    # 计算总计和环比
    product_monthly['总计'] = product_monthly.sum(axis=1)
    product_monthly = product_monthly.sort_values('总计', ascending=False)
    
    # 创建Excel工作簿
    wb = Workbook()
    
    # 创建产品月度销售工作表
    ws1 = wb.active
    ws1.title = "产品月度销售"
    
    # 写入表头
    headers = ['产品名称'] + [f"{i}月" for i in sorted(product_monthly.columns[:-1])] + ['总计']
    for col_num, header in enumerate(headers, 1):
        cell = ws1.cell(row=1, column=col_num)
        cell.value = header
        cell.font = Font(bold=True)
        cell.fill = PatternFill("solid", fgColor="4F81BD")
        cell.alignment = Alignment(horizontal="center")
    
    # 写入数据
    for row_num, (index, data) in enumerate(product_monthly.iterrows(), 2):
        ws1.cell(row=row_num, column=1).value = index  # 产品名称
        
        for col_num, value in enumerate(data.values, 2):
            cell = ws1.cell(row=row_num, column=col_num)
            cell.value = value
            cell.number_format = '#,##0.00'
    
    # 添加合计行
    total_row = len(product_monthly) + 2
    ws1.cell(row=total_row, column=1).value = "总计"
    ws1.cell(row=total_row, column=1).font = Font(bold=True)
    
    for col in range(2, len(headers) + 1):
        col_letter = get_column_letter(col)
        ws1.cell(row=total_row, column=col).value = f"=SUM({col_letter}2:{col_letter}{total_row-1})"
        ws1.cell(row=total_row, column=col).font = Font(bold=True)
        ws1.cell(row=total_row, column=col).number_format = '#,##0.00'
    
    # 创建图表
    chart = BarChart()
    chart.title = "产品销售额对比"
    chart.x_axis.title = "产品"
    chart.y_axis.title = "销售额"
    
    # 设置图表数据范围
    data = Reference(ws1, min_col=2, min_row=1, max_row=min(11, total_row-1), max_col=len(headers)-1)
    cats = Reference(ws1, min_col=1, min_row=2, max_row=min(11, total_row-1))
    
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    
    # 添加图表到工作表
    ws1.add_chart(chart, "A" + str(total_row + 2))
    
    # 创建类别季度销售工作表
    ws2 = wb.create_sheet(title="类别季度分析")
    
    # 重新组织数据以便于写入
    category_data = []
    for category in category_quarterly.index:
        row = [category]
        for quarter in sorted(df['季度'].unique()):
            row.append(category_quarterly.loc[category, ('销售量', quarter)])
            row.append(category_quarterly.loc[category, ('销售额', quarter)])
        category_data.append(row)
    
    # 写入表头
    headers = ['类别']
    for quarter in sorted(df['季度'].unique()):
        headers.extend([f"Q{quarter}销量", f"Q{quarter}销售额"])
    
    for col_num, header in enumerate(headers, 1):
        cell = ws2.cell(row=1, column=col_num)
        cell.value = header
        cell.font = Font(bold=True)
        cell.fill = PatternFill("solid", fgColor="4F81BD")
        cell.alignment = Alignment(horizontal="center")
    
    # 写入数据
    for row_num, row_data in enumerate(category_data, 2):
        for col_num, value in enumerate(row_data, 1):
            cell = ws2.cell(row=row_num, column=col_num)
            cell.value = value
            if col_num % 2 == 0:  # 销量列
                cell.number_format = '#,##0'
            elif col_num % 2 == 1 and col_num > 1:  # 销售额列
                cell.number_format = '#,##0.00'
    
    # 创建折线图
    line_chart = LineChart()
    line_chart.title = "季度销售趋势"
    line_chart.x_axis.title = "季度"
    line_chart.y_axis.title = "销售额"
    
    # 设置图表数据范围（只取销售额列）
    quarters = len(df['季度'].unique())
    data = Reference(ws2, min_col=3, min_row=1, max_row=len(category_data)+1, max_col=2*quarters, min_col_offset=1)
    cats = Reference(ws2, min_col=1, min_row=2, max_row=len(category_data)+1)
    
    line_chart.add_data(data, titles_from_data=True)
    line_chart.set_categories(cats)
    
    # 添加图表到工作表
    ws2.add_chart(line_chart, "A" + str(len(category_data) + 3))
    
    # 调整列宽
    for ws in [ws1, ws2]:
        for col in range(1, ws.max_column + 1):
            ws.column_dimensions[get_column_letter(col)].width = 15
    
    # 保存工作簿
    wb.save(output_file)
    print(f"销售报表已生成: {output_file}")

# 使用示例
# generate_sales_report("原始销售数据.xlsx", "销售分析报表.xlsx")

场景二：库存管理系统 ​

python
import pandas as pd
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import datetime
import os

class InventoryManager:
    def __init__(self, inventory_file):
        """初始化库存管理系统"""
        self.inventory_file = inventory_file
        
        # 如果文件不存在，创建一个新的库存文件
        if not os.path.exists(inventory_file):
            self._create_new_inventory_file()
        
        # 加载库存数据
        self.load_inventory()
    
    def _create_new_inventory_file(self):
        """创建新的库存文件"""
        wb = Workbook()
        ws = wb.active
        ws.title = "库存"
        
        # 设置表头
        headers = ['产品ID', '产品名称', '类别', '供应商', '单价', '库存量', '库存价值', '最后更新']
        for col_num, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_num)
            cell.value = header
            cell.font = Font(bold=True)
            cell.fill = PatternFill("solid", fgColor="4F81BD")
            cell.alignment = Alignment(horizontal="center")
        
        # 设置示例数据
        sample_data = [
            [1001, '笔记本电脑', '电子产品', 'A供应商', 5999, 10, '=E2*F2', datetime.datetime.now()],
            [1002, '办公椅', '办公家具', 'B供应商', 899, 20, '=E3*F3', datetime.datetime.now()],
        ]
        
        for row_num, row_data in enumerate(sample_data, 2):
            for col_num, value in enumerate(row_data, 1):
                cell = ws.cell(row=row_num, column=col_num)
                cell.value = value
                if col_num == 5:  # 单价列
                    cell.number_format = '¥#,##0.00'
                elif col_num == 7:  # 库存价值列
                    cell.number_format = '¥#,##0.00'
                elif col_num == 8:  # 日期列
                    cell.number_format = 'yyyy-mm-dd hh:mm:ss'
        
        # 创建入库记录工作表
        ws_in = wb.create_sheet(title="入库记录")
        headers = ['记录ID', '产品ID', '产品名称', '入库数量', '单价', '总价值', '供应商', '入库日期', '操作人']
        for col_num, header in enumerate(headers, 1):
            cell = ws_in.cell(row=1, column=col_num)
            cell.value = header
            cell.font = Font(bold=True)
            cell.fill = PatternFill("solid", fgColor="4F81BD")
            cell.alignment = Alignment(horizontal="center")
        
        # 创建出库记录工作表
        ws_out = wb.create_sheet(title="出库记录")
        headers = ['记录ID', '产品ID', '产品名称', '出库数量', '单价', '总价值', '客户', '出库日期', '操作人']
        for col_num, header in enumerate(headers, 1):
            cell = ws_out.cell(row=1, column=col_num)
            cell.value = header
            cell.font = Font(bold=True)
            cell.fill = PatternFill("solid", fgColor="4F81BD")
            cell.alignment = Alignment(horizontal="center")
        
        # 调整所有工作表的列宽
        for ws in wb.worksheets:
            for col in range(1, len(headers) + 1):
                ws.column_dimensions[get_column_letter(col)].width = 15
        
        # 保存文件
        wb.save(self.inventory_file)
        print(f"已创建新的库存文件: {self.inventory_file}")
    
    def load_inventory(self):
        """加载库存数据"""
        # 使用pandas读取Excel文件的所有工作表
        self.inventory_df = pd.read_excel(self.inventory_file, sheet_name="库存")
        self.in_records_df = pd.read_excel(self.inventory_file, sheet_name="入库记录")
        self.out_records_df = pd.read_excel(self.inventory_file, sheet_name="出库记录")
        
        print("库存数据已加载")
        print(f"当前库存: {len(self.inventory_df)} 种产品")
        print(f"入库记录: {len(self.in_records_df)} 条")
        print(f"出库记录: {len(self.out_records_df)} 条")
    
    def add_product(self, product_id, name, category, supplier, price, quantity):
        """添加新产品到库存"""
        # 检查产品ID是否已存在
        if product_id in self.inventory_df['产品ID'].values:
            print(f"错误: 产品ID {product_id} 已存在")
            return False
        
        # 创建新产品记录
        new_product = {
            '产品ID': product_id,
            '产品名称': name,
            '类别': category,
            '供应商': supplier,
            '单价': price,
            '库存量': quantity,
            '库存价值': price * quantity,
            '最后更新': datetime.datetime.now()
        }
        
        # 添加到DataFrame
        self.inventory_df = self.inventory_df.append(new_product, ignore_index=True)
        
        # 添加入库记录
        in_record = {
            '记录ID': len(self.in_records_df) + 1,
            '产品ID': product_id,
            '产品名称': name,
            '入库数量': quantity,
            '单价': price,
            '总价值': price * quantity,
            '供应商': supplier,
            '入库日期': datetime.datetime.now(),
            '操作人': 'system'
        }
        self.in_records_df = self.in_records_df.append(in_record, ignore_index=True)
        
        # 保存更改
        self._save_to_excel()
        print(f"已添加新产品: {name} (ID: {product_id})")
        return True
    
    def update_stock(self, product_id, quantity_change, is_incoming=True, customer_or_supplier=None, operator='system'):
        """更新库存"""
        # 查找产品
        product_mask = self.inventory_df['产品ID'] == product_id
        if not any(product_mask):
            print(f"错误: 产品ID {product_id} 不存在")
            return False
        
        # 获取产品信息
        product_idx = product_mask.idxmax()
        product = self.inventory_df.loc[product_idx]
        
        # 计算新库存量
        new_quantity = product['库存量'] + quantity_change if is_incoming else product['库存量'] - quantity_change
        
        # 检查库存是否足够（出库时）
        if not is_incoming and new_quantity < 0:
            print(f"错误: 产品 {product['产品名称']} 库存不足，当前库存: {product['库存量']}")
            return False
        
        # 更新库存
        self.inventory_df.at[product_idx, '库存量'] = new_quantity
        self.inventory_df.at[product_idx, '库存价值'] = new_quantity * product['单价']
        self.inventory_df.at[product_idx, '最后更新'] = datetime.datetime.now()
        
        # 添加记录
        if is_incoming:
            # 入库记录
            record = {
                '记录ID': len(self.in_records_df) + 1,
                '产品ID': product_id,
                '产品名称': product['产品名称'],
                '入库数量': quantity_change,
                '单价': product['单价'],
                '总价值': quantity_change * product['单价'],
                '供应商': customer_or_supplier or product['供应商'],
                '入库日期': datetime.datetime.now(),
                '操作人': operator
            }
            self.in_records_df = self.in_records_df.append(record, ignore_index=True)
        else:
            # 出库记录
            record = {
                '记录ID': len(self.out_records_df) + 1,
                '产品ID': product_id,
                '产品名称': product['产品名称'],
                '出库数量': quantity_change,
                '单价': product['单价'],
                '总价值': quantity_change * product['单价'],
                '客户': customer_or_supplier or '未指定',
                '出库日期': datetime.datetime.now(),
                '操作人': operator
            }
            self.out_records_df = self.out_records_df.append(record, ignore_index=True)
        
        # 保存更改
        self._save_to_excel()
        action = "入库" if is_incoming else "出库"
        print(f"已{action} {product['产品名称']} {quantity_change} 个，当前库存: {new_quantity}")
        return True
    
    def generate_inventory_report(self, output_file):
        """生成库存报表"""
        # 创建一个新的工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = "库存报表"
        
        # 添加报表标题
        ws.merge_cells('A1:H1')
        title_cell = ws['A1']
        title_cell.value = "库存状况报表"
        title_cell.font = Font(size=16, bold=True)
        title_cell.alignment = Alignment(horizontal="center")
        
        # 添加报表生成时间
        ws.merge_cells('A2:H2')
        date_cell = ws['A2']
        date_cell.value = f"生成时间: {datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        date_cell.alignment = Alignment(horizontal="center")
        
        # 添加表头
        headers = ['产品ID', '产品名称', '类别', '供应商', '单价', '库存量', '库存价值', '库存状态']
        for col_num, header in enumerate(headers, 1):
            cell = ws.cell(row=4, column=col_num)
            cell.value = header
            cell.font = Font(bold=True)
            cell.fill = PatternFill("solid", fgColor="4F81BD")
            cell.alignment = Alignment(horizontal="center")
        
        # 添加数据
        # 计算库存状态
        def get_stock_status(row):
            if row['库存量'] <= 0:
                return "缺货"
            elif row['库存量'] < 5:
                return "库存不足"
            elif row['库存量'] > 20:
                return "库存过多"
            else:
                return "正常"
        
        # 添加库存状态列
        self.inventory_df['库存状态'] = self.inventory_df.apply(get_stock_status, axis=1)
        
        # 按类别和库存状态排序
        sorted_df = self.inventory_df.sort_values(['类别', '库存状态'])
        
        # 写入数据
        for row_num, (_, row) in enumerate(sorted_df.iterrows(), 5):
            for col_num, column in enumerate(headers, 1):
                cell = ws.cell(row=row_num, column=col_num)
                value = row[column] if column in row else ""
                cell.value = value
                
                # 设置格式
                if column == '单价':
                    cell.number_format = '¥#,##0.00'
                elif column == '库存价值':
                    cell.number_format = '¥#,##0.00'
                
                # 设置库存状态的颜色
                if column == '库存状态':
                    if value == "缺货":
                        cell.fill = PatternFill("solid", fgColor="FF0000")
                    elif value == "库存不足":
                        cell.fill = PatternFill("solid", fgColor="FFC000")
                    elif value == "库存过多":
                        cell.fill = PatternFill("solid", fgColor="92D050")
        
        # 添加合计行
        total_row = len(sorted_df) + 5
        ws.cell(row=total_row, column=1).value = "合计"
        ws.cell(row=total_row, column=1).font = Font(bold=True)
        
        # 计算总库存量和总价值
        ws.cell(row=total_row, column=6).value = sorted_df['库存量'].sum()
        ws.cell(row=total_row, column=6).font = Font(bold=True)
        
        ws.cell(row=total_row, column=7).value = sorted_df['库存价值'].sum()
        ws.cell(row=total_row, column=7).font = Font(bold=True)
        ws.cell(row=total_row, column=7).number_format = '¥#,##0.00'
        
        # 添加类别统计
        ws.cell(row=total_row + 2, column=1).value = "类别统计"
        ws.cell(row=total_row + 2, column=1).font = Font(bold=True)
        
        category_stats = sorted_df.groupby('类别').agg({
            '产品ID': 'count',
            '库存量': 'sum',
            '库存价值': 'sum'
        }).reset_index()
        
        # 写入类别统计表头
        category_headers = ['类别', '产品数量', '总库存量', '总库存价值']
        for col_num, header in enumerate(category_headers, 1):
            cell = ws.cell(row=total_row + 3, column=col_num)
            cell.value = header
            cell.font = Font(bold=True)
            cell.fill = PatternFill("solid", fgColor="A5A5A5")
        
        # 写入类别统计数据
        for row_num, (_, row) in enumerate(category_stats.iterrows(), total_row + 4):
            ws.cell(row=row_num, column=1).value = row['类别']
            ws.cell(row=row_num, column=2).value = row['产品ID']
            ws.cell(row=row_num, column=3).value = row['库存量']
            ws.cell(row=row_num, column=4).value = row['库存价值']
            ws.cell(row=row_num, column=4).number_format = '¥#,##0.00'
        
        # 调整列宽
        for col in range(1, len(headers) + 1):
            ws.column_dimensions[get_column_letter(col)].width = 15
        
        # 保存报表
        wb.save(output_file)
        print(f"库存报表已生成: {output_file}")
        return output_file
    
    def _save_to_excel(self):
        """保存数据到Excel文件"""
        with pd.ExcelWriter(self.inventory_file, engine='openpyxl') as writer:
            self.inventory_df.to_excel(writer, sheet_name="库存", index=False)
            self.in_records_df.to_excel(writer, sheet_name="入库记录", index=False)
            self.out_records_df.to_excel(writer, sheet_name="出库记录", index=False)

# 使用示例
# inventory = InventoryManager("库存管理.xlsx")
# inventory.add_product(1003, "打印机", "办公设备", "C供应商", 1299, 5)
# inventory.update_stock(1001, 5, is_incoming=True, customer_or_supplier="A供应商", operator="张三")
# inventory.update_stock(1002, 2, is_incoming=False, customer_or_supplier="客户A", operator="李四")
# inventory.generate_inventory_report("库存报表.xlsx")

通过这些代码示例和实际应用场景，你可以轻松掌握Python Excel自动化的各种技巧，大幅提高工作效率。无论是简单的数据处理，还是复杂的报表生成，Python都能帮你轻松应对。