如何使用python快速修改Excel表单中的大量数据

python修改Excel中的内容进阶加速版

前面有一篇文章讲到了使用python处理Excel中的数据文件，即修改Excel中的数据，但是那个版本的代码跑点小规模、小数据量的excel还行，一旦数据量达到万条级别，代码运行会非常慢！因此，特意对之前的代码进行了优化，大幅的提升了代码的运行速率。

1、修改思路

首先是使用库的区别：操作Excel数据一般常用的两个库是Openpyxl和Pandas，

• Openpyxl是一个用于读写Excel文件的Python库。它可以让你创建、修改和处理Excel文件，包括读取、写入、复制、剪切、替换等操作。Openpyxl可以处理xlsx、xlsm、xltx、xltm等Excel文件格式，并提供了丰富的API来进行数据操作和样式设置。
• Pandas是一个用于数据分析和数据处理的Python库。它提供了强大的数据结构和数据操作功能，特别是对于结构化数据的处理非常方便。Pandas可以读取和写入多种文件格式，包括Excel、CSV、JSON等。在数据处理方面，Pandas可以进行数据筛选、排序、聚合、合并等多种操作，并且支持处理缺失值和处理时间序列数据等常见问题。

总结来说，Openpyxl主要用于Excel文件的读写和操作，而Pandas则更适用于数据分析和数据处理。Openpyxl提供了更底层的操作，可以直接对Excel文件进行读写和样式设置，而Pandas则提供了更高层次的数据操作接口，方便处理和分析结构化数据。两者在功能和应用场景上有所区别，选择使用哪一个库取决于具体的需求和任务。

代码展示

使用openpyxl进行数据操作

import openpyxl# 打开Excel文件
workbook = openpyxl.load_workbook('变量信息.xlsx')# 选择要操作的工作表
worksheet = workbook.active# 遍历每一行
for row in worksheet.iter_rows():# 获取该行的第一个单元格的值cell_value = row[1].value# 如果该行的字符串以X开头，则将X替换为BJ1并拼接后续字符串，同时保存数据if cell_value and str(cell_value).startswith('X' or '1'):new_value = 'BJ1' + str(cell_value)[1:]row[1].value = new_valueworkbook.save('001.xlsx')print(new_value)if cell_value and str(cell_value).startswith('0'):new_value = 'BJ0' + str(cell_value)[1:]row[1].value = new_valueworkbook.save('001.xlsx')if cell_value and str(cell_value).startswith('9'):new_value = 'BJ9' + str(cell_value)[1:]row[1].value = new_valueworkbook.save('001.xlsx')# 否则遍历下一行else:continue

处理数据不会改变原先的数据格式和数据类型，但是运行速率较差

使用Pandas库进行数据修改

import pandas as pd# 读取Excel文件
df = pd.read_excel('KIC.xlsx')# 遍历每一行
for i, row in df.iterrows():# 获取该行的第一个单元格的值cell_value = row[1]# 如果该行的字符串以X或1开头，则将X或1替换为BJ1并拼接后续字符串if cell_value and str(cell_value).startswith(('X', '1')):new_value = 'BJ1' + str(cell_value)[1:]df.at[i, 'Column2'] = new_valueelif cell_value and str(cell_value).startswith('0'):new_value = 'BJ0' + str(cell_value)[1:]df.at[i, 'Column2'] = new_valueelif cell_value and str(cell_value).startswith('9'):new_value = 'BJ9' + str(cell_value)[1:]df.at[i, 'Column2'] = new_value# 将修改后的数据保存到新的Excel文件中
df.to_excel('KIC01.xlsx', index=False)

使用Pandas操作Excel数据运行速率非常快，但是会破坏Excel文件原先的文件格式

2、最终改进

要提升代码的运行速度，可以考虑以下几个方面的优化：

1. 使用批量写入数据：在当前代码中，每次修改单元格后都会保存一次文件，这会导致频繁的磁盘操作，影响性能。可以将修改的数据先存储在一个临时的数据结构中，然后一次性写入到Excel文件中。
2. 使用列表推导式或生成器表达式替代循环：使用列表推导式或生成器表达式可以提供更高效的迭代方式，避免使用显式的循环。这样可以减少迭代次数，提升代码的执行速度。

下面是修改后的代码示例：

import openpyxl# 打开Excel文件
workbook = openpyxl.load_workbook('KIC.xlsx')# 选择要操作的工作表
worksheet = workbook.active# 创建一个临时列表，用于存储修改后的数据
new_data = []# 遍历每一行
for row in worksheet.iter_rows():# 获取该行的第一个单元格的值cell_value = row[1].value# 如果该行的字符串以X开头，则将X替换为BJ1并拼接后续字符串，同时保存数据if cell_value and str(cell_value).startswith(('X', '1')):new_value = 'BJ1' + str(cell_value)[1:]elif cell_value and str(cell_value).startswith('0'):new_value = 'BJ0' + str(cell_value)[1:]elif cell_value and str(cell_value).startswith('9'):new_value = 'BJ9' + str(cell_value)[1:]else:# 如果不需要修改，则直接保存原始数据new_value = cell_value# 将修改后的数据添加到临时列表中new_data.append(new_value)# 将修改后的数据一次性写入Excel文件
for index, value in enumerate(new_data, start=1):worksheet.cell(row=index, column=2, value=value)# 保存修改后的Excel文件
workbook.save('KIC04.xlsx')

通过以上优化，代码将会更高效地执行，并提升运行速度。