简介：本文介绍如何使用Python处理Excel文件，重点讨论 pandas 和 openpyxl 库的使用。通过 pandas 的 read_excel() 和 to_excel() 函数可以轻松实现Excel文件的读取和写入，适用于数据处理和自动化报告等场景。 openpyxl 则提供了处理带有公式、样式或图表等复杂Excel文件的功能。文章通过实例代码演示了如何使用这些库进行数据筛选、写入数据以及遍历单元格等操作，并强调了掌握这些库对于提升数据处理能力的重要性。 
1. Python读写Excel基础
在数字信息处理的时代，数据存储与分析是IT行业中的核心任务之一。而Microsoft Excel作为最流行的电子表格程序，几乎成为了每一名IT专业人士必备的技能。Excel文件（.xls或.xlsx格式）存储着大量的数据，对这些数据的读取、处理和写入在数据分析、报告生成、以及自动化任务等领域至关重要。
Python作为一种高级编程语言，因其简洁明了的语法和强大的第三方库支持，在处理Excel文件方面表现得尤为出色。Python的第三方库如 xlrd 、 xlwt 、 openpyxl 和 pandas 提供了丰富的接口来读取和写入Excel文件。这些库不仅能够简化数据处理流程，还能轻松地与数据分析、机器学习等高级任务进行整合。
在本章中，我们将从Python读写Excel的基础开始。首先，我们将介绍如何使用标准库来读写Excel文件，这将帮助读者了解基本的操作流程。随后，在第二章至第四章中，我们将深入探讨使用 pandas 和 openpyxl 库进行更高级的Excel数据处理。最终，在第五章中，我们将通过实例代码演示，将所学知识应用于实际操作，展示如何处理真实世界的数据。这一切，都将成为您走向高效数据处理的第一步。
2. 使用pandas库读取Excel文件
在数据分析和数据处理中，Excel文件因其直观性和易用性仍然是一个非常重要的数据存储和传输格式。Pandas库为Python用户提供了强大而高效的数据结构和数据分析工具，尤其在处理表格数据方面表现出色。在这一章节中，我们将探讨如何使用pandas库读取Excel文件，并分享一些高级技巧来进一步提升处理效率。
2.1 pandas库的基本使用
2.1.1 pandas库的安装和导入
在开始之前，确保你的Python环境中已经安装了pandas库。如果没有安装，可以通过pip进行安装：
pip install pandas
安装完成后，可以在你的Python脚本或Jupyter笔记本中导入pandas库：
import pandas as pd
2.1.2 pandas库读取Excel文件的基本方法
使用pandas读取Excel文件非常简单，主要通过 pandas.read_excel 函数实现。这个函数可以读取Excel文件并将其转换为DataFrame对象，这是pandas库中用于存储和操作表格数据的核心数据结构。
# 假设有一个名为 'example.xlsx' 的Excel文件
df = pd.read_excel('example.xlsx')
上述代码读取了名为'example.xlsx'的Excel文件，并将其内容加载到一个DataFrame对象 df 中。pandas会自动将Excel中的表头读取为列名。
2.2 pandas库的高级读取技巧
2.2.1 读取指定的工作表
当一个Excel文件包含多个工作表时，你可能只对其中一个工作表感兴趣。pandas允许你通过 sheet_name 参数指定要读取的工作表名称或索引：
# 读取名为 'Sheet1' 的工作表
df_sheet1 = pd.read_excel('example.xlsx', sheet_name='Sheet1')
# 读取索引为0的工作表（第一个工作表）
df_first_sheet = pd.read_excel('example.xlsx', sheet_name=0)
2.2.2 读取指定的单元格范围
在某些情况下，你可能不需要读取整个工作表的所有数据。你可以通过 usecols 参数指定需要读取的列，或使用 skiprows 和 nrows 参数来跳过不需要的行或读取特定数量的行：
# 读取前10行数据
df_skip_rows = pd.read_excel('example.xlsx', skiprows=10)
# 读取20行数据，从第11行开始
df_nrows = pd.read_excel('example.xlsx', skiprows=10, nrows=20)
2.2.3 读取Excel文件的数据类型处理
Excel文件中可能包含数字、日期、文本等不同格式的数据。pandas的 read_excel 函数提供了 dtype 参数，允许你指定列的数据类型：
# 强制将'A'列数据转换为字符串类型
df = pd.read_excel('example.xlsx', dtype={'A': str})
此外，pandas会自动推断数据类型，但有时可能需要手动调整，以确保数据的正确解析。
表格、mermaid格式流程图、代码块等元素将在后续章节中展示，以丰富内容并加深理解。在进入下一章节前，让我们回顾本章节所涉及的关键概念和操作技巧。通过掌握pandas库的基本使用和高级读取技巧，你可以更加灵活高效地处理Excel文件数据。在接下来的章节中，我们将进一步深入学习如何使用pandas进行数据筛选与处理，探索更复杂的数据操作技术。
3. 使用pandas库筛选与处理数据
3.1 pandas库的数据筛选
在数据分析过程中，筛选数据是基本且必要的步骤之一。pandas库提供了多种数据筛选方法，允许我们根据条件、位置、索引等快速提取特定数据。
3.1.1 基于条件的筛选
条件筛选是根据数据的值来筛选数据，这通常涉及布尔索引。布尔索引是一种高效的数据筛选方法，可以通过对DataFrame应用一个条件来实现。
import pandas as pd
# 创建一个示例DataFrame
df = pd.DataFrame({
'A': [1, 2, 3, 4, 5],
'B': [10, 20, 30, 40, 50],
'C': ['foo', 'bar', 'baz', 'qux', 'quux']
})
# 使用条件筛选出列A中值大于2的行
filtered_df = df[df['A'] > 2]
在上述代码中， df['A'] > 2 创建了一个布尔序列，其中A列的每个值都与条件进行了比较，结果是一个布尔序列，然后这个布尔序列被用作DataFrame的索引器来筛选数据。
3.1.2 基于位置的筛选
基于位置的筛选通常是指根据行索引或列名的位置来选取数据。pandas中使用 .iloc[] 和 .loc[] 索引器来进行基于位置和基于标签的筛选。
# 使用.iloc[]根据行位置筛选前三行数据
top_three_rows = df.iloc[:3]
# 使用.loc[]根据标签选择列'A'和'C'
specific_columns = df.loc[:, ['A', 'C']]
在上述代码中， .iloc[] 索引器接受行号和列号，而 .loc[] 索引器接受行标签和列标签。
3.1.3 基于索引的筛选
基于索引的筛选是指使用索引标签来选择数据，这在数据集中行或列具有有意义的索引时非常有用。
# 设置DataFrame的索引为列C的值
df.set_index('C', inplace=True)
# 使用.loc[]根据索引标签选择数据
selected_data = df.loc['foo']
通过 .set_index() 函数，我们可以改变DataFrame的索引，之后使用 .loc[] 索引器通过索引标签进行数据选择。
3.2 pandas库的数据处理
数据处理包括数据清洗、数据转换、数据聚合和分组等多个方面。pandas库提供了强大的工具集来进行这些操作，以准备数据以供进一步分析或报告。
3.2.1 数据清洗
数据清洗是数据预处理中不可或缺的环节，它涉及处理缺失值、异常值、重复数据等问题。
# 处理缺失值，可以使用dropna()方法删除包含缺失值的行
cleaned_df = df.dropna()
# 填充缺失值，例如使用列的平均值填充
df_filled = df.fillna(df.mean())
在上述代码中， dropna() 和 fillna() 是pandas中处理缺失数据的两个重要方法。 dropna() 用于删除包含缺失值的行或列，而 fillna() 用于填充缺失值。
3.2.2 数据转换
数据转换是将数据从一个格式转换为另一个格式的过程。这可能包括类型转换、排序、重命名列等。
# 将列B的数据类型转换为float
df['B'] = df['B'].astype(float)
# 对DataFrame按照列A的值进行排序
sorted_df = df.sort_values(by='A')
# 重命名列，例如将列'A'重命名为'Alpha'
df_renamed = df.rename(columns={'A': 'Alpha'})
数据类型转换是通过调用 .astype() 方法实现的，而排序是通过 .sort_values() 方法实现的。使用 .rename() 方法可以改变DataFrame列的名字。
3.2.3 数据聚合和分组
聚合是指将数据集组合在一起并进行计算的过程。分组是指将数据集分解为多个组，并对每个组分别进行计算。
# 使用groupby()对列'A'的值进行分组，然后计算每组的平均值
grouped_df = df.groupby('A').mean()
# 使用agg()方法进行聚合操作，计算不同列的不同聚合统计值
aggregated_data = df.agg({
'A': 'sum',
'B': 'mean'
})
在上述代码中， groupby() 方法用于分组，而 agg() 方法用于执行聚合操作，可以同时对多个列执行不同的聚合函数。
在处理数据时，我们经常会遇到需要更复杂的数据处理逻辑，例如结合多种筛选条件、数据清洗策略，或者执行特定的数据转换和聚合操作。通过pandas库，我们可以灵活地实现这些需求，从而为数据建模和分析提供可靠的基础。
数据处理是数据科学的核心部分，有效的数据处理可以帮助我们更好地理解数据，发现数据中的模式和趋势，并提高数据质量。通过本章节的介绍，我们已经对pandas库的数据筛选和处理有了一个基础的了解，为接下来的实例演示打下了坚实的基础。
4. 使用pandas库和openpyxl库写入Excel文件
4.1 使用pandas库写入Excel文件
4.1.1 基本写入方法
pandas库提供了强大的数据处理功能，同样在写入Excel文件方面也表现卓越。我们可以使用 pandas.DataFrame.to_excel() 方法将DataFrame对象直接写入到Excel文件中。该方法的基本使用如下：
import pandas as pd
# 创建一个DataFrame实例用于演示
df = pd.DataFrame({
'Column1': [1, 2, 3, 4],
'Column2': ['a', 'b', 'c', 'd']
})
# 写入DataFrame到Excel文件，不包含索引
df.to_excel('output.xlsx', index=False)
执行上述代码后，将会在当前工作目录下生成一个名为 output.xlsx 的文件，文件中包含了DataFrame的数据，其中不包含行索引。如果需要包含索引，可以将 index 参数设置为 True 。
4.1.2 写入复杂数据结构
pandas允许我们写入更复杂的数据结构到Excel文件。例如，我们可以写入多级列索引（MultiIndex），以及使用 ExcelWriter 上下文管理器来写入多个DataFrame到同一个Excel工作簿中。
from io import StringIO
# 创建一个包含多级索引的DataFrame
df = pd.read_csv(StringIO(
"""level1(level2) column1 column2
A X 1 2
Y 3 4
B X 5 6
Y 7 8
), sep=' ')
# 写入具有多级索引的DataFrame到Excel
with pd.ExcelWriter('complex_output.xlsx') as writer:
df.to_excel(writer, sheet_name='MultiIndex')
在上述代码中，我们首先创建了一个包含多级列索引的DataFrame，并通过 ExcelWriter 将该DataFrame写入到名为 complex_output.xlsx 的Excel工作簿中。
4.1.3 写入时格式化样式
为了改善Excel文件的可读性和专业度，pandas允许我们对写入的数据进行样式设置。这可以通过 Styler 对象来实现，它提供了多种样式操作的方法。
# 为DataFrame设置样式
styled_df = df.style.format("{:.2f}", na_rep="N/A")
with pd.ExcelWriter('formatted_output.xlsx') as writer:
styled_df.to_excel(writer, sheet_name='Styled Data')
在这段代码中，我们使用 format 方法对 df 中的数值进行了两位小数的格式化，并将空值显示为"N/A"。然后我们将格式化后的DataFrame写入到Excel文件中。
4.2 使用openpyxl库写入复杂Excel文件
4.2.1 openpyxl库的基本使用
openpyxl 是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它允许我们创建、修改、访问和写入Excel文件，并且支持复杂的Excel功能，如图表、样式、宏等。
from openpyxl import Workbook
# 创建一个新的工作簿
wb = Workbook()
ws = wb.active
# 向工作表中添加数据
ws['A1'] = 'Hello'
ws['B1'] = 'World!'
# 保存工作簿到文件
wb.save('hello_world.xlsx')
上述代码演示了如何使用 openpyxl 创建一个新的Excel工作簿，并在一个单元格中写入数据，然后保存该工作簿。
4.2.2 创建和编辑工作表
openpyxl 允许我们创建多个工作表，并对它们进行编辑。我们可以设置工作表的名称、工作表中单元格的值，甚至可以添加或删除行和列。
# 创建一个新的工作簿
wb = Workbook()
# 添加一个新的工作表
ws1 = wb.create_sheet(title='Sheet1')
ws2 = wb.create_sheet(index=1, title='Sheet2')
# 在Sheet1中添加数据
for row in range(1, 10):
ws1.append([row, f"Data {row}"])
# 在Sheet2中删除第二行
ws2.delete_rows(2)
# 保存工作簿到文件
wb.save('multiple_sheets.xlsx')
在此代码中，我们创建了一个包含两个工作表的工作簿，向第一个工作表中添加了数据，并删除了第二个工作表中的第二行。
4.2.3 设置工作表样式和属性
openpyxl 不仅限于编辑数据，它还提供了广泛的样式和格式选项来增强Excel文件的视觉效果。
# 加载已存在的工作簿
wb = openpyxl.load_workbook('formatted_output.xlsx')
ws = wb.active
# 设置字体样式
font = openpyxl.styles.Font(name='Courier New', size=18, bold=True, color="FF0000")
cell = ws['A1']
cell.font = font
# 设置单元格边框样式
border = openpyxl.styles.PatternFill(start_color='FFD700', end_color='FFD700', fill_type='solid')
cell.border = openpyxl.styles.Borders(
top=openpyxl.styles.Border(left=border, right=border, top=border, bottom=border),
right=border,
bottom=border,
left=border
)
# 设置单元格背景色
cell.fill = openpyxl.styles.PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
# 保存工作簿到文件
wb.save('styled_excel.xlsx')
在这个示例中，我们对一个单元格设置了字体、边框和背景色样式，并保存了工作簿。通过这种方式，我们可以对Excel文件进行深度定制，以满足特定的格式要求。
5. 实例代码演示操作
在本章节中，我们将通过一系列的实例代码，演示如何使用Python和pandas库以及openpyxl库来实现Excel文件的读取和写入操作。我们会从简单的例子开始，逐步过渡到更加复杂的操作，展示这些工具的真正力量。
5.1 简单的Excel读取和写入实例
5.1.1 读取Excel文件并显示内容
在这个简单的例子中，我们将演示如何使用pandas读取一个基本的Excel文件，并将其内容展示出来。首先，确保你已经安装了pandas库，如果没有，请使用以下命令安装：
pip install pandas
以下是简单的Python代码，用于读取Excel文件并打印其内容：
import pandas as pd
# 读取Excel文件
df = pd.read_excel('example.xlsx')
# 显示Excel文件的内容
print(df)
这段代码首先导入了pandas库，并使用 read_excel 函数读取名为 example.xlsx 的Excel文件。之后，我们简单地使用 print 函数打印整个DataFrame（数据的二维结构）的内容。
5.1.2 对读取的数据进行处理并写回Excel
现在，我们对刚刚读取的数据进行一些基本的数据处理，然后将处理后的数据写回到一个新的Excel文件中。我们将进行以下步骤：
- 删除第一列
- 将某列的字符串转换为小写
- 写入到新的Excel文件中
代码如下：
import pandas as pd
# 读取Excel文件
df = pd.read_excel('example.xlsx')
# 删除第一列
del df[df.columns[0]]
# 将'name'列的字符串转换为小写
df['name'] = df['name'].str.lower()
# 将处理后的数据写入到新的Excel文件中
df.to_excel('processed_example.xlsx', index=False)
在这个代码块中，我们首先删除了DataFrame中的第一列，接着将名为 name 的列中的所有字符串转换成小写。最后，我们使用 to_excel 函数将修改后的DataFrame写回到一个新的Excel文件 processed_example.xlsx 中，并且设置 index=False 参数，以防止将DataFrame的索引也写入Excel文件。
5.2 复杂的Excel读取和写入实例
5.2.1 读取复杂Excel文件并提取特定信息
在现实世界中，Excel文件往往包含多个工作表(sheet)，以及复杂的数据结构。下面的代码演示了如何读取包含多个工作表的Excel文件，并从一个特定的工作表中提取数据。
import pandas as pd
# 读取包含多个工作表的Excel文件
xls = pd.ExcelFile('complex_example.xlsx')
# 选择特定工作表
df = pd.read_excel(xls, 'Sheet2')
# 显示选定工作表的内容
print(df)
在这个例子中，我们使用 pandas.ExcelFile 对象读取了一个包含多个工作表的Excel文件。然后，我们用 read_excel 函数指定了工作表名 Sheet2 来读取特定的工作表。最终，我们打印出所选工作表的内容。
5.2.2 处理数据并写入新的复杂Excel文件
接下来，我们将处理这些数据，然后写入到一个新的Excel文件中，同时创建多个工作表，并对数据进行适当的格式化。我们将执行以下操作：
- 对每个工作表中的数据进行排序
- 将不同的工作表数据写入到单独的工作表中
- 为新的Excel文件添加一些样式
代码如下：
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font
# 读取包含多个工作表的Excel文件
xls = pd.ExcelFile('complex_example.xlsx')
# 创建一个新的Excel工作簿
wb = Workbook()
ws1 = wb.create_sheet(title='Sorted_Sheet1')
ws2 = wb.create_sheet(title='Sorted_Sheet2')
# 读取数据并排序
for sheet_name in xls.sheet_names:
df = pd.read_excel(xls, sheet_name)
df.sort_values(by='id', inplace=True)
# 将排序后的数据写入对应的工作表
if sheet_name == 'Sheet1':
df.to_excel(wb, sheet_name='Sorted_Sheet1', index=False)
elif sheet_name == 'Sheet2':
df.to_excel(wb, sheet_name='Sorted_Sheet2', index=False)
# 添加标题样式
for ws in wb.worksheets:
title = ws['A1']
title.font = Font(size=18, bold=True)
# 保存新的Excel文件
wb.save('sorted_complex_example.xlsx')
在这个例子中，我们首先创建了一个新的Excel工作簿，并为两个工作表指定了标题。接着，我们对每个工作表中的数据根据 id 列进行了排序。然后，我们将排序后的数据写入到相应的工作表中，并为每个工作表的标题设置了一个样式，最后保存为新的Excel文件 sorted_complex_example.xlsx 。
通过这些实例，我们向您展示了如何使用pandas和openpyxl处理简单的以及复杂的Excel文件。实际应用中，这些库提供了高度的灵活性和强大的功能，可以应对各种数据处理需求。
简介：本文介绍如何使用Python处理Excel文件，重点讨论 pandas 和 openpyxl 库的使用。通过 pandas 的 read_excel() 和 to_excel() 函数可以轻松实现Excel文件的读取和写入，适用于数据处理和自动化报告等场景。 openpyxl 则提供了处理带有公式、样式或图表等复杂Excel文件的功能。文章通过实例代码演示了如何使用这些库进行数据筛选、写入数据以及遍历单元格等操作，并强调了掌握这些库对于提升数据处理能力的重要性。
扫一扫
1192
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
