Python openpyxl库处理Excel文件高级应用技巧实例
目录
- openpyxl的重要性和优势
- 安装openpyxl
- 基本概念
- 1 Workbook（工作簿）
- 2 Sheet（工作表）
- 3 Cell（单元格）
- 读取数据
- 1 打开Excel文件
- 2 读取文本数据
- 3 读取数字数据
- 4 读取日期数据
- 写入数据
- 1 写入文本和数字数据
- 2 写入日期数据
- 3 样式设置
- 4 合并单元格
- 5 保存文件
- 图表和图片
- 1 添加图表
- 2 插入图片
- 3 设置图表和图片属性
- 4 保存文件
- 数据筛选和排序
- 1 数据筛选
- 2 数据排序
- 公式计算
- 1 基本公式
- 2 复杂公式
- 3 计算日期差
- 4 注意事项
- 样式设置
- 1 设置字体样式
- 2 设置背景颜色
- 3 设置边框
- 实际应用场景
- 1 数据报告生成
- 2 数据导出
- 3 批量处理数据
- 总结
openpyxl的重要性和优势
跨平台性： openpyxl可以在多个平台上运行，包括Windows、linux和MACOS，使得数据处理任务更加灵活和便捷。
无需Excel软件： 与使用Excel软件相比，openpyxl允许在没有安装Microsoft Excel的环境中进行Excel文件的读写和操作。
开放源代码： openpyxl是一个开源项目，可以自由使用和修改，使得开发者能够根据自己的需求对其进行定制和扩展。
活跃的社区支持： 由于其在开源社区中的活跃参与，openpyxl不断更新和改进，确保与最新的Excel格式兼容，并提供最佳性能。
功能丰富： openpyxl提供了丰富的功能，包括但不限于读取和写入数据、创建图表、设置样式、执行公式计算等，使其适用于各种复杂的Excel处理任务。
安装openpyxl
使用pip（python包管理工具）来安装openpyxl。
pip install openpyxl
基本概念
1 Workbook（工作簿）
在openpyxl中，Workbook是一个Excel工作簿的抽象表示。它是整个Excel文件的最顶层对象，包含了所有的Sheet和相关数据。
示例代码：创建Workbook
from openpyxl import Workbook # 创建一个Workbook对象 wb = Workbook()
在这个例子中，使用Workbook类创建了一个新的Excel工作簿。
2 Sheet（工作表）
Sheet是工作簿中的一个单独的工作表。工作簿可以包含一个或多个工作表。默认情况下，创建工作簿时会包含一个名为”Sheet”的工作表。
示例代码：添加Sheet
# 获取活动的Sheet
sheet = wb.active
# 创建一个名为"NewSheet"的Sheet
new_sheet = wb.create_sheet("NewSheet")
在这个例子中，通过active属性获取了活动的工作表，然后使用create_sheet方法创建了一个名为”NewSheet”的新工作表。
3 Cell（单元格）
Cell是工作表中的一个单元格，用于存储数据。单元格由列字母和行号唯一标识，例如”A1″表示第一列第一行的单元格。
示例代码：写入数据到单元格
# 向单元格写入数据 sheet['A1'] = 'Hello' sheet['B1'] = 'World'
在这个例子中，使用单元格坐标（’A1’和’B1’）向工作表中的特定单元格写入了数据。
读取数据
在openpyxl中，可以使用不同的方法来从已有的Excel文件中读取数据，包括文本、数字和日期等不同数据类型。
1 打开Excel文件
首先，需要使用load_workbook函数打开已有的Excel文件。
from openpyxl import load_workbook
# 打开Excel文http://www.devze.com件
wb = load_workbook('example.xlsx')
# 获取活动的Sheet
sheet = wb.active
2 读取文本数据
对于包含文本数据的单元格，可以直接通过value属性读取。
# 读取文本数据
text_data = sheet['A1'].value
print("文本数据:", text_data)
3 读取数字数据
对于包含数字数据的单元格，同样可以通过value属性读取。
# 读取数字数据
number_data =js sheet['B1'].value
print("数字数据:", number_data)
4 读取日期数据
如果单元格包含日期数据，可以通过value属性读取日期对象。
# 读取日期数据
date_data = sheet['C1'].value
print("日期数据:", date_data)
在读取日期数据时，openpyxl会将其转换为Python的datetime对象。
写入数据
在openpyxl中，可以使用不同的方法向Excel文件中写入不同类型的数据，同时还可以进行一些样式设置和合并单元格等操作。
1 写入文本和数字数据
对于文本和数字数据，可以直接在单元格中使用等号赋值。
# 写入文本和数字数据 sheet['A1'] = 'Hello' sheet['B1'] = 123
2 写入日期数据
对于日期数据，可以使用Python的datetime对象。
from datetime import datetime # 写入日期数据 sheet['C1'] = datetime(2023, 1, 1)
3 样式设置
openpyxl可以对单元格进行样式设置，包括字体、颜色和边框等。
from openpyxl.styles import Font, PatternFill, Border
# 设置字体样式
sheet['A1'].font = Font(bold=True, color='FF0000')
# 设置背景颜色
sheet['A1'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
# 设置边框
sheet['A1'].border = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'))
4 合并单元格
可以使用merge_cells方法合并单元格。
# 合并单元格
sheet.merge_cells('A1:B2')
5 保存文件
最后，需要保存Workbook，以便在Excel中查看结果。
# 保存文件
wb.save('write_example.xlsx')
图表和图片
在openpyxl中，可以使用图表和图片来更生动地展示数据和信息。以下是如何处理图表和图片的示例。
1 添加图表
首先，创建一个简单的柱状图，并将其添加到Excel文件中。
from openpyxl.chart import BarChart, Reference # 创建柱状图对象 chart = BarChart() # 设置图表数据范围 data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=2) chart.add_data(data) # 将图表添加到Sheet sheet.add_chart(chart, "D4")
2 插入图片
接下来，插入一张图片到Excel文件中。
from openpyxl.drawing.image import Image
# 插入图片
img = Image('path/to/image.jpg')
sheet.add_image(img, 'E4')
确保替换&http://www.devze.comnbsp;'path/to/image.jpg' 为实际图片的路径，并将图片添加到Excel文件的指定位置。
3 设置图表和图片属性
还可以设置图表和图片的各种属性，如位置、大小、标题等。
# 设置图表位置和大小 chart.x = 50 chart.y = 100 chart.width = 300 chart.height = 200 # 设置图片位置和大小 img.width = 100 img.height = 100 img.anchor = 'F4'
4 保存文件
最后，需要保存Workbook，以便在Excel中查看结果。
# 保存文件
wb.save('chart_and_image_example.xlsx')
数据筛选和排序
在openpyxl中，可以使用筛选和排序功能对Excel表格中的数据进行进一步的处理。以下是如何在openpyxl中实现数据筛选和排序的示例。
1 数据筛选
使用AutoFilter来实现数据筛选。下面是一个示例，演示如何按条件筛选数据。
from openpyxl import Workbook
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入示例数据
sheet['A1'] = 'Name'
sheet['B1'] = 'Score'
data = [
('Alice', 85),
('Bob', 92),
('Charlie', 78),
('David', 95),
('Eva', 88)
]
for row in data:
sheet.append(row)
# 启用AutoFilter
sheet.auto_filter.ref = sheet.dimensions
# 按条件筛选数据
sheet.auto_filter.add_filter_column(1, ["Alice", "David"])
# 保存文件
wb.save('filter_example.xlsx')
在上述代码中，使用auto_filter属性启用了AutoFilter，并通过add_filter_column方法按条件筛选了数据。
2 数据排序
openpyxl也提供了对数据进行排序的功能。以下是一个示例，演示如何按指定列对数据进行升序排序。
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入示例数据
sheet['A1'] = 'Name'
sheet['B1'] = 'Score'
data = [
('Alice', 85),
('Bob', 92),
('Charlie', 78),
('David', 95),
('Eva', 88)
]
for row in data:
sheet.append(row)
# 将数据转换为DataFrame
df = pd.DataFrame(data, columns=['Name', 'Score'])
# 按Score列升序排序
df.sort_values(by='Score', inplace=True)
# 将排序后的数据写回Excel
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), 2):
for c_idx, value in enumerate(row, 1):
sheet.cell(row=r_idx, column=c_idx, value=value)
# 保存文件
wb.save('sort_example.xlsx')
在上述代码中，使用pandas库将数据转换为DataFrame，然后使用sort_values方法按指定列（这里是’Score’列）进行升序排序，最后将排序后的数据写回Excel。
公式计算
在openpyxl中，可以使用公式对Excel文件中的数据进行计算。以下是如何使用公式的示例，以及一些注意事项。
1 基本公式
首先，可以在单元格中使用基本的Excel函数进行计算。
from openpyxl import Workbook
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入示例数据
sheet['A1'] = 10
sheet['A2'] = 20
# 使用SUM函数计算A1和A2的总和
sheet['A3'] = '=SUM(A1:A2)'
# 保存文件
wb.save('formula_example.xlsx')
在上述代码中，使用了Excel的SUM函数计算了A1和A2的总和。
2 复杂公式
还可以编写更复杂的公式，涉及多个单元格和函数。
from openpyxl import Workbook
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入示例数据
sheet['B1'] = 5
sheet['B2'] = 10
sheet['B3'] = 15
# 使用AVERAGE函数计算B1到B3的平均值
sheet['B4'] = '=AVERAGE(B1:B3)'
# 保存文件
wb.save('complex_formula_example.xlsx')
在上述代码中，使用了Excel的AVERAGE函数计算了B1到B3的平均值。
3 计算日期差
如果数据涉及日期，可以使用日期函数计算日期之间的差值。
from openpyxl import Workbook
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入日期数据
sheet['C1'] = '2023-01-01'
sheet['C2'] = '2023-01-10'
# 使用DATEDIF函数计算C1和C2之间的天数差
sheet['C3'] = '=DATEDIF(C1, C2, "D")'
# 保存文件
wb.save('date_formula_example.xlsx')
在上述代码中，使用了Excel的DATEDIF函数计算了C1和C2之间的天数差。
4 注意事项
在写入公式时，确保在公式字符串前面加上等号（=）。
请注意使用Excel支持的函数和语法。
样式设置
在openpyxl中，可以通过设置样式来美化Excel文件，包括设置字体、颜色、边框等方面。以下是如何使用openpyxl进行样式设置的示例。
1 设置字体样式
设置单元格的字体样式，如粗体、斜体、字体颜色等。
from openpyxl import Workbook
from openpyxl.styles import Font
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入数据
sheet['A1'] = 'Hello, World!'
# 设置字体样式
font_style = Font(size=14, bold=True, italic=True, color='FF0000')
sheet['A1'].font = font_style
# 保存文件
wb.save('font_style_example.xlsx')
在上述代码中，创建了一个字体样式对象font_style，并将其应用到单元格’A1’。
2 设置背景颜色
还可以设置单元格的背景颜色。
from openpyxl import Workbook
from openpyxl.styles import PatternFill
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入数据
sheet['A1'] = 'Colored Cell'
# 设置背景颜色
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
sheet['A1'].fill = fill
# 保存文件
wb.save('background_color_example.xlsx')
在上述代码中，创建了一个填充样式对象fill，并将其应用到单元格’A1’。
3 设置边框
设置单元格的边框样式，包括左、右、上、下边框。
from openpyxl import Workbook
from openpyxl.styles import Border, Side
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入数据
sheet['A1'] = 'Bordered Cell'
# 设置边框
border_style = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'))
sheet['A1'].border = border_style
# 保存文件
wb.save('border_style_example.xlsx')
在上述代码中，创建了一个边框样式对象border_style，并将其应用到单元格’A1’。
实际应用场景
openpyxl在实际项目中有着广泛的应用，特别是在需要处理Excel文件的数据报告、数据导出等场景。以下是一些实际应用示例，展示openpyxl在真实项目中的用途。
1 数据报告生成
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入数据报告标python题
sheet['A1'] = '月度销售报告'
sheet['A1'].font = Font(size=18, bold=True)
sheet['A1'].alignment = Alignment(horizontal='center')
# 写入销售数据
sales_data = [
('产品', '销售额', '利润'),
('A产品', 5000, 2000),
('B产品', 8000, 3500),
('C产品', 6000, 2800),
]
for row_idx, row_data in enumerate(sales_data, start=3):
for col_idx, value in enumerate(row_data, start=1):
sheet.cell(row=row_idx, column=col_idx, value=value)
# 保存文件
wb.save('sales_report.xlsx')
在这个示例中，使用openpyxl创建了一个销售报告，包括标题、销售数据表格等。通过设置字体、居中对齐等样式，使得报告看起来更加专业。
2 数据导出
from openpyxl import Workbook
# 创建一个Workbook对象
wb = Workbook()
# 获取活动的Sheet
sheet = wb.active
# 写入数据
data_to_export = [
['Name', 'Age', 'City'],
['Alice', 25, 'New York'],
['Bob', 30, 'London'],
['Charlie', 22, 'Tokyo'],
]
for row_idx, row_data in enumerate(data_to_export, start=1):
for col_idx, value in enumerate(row_data, start=1):
sheet.cell(row=row_idx, column=col_idx, value=value)
# 保存文件
wb.save('exported_data.xlsx')
在这个示例中，使用openpyxl创建了一个简单的数据表格，并将其导出为Excel文件。这在数据导出和交换方面非常常见。
3 批量处理数据
from openpyxl import load_workbook
from openpyxl.styles import Font
# 加载已有的Excel文件
wb = load_workbook('existing_data.xlsx')
# 获取活动的Sheet
sheet = wb.active
# 在现有数据上追加新数据
new_data = [
['Eva', 28, 'Berlin'],
['David', 35, 'Paris'],
]
for row_data in new_data:
sheet.append(row_data)
# 设置标题行的字体样式
for cell in sheet['1']:
cell.font = Font(bold=True)
# 保存文件
wb.sajavascriptve('updated_data.xlsx')
在这个示例中，加载了一个已有的Excel文件，追加了新的数据，并设置了标题行的字体样式。这在批量处理数据的场景中非常实用。
总结
在总结中，深入探索了openpyxl这一强大的Python库，用于处理Excel文件。首先了解了其基本概念，包括Workbook、Sheet和Cell，然后学习了如何读取和写入不同类型的数据。通过示例代码，演示了如何处理图表、图片，以及使用公式进行计算。在样式设置方面，详细介绍了如何设置字体、颜色、边框等，以美化Excel文件中的数据。通过实际应用示例，展示了openpyxl在数据报告生成、数据导出、批量处理数据等方面的广泛应用。
尤其值得强调的是，openpyxl的灵活性和功能丰富性使其成为处理Excel数据的理想选择，适用于没有安装Microsoft Excel的环境中。
以上就是Python openpyxl库处理Excel文件高级应用技巧实例的详细内容，更多关于Python openpyxl处理Excel的资料请关注编程客栈(www.devze.com)其它相关文章！
加载中,请稍侯......
精彩评论