如何使用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_value
        workbook.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_value
        workbook.save('001.xlsx')
    if cell_value and str(cell_value).startswith('9'):
        new_value = 'BJ9' + str(cell_value)[1:]
        row[1].value = new_value
        workbook.save('001.xlsx')

    # 否则遍历下一行
    else:
        continue
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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

使用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_value
    elif cell_value and str(cell_value).startswith('0'):
        new_value = 'BJ0' + str(cell_value)[1:]
        df.at[i, 'Column2'] = new_value
    elif 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

使用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')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

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