使用Python自动化处理Excel表格的操作方法

pip install xlrd xlwt xlutils

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")

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")

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)

pip install openpyxl

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")

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")

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")

pip install xlwings

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()

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()

pip install pandas

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")

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")

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")

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")

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")