pip install xlrd xlwt xlutils

import xlrd
def read_excel_file(file_path):
    """讀取Excel文件并打印內(nèi)容"""
    # 打開工作簿
    workbook = xlrd.open_workbook(file_path)
    # 獲取所有工作表名稱
    sheet_names = workbook.sheet_names()
    print(f"工作表列表: {sheet_names}")
    # 遍歷每個(gè)工作表
    for sheet_name in sheet_names:
        sheet = workbook.sheet_by_name(sheet_name)
        print(f"\n工作表: {sheet_name}, 行數(shù): {sheet.nrows}, 列數(shù): {sheet.ncols}")
        # 打印表頭
        if sheet.nrows > 0:
            header = [sheet.cell_value(0, col) for col in range(sheet.ncols)]
            print(f"表頭: {header}")
        # 打印數(shù)據(jù)（最多顯示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):
    """創(chuàng)建新的Excel文件"""
    # 創(chuàng)建工作簿
    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)
    # 準(zhǔn)備數(shù)據(jù)
    data = [
        [1001, '張三', '技術(shù)部', '2020-01-15', 12000],
        [1002, '李四', '市場部', '2019-05-23', 15000],
        [1003, '王五', '財(cái)務(wù)部', '2021-03-08', 13500],
        [1004, '趙六', '人事部', '2018-11-12', 14000],
    ]
    # 寫入數(shù)據(jù)
    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文件已創(chuàng)建: {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)
    # 創(chuàng)建一個(gè)可寫的副本
    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:  # 假設(shè)ID在第一列
            w_sheet.write(row, 4, new_salary)  # 假設(shè)薪資在第五列
            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}")
    # 遍歷每個(gè)工作表
    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}")
        # 打印數(shù)據(jù)（最多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
    # 關(guān)閉工作簿
    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創(chuàng)建格式化的Excel文件"""
    # 創(chuàng)建工作簿
    wb = Workbook()
    sheet = wb.active
    sheet.title = "銷售數(shù)據(jù)"
    # 定義樣式
    header_font = Font(name='Arial', size=12, bold=True, color="FFFFFF")
    header_fill = PatternFill("solid", fgColor="4F81BD")
    centered = Alignment(horizontal="center")
    # 寫入表頭
    headers = ['產(chǎn)品ID', '產(chǎn)品名稱', '類別', '單價(jià)', '銷售日期', '銷售量', '銷售額']
    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
    # 準(zhǔn)備數(shù)據(jù)
    data = [
        [101, '筆記本電腦', '電子產(chǎn)品', 5999, datetime.date(2023, 1, 15), 10, '=D2*F2'],
        [102, '辦公椅', '辦公家具', 899, datetime.date(2023, 1, 16), 20, '=D3*F3'],
        [103, '打印機(jī)', '辦公設(shè)備', 1299, datetime.date(2023, 1, 18), 5, '=D4*F4'],
        [104, '顯示器', '電子產(chǎn)品', 1499, datetime.date(2023, 1, 20), 15, '=D5*F5'],
        [105, '文件柜', '辦公家具', 699, datetime.date(2023, 1, 22), 8, '=D6*F6'],
    ]
    # 寫入數(shù)據(jù)
    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'
    # 添加合計(jì)行
    total_row = len(data) + 2
    sheet.cell(row=total_row, column=1).value = "合計(jì)"
    sheet.cell(row=total_row, column=1).font = Font(bold=True)
    # 銷售量合計(jì)
    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)
    # 銷售額合計(jì)
    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'
    # 調(diào)整列寬
    for col in range(1, len(headers) + 1):
        sheet.column_dimensions[get_column_letter(col)].width = 15
    # 保存文件
    wb.save(file_path)
    print(f"Excel文件已創(chuàng)建: {file_path}")
# 使用示例
create_excel_with_openpyxl("銷售數(shù)據(jù).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
    # 統(tǒng)計(jì)數(shù)據(jù)
    row_count = 0
    sum_value = 0
    # 假設(shè)第5列是數(shù)值，我們要計(jì)算其總和
    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行打印一次進(jìn)度
        if row_count % 10000 == 0:
            print(f"已處理 {row_count} 行...")
    # 關(guān)閉工作簿
    wb.close()
    end_time = time.time()
    print(f"處理完成，共 {row_count} 行數(shù)據(jù)")
    print(f"第5列數(shù)值總和: {sum_value}")
    print(f"處理時(shí)間: {end_time - start_time:.2f} 秒")
# 使用示例（對于大型文件）
# process_large_excel("大型數(shù)據(jù)集.xlsx")

pip install xlwings

import xlwings as xw
def automate_excel_with_xlwings():
    """使用xlwings自動(dòng)化Excel操作"""
    # 啟動(dòng)Excel應(yīng)用
    app = xw.App(visible=True)  # visible=True讓Excel可見，便于觀察操作過程
    try:
        # 創(chuàng)建新工作簿
        wb = app.books.add()
        sheet = wb.sheets[0]
        sheet.name = "銷售報(bào)表"
        # 添加表頭
        sheet.range("A1").value = "產(chǎn)品"
        sheet.range("B1").value = "一季度"
        sheet.range("C1").value = "二季度"
        sheet.range("D1").value = "三季度"
        sheet.range("E1").value = "四季度"
        sheet.range("F1").value = "年度總計(jì)"
        # 設(shè)置表頭格式
        header_range = sheet.range("A1:F1")
        header_range.color = (0, 112, 192)  # 藍(lán)色背景
        header_range.font.color = (255, 255, 255)  # 白色文字
        header_range.font.bold = True
        # 添加數(shù)據(jù)
        data = [
            ["產(chǎn)品A", 100, 120, 140, 130],
            ["產(chǎn)品B", 90, 100, 110, 120],
            ["產(chǎn)品C", 80, 85, 90, 95],
        ]
        # 寫入數(shù)據(jù)
        sheet.range("A2").value = data
        # 添加公式計(jì)算年度總計(jì)
        for i in range(len(data)):
            row = i + 2  # 數(shù)據(jù)從第2行開始
            sheet.range(f"F{row}").formula = f"=SUM(B{row}:E{row})"
        # 添加合計(jì)行
        total_row = len(data) + 2
        sheet.range(f"A{total_row}").value = "合計(jì)"
        sheet.range(f"A{total_row}").font.bold = True
        # 添加合計(jì)公式
        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
        # 調(diào)整列寬
        sheet.autofit()
        # 保存文件
        wb.save("xlwings_銷售報(bào)表.xlsx")
        print("Excel文件已創(chuàng)建并保存")
    finally:
        # 關(guān)閉工作簿和應(yīng)用
        wb.close()
        app.quit()
# 使用示例
# automate_excel_with_xlwings()

import xlwings as xw
def run_excel_macro():
    """運(yùn)行Excel中的VBA宏"""
    # 打開包含宏的工作簿
    wb = xw.Book("帶宏的工作簿.xlsm")
    try:
        # 運(yùn)行名為'ProcessData'的宏
        wb.macro("ProcessData")()
        print("宏已執(zhí)行完成")
        # 讀取宏處理后的結(jié)果
        sheet = wb.sheets["結(jié)果"]
        result = sheet.range("A1:C10").value
        print("處理結(jié)果:")
        for row in result:
            print(row)
    finally:
        # 保存并關(guān)閉工作簿
        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}")
    # 遍歷每個(gè)工作表
    for sheet_name in sheet_names:
        # 讀取工作表到DataFrame
        df = pd.read_excel(xlsx, sheet_name)
        print(f"\n工作表: {sheet_name}, 形狀: {df.shape}")
        # 顯示前5行數(shù)據(jù)
        print("\n數(shù)據(jù)預(yù)覽:")
        print(df.head())
        # 顯示基本統(tǒng)計(jì)信息
        print("\n數(shù)值列統(tǒng)計(jì)信息:")
        print(df.describe())
# 使用示例
read_excel_with_pandas("銷售數(shù)據(jù).xlsx")

import pandas as pd
import matplotlib.pyplot as plt
def analyze_sales_data(file_path):
    """使用pandas分析銷售數(shù)據(jù)"""
    # 讀取Excel文件
    df = pd.read_excel(file_path)
    # 顯示基本信息
    print("數(shù)據(jù)基本信息:")
    print(df.info())
    # 按類別分組統(tǒng)計(jì)
    category_stats = df.groupby('類別').agg({
        '銷售量': 'sum',
        '銷售額': 'sum'
    }).sort_values('銷售額', ascending=False)
    print("\n按類別統(tǒng)計(jì):")
    print(category_stats)
    # 按月份分析銷售趨勢
    df['月份'] = pd.to_datetime(df['銷售日期']).dt.month
    monthly_sales = df.groupby('月份').agg({
        '銷售量': 'sum',
        '銷售額': 'sum'
    })
    print("\n按月份統(tǒng)計(jì):")
    print(monthly_sales)
    # 創(chuàng)建圖表
    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'")
    # 返回處理后的數(shù)據(jù)
    return df, category_stats, monthly_sales
# 使用示例
# analyze_sales_data("銷售數(shù)據(jù).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)} 個(gè)Excel文件")
    # 創(chuàng)建一個(gè)空的DataFrame列表
    dfs = []
    # 讀取每個(gè)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)
        # 保存合并后的數(shù)據(jù)
        merged_df.to_excel(output_file, index=False)
        print(f"已將 {len(dfs)} 個(gè)工作表合并到 {output_file}")
        print(f"合并后的數(shù)據(jù)形狀: {merged_df.shape}")
    else:
        print("沒有找到有效的數(shù)據(jù)表")
# 使用示例
# merge_excel_files("excel_files", "合并數(shù)據(jù).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):
    """生成銷售數(shù)據(jù)分析報(bào)表"""
    # 讀取銷售數(shù)據(jù)
    df = pd.read_excel(sales_data_file)
    # 數(shù)據(jù)清洗和準(zhǔn)備
    df['銷售日期'] = pd.to_datetime(df['銷售日期'])
    df['月份'] = df['銷售日期'].dt.month
    df['季度'] = df['銷售日期'].dt.quarter
    # 按產(chǎn)品和月份分組統(tǒng)計(jì)
    product_monthly = df.pivot_table(
        index='產(chǎn)品名稱',
        columns='月份',
        values='銷售額',
        aggfunc='sum',
        fill_value=0
    )
    # 按類別和季度分組統(tǒng)計(jì)
    category_quarterly = df.pivot_table(
        index='類別',
        columns='季度',
        values=['銷售量', '銷售額'],
        aggfunc='sum',
        fill_value=0
    )
    # 計(jì)算總計(jì)和環(huán)比
    product_monthly['總計(jì)'] = product_monthly.sum(axis=1)
    product_monthly = product_monthly.sort_values('總計(jì)', ascending=False)
    # 創(chuàng)建Excel工作簿
    wb = Workbook()
    # 創(chuàng)建產(chǎn)品月度銷售工作表
    ws1 = wb.active
    ws1.title = "產(chǎn)品月度銷售"
    # 寫入表頭
    headers = ['產(chǎn)品名稱'] + [f"{i}月" for i in sorted(product_monthly.columns[:-1])] + ['總計(jì)']
    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")
    # 寫入數(shù)據(jù)
    for row_num, (index, data) in enumerate(product_monthly.iterrows(), 2):
        ws1.cell(row=row_num, column=1).value = index  # 產(chǎn)品名稱
        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'
    # 添加合計(jì)行
    total_row = len(product_monthly) + 2
    ws1.cell(row=total_row, column=1).value = "總計(jì)"
    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'
    # 創(chuàng)建圖表
    chart = BarChart()
    chart.title = "產(chǎn)品銷售額對比"
    chart.x_axis.title = "產(chǎn)品"
    chart.y_axis.title = "銷售額"
    # 設(shè)置圖表數(shù)據(jù)范圍
    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))
    # 創(chuàng)建類別季度銷售工作表
    ws2 = wb.create_sheet(title="類別季度分析")
    # 重新組織數(shù)據(jù)以便于寫入
    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")
    # 寫入數(shù)據(jù)
    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'
    # 創(chuàng)建折線圖
    line_chart = LineChart()
    line_chart.title = "季度銷售趨勢"
    line_chart.x_axis.title = "季度"
    line_chart.y_axis.title = "銷售額"
    # 設(shè)置圖表數(shù)據(jù)范圍（只取銷售額列）
    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))
    # 調(diào)整列寬
    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"銷售報(bào)表已生成: {output_file}")
# 使用示例
# generate_sales_report("原始銷售數(shù)據(jù).xlsx", "銷售分析報(bào)表.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):
        """初始化庫存管理系統(tǒng)"""
        self.inventory_file = inventory_file
        # 如果文件不存在，創(chuàng)建一個(gè)新的庫存文件
        if not os.path.exists(inventory_file):
            self._create_new_inventory_file()
        # 加載庫存數(shù)據(jù)
        self.load_inventory()
    def _create_new_inventory_file(self):
        """創(chuàng)建新的庫存文件"""
        wb = Workbook()
        ws = wb.active
        ws.title = "庫存"
        # 設(shè)置表頭
        headers = ['產(chǎn)品ID', '產(chǎn)品名稱', '類別', '供應(yīng)商', '單價(jià)', '庫存量', '庫存價(jià)值', '最后更新']
        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")
        # 設(shè)置示例數(shù)據(jù)
        sample_data = [
            [1001, '筆記本電腦', '電子產(chǎn)品', 'A供應(yīng)商', 5999, 10, '=E2*F2', datetime.datetime.now()],
            [1002, '辦公椅', '辦公家具', 'B供應(yīng)商', 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:  # 單價(jià)列
                    cell.number_format = '￥#,##0.00'
                elif col_num == 7:  # 庫存價(jià)值列
                    cell.number_format = '￥#,##0.00'
                elif col_num == 8:  # 日期列
                    cell.number_format = 'yyyy-mm-dd hh:mm:ss'
        # 創(chuàng)建入庫記錄工作表
        ws_in = wb.create_sheet(title="入庫記錄")
        headers = ['記錄ID', '產(chǎn)品ID', '產(chǎn)品名稱', '入庫數(shù)量', '單價(jià)', '總價(jià)值', '供應(yīng)商', '入庫日期', '操作人']
        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")
        # 創(chuàng)建出庫記錄工作表
        ws_out = wb.create_sheet(title="出庫記錄")
        headers = ['記錄ID', '產(chǎn)品ID', '產(chǎn)品名稱', '出庫數(shù)量', '單價(jià)', '總價(jià)值', '客戶', '出庫日期', '操作人']
        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")
        # 調(diào)整所有工作表的列寬
        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"已創(chuàng)建新的庫存文件: {self.inventory_file}")
    def load_inventory(self):
        """加載庫存數(shù)據(jù)"""
        # 使用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("庫存數(shù)據(jù)已加載")
        print(f"當(dāng)前庫存: {len(self.inventory_df)} 種產(chǎn)品")
        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):
        """添加新產(chǎn)品到庫存"""
        # 檢查產(chǎn)品ID是否已存在
        if product_id in self.inventory_df['產(chǎn)品ID'].values:
            print(f"錯(cuò)誤: 產(chǎn)品ID {product_id} 已存在")
            return False
        # 創(chuàng)建新產(chǎn)品記錄
        new_product = {
            '產(chǎn)品ID': product_id,
            '產(chǎn)品名稱': name,
            '類別': category,
            '供應(yīng)商': supplier,
            '單價(jià)': price,
            '庫存量': quantity,
            '庫存價(jià)值': 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,
            '產(chǎn)品ID': product_id,
            '產(chǎn)品名稱': name,
            '入庫數(shù)量': quantity,
            '單價(jià)': price,
            '總價(jià)值': price * quantity,
            '供應(yīng)商': 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"已添加新產(chǎn)品: {name} (ID: {product_id})")
        return True
    def update_stock(self, product_id, quantity_change, is_incoming=True, customer_or_supplier=None, operator='system'):
        """更新庫存"""
        # 查找產(chǎn)品
        product_mask = self.inventory_df['產(chǎn)品ID'] == product_id
        if not any(product_mask):
            print(f"錯(cuò)誤: 產(chǎn)品ID {product_id} 不存在")
            return False
        # 獲取產(chǎn)品信息
        product_idx = product_mask.idxmax()
        product = self.inventory_df.loc[product_idx]
        # 計(jì)算新庫存量
        new_quantity = product['庫存量'] + quantity_change if is_incoming else product['庫存量'] - quantity_change
        # 檢查庫存是否足夠（出庫時(shí)）
        if not is_incoming and new_quantity < 0:
            print(f"錯(cuò)誤: 產(chǎn)品 {product['產(chǎn)品名稱']} 庫存不足，當(dāng)前庫存: {product['庫存量']}")
            return False
        # 更新庫存
        self.inventory_df.at[product_idx, '庫存量'] = new_quantity
        self.inventory_df.at[product_idx, '庫存價(jià)值'] = new_quantity * product['單價(jià)']
        self.inventory_df.at[product_idx, '最后更新'] = datetime.datetime.now()
        # 添加記錄
        if is_incoming:
            # 入庫記錄
            record = {
                '記錄ID': len(self.in_records_df) + 1,
                '產(chǎn)品ID': product_id,
                '產(chǎn)品名稱': product['產(chǎn)品名稱'],
                '入庫數(shù)量': quantity_change,
                '單價(jià)': product['單價(jià)'],
                '總價(jià)值': quantity_change * product['單價(jià)'],
                '供應(yīng)商': customer_or_supplier or product['供應(yīng)商'],
                '入庫日期': 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,
                '產(chǎn)品ID': product_id,
                '產(chǎn)品名稱': product['產(chǎn)品名稱'],
                '出庫數(shù)量': quantity_change,
                '單價(jià)': product['單價(jià)'],
                '總價(jià)值': quantity_change * product['單價(jià)'],
                '客戶': 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['產(chǎn)品名稱']} {quantity_change} 個(gè)，當(dāng)前庫存: {new_quantity}")
        return True
    def generate_inventory_report(self, output_file):
        """生成庫存報(bào)表"""
        # 創(chuàng)建一個(gè)新的工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = "庫存報(bào)表"
        # 添加報(bào)表標(biāo)題
        ws.merge_cells('A1:H1')
        title_cell = ws['A1']
        title_cell.value = "庫存狀況報(bào)表"
        title_cell.font = Font(size=16, bold=True)
        title_cell.alignment = Alignment(horizontal="center")
        # 添加報(bào)表生成時(shí)間
        ws.merge_cells('A2:H2')
        date_cell = ws['A2']
        date_cell.value = f"生成時(shí)間: {datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        date_cell.alignment = Alignment(horizontal="center")
        # 添加表頭
        headers = ['產(chǎn)品ID', '產(chǎn)品名稱', '類別', '供應(yīng)商', '單價(jià)', '庫存量', '庫存價(jià)值', '庫存狀態(tài)']
        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")
        # 添加數(shù)據(jù)
        # 計(jì)算庫存狀態(tài)
        def get_stock_status(row):
            if row['庫存量'] <= 0:
                return "缺貨"
            elif row['庫存量'] < 5:
                return "庫存不足"
            elif row['庫存量'] > 20:
                return "庫存過多"
            else:
                return "正常"
        # 添加庫存狀態(tài)列
        self.inventory_df['庫存狀態(tài)'] = self.inventory_df.apply(get_stock_status, axis=1)
        # 按類別和庫存狀態(tài)排序
        sorted_df = self.inventory_df.sort_values(['類別', '庫存狀態(tài)'])
        # 寫入數(shù)據(jù)
        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
                # 設(shè)置格式
                if column == '單價(jià)':
                    cell.number_format = '￥#,##0.00'
                elif column == '庫存價(jià)值':
                    cell.number_format = '￥#,##0.00'
                # 設(shè)置庫存狀態(tài)的顏色
                if column == '庫存狀態(tài)':
                    if value == "缺貨":
                        cell.fill = PatternFill("solid", fgColor="FF0000")
                    elif value == "庫存不足":
                        cell.fill = PatternFill("solid", fgColor="FFC000")
                    elif value == "庫存過多":
                        cell.fill = PatternFill("solid", fgColor="92D050")
        # 添加合計(jì)行
        total_row = len(sorted_df) + 5
        ws.cell(row=total_row, column=1).value = "合計(jì)"
        ws.cell(row=total_row, column=1).font = Font(bold=True)
        # 計(jì)算總庫存量和總價(jià)值
        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['庫存價(jià)值'].sum()
        ws.cell(row=total_row, column=7).font = Font(bold=True)
        ws.cell(row=total_row, column=7).number_format = '￥#,##0.00'
        # 添加類別統(tǒng)計(jì)
        ws.cell(row=total_row + 2, column=1).value = "類別統(tǒng)計(jì)"
        ws.cell(row=total_row + 2, column=1).font = Font(bold=True)
        category_stats = sorted_df.groupby('類別').agg({
            '產(chǎn)品ID': 'count',
            '庫存量': 'sum',
            '庫存價(jià)值': 'sum'
        }).reset_index()
        # 寫入類別統(tǒng)計(jì)表頭
        category_headers = ['類別', '產(chǎn)品數(shù)量', '總庫存量', '總庫存價(jià)值']
        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")
        # 寫入類別統(tǒng)計(jì)數(shù)據(jù)
        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['產(chǎn)品ID']
            ws.cell(row=row_num, column=3).value = row['庫存量']
            ws.cell(row=row_num, column=4).value = row['庫存價(jià)值']
            ws.cell(row=row_num, column=4).number_format = '￥#,##0.00'
        # 調(diào)整列寬
        for col in range(1, len(headers) + 1):
            ws.column_dimensions[get_column_letter(col)].width = 15
        # 保存報(bào)表
        wb.save(output_file)
        print(f"庫存報(bào)表已生成: {output_file}")
        return output_file
    def _save_to_excel(self):
        """保存數(shù)據(jù)到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, "打印機(jī)", "辦公設(shè)備", "C供應(yīng)商", 1299, 5)
# inventory.update_stock(1001, 5, is_incoming=True, customer_or_supplier="A供應(yīng)商", operator="張三")
# inventory.update_stock(1002, 2, is_incoming=False, customer_or_supplier="客戶A", operator="李四")
# inventory.generate_inventory_report("庫存報(bào)表.xlsx")