喜欢的条友记得关注、点赞、转发、收藏，你们的支持就是我最大的动力源泉。

前期基础教程：

「Python3.11.0」手把手教你安装最新版Python运行环境

讲讲Python环境使用Pip命令快速下载各类库的方法

Python启航：30天编程速成之旅（第2天）-IDE安装

【Python教程】JupyterLab 开发环境安装

Python 中openpyxl和xlwings的完整、详细教程

目录

1. 简介什么是 openpyxl？什么是 xlwings？
2. 安装和配置安装 openpyxl安装 xlwings
3. openpyxl 基础操作创建和保存 Excel 文件读取和写入单元格数据操作工作表格式化单元格
4. xlwings 基础操作连接 Excel 应用程序读取和写入数据调用 Excel 函数使用 VBA 宏
5. 高级功能数据可视化（图表）处理大数据集自动化 Excel 操作
6. 实战案例案例 1：生成销售报告案例 2：自动化财务报表
7. 总结与常见问题解答

1. 简介

什么是openpyxl？

openpyxl 是一个用于读取和写入 Excel 文件的 Python 库，支持 .xlsx 格式的文件。它提供了简单易用的 API，可以轻松地创建、修改和保存 Excel 文件。openpyxl 是纯 Python 实现，不需要依赖 Microsoft Excel 应用程序。

什么是xlwings？

xlwings 是一个强大的 Python 库，允许你在 Python 和 Excel 之间进行交互。它不仅可以读取和写入 Excel 文件，还可以直接控制 Excel 应用程序，调用 Excel 函数、VBA 宏，并在 Excel 中执行复杂的计算和数据分析。xlwings 适合需要与 Excel 进行深度集成的场景。

2. 安装和配置

安装openpyxl

你可以通过 pip 安装 openpyxl：

pip install openpyxl

安装xlwings

xlwings 需要安装两个包：xlwings 和 pywin32（仅限 Windows）。你可以通过以下命令安装：

pip install xlwings pywin32

如果你使用的是 macOS 或 Linux，xlwings 仍然可以读取和写入 Excel 文件，但无法直接控制 Excel 应用程序。

3.openpyxl基础操作

3.1 创建和保存 Excel 文件

from openpyxl import Workbook

# 创建一个新的工作簿
wb = Workbook()

# 获取默认的工作表
ws = wb.active

# 修改工作表的标题
ws.title = "销售数据"

# 写入数据到单元格
ws['A1'] = "产品名称"
ws['B1'] = "销售额"
ws['C1'] = "日期"

# 添加更多数据
data = [
    ["苹果", 100, "2024-09-01"],
    ["香蕉", 80, "2024-09-02"],
    ["橙子", 120, "2024-09-03"]
]

for row in data:
    ws.append(row)

# 保存工作簿
wb.save(r".\第25天\sales_report.xlsx")
print("sales_report.xlsx表格建立完毕。")

打开excel表格，显示如下：

3.2 读取和写入单元格数据

from openpyxl import load_workbook

# 加载现有的 Excel 文件
wb = load_workbook(r".\第25天\sales_report.xlsx")

# 选择工作表
ws = wb.active

# 读取单元格数据
product_name = ws['A2'].value
sales_amount = ws['B2'].value
date = ws['C2'].value

print(f"产品: {product_name}, 销售额: {sales_amount}, 日期: {date}")

# 修改单元格数据
ws['B2'] = 150

# 保存修改后的文件
wb.save(r".\第25天\sales_report_modified.xlsx")

print("sales_report_modified.xlsx表格建立完毕。")

打开excel表格，显示如下：

3.3 操作工作表

# 创建新的工作表
ws2 = wb.create_sheet("库存数据")

# 删除工作表
del wb["库存数据"]

# 复制工作表
wb.copy_worksheet(ws).title = "销售数据副本"

# 保存工作簿
wb.save("sales_report_modified.xlsx")

3.4 格式化单元格

from openpyxl.styles import Font, Alignment, PatternFill

# 设置字体样式
ws['A1'].font = Font(bold=True, color="FF0000")  # 红色加粗字体

# 设置对齐方式
ws['A1'].alignment = Alignment(horizontal="center", vertical="center")

# 设置背景颜色
ws['A1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")  # 黄色背景

# 保存工作簿
wb.save("sales_report_styled.xlsx")

打开excel表格，显示如下：

4.xlwings基础操作

4.1 连接 Excel 应用程序

import xlwings as xw

# 打开一个新的 Excel 应用程序
app = xw.App(visible=True)

# 创建一个新的工作簿
wb = app.books.add()

# 选择活动工作表
ws = wb.sheets[0]

# 关闭 Excel 应用程序
app.quit()

4.2 读取和写入数据

# 写入数据到单元格
ws.range('A1').value = "产品名称"
ws.range('B1').value = "销售额"
ws.range('C1').value = "日期"

# 写入多行数据
data = [
    ["苹果", 100, "2024-09-01"],
    ["香蕉", 80, "2024-09-02"],
    ["橙子", 120, "2024-09-03"]
]
ws.range('A2').value = data

# 读取数据
product_name = ws.range('A2').value
sales_amount = ws.range('B2').value
date = ws.range('C2').value

print(f"产品: {product_name}, 销售额: {sales_amount}, 日期: {date}")

4.3 调用 Excel 函数

# 调用 SUM 函数
total_sales = ws.api.Range('B2:B4').Application.WorksheetFunction.Sum(ws.api.Range('B2:B4'))
print(f"总销售额: {total_sales}")

# 调用 AVERAGE 函数
average_sales = ws.api.Range('B2:B4').Application.WorksheetFunction.Average(ws.api.Range('B2:B4'))
print(f"平均销售额: {average_sales}")

4.4 使用 VBA 宏

# 运行 VBA 宏
wb.macro('MyMacro')()

完整代码：

import xlwings as xw

# 打开一个新的 Excel 应用程序
app = xw.App(visible=True)

# 创建一个新的工作簿
wb = app.books.add()

# 选择活动工作表
ws = wb.sheets[0]

# 写入数据到单元格
ws.range('A1').value = "产品名称"
ws.range('B1').value = "销售额"
ws.range('C1').value = "日期"

# 写入多行数据
data = [
    ["苹果", 100, "2024-09-01"],
    ["香蕉", 80, "2024-09-02"],
    ["橙子", 120, "2024-09-03"]
]
ws.range('A2').value = data

# 读取数据
product_name = ws.range('A2').value
sales_amount = ws.range('B2').value
date = ws.range('C2').value

print(f"产品: {product_name}, 销售额: {sales_amount}, 日期: {date}")

# 调用 SUM 函数
total_sales = ws.api.Range('B2:B4').Application.WorksheetFunction.Sum(ws.api.Range('B2:B4'))
print(f"总销售额: {total_sales}")

# 调用 AVERAGE 函数
average_sales = ws.api.Range('B2:B4').Application.WorksheetFunction.Average(ws.api.Range('B2:B4'))
print(f"平均销售额: {average_sales}")

excel表格，显示如下：

5. 高级功能

5.1 数据可视化（图表）

使用openpyxl创建图表

from openpyxl.chart import BarChart, Reference
from openpyxl import load_workbook

# 加载现有的 Excel 文件
wb = load_workbook(r".\第25天\sales_report.xlsx")

# 选择工作表
ws = wb.active

# 创建数据引用
data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=4)

# 创建柱状图
chart = BarChart()
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.title = "销售数据图表"

# 将图表添加到工作表
ws.add_chart(chart, "E1")

# 保存工作簿
wb.save(r".\第25天\sales_report_with_chart.xlsx")
print("sales_report_with_chart.xlsx表格建立完毕。")

excel表格，显示如下：

使用xlwings创建图表

# 插入柱状图
chart = ws.charts.add(left=500, top=50, width=400, height=300)
chart.chart_type = 'column_clustered'
chart.set_source_data(ws.range('A1:C4'))
chart.name = "销售数据图表"

5.2 处理大数据集

对于处理大数据集，openpyxl 提供了 read_only 和 write_only 模式，可以显著提高性能。

from openpyxl import load_workbook

# 以只读模式打开大文件
wb = load_workbook(r".\第25天\sales_report.xlsx", read_only=True)

# 遍历工作表中的所有行
for row in wb.active.rows:
    print([cell.value for cell in row])

print("sales_report.xlsx显示完毕。")

5.3 自动化 Excel 操作

xlwings 可以用于自动化复杂的 Excel 操作，例如批量生成报告、更新多个工作表等。

# 批量生成多个报告
for i in range(1, 6):
    wb = xw.Book()
    ws = wb.sheets[0]
    ws.range('A1').value = f"报告 {i}"
    wb.save(f"report_{i}.xlsx")
    wb.close()

6. 实战案例

案例 1：生成销售报告

假设你有一个包含销售数据的 CSV 文件，你需要将其导入到 Excel 中，并生成一份带有图表的销售报告。

import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

# 读取 CSV 文件
df = pd.read_csv("sales_data.csv")

# 创建一个新的工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售报告"

# 将 DataFrame 写入 Excel
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# 创建柱状图
data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=len(df) + 1)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(df) + 1)

chart = BarChart()
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.title = "销售数据图表"

ws.add_chart(chart, "E1")

# 保存工作簿
wb.save("sales_report.xlsx")

案例 2：自动化财务报表

假设你需要每月自动生成一份财务报表，包括收入、支出和利润。你可以使用 xlwings 来自动化这个过程。

import xlwings as xw
import datetime

# 打开模板文件
template_wb = xw.Book("financial_report_template.xlsx")
ws = template_wb.sheets["Sheet1"]

# 获取当前月份
current_month = datetime.datetime.now().strftime("%Y-%m")

# 更新收入、支出和利润
income = 10000
expenses = 5000
profit = income - expenses

ws.range('B2').value = current_month
ws.range('B3').value = income
ws.range('B4').value = expenses
ws.range('B5').value = profit

# 保存为新文件
template_wb.save(f"financial_report_{current_month}.xlsx")
template_wb.close()

7. 总结与常见问题解答

总结

• openpyxl 是一个轻量级的库，适合处理 .xlsx 文件，提供简单的 API 来创建、读取和写入 Excel 文件。
• xlwings 是一个更强大的工具，允许你直接与 Excel 应用程序交互，适合需要与 Excel 进行深度集成的场景。
• 通过结合这两个库，你可以轻松实现从简单的数据操作到复杂的自动化任务。

常见问题解答

Q1: openpyxl 是否支持 .xls 文件？

• openpyxl 仅支持 .xlsx 文件格式。如果你需要处理 .xls 文件，建议使用 xlrd 或 xlwt 库。

Q2: xlwings 是否可以在没有 Excel 安装的环境中使用？

• xlwings 在 Windows 上需要安装 Excel 应用程序才能控制 Excel。如果你使用的是 macOS 或 Linux，xlwings 仍然可以读取和写入 Excel 文件，但无法直接控制 Excel 应用程序。

Q3: 如何处理 Excel 文件中的公式？

• openpyxl 可以读取和写入公式，但在保存文件时不会重新计算公式的结果。如果你需要重新计算公式，建议使用 xlwings，因为它可以直接控制 Excel 应用程序并执行公式计算。

喜欢的条友记得关注、点赞、转发、收藏，你们的支持就是我最大的动力源泉。