如何使用 Python 分块处理 excel 文件数据?

pythonserver side programmingprogramming更新于 2026/2/14 21:32:17

简介

似乎世界由 Excel 统治。我在数据工程工作中惊讶地发现，有多少同事将 Excel 用作决策的关键工具。虽然我不是 MS Office 及其 Excel 电子表格的忠实粉丝，但我仍将向您展示一个有效处理大型 Excel 电子表格的巧妙技巧。

如何做到这一点..

在我们直接进入程序之前，让我们先了解一些使用 Pandas 处理 Excel 电子表格的基础知识。

1. 安装。继续安装 openpyxl 和 xlwt。如果您不确定是否已安装，只需使用 pip freeze 或 pip list 从 python 终端获取可用的软件包即可。

我们将首先通过传递数据元组来创建一个 excel 电子表格。然后我们将数据加载到 pandas 数据框中。最后，我们将数据框数据写入新的工作簿。

import xlsxwriter
import pandas as pd

2.创建一个包含小数据的 Excel 电子表格。我们将有一个小函数将字典数据写入 excel 电子表格。所有代码逻辑在每个步骤中定义。

# 函数:write_data_to_files
def write_data_to_files(inp_data, inp_file_name):
"""
函数:使用传递给此代码的数据创建一个 csv 文件
参数:inp_data:要写入目标文件的元组数据
file_name:用于存储数据的目标文件名
返回:无
假设:要创建的文件和此代码位于同一目录中。
"""
print(f" *** Writing the data to - {inp_file_name}")

# 创建工作簿。
workbook = xlsxwriter.Workbook(inp_file_name)

# 添加工作表。
worksheet = workbook.add_worksheet()

# 从第一个单元格开始。行和列的索引为零。
row = 0
col = 0

# 读取输入数据并将其写入行和列中
for player,titles in inp_data:
worksheet.write(row, col, player)
worksheet.write(row, col + 1,titles)
row += 1

# 关闭工作簿。
workbook.close()
print(f" *** Completed writing the data to - {inp_file_name}")

# 函数:excel_functions_with_pandas
def excel_functions_with_pandas(inp_file_name):
"""
function : Quick overview of functions you can apply on excel with pandas
args : inp_file_name : input excel spread sheet.
return : none
assumption : Input excel spreadsheet and this code are in same directory.
"""
data = pd.read_excel(inp_file_name)

# print top 2 rows
print(f" *** Displaying info about {inp_file_name} - {data.info()}")

# 查看数据类型
print(f" *** 显示有关 {inp_file_name} - {data.info()}" 的信息)

# 创建新电子表格"Sheet2"并将数据写入其中。
new_players_info = pd.DataFrame(data=[
{"players": "new Roger Federer", "titles": 20},
{"players": "new Rafael Nadal", "titles": 20},
{"players": "new Novak Djokovic", "titles": 17},
{"players": "new Andy Murray", "titles": 3}], columns=["players", "titles"])

new_data = pd.ExcelWriter(inp_file_name)
new_players_info.to_excel(new_data, sheet_name="Sheet2")
if __name__ == '__main__':
# 定义文件名和数据
file_name = "temporary_file.xlsx"

# 用于存储的元组数据
file_data = (['player', 'titles'], ['Federer', 20], ['Nadal', 20], ['Djokovic', 17], ['Murray', 3])

# 将 file_data 写入 file_name
# write_data_to_files(file_data, file_name)

# # 将 excel 文件读入 pandas 并应用函数。
# excel_functions_with_pandas(file_name)

if __name__ == '__main__':
# 定义文件名和数据
file_name = "temporary_file.xlsx"

# 用于存储的元组数据
file_data = (['player', 'titles'], ['Federer', 20], ['Nadal', 20], ['Djokovic', 17], ['Murray', 3])

# 将 file_data 写入 file_name
# write_data_to_files(file_data, file_name)

# # 将 excel 文件读入 pandas 并应用函数。
# excel_functions_with_pandas(file_name)

输出

*** Writing the data to - temporary_file.xlsx
*** Completed writing the data to - temporary_file.xlsx
*** Displaying top 2 rows of - temporary_file.xlsx
player titles
0 Federer 20
1 Nadal 20
2 Djokovic 17
3 Murray 3
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 4 entries, 0 to 3
Data columns (total 2 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 player 4 non-null object
1 titles 4 non-null int64
dtypes: int64(1), object(1)
memory usage: 192.0+ bytes
*** Displaying info about temporary_file.xlsx - None

现在，在处理大型 csv 文件时，我们有很多选项，包括分块处理它们，但是对于 excel 电子表格，Pandas 默认不提供分块选项。

因此，如果您想分块处理 excel 电子表格，下面的程序非常有用。

示例

def global_excel_to_db_chunks(file_name, nrows):
"""
function : handle excel spreadsheets in chunks
args : inp_file_name : input excel spread sheet.
return : none
assumption : Input excel spreadsheet and this code are in same directory.
"""
chunks = []
i_chunk = 0

# 第一行是标题。我们已经读过了，所以跳过它。
skiprows = 1
df_header = pd.read_excel(file_name, nrows=1)

while True:
df_chunk = pd.read_excel(
file_name, nrows=nrows, skiprows=skiprows, header=None)
skiprows += nrows

# 当没有数据时，我们知道可以跳出循环。
if not df_chunk.shape[0]:
break
else:
print(
f" ** Reading chunk number {i_chunk} with {df_chunk.shape[0]} Rows")
# print(f" *** Reading chunk {i_chunk} ({df_chunk.shape[0]} rows)")
chunks.append(df_chunk)
i_chunk += 1

df_chunks = pd.concat(chunks)

# 重命名列以将块与标题连接起来。
columns = {i: col for i, col in enumerate(df_header.columns.tolist())}
df_chunks.rename(columns=columns, inplace=True)
df = pd.concat([df_header, df_chunks])

print(f' *** Reading is Completed in chunks...')

if __name__ == '__main__':
print(f" *** Gathering & Displaying Stats on the excel spreadsheet ***")
file_name = 'Sample-sales-data-excel.xls'
stats = pd.read_excel(file_name)
print(f" ** Total rows in the spreadsheet are - {len(stats.index)} Rows")

# 每次以 1000 行为单位处理 Excel 文件。
global_excel_to_db_chunks(file_name, 1000)

*** Gathering & Displaying Stats on the excel spreadsheet ***
** Total rows in the spreadsheet are - 9994 Rows
** Reading chunk number 0 with 1000 Rows
** Reading chunk number 1 with 1000 Rows
** Reading chunk number 2 with 1000 Rows
** Reading chunk number 3 with 1000 Rows
** Reading chunk number 4 with 1000 Rows
** Reading chunk number 5 with 1000 Rows
** Reading chunk number 6 with 1000 Rows
** Reading chunk number 7 with 1000 Rows
** Reading chunk number 8 with 1000 Rows
** Reading chunk number 9 with 994 Rows
*** Reading is Completed in chunks...

输出

*** Gathering & Displaying Stats on the excel spreadsheet ***
** Total rows in the spreadsheet are - 9994 Rows
** Reading chunk number 0 with 1000 Rows
** Reading chunk number 1 with 1000 Rows
** Reading chunk number 2 with 1000 Rows
** Reading chunk number 3 with 1000 Rows
** Reading chunk number 4 with 1000 Rows
** Reading chunk number 5 with 1000 Rows
** Reading chunk number 6 with 1000 Rows
** Reading chunk number 7 with 1000 Rows
** Reading chunk number 8 with 1000 Rows
** Reading chunk number 9 with 994 Rows
*** Reading is Completed in chunks...