一文熟练使用python修改Excel中的数据

使用python修改Excel中的内容

1.初级修改

1.1 openpyxl库的功能：

openpyxl模块是一个读写Excel 2010文档的Python库，如果要处理更早格式的Excel文档，需要用到额外的库，例如Xlwings。openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。

1.2 excel文档的基本定义

• 工作薄(workbook):一个EXCEL文件就称为一个工作薄,一个工作薄中可以包含若干张工作表。
• 工作表(sheet):工作薄中的每一张表格称为工作表,每张工作表都有一个标签,默认为sheet1\sheet2\sheet3来命名，(一个工作 薄默认为由3个工作表组成)
• 活动表(active sheet)：指当前正在操作的工作表
• 行(row): 工作表中的每一行行首数字(1、2、3、)称为行标题;一张工作表最多有65536行
• 列(column): 列标题:工作表中每一列列首的字母(A、B、C)称为列标题;一张工作表最多有256列
• 单元格(cell): 工作表的每一个格称为单元格

1.3 基本信息表的数据修改

要使用Python批量修改Excel的数据，可以使用Python的第三方库openpyxl。

首先，需要安装openpyxl库。可以使用以下命令安装：

pip install openpyxl
1

然后，可以使用以下代码来实现批量修改Excel的数据：

from openpyxl import load_workbook

# 打开Excel文件
workbook = load_workbook('example.xlsx')

# 选择要修改的工作表
worksheet = workbook['Sheet1']

# 遍历要修改的数据
for row in worksheet.iter_rows(min_row=2, min_col=1, max_col=2, values_only=True):
    # 修改数据
    new_value1 = row[0] + 1
    new_value2 = row[1] * 2

    # 将修改后的数据写回Excel
    worksheet.cell(row=row[0].row, column=3, value=new_value1)
    worksheet.cell(row=row[0].row, column=4, value=new_value2)

# 保存修改后的Excel文件
workbook.save('example_modified.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

以上代码假设要修改的Excel文件名为example.xlsx，要修改的工作表名为Sheet1。代码中的min_row、min_col、max_col参数分别指定了要修改的数据的起始行、起始列和结束列。可以根据实际情况进行修改。

代码中使用iter_rows方法遍历要修改的数据，并使用values_only参数将数据以元组的形式返回。然后，可以根据需要修改数据，并使用cell方法将修改后的数据写回Excel。最后，使用save方法保存修改后的Excel文件。

执行以上代码后，修改后的Excel文件将保存为example_modified.xlsx。

1.4 异常解决

AttributeError: ‘int’ object has no attribute ‘row’

这是一个AttributeError异常，表示在一个整数对象上尝试访问名为’row’的属性时出错。整数对象是没有row属性的。

可能的原因是代码中错误地将一个整数对象作为一个具有row属性的对象来使用。你需要检查代码，找到错误的地方并修复它。确保你正在使用正确的对象类型，并且在访问属性之前，该对象已经被正确地初始化或赋值。

2. 进阶修改

2.1 代码如下：

import pandas as pd

# 读取Excel文件
df = pd.read_excel('input.xlsx')

# 遍历每一行数据
for index, row in df.iterrows():
    # 判断特定条件
    if row['列名'] == '特定条件':
        # 修改数据
        df.at[index, '需要修改的列名'] = '修改后的值'

# 保存修改后的Excel文件
df.to_excel('output.xlsx', index=False)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

其中，'input.xlsx’是输入的Excel文件名，'列名’是要判断的列名，'特定条件’是符合要求的条件，'需要修改的列名’是要修改的列名，'修改后的值’是修改后的值。代码中的’output.xlsx’是输出的Excel文件名，可以根据实际情况进行修改。

2.2 简单示例

需求：使用python批量修改Excel中的数据，如果查询到该行某列数据为X开头的字符串，则将X替换为BJ1，修改并保存后遍历下一行

from openpyxl import load_workbook

# 打开Excel文件
workbook = load_workbook('data.xlsx')
# 选择第一个工作表
sheet = workbook.active

# 遍历每一行
for row in sheet.iter_rows(min_row=2):
    # 获取第一列的值
    value = row[0].value
    # 判断是否以X开头
    if value.startswith('X'):
        # 替换为BJ1
        row[0].value = 'BJ1'

# 保存修改后的Excel文件
workbook.save('modified_data.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

上述代码假设Excel文件名为data.xlsx，且数据从第二行开始，第一列是需要进行替换的数据。修改后的文件将保存为modified_data.xlsx。

2.3 具体示例

需求：使用python批量修改Excel中的数据，如果查询到该行某列数据为X开头的字符串，则将X替换为BJ1；如果为0则替换为“BJ0”；如果为9则替换为BJ9，修改并保存后继续遍历下一行。

2.4 Excel源文件如下：

2.4 代码如下：

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

2.5 运行结果如下：