Python使用OpenPyXL库操作Excel表的操作_F11 - 专业站长和开发者的学习网站

本站首页收藏本站

广告位联系

APP正在开发中...

返回顶部

分享到

Python使用OpenPyXL库操作Excel表的操作

python 来源：互联网作者：佚名发布时间：2024-11-19 08:12:04 人浏览

摘要

在现代办公中，Excel表格无疑是处理数据、生成报告和分析信息的得力助手。无论是财务统计、数据整理还是业务分析，Excel 都扮演着不可或缺的角色。然而，手动处理Excel表格既费时又容易出

在现代办公中，Excel表格无疑是处理数据、生成报告和分析信息的得力助手。无论是财务统计、数据整理还是业务分析，Excel 都扮演着不可或缺的角色。然而，手动处理Excel表格既费时又容易出错。有没有一种方式能自动化这些繁琐的操作呢？答案是肯定的！在Python的众多库中，OpenPyXL 是操作Excel文件的利器，它可以轻松帮我们实现自动化办公，让数据处理效率大幅提升！

在这篇文章中，我们将介绍 OpenPyXL 库的基本功能以及如何利用它高效操作 Excel 文件。

一、安装 OpenPyXL

在开始之前，需要安装 OpenPyXL 库。您可以使用 pip 命令来进行安装：

1	pip install openpyxl

安装完成后，您就可以开始使用 OpenPyXL 处理 Excel 表格了。

二、OpenPyXL的基本功能

OpenPyXL 允许我们在 Excel 文件（以 .xlsx 为后缀）中进行多种操作，包括创建、读取、编辑和保存工作表。以下是它的一些主要功能：

创建和加载工作簿
操作工作表
单元格读写
操作格式
保存修改

让我们通过示例代码详细了解如何使用这些功能。

1. 创建和加载工作簿

在 OpenPyXL 中，工作簿（Workbook）是 Excel 文件的主体，包含多个工作表（Worksheet）。首先，我们来看如何创建一个新的工作簿和加载现有的 Excel 文件。

from openpyxl import Workbook, load_workbook

# 创建新的工作簿

wb = Workbook()

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

# 加载现有的工作簿

wb = load_workbook("example.xlsx")

ws = wb.active

在这里，我们可以选择打开现有文件或者创建新的文件。

2. 操作工作表

在 Excel 中，工作表是用于存放数据的表格。我们可以创建、重命名或删除工作表：

# 创建新的工作表

ws_new = wb.create_sheet("NewSheet")

# 重命名工作表

ws.title = "MainSheet"

# 删除工作表

wb.remove(ws_new)

创建和删除工作表可以帮助我们灵活管理数据分区，并保持文件的清晰有序。

3. 单元格的读写操作

OpenPyXL 提供了简单的接口来操作单元格数据，我们可以通过单元格的坐标来读取或写入数据：

# 写入数据

ws["A1"] = "Hello, OpenPyXL!"

# 读取数据

data = ws["A1"].value

print(data) # 输出: Hello, OpenPyXL!

# 使用行列索引访问

ws.cell(row=2, column=1, value="This is row 2, column 1")

print(ws.cell(row=2, column=1).value) # 输出: This is row 2, column 1

可以看到，OpenPyXL 允许我们通过坐标和索引来进行单元格的读写操作。这样可以方便地对数据进行修改、添加或删除。

4. 操作格式：更改样式和格式

格式化是 Excel 表格的重要特性之一。在 OpenPyXL 中，我们可以为单元格设置字体、颜色、边框等，以实现更美观的展示效果：

from openpyxl.styles import Font, Color, Alignment

# 设置字体

ws["A1"].font = Font(name="Arial", size=12, bold=True, color="FF0000") # 红色粗体

# 设置对齐方式

ws["A1"].alignment = Alignment(horizontal="center", vertical="center")

# 设置单元格颜色

from openpyxl.styles import PatternFill

ws["A1"].fill = PatternFill("solid", fgColor="FFFF00") # 黄色填充

通过上述代码，我们可以对单元格的格式进行自定义，制作更加清晰、专业的报表。

5. 保存修改

完成所有操作后，记得保存工作簿：

1	wb.save("modified_example.xlsx")

这将保存所有的修改到指定的文件中。

三、综合示例：批量填充数据

以下是一个完整的示例代码，用于批量填充数据到 Excel 文件中，并为特定的单元格添加格式。这个例子模拟了一个简单的数据表填充过程。

from openpyxl import Workbook

from openpyxl.styles import Font, PatternFill

# 创建新的工作簿和工作表

wb = Workbook()

ws = wb.active

ws.title = "Sales Report"

# 添加标题行

ws.append(["Product", "Region", "Sales"])

ws["A1"].font = Font(bold=True, color="FFFFFF")

ws["B1"].font = Font(bold=True, color="FFFFFF")

ws["C1"].font = Font(bold=True, color="FFFFFF")

# 设置标题背景颜色

ws["A1"].fill = PatternFill("solid", fgColor="4F81BD")

ws["B1"].fill = PatternFill("solid", fgColor="4F81BD")

ws["C1"].fill = PatternFill("solid", fgColor="4F81BD")

# 填充数据

data = [

["Widget", "North", 1200],

["Widget", "South", 900],

["Gadget", "North", 500],

["Gadget", "South", 300],

]

for row in data:

ws.append(row)

# 保存工作簿

wb.save("sales_report.xlsx")

print("Sales report created and saved as sales_report.xlsx")

示例说明

在这个示例中，我们创建了一个销售报告，包含产品名称、销售区域和销售额。通过 append 方法批量添加数据，同时对标题行进行了格式设置，使得表格更加美观。完成所有填充后，保存文件即可。

四、OpenPyXL 的优势和应用场景

OpenPyXL 是一个功能强大且易用的 Python 库，特别适合用来自动化处理 Excel 文件。相比手动操作 Excel，OpenPyXL 提供了很多优势：

批量处理数据：可以快速处理大量数据，节省人工操作的时间。
自定义格式：可以为数据自动添加格式，生成符合公司标准的报告。
高度自动化：适合定期生成的文件报表，通过脚本即可完成，避免重复劳动。

应用场景

财务报表生成：自动化生成和格式化财务报表、销售报告。
数据处理和清洗：批量处理数据，生成整理后的数据表。
统计分析报告：结合 Python 数据分析库（如 Pandas）和 OpenPyXL，将分析结果直接写入 Excel 文件。

OpenPyXL 带来的不仅是简单的数据写入和读取功能，更是一个全方位的 Excel 处理工具，它不仅让我们自动化处理大批量数据，还为我们提供了灵活的格式化和自定义功能，适合多种办公场景。下面我们深入了解更多功能，例如公式应用、数据验证和图表创建，帮助您更全面地掌握 OpenPyXL 的强大之处。

五、进阶功能

1. 使用公式

Excel 的公式功能在数据计算和分析中非常强大。在 OpenPyXL 中，我们也可以直接在单元格中使用公式，这样可以让 Excel 文件在打开时自动计算结果。

from openpyxl import Workbook

# 创建工作簿和工作表

wb = Workbook()

ws = wb.active

# 填充数据

ws["A1"] = "Value 1"

ws["A2"] = 10

ws["B1"] = "Value 2"

ws["B2"] = 20

# 添加公式

ws["C1"] = "Total"

ws["C2"] = "=A2+B2"

# 保存文件

wb.save("formula_example.xlsx")

在这个例子中，C2 单元格中插入了公式 =A2+B2，保存文件后打开 Excel 时，C2 会自动显示 A2 和 B2 的和。这样可以灵活地设置动态计算，让报表更具实用性。

2. 数据验证

数据验证是 Excel 中用来限制输入内容的功能。使用 OpenPyXL 可以为特定单元格添加数据验证规则，比如限制输入范围、指定单选选项等。以下是一个例子，通过数据验证设置单元格的输入为特定选项：

from openpyxl import Workbook

from openpyxl.worksheet.datavalidation import DataValidation

# 创建工作簿和工作表

wb = Workbook()

ws = wb.active

# 创建数据验证规则：下拉菜单

dv = DataValidation(type="list", formula1='"Option1,Option2,Option3"', showDropDown=True)

ws.add_data_validation(dv)

# 将验证应用到单元格范围

dv.add(ws["A1"])

# 保存文件

wb.save("data_validation_example.xlsx")

在 Excel 文件中打开后，A1 单元格会显示一个下拉菜单，用户只能选择“Option1”、“Option2”或“Option3”。这种功能在收集规范化数据时非常有用。

3. 创建图表

Excel 的图表功能能够直观展示数据的趋势和变化。在 OpenPyXL 中可以使用内置的 chart 模块生成常见的图表，如折线图、柱状图和饼图。以下是一个创建柱状图的示例：

from openpyxl import Workbook

from openpyxl.chart import BarChart, Reference

# 创建工作簿和工作表

wb = Workbook()

ws = wb.active

# 填充数据

data = [

["Product", "Sales"],

["Widget", 1200],

["Gadget", 900],

["Doohickey", 700],

]

for row in data:

ws.append(row)

# 创建柱状图

chart = BarChart()

chart.title = "Sales Data"

chart.x_axis.title = "Product"

chart.y_axis.title = "Sales"

# 设置数据范围

data_ref = Reference(ws, min_col=2, min_row=2, max_row=4)

cats = Reference(ws, min_col=1, min_row=2, max_row=4)

chart.add_data(data_ref, titles_from_data=True)

chart.set_categories(cats)

# 添加图表到工作表

ws.add_chart(chart, "E5")

# 保存文件

wb.save("chart_example.xlsx")

在此示例中，我们创建了一个柱状图并将其插入到工作表中。add_chart() 方法会将图表显示在指定位置（“E5” 单元格位置）。保存文件后，打开 Excel 即可看到生成的图表。这个功能非常适合自动生成数据可视化报表。

4. 合并与拆分单元格

在 Excel 中，为了美化表格或汇总数据，我们经常需要合并或拆分单元格。OpenPyXL 也支持这一功能：

from openpyxl import Workbook

# 创建工作簿和工作表

wb = Workbook()

ws = wb.active

# 合并单元格

ws.merge_cells("A1:D1")

ws["A1"] = "Merged Cells Example"

# 拆分单元格

ws.unmerge_cells("A1:D1")

# 保存文件

wb.save("merge_cells_example.xlsx")

在这个例子中，我们首先将 A1:D1 范围的单元格合并为一个单元格，并输入文本内容。后续如果不需要合并，可以使用 unmerge_cells() 将其恢复为独立单元格。

5. 调整行高和列宽

我们还可以调整 Excel 中的行高和列宽，使表格内容更加清晰整齐：

# 设置列宽

ws.column_dimensions["A"].width = 20

# 设置行高

ws.row_dimensions[1].height = 30

通过 column_dimensions 和 row_dimensions 可以分别控制列和行的尺寸，从而使表格看起来更加美观。

六、自动化办公应用示例

以下是一个综合应用的示例代码，展示了如何用 OpenPyXL 生成一个自动化的销售报表，包括格式化、公式、数据验证和图表：

from openpyxl import Workbook

from openpyxl.styles import Font, PatternFill

from openpyxl.chart import BarChart, Reference

from openpyxl.worksheet.datavalidation import DataValidation

# 创建工作簿和工作表

wb = Workbook()

ws = wb.active

ws.title = "Monthly Sales Report"

# 添加标题行并格式化

ws.append(["Product", "Region", "Sales"])

for cell in ws[1]:

cell.font = Font(bold=True)

cell.fill = PatternFill("solid", fgColor="ADD8E6") # 浅蓝色背景

# 添加数据和数据验证

products = ["Widget", "Gadget", "Doohickey"]

dv = DataValidation(type="list", formula1=f'"{",".join(products)}"', showDropDown=True)

ws.add_data_validation(dv)

data = [

["Widget", "North", 1200],

["Widget", "South", 900],

["Gadget", "North", 700],

["Doohickey", "South", 300],

]

for row in data:

ws.append(row)

dv.add(ws[f"A{ws.max_row}"])

# 添加公式：总和

ws["D1"] = "Total Sales"

ws["D2"] = "=SUM(C2:C5)"

# 创建柱状图

chart = BarChart()

chart.title = "Sales by Product"

chart.x_axis.title = "Product"

chart.y_axis.title = "Sales"

data_ref = Reference(ws, min_col=3, min_row=2, max_row=5)

chart.add_data(data_ref, titles_from_data=True)

ws.add_chart(chart, "F5")

# 保存文件

wb.save("automated_sales_report.xlsx")

print("Automated sales report created successfully!")

示例分析

这个示例代码展示了如何使用 OpenPyXL 生成一个销售报告表格：

标题行格式化：对表格标题行的字体和背景色进行设置，使表格更清晰。
数据验证：在产品列应用了数据验证，使得输入数据时只能选择指定的产品。
公式计算：自动计算销售数据的总和，并显示在 D2 单元格。
图表生成：生成一个柱状图展示不同产品的销售额分布。

通过这样一份自动生成的 Excel 文件，可以快速完成数据的统计与展示，大大提升办公效率。

七、总结与展望

OpenPyXL 是一个非常实用的 Python 库，能够大大提高我们在处理 Excel 表格时的自动化水平。无论是简单的数据录入、格式化，还是复杂的公式计算和图表生成，都可以轻松实现。借助 OpenPyXL，您可以快速生成符合公司需求的专业报表，在节省时间的同时确保数据准确无误。

您可能感兴趣的文章 :

原文链接 :

Tag : python(964)

Python正则表达式进阶用法之字符集与字符范围介

Python 正则表达式进阶用法：字符集与字符范围详解正则表达式是文本处理和数据清洗中不可或缺的工具。在前面的学习中，我们已经了解了
Python对列表进行排序的五种方法

在Python编程中，排序是一个常见的需求。今天我来和大家分享5种强大且实用的排序方法，包括使用sort()方法、sorted()函数、lambda表达式作为
15个令人相见恨晚的Python字符串格式化方法

今天，我将向大家分享15个令人相见恨晚的Python字符串格式化技巧，帮你写出更高效、优雅且Pythonic的代码。 1.f{variabe=} 如果我们在f-字符串
Python使用OpenPyXL库操作Excel表的操作

在现代办公中，Excel表格无疑是处理数据、生成报告和分析信息的得力助手。无论是财务统计、数据整理还是业务分析，Excel 都扮演着不可或
使用Python自动备份重要文件

在数字化时代，数据是非常宝贵的资源。从个人照片和文档到重要的工作文件，我们的数字资产对我们来说越来越重要。因此，确保这些文
Python3访问MySQL数据库的实现步骤

要实现一个简单的IM（即时通讯）系统，支持用户注册、登录和聊天记录存储，你可以使用Python和mysql数据库。以下是一个基本的实现示例：
Python中的＂没有那个文件＂错误(FileNotFoundError)的

在Python编程中，遇到没有那个文件错误（FileNotFoundError）是常见的问题之一。这个错误通常发生在尝试访问一个不存在的文件或目录时，导致
python清理pip包管理器

在 Python 开发中，pip 是最常用的包管理工具。随着项目的不断发展，开发者往往会在环境中安装大量的依赖包。随着时间的推移，这些包可
Python怎么获取HTTP请求的Response Body

在Python中进行网络编程和Web开发时，经常需要发送HTTP请求并处理服务器返回的响应。其中，获取响应体（Response Body）是常见的需求之一。本
Python字符串检索方式的介绍

字符串查找方法主要有以下几种： 1.count(）用于检索指定字符串在另一个字符串中出现的次数。如果检索的字符串不存在，则返回0，否则

Python使用OpenPyXL库操作Excel表的操作

一、安装 OpenPyXL

二、OpenPyXL的基本功能

1. 创建和加载工作簿

2. 操作工作表

3. 单元格的读写操作

4. 操作格式：更改样式和格式

5. 保存修改

三、综合示例：批量填充数据

示例说明

四、OpenPyXL 的优势和应用场景

应用场景

五、进阶功能

1. 使用公式

2. 数据验证

3. 创建图表

4. 合并与拆分单元格

5. 调整行高和列宽

六、自动化办公应用示例

示例分析

七、总结与展望

您可能感兴趣的文章 :

Python正则表达式进阶用法之字符集与字符范围介

Python对列表进行排序的五种方法

15个令人相见恨晚的Python字符串格式化方法

Python使用OpenPyXL库操作Excel表的操作

使用Python自动备份重要文件

Python3访问MySQL数据库的实现步骤

Python中的＂没有那个文件＂错误(FileNotFoundError)的

python清理pip包管理器

Python怎么获取HTTP请求的Response Body

Python字符串检索方式的介绍

python批量下载抖音视频

利用Pyecharts可视化微信好友的方法

python爬取豆瓣电影TOP250数据

基于tensorflow权重文件的解读

解决Python字典查找报Keyerror的问题