欢迎来到我的个人博客,有Python技术,自媒体,创业,APP开发问题随时讨论交流

Python openpyxl 秘籍:掌握Excel的高级应用技巧

Python sitin 3个月前 (12-11) 92次浏览 已收录 0个评论
Python openpyxl 秘籍:掌握Excel的高级应用技巧

大家好,我是涛哥,今天为大家分享 Python openpyxl 秘籍,文章8000字,阅读大约25分钟,大家enjoy~~

openpyxl是一个用于处理Excel文件的Python库。它提供了丰富的功能,使得用户能够轻松地读取、写入和操作Excel文件,而不需要依赖于Microsoft Excel软件。作为一个开源项目,openpyxl在Python生态系统中得到了广泛的应用,成为处理Excel数据的首选工具之一。

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文件
wb = load_workbook('example.xlsx')

# 获取活动的Sheet
sheet = wb.active

2 读取文本数据

对于包含文本数据的单元格,可以直接通过value属性读取。

# 读取文本数据
text_data = sheet['A1'].value
print("文本数据:", text_data)

3 读取数字数据

对于包含数字数据的单元格,同样可以通过value属性读取。

# 读取数字数据
number_data = 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(202311)

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')

确保替换 '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

# 写入数据报告标题
sheet['A1'] = '月度销售报告'
sheet['A1'].font = Font(size=18, bold=True)
sheet['A1'].alignment = Alignment(horizontal='center')

# 写入销售数据
sales_data = [
    ('产品''销售额''利润'),
    ('A产品'50002000),
    ('B产品'80003500),
    ('C产品'60002800),
]

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.save('updated_data.xlsx')

在这个示例中,加载了一个已有的Excel文件,追加了新的数据,并设置了标题行的字体样式。这在批量处理数据的场景中非常实用。

总结

在总结中,深入探索了openpyxl这一强大的Python库,用于处理Excel文件。首先了解了其基本概念,包括Workbook、Sheet和Cell,然后学习了如何读取和写入不同类型的数据。通过示例代码,演示了如何处理图表、图片,以及使用公式进行计算。在样式设置方面,详细介绍了如何设置字体、颜色、边框等,以美化Excel文件中的数据。通过实际应用示例,展示了openpyxl在数据报告生成、数据导出、批量处理数据等方面的广泛应用。

尤其值得强调的是,openpyxl的灵活性和功能丰富性使其成为处理Excel数据的理想选择,适用于没有安装Microsoft Excel的环境中。

喜欢 (1)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址