【办公自动化】使用Python来自动化处理Excel表格有哪些方法？

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

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

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

安装这些库

pip install xlrd xlwt xlutils

读取Excel文件

import xlrddef 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文件

import xlwtdef 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 datetimedate_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文件

import xlrd
import xlwt
from xlutils.copy import copydef 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 = Falsefor row in range(1, sheet.nrows):if int(sheet.cell_value(row, 0)) == employee_id:  # 假设ID在第一列w_sheet.write(row, 4, new_salary)  # 假设薪资在第五列found = Truebreakif 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

pip install openpyxl

读取Excel文件

from openpyxl import load_workbookdef read_excel_with_openpyxl(file_path):"""使用openpyxl读取Excel文件"""# 加载工作簿wb = load_workbook(file_path, read_only=True)# 获取所有工作表名称sheet_names = wb.sheetnamesprint(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 = 0for row in sheet.iter_rows(min_row=2):  # 从第二行开始if row_count >= 5:breakrow_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文件

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.utils import get_column_letter
import datetimedef create_excel_with_openpyxl(file_path):"""使用openpyxl创建格式化的Excel文件"""# 创建工作簿wb = Workbook()sheet = wb.activesheet.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 = headercell.font = header_fontcell.fill = header_fillcell.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_valueif col_num == 5:  # 日期列使用日期格式cell.number_format = 'yyyy-mm-dd'elif col_num == 7:  # 销售额列使用公式和货币格式cell.number_format = '¥#,##0.00'# 添加合计行total_row = len(data) + 2sheet.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文件

from openpyxl import load_workbook
import timedef 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 = 0sum_value = 0# 假设第5列是数值，我们要计算其总和for row in sheet.iter_rows(min_row=2, values_only=True):row_count += 1if 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

pip install xlwings

基本操作

import xlwings as xwdef 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) + 2sheet.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}").topchart.left = sheet.range("A1").left# 调整列宽sheet.autofit()# 保存文件wb.save("xlwings_销售报表.xlsx")print("Excel文件已创建并保存")finally:# 关闭工作簿和应用wb.close()app.quit()# 使用示例
# automate_excel_with_xlwings()

与Excel VBA结合使用

import xlwings as xwdef run_excel_macro():"""运行Excel中的VBA宏"""# 打开包含宏的工作簿wb = xw.Book("带宏的工作簿.xlsm")try:# 运行名为'ProcessData'的宏wb.macro("ProcessData")()print("宏已执行完成")# 读取宏处理后的结果sheet = wb.sheets["结果"]result = sheet.range("A1:C10").valueprint("处理结果:")for row in result:print(row)finally:# 保存并关闭工作簿wb.save()wb.close()# 使用示例（需要有包含'ProcessData'宏的Excel文件）
# run_excel_macro()

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

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

安装Pandas

pip install pandas

读取Excel文件

import pandas as pddef read_excel_with_pandas(file_path):"""使用pandas读取Excel文件"""# 读取所有工作表xlsx = pd.ExcelFile(file_path)# 获取所有工作表名称sheet_names = xlsx.sheet_namesprint(f"工作表列表: {sheet_names}")# 遍历每个工作表for sheet_name in sheet_names:# 读取工作表到DataFramedf = 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 pltdef 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.monthmonthly_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文件

import pandas as pd
import osdef 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文件")returnprint(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['源文件'] = filedf['工作表'] = sheet_name# 添加到列表dfs.append(df)# 合并所有DataFrameif 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 datetimedef generate_sales_report(sales_data_file, output_file):"""生成销售数据分析报表"""# 读取销售数据df = pd.read_excel(sales_data_file)# 数据清洗和准备df['销售日期'] = pd.to_datetime(df['销售日期'])df['月份'] = df['销售日期'].dt.monthdf['季度'] = 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.activews1.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 = headercell.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 = valuecell.number_format = '#,##0.00'# 添加合计行total_row = len(product_monthly) + 2ws1.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 = headercell.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 = valueif 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 osclass 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.activews.title = "库存"# 设置表头headers = ['产品ID', '产品名称', '类别', '供应商', '单价', '库存量', '库存价值', '最后更新']for col_num, header in enumerate(headers, 1):cell = ws.cell(row=1, column=col_num)cell.value = headercell.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 = valueif 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 = headercell.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 = headercell.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()}# 添加到DataFrameself.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 Truedef update_stock(self, product_id, quantity_change, is_incoming=True, customer_or_supplier=None, operator='system'):"""更新库存"""# 查找产品product_mask = self.inventory_df['产品ID'] == product_idif 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_quantityself.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 Truedef generate_inventory_report(self, output_file):"""生成库存报表"""# 创建一个新的工作簿wb = Workbook()ws = wb.activews.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 = headercell.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) + 5ws.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 = headercell.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_filedef _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都能帮你轻松应对。