Python|玩转 Excel:Pandas、openpyxl、pywin32

2024-09-07 03:04

本文主要是介绍Python|玩转 Excel:Pandas、openpyxl、pywin32,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

    • 引言
    • Pandas
      • 读取 Excel
      • 写入 Excel
      • 数据操作
      • 样式设置
      • 数据验证
      • 公式支持
    • openpyxl
      • 读取 Excel
      • 写入 Excel
      • 数据操作
      • 样式设置
      • 数据验证
      • 公式支持
      • 图表创建
    • xlrd / xlwt
      • 读取 Excel(xlrd)
      • 写入 Excel(xlwt)
    • pyxlsb
      • 读取 Excel(pyxlsb)
    • xlsxwriter
      • 写入 Excel
      • 样式设置
      • 公式支持
      • 图表创建
    • pywin32 (Windows Only)
      • 读取 Excel
      • 写入 Excel
      • 数据操作
      • 样式设置
      • 数据验证
      • 公式支持
      • 图表创建
    • 横向对比

引言

Excel 是在数据处理和分析中一种最常用的工具,而 Python 是一种强大的编程语言。结合 Python 的数据处理库,可以方便地操作 Excel 文件,进行数据读取、处理、分析和报告生成等工作。本文将介绍如何使用 Python 操作 Excel 文件,包括读取、写入、数据处理、样式设置、数据验证、公式支持和图表创建等方面的内容。

在这里插入图片描述

在 Python 中,有多种库和方法可以操作 Excel 文件。下面我们捡一些常用的库及其基本操作进行介绍。

Pandas

Pandas 是一个强大的数据分析和处理库,非常适合处理表格数据。它可以读取 Excel 文件:pd.read_excel();写入 Excel 文件:DataFrame.to_excel();处理 DataFrame,对数据进行筛选、聚合等操作。

安装 Pandas

pip install pandas

导入 Pandas

import pandas as pd

读取 Excel

Pandas 可以读取 .xls.xlsx 文件。可以指定读取特定的 sheet。

import pandas as pd# 读取整个 Excel 文件
df = pd.read_excel('file.xlsx')# 读取特定的 sheet
df_sheet1 = pd.read_excel('file.xlsx', sheet_name='Sheet1')# 读取多个 sheet
dfs = pd.read_excel('file.xlsx', sheet_name=['Sheet1', 'Sheet2'])

写入 Excel

Pandas 可以将 DataFrame 保存为 Excel 文件。可以选择是否写入索引。

# 创建一个简单的 DataFrame
data = {'Name': ['Alice', 'Bob', 'Charlie'],'Age': [24, 30, 22]
}
df = pd.DataFrame(data)# 写入 Excel 文件
df.to_excel('output.xlsx', index=False)  # 不写入索引

数据操作

Pandas 可以对 DataFrame 进行数据筛选、分组、合并和聚合等。

# 假设 df 包含以下数据
#    Name   Age   Salary
# 0  Alice   24   50000
# 1    Bob   30   60000
# 2 Charlie   22   45000
# 3    Eve   35   70000# 数据筛选:筛选出年龄大于 25 的数据
filtered_df = df[df['Age'] > 25]# 数据分组和聚合:计算每个年龄的平均工资
grouped_df = df.groupby('Age').mean()# 合并多个 DataFrame:合并 df1 和 df2
df1 = pd.DataFrame({'Name': ['Alice', 'Bob'], 'Age': [24, 30]})
df2 = pd.DataFrame({'Name': ['Charlie', 'David'], 'Age': [22, 28]})
result = pd.concat([df1, df2])

样式设置

在 Pandas 中通过 Styler 类进行样式设置,可以在写入文件时使用。

# 使用样式设置
styled_df = df.style.highlight_max(axis=0)  # 高亮最大值
styled_df.to_excel('styled_output.xlsx', engine='openpyxl')  # 需要指定引擎

数据验证

Pandas 本身不直接提供数据验证的功能,但可以使用 openpyxlxlsxwriter 进行进一步的样式和数据验证。

import pandas as pd# 假设已有 DataFrame df
data = {'Names': ['Alice', 'Bob', 'Charlie'],'Ages': [24, 30, 'thirty']
}
df = pd.DataFrame(data)# 数据验证(过滤掉不合格的年龄)
df['Ages'] = pd.to_numeric(df['Ages'], errors='coerce')  # 转为数值类型,不合格的设置为 NaN
valid_ages = df[df['Ages'].notnull()]

公式支持

Pandas 自身不支持添加公式,但可以通过 openpyxl 来实现。

import pandas as pd
from openpyxl import load_workbook# 创建 DataFrame
data = {'Numbers': [1, 2, 3, 4],'Doubles': [2, 4, 6, 8]
}
df = pd.DataFrame(data)# 写入 Excel
df.to_excel('output_with_formula.xlsx', index=False)# 使用 openpyxl 添加公式
wb = load_workbook('output_with_formula.xlsx')
ws = wb.active
ws['C1'] = 'Sum'
ws['C2'] = '=SUM(A2:A5)'  # 在 C2 中写入公式
wb.save('output_with_formula.xlsx')

Pandas 提供了强大的数据处理和分析功能,配合其他库如 openpyxlxslxwriter,可以实现复杂的 Excel 文件操作,包括读取、写入、数据操作、样式设置、数据验证、公式支持和图表创建等。

对于非常大的 Excel 文件,使用 pandas 进行批量操作通常更高效。使用 pandas 读取 Excel 时,注意数据类型的自动推断,可能需要使用 dtype 参数进行控制。Pandas 提供了丰富的工具来处理缺失值(如 isnull()fillna()等)。

openpyxl

openpyxl 是一个用于处理 Excel 2007 及更高版本的 .xlsx 文件的强大库。openpyxl 可用于 Excel 文件的读取、写入、数据操作、样式设置、数据验证、公式支持和图表创建等方面。

安装 openpyxl

pip install openpyxl

读取 Excel

openpyxl 可以读取整个 Excel 文件的内容,获取指定工作表的值。

from openpyxl import load_workbook# 读取 Excel 文件
workbook = load_workbook('file.xlsx')# 获取默认工作表
sheet = workbook.active# 获取单元格值
value = sheet['A1'].value
print(value)# 获取所有行的值
for row in sheet.iter_rows(values_only=True):print(row)

写入 Excel

openpyxl 可以创建新的工作簿、工作表,并写入单元格值。

from openpyxl import Workbook# 创建新的工作簿
workbook = Workbook()
sheet = workbook.active# 写入数据到单元格
sheet['A1'] = 'Name'
sheet['B1'] = 'Age'
sheet['A2'] = 'Alice'
sheet['B2'] = 24# 保存工作簿
workbook.save('output.xlsx')

数据操作

openpyxl 支持读取、修改和删除单元格的值。

# 读取和修改数据
workbook = load_workbook('output.xlsx')
sheet = workbook.active# 修改单元格的值
sheet['B2'] = 25  # 修改 Alice 的年龄# 删除一行
sheet.delete_rows(2)  # 删除第二行# 添加新行
sheet.append(['Bob', 30])# 保存修改
workbook.save('output_modified.xlsx')

样式设置

openpyxl 可以设置单元格的样式,包括字体、颜色、边框、填充等。

from openpyxl.styles import Font, Color, PatternFill, Border, Sideworkbook = load_workbook('output.xlsx')
sheet = workbook.active# 设置字体
font = Font(name='Arial', size=12, bold=True, color='FF0000')
sheet['A1'].font = font# 设置填充颜色
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
sheet['B1'].fill = fill# 设置边框
border = Border(left=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'), bottom=Side(style='thin'))
sheet['A1'].border = border# 保存修改
workbook.save('styled_output.xlsx')

数据验证

openpyxl 可以设置数据有效性规则,例如下拉菜单。

from openpyxl.worksheet.datavalidation import DataValidationworkbook = load_workbook('output.xlsx')
sheet = workbook.active# 创建数据验证规则(下拉列表)
dv = DataValidation(type="list", formula1='"Option1,Option2,Option3"', showDropDown=True)
sheet.add_data_validation(dv)# 应用验证到单元格
dv.add(sheet['C1'])# 保存修改
workbook.save('validated_output.xlsx')

公式支持

openpyxl 可以在单元格中添加公式。

workbook = load_workbook('output.xlsx')
sheet = workbook.active# 设置公式
sheet['D1'] = 'Total'
sheet['D2'] = '=B2*2'  # 假设 B2 存储了某个值# 保存修改
workbook.save('formula_output.xlsx')

图表创建

openpyxl 可以创建各种类型的图表。

from openpyxl.chart import BarChart, Referenceworkbook = load_workbook('output.xlsx')
sheet = workbook.active# 假设数据在 A1 到 B4 的区域
data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=4)
categories = Reference(sheet, min_col=1, min_row=2, max_row=4)bar_chart = BarChart()
bar_chart.add_data(data, titles_from_data=True)
bar_chart.set_categories(categories)
bar_chart.title = "Sample Bar Chart"
sheet.add_chart(bar_chart, "E5")  # 放置图表的位置# 保存修改
workbook.save('chart_output.xlsx')

openpyxl 提供丰富的功能,可以高效地读取、写入和操作 Excel 文件。通过灵活地设置单元格样式、数据验证以及公式支持,用户可以创建功能强大的 Excel 报告和分析。同时,图表的创建功能使得数据可视化变得更简单直观。

xlrd / xlwt

xlrd 用于读取 .xls 文件,但不支持写入操作;xlwt 用于写入 .xls 文件,还可以创建新工作簿和工作表。这两个库主要适用于旧版 Excel 文件。从xlrd 2.0 版本开始,仅支持读取 xls 格式文件,不再支持 xlsx

安装 xlrd / xlwt

pip install xlrd xlwt

读取 Excel(xlrd)

import xlrd# 打开 Excel 文件
workbook = xlrd.open_workbook('file.xls')# 选择工作表
sheet = workbook.sheet_by_index(0)  # 选择第一个工作表# 获取单元格值
value = sheet.cell_value(0, 0)  # 获取 A1 单元格的值
print(value)# 获取所有行的值
for row_idx in range(sheet.nrows):print(sheet.row_values(row_idx))

xlwt 用于写入 .xls 文件。它用于。

写入 Excel(xlwt)

import xlwt# 创建一个工作簿
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('Sheet1')# 写入数据
sheet.write(0, 0, 'Name')  # A1
sheet.write(0, 1, 'Age')   # B1
sheet.write(1, 0, 'Alice')  # A2
sheet.write(1, 1, 24)      # B2# 保存工作簿
workbook.save('output.xls')

pyxlsb

pyxlsb 是一个用于读取 Excel 二进制格式 .xlsb 文件的库。

安装 pyxlsb

pip install pyxlsb

读取 Excel(pyxlsb)

from pyxlsb import open_workbook# 打开 Excel 文件
with open_workbook('file.xlsb') as wb:with wb.get_sheet(1) as sheet:  # 获取第一个工作表for row in sheet.rows():print([item.v for item in row])  # 打印行

xlsxwriter

xlsxwriter 是一个用于创建复杂 .xlsx 文件的库,支持写入和格式化操作。

安装 xlsxwriter

pip install xlsxwriter

写入 Excel

import xlsxwriter# 创建一个工作簿
workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()# 写入数据
worksheet.write('A1', 'Name')
worksheet.write('B1', 'Age')
worksheet.write('A2', 'Alice')
worksheet.write('B2', 24)# 保存工作簿
workbook.close()

样式设置

# 设置单元格格式
bold = workbook.add_format({'bold': True})
worksheet.write('A1', 'Name', bold)  # 使用格式写入# 设置背景颜色
cell_format = workbook.add_format({'bg_color': '#FFCC00'})
worksheet.write('A2', 'Alice', cell_format)  # 设置背景颜色

公式支持

# 写入公式
worksheet.write('C1', 'Total')
worksheet.write_formula('C2', '=B2*2')  # 在 C2 中写入公式

图表创建

# 创建图表
chart = workbook.add_chart({'type': 'column'})# 配置数据系列
chart.add_series({'name': 'Age Data','categories': '=Sheet1!$A$2:$A$3','values': '=Sheet1!$B$2:$B$3',
})worksheet.insert_chart('E2', chart)  # 插入图表# 保存工作簿
workbook.close()

pywin32 (Windows Only)

pywin32 是一个 Python 库,允许 Python 程序与 Windows COM(组件对象模型)进行交互,因此可以直接操作安装在 Windows 系统上的 Microsoft Excel。以下是使用 pywin32 操作 Excel 文件的详细介绍,涵盖读取、写入、数据操作、样式设置、数据验证、公式支持和图表创建等方面。

安装 pywin32

pip install pywin32

读取 Excel

pywin32 可以打开现有的 Excel 文件,读取工作表及单元格内容。

import win32com.client# 启动 Excel 应用程序
excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = False  # 不显示 Excel 窗口# 打开 Excel 文件
workbook = excel.Workbooks.Open(r'path\to\your\file.xlsx')# 选择工作表
sheet = workbook.Sheets('Sheet1')# 获取单元格值
value = sheet.Cells(1, 1).Value  # A1
print(value)# 遍历整行数据
for row in range(1, 4):print([sheet.Cells(row, col).Value for col in range(1, 4)])# 关闭工作簿
workbook.Close(SaveChanges=False)
excel.Quit()

写入 Excel

pywin32 可以创建新的 Excel 文件或在现有文件中写入数据。

# 启动 Excel 应用程序
excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = True  # 可视化 Excel 窗口# 创建新工作簿
workbook = excel.Workbooks.Add()
sheet = workbook.ActiveSheet# 写入数据
sheet.Cells(1, 1).Value = 'Name'
sheet.Cells(1, 2).Value = 'Age'
sheet.Cells(2, 1).Value = 'Alice'
sheet.Cells(2, 2).Value = 24# 保存工作簿
workbook.SaveAs(r'path\to\your\new_file.xlsx')
workbook.Close()
excel.Quit()

数据操作

pywin32 可以修改单元格的值,删除行或列,插入新行等。

# 打开现有工作簿
workbook = excel.Workbooks.Open(r'path\to\your\file.xlsx')
sheet = workbook.Sheets('Sheet1')# 修改单元格的值
sheet.Cells(2, 2).Value = 25  # 修改 B2# 插入新的行
sheet.Rows(3).Insert()  # 在第三行插入新行
sheet.Cells(3, 1).Value = 'Bob'
sheet.Cells(3, 2).Value = 30# 删除一行
sheet.Rows(4).Delete()  # 删除第四行# 保存修改
workbook.Save()
workbook.Close()
excel.Quit()

样式设置

pywin32 可以设置单元格的字体、颜色、边框等样式。

# 打开现有工作簿
workbook = excel.Workbooks.Open(r'path\to\your\file.xlsx')
sheet = workbook.Sheets('Sheet1')# 设置字体样式
font = sheet.Cells(1, 1).Font
font.Bold = True
font.Color = 0xFF0000  # 红色# 设置单元格背景颜色
sheet.Cells(1, 1).Interior.Color = 0xFFFF00  # 黄色背景# 保存修改
workbook.Save()
workbook.Close()
excel.Quit()

数据验证

pywin32 可以在单元格中设置数据有效性规则(例如下拉列表)。

# 打开现有工作簿
workbook = excel.Workbooks.Open(r'path\to\your\file.xlsx')
sheet = workbook.Sheets('Sheet1')# 创建下拉列表
validation = sheet.Range("C1").Validation
validation.Delete()  # 删除之前的验证
validation.Add(Type=1,  # 1 = ListAlertStyle=1,Operator=1,Formula1='"Option1,Option2,Option3"',InCellDropdown=True
)# 保存修改
workbook.Save()
workbook.Close()
excel.Quit()

公式支持

pywin32 可以在单元格中输入公式。

# 打开现有工作簿
workbook = excel.Workbooks.Open(r'path\to\your\file.xlsx')
sheet = workbook.Sheets('Sheet1')# 设置公式
sheet.Cells(2, 3).Formula = '=B2*2'  # 在 C2 中写入公式# 保存修改
workbook.Save()
workbook.Close()
excel.Quit()

图表创建

pywin32 可以创建各种类型的图表。

# 打开现有工作簿
workbook = excel.Workbooks.Open(r'path\to\your\file.xlsx')
sheet = workbook.Sheets('Sheet1')# 添加图表
chart = workbook.Charts.Add()
chart.ChartType = 51  # 51 = xlColumnClustered
chart.SetSourceData(sheet.Range("A1:B3"))  # 数据来源# 保存修改
workbook.Save()
workbook.Close()
excel.Quit()

pywin32 提供了与 Excel 的深度集成,能够进行全面的文件操作。通过 Windows COM 接口,可以充分利用 Excel 的所有功能,这使得它成为处理 Excel 文件的强大工具。但是需要注意,pywin32 只在 Windows 环境中有效,并且需要安装 Excel 软件。

横向对比

以下是各个 Python 模块在操作 Excel 文件时支持的各种操作类型的详细描述。

模块描述支持的操作
Pandas强大的数据分析库,适合处理表格数据。- 读取 Excel 文件(.xls.xlsx,可指定 sheet_name
- 写入 Excel 文件(可选择是否写入索引)
- 数据操作(筛选、分组、合并、清洗)
- 样式设置(通过 Styler 对象)
openpyxl主要用于处理 .xlsx 文件的库。- 读取单元格、行、列和整个表格
- 写入单元格、行、列,设置格式
- 数据验证(如下拉菜单)
- 公式支持
- 创建图表(饼图、柱形图等)
- 自定义单元格样式
xlrd / xlwt主要用于处理 .xls 格式的文件。- xlrd: 读取单元格、行、列,获取工作表元数据
- xlwt: 创建工作簿和工作表,写入单元格,设置样式,合并单元格
pyxlsb用于读取 .xlsb(Excel 二进制文件)格式。- 读取表格数据,支持逐行遍历和特定 sheet 的数据
xlsxwriter创建复杂的 .xlsx 文件的库。- 创建新的工作簿和工作表
- 写入各种数据类型
- 创建图表(饼图、柱形图、折线图等)
- 自定义单元格样式
- 设置工作簿保护
pywin32通过 Windows COM 接口与 Excel 交互。- 启动 Excel 应用程序
- 读取单元格和写入数据
- 操作 Excel 对象(如图表、表格)
- 设置公式

不同的库和方法适用于不同文件格式和需求。选择适合的库能够有效完成数据处理、分析和报告生成的需求。

确保使用正确的库对应正确的 Excel 文件格式(.xls.xlsx)。通过上面介绍的库和功能,Python 提供了一系列灵活和方便的方式来操作 Excel 文件,能够很好地满足数据处理与分析的需求。


PS:感谢每一位志同道合者的阅读,欢迎关注、点赞、评论!


  • 上一篇:Nginx 维护与应用:最佳实践
  • 专栏:「计算通践」

这篇关于Python|玩转 Excel:Pandas、openpyxl、pywin32的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1143871

相关文章

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景:内存吃得满满的类__slots__:你的内存管理小助手举个大概的例子:看看效果如何?1.

Python+PyQt5实现多屏幕协同播放功能

《Python+PyQt5实现多屏幕协同播放功能》在现代会议展示、数字广告、展览展示等场景中,多屏幕协同播放已成为刚需,下面我们就来看看如何利用Python和PyQt5开发一套功能强大的跨屏播控系统吧... 目录一、项目概述:突破传统播放限制二、核心技术解析2.1 多屏管理机制2.2 播放引擎设计2.3 专

Python中随机休眠技术原理与应用详解

《Python中随机休眠技术原理与应用详解》在编程中,让程序暂停执行特定时间是常见需求,当需要引入不确定性时,随机休眠就成为关键技巧,下面我们就来看看Python中随机休眠技术的具体实现与应用吧... 目录引言一、实现原理与基础方法1.1 核心函数解析1.2 基础实现模板1.3 整数版实现二、典型应用场景2

Python实现无痛修改第三方库源码的方法详解

《Python实现无痛修改第三方库源码的方法详解》很多时候,我们下载的第三方库是不会有需求不满足的情况,但也有极少的情况,第三方库没有兼顾到需求,本文将介绍几个修改源码的操作,大家可以根据需求进行选择... 目录需求不符合模拟示例 1. 修改源文件2. 继承修改3. 猴子补丁4. 追踪局部变量需求不符合很

java中使用POI生成Excel并导出过程

《java中使用POI生成Excel并导出过程》:本文主要介绍java中使用POI生成Excel并导出过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录需求说明及实现方式需求完成通用代码版本1版本2结果展示type参数为atype参数为b总结注:本文章中代码均为

python+opencv处理颜色之将目标颜色转换实例代码

《python+opencv处理颜色之将目标颜色转换实例代码》OpenCV是一个的跨平台计算机视觉库,可以运行在Linux、Windows和MacOS操作系统上,:本文主要介绍python+ope... 目录下面是代码+ 效果 + 解释转HSV: 关于颜色总是要转HSV的掩膜再标注总结 目标:将红色的部分滤

Python 中的异步与同步深度解析(实践记录)

《Python中的异步与同步深度解析(实践记录)》在Python编程世界里,异步和同步的概念是理解程序执行流程和性能优化的关键,这篇文章将带你深入了解它们的差异,以及阻塞和非阻塞的特性,同时通过实际... 目录python中的异步与同步:深度解析与实践异步与同步的定义异步同步阻塞与非阻塞的概念阻塞非阻塞同步

Python Dash框架在数据可视化仪表板中的应用与实践记录

《PythonDash框架在数据可视化仪表板中的应用与实践记录》Python的PlotlyDash库提供了一种简便且强大的方式来构建和展示互动式数据仪表板,本篇文章将深入探讨如何使用Dash设计一... 目录python Dash框架在数据可视化仪表板中的应用与实践1. 什么是Plotly Dash?1.1

在C#中调用Python代码的两种实现方式

《在C#中调用Python代码的两种实现方式》:本文主要介绍在C#中调用Python代码的两种实现方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录C#调用python代码的方式1. 使用 Python.NET2. 使用外部进程调用 Python 脚本总结C#调

Python下载Pandas包的步骤

《Python下载Pandas包的步骤》:本文主要介绍Python下载Pandas包的步骤,在python中安装pandas库,我采取的方法是用PIP的方法在Python目标位置进行安装,本文给大... 目录安装步骤1、首先找到我们安装python的目录2、使用命令行到Python安装目录下3、我们回到Py