一、openpyxl库介绍

openpyxl是Python中最流行的Excel处理库之一，它支持读写Excel 2010及以后版本的xlsx/xlsm文件。openpyxl的功能非常全面，可以处理单元格数据、公式、样式、图表、数据验证、条件格式等各种Excel元素。与xlrd/xlwt等老牌库相比，openpyxl对xlsx格式有更好的支持，而且仍在积极维护中。

安装openpyxl非常简单，只需执行pip install openpyxl命令即可。使用openpyxl的基本流程是：首先加载或创建工作簿（Workbook），然后获取工作表（Worksheet），接着操作单元格（Cell），最后保存工作簿。openpyxl提供了直观的API，如wb.active获取活动工作表，ws[‘A1’]访问单元格，ws.append()追加数据行等，使用起来非常方便。

二、批量数据处理实战

批量数据处理是Excel自动化最常见的应用场景。假设有一个场景：需要将多个Excel文件中的数据合并到一个汇总表中。使用openpyxl可以轻松实现这个功能。首先，遍历文件夹中的所有Excel文件，逐个打开并读取数据；然后，将数据写入汇总表；最后保存汇总表。整个过程可以完全自动化，无需人工干预。

以下是一个完整的Excel合并示例代码：

┌────────────────────────────────────────────────────────────┐

│ import os│

│ from openpyxl import Workbook, load_workbook│

││

│ def merge_excel_files(folder_path, output_file):│

│“””合并文件夹中所有Excel文件的数据“””│

│# 创建汇总工作簿│

│merged_wb = Workbook()│

│merged_ws = merged_wb.active│

│merged_ws.title = “汇总数据“│

││

│header_written = False│

││

│# 遍历文件夹中的所有Excel文件│

│for filename in os.listdir(folder_path):│

│if filename.endswith(“.xlsx”) and not filename.startswith(“~$”):│

│file_path = os.path.join(folder_path, filename)│

│wb = load_workbook(file_path)│

│ws = wb.active│

││

│# 写入表头（只写一次）│

│if not header_written:│

│headers = [cell.value for cell in ws[1]]│

│merged_ws.append(headers)│

│header_written = True│

││

│# 写入数据行（跳过表头）│

│for row in ws.iter_rows(min_row=2, values_only=True):│

│if any(row):# 跳过空行│

│merged_ws.append(row)│

││

│wb.close()│

│print(f”已合并: {filename}”)│

││

│# 保存汇总文件│

│merged_wb.save(output_file)│

│print(f”汇总完成，保存至: {output_file}”)│

││

│ # 使用示例│

│ merge_excel_files(“./data_files”, “汇总表.xlsx”)│

└────────────────────────────────────────────────────────────┘

另一个常见场景是数据清洗和转换。例如，从系统导出的数据可能存在格式问题，如日期格式不统一、数字存储为文本、存在多余空格等。使用openpyxl可以编写脚本自动处理这些问题：遍历所有单元格，检查并修正数据格式，去除多余空格，转换数据类型等。相比手动处理，自动化脚本不仅效率更高，而且更加准确可靠。

三、自动生成报表

自动生成报表是Excel自动化的高级应用。在企业中，经常需要定期生成格式化的报表，如日报、周报、月报等。这些报表通常有固定的模板和格式要求，如果手动制作，需要花费大量时间进行格式调整和数据填充。使用openpyxl可以创建报表模板，然后通过脚本自动填充数据，生成格式化的报表。

以下是一个生成销售报表的示例代码，包含样式设置和图表创建：

┌────────────────────────────────────────────────────────────┐

│ from openpyxl import Workbook│

│ from openpyxl.styles import Font, Alignment, Border, Side, PatternFill│

│ from openpyxl.chart import BarChart, Reference│

││

│ def create_sales_report(data, output_file):│

│“””生成格式化的销售报表“””│

│wb = Workbook()│

│ws = wb.active│

│ws.title = “销售报表“│

││

│# 定义样式│

│header_font = Font(bold=True, size=12, color=”FFFFFF”)│

│header_fill = PatternFill(start_color=”4472C4″, end_color=”4472C4″)│

│header_alignment = Alignment(horizontal=”center”, vertical=”center”)│

│thin_border = Border(│

│left=Side(style=”thin”),│

│right=Side(style=”thin”),│

│top=Side(style=”thin”),│

│bottom=Side(style=”thin”)│

│)│

││

│# 写入表头│

│headers = [“产品名称“, “销售数量“, “销售金额“, “同比增长“]│

│for col, header in enumerate(headers, 1):│

│cell = ws.cell(row=1, column=col, value=header)│

│cell.font = header_font│

│cell.fill = header_fill│

│cell.alignment = header_alignment│

│cell.border = thin_border│

││

│# 写入数据│

│for row_idx, row_data in enumerate(data, 2):│

│for col_idx, value in enumerate(row_data, 1):│

│cell = ws.cell(row=row_idx, column=col_idx, value=value)│

│cell.border = thin_border│

│cell.alignment = Alignment(horizontal=”center”)│

││

│# 设置列宽│

│ws.column_dimensions[“A”].width = 20│

│ws.column_dimensions[“B”].width = 12│

│ws.column_dimensions[“C”].width = 15│

│ws.column_dimensions[“D”].width = 12│

││

│# 创建柱状图│

│chart = BarChart()│

│chart.type = “col”│

│chart.title = “销售金额对比“│

│data_ref = Reference(ws, min_col=3, min_row=1, max_row=len(data)+1)│

│cats_ref = Reference(ws, min_col=1, min_row=2, max_row=len(data)+1)│

│chart.add_data(data_ref, titles_from_data=True)│

│chart.set_categories(cats_ref)│

│ws.add_chart(chart, “F2”)│

││

│wb.save(output_file)│

│print(f”报表已生成: {output_file}”)│

││

│ # 使用示例│

│ sales_data = [│

│[“产品A”, 150, 45000, 0.15],│

│[“产品B”, 200, 60000, 0.22],│

│[“产品C”, 180, 54000, 0.08]│

│ ]│

│ create_sales_report(sales_data, “销售报表.xlsx”)│

└────────────────────────────────────────────────────────────┘

四、实用代码示例

下面再分享一个数据清洗的实用示例，用于处理常见的Excel数据问题：

┌────────────────────────────────────────────────────────────┐

│ from openpyxl import load_workbook│

│ from datetime import datetime│

││

│ def clean_excel_data(file_path):│

│“””清洗Excel数据：去除空格、统一格式、处理空值“””│

│wb = load_workbook(file_path)│

│ws = wb.active│

││

│for row in ws.iter_rows():│

│for cell in row:│

│if cell.value is None:│

│continue│

││

│# 字符串处理：去除首尾空格│

│if isinstance(cell.value, str):│

│cell.value = cell.value.strip()│

│# 尝试转换为数字│

│try:│

│cell.value = float(cell.value)│

│except ValueError:│

│pass│

││

│# 日期格式统一│

│if isinstance(cell.value, datetime):│

│cell.number_format = “YYYY-MM-DD”│

││

│wb.save(file_path)│

│print(f”数据清洗完成: {file_path}”)│

││

│ # 使用示例│

│ clean_excel_data(“原始数据.xlsx”)│

└────────────────────────────────────────────────────────────┘