使用 Python 轻松移除 Excel 文档中的重复行

0 阅读11分钟

将多个系统导出的数据合并到一张 Excel 表格中,难免会出现重复记录。例如,同一条数据被重复抓取、分页导出时首尾内容重叠,或者人工复制时不小心多粘贴了一部分。这些重复行不一定相邻,单靠人工查找既费时,也容易遗漏。

重复数据还可能影响后续处理:统计报表可能重复计算金额,数据导入可能触发唯一键冲突,文件体积和处理时间也会随之增加。使用 Python 编写去重脚本,可以明确指定比较哪些列、保留哪条记录,以及如何处理重复行,让不同文件按照相同规则完成清理。

本文使用 Spire.XLS for Python 演示 Excel 重复行的几种处理方式:按整行内容判断重复、按指定列判断重复、删除前标记重复值,以及将不重复的数据复制到新工作簿。最后还会封装一个可复用的去重函数,方便处理多个工作表。

为什么要使用脚本移除重复行

相比手动查找和删除,使用 Python 处理重复数据有几个明显的优势:

  • 比较范围灵活:可以比较整行所有列,也可以只选择订单号、电话号码等关键字段,避免无关列影响判断。
  • 保留规则明确:可以指定保留第一次出现的记录,删除后续重复项,避免人工操作带来的差异。
  • 删除前可以预览:先标记重复值或记录重复行号,确认判断规则无误后再执行删除。
  • 适合批量处理:将去重逻辑封装成函数后,可以复用于多个工作表和 Excel 文件。

需要注意的是,本文所说的“重复”是指参与比较的列内容完全相同。它并不代表两条记录在业务意义上一定相同。如果两行数据虽然内容一致,但实际代表不同事件,就不应该直接删除。

环境准备

安装 Spire.XLS for Python 后,即可使用 Python 读取和编辑 Excel 文件,无需本地安装 Microsoft Excel。通过 pip 安装:

pip install Spire.XLS

后续示例使用以下命名空间:

from spire.xls import *
from spire.xls.common import *

实现去重前需要明确的三个问题

本文没有使用内置的“一键去重”功能,而是通过 Python 的集合和元组自行实现判重逻辑。这样可以灵活控制去重规则,但在编写代码前,需要先明确三个问题。

1. 如何判断两行是否重复?

示例使用 sheet.Range[row, col].Text 逐个读取单元格的显示文本,再将一行中参与比较的内容组合成元组,作为该行的比较标识。两个元组相同,就认为对应的两行重复。

2. 重复时保留哪一行?

常见做法是从上到下扫描工作表。第一次遇到某组内容时,将其保存到集合中;之后再次遇到相同内容,就记录对应的行号。这样最终会保留最早出现的记录。

3. 如何避免删除时行号错位?

删除一行后,其下方的行会整体上移。因此,不能在遍历工作表的同时直接删除重复行。正确做法是先收集所有需要删除的行号,再按照从大到小的顺序删除。

方法一:按整行内容移除重复行

如果需要比较整行数据,可以从第二行开始遍历工作表的所有列。只要某一行的内容与之前出现过的行完全相同,就将它记录为重复行,最后统一删除。

下面的示例假设第一行为表头:

from spire.xls import *
from spire.xls.common import *

inputFile = "示例.xlsx"
outputFile = "移除重复行.xlsx"

workbook = Workbook()
workbook.LoadFromFile(inputFile)
sheet = workbook.Worksheets[0]

seen = set()
duplicate_rows = []

# 第一行为表头,从第二行开始比较
for row in range(2, sheet.LastRow + 1):
    key = tuple(
        sheet.Range[row, col].Text
        for col in range(1, sheet.LastColumn + 1)
    )

    if key in seen:
        duplicate_rows.append(row)
    else:
        seen.add(key)

# 从后往前删除重复行
for row in reversed(duplicate_rows):
    sheet.DeleteRow(row)

workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

移除 Excel 中的重复行.png

这里使用 tuple() 将一行中各个单元格的文本组合成一个元组。元组可以作为集合中的元素,因此能够通过 seen 快速判断某行是否已经出现过。

LastRow 和 LastColumn 用于限定遍历范围,避免扫描整张工作表。由于第一行是表头,循环从第 2 行开始。如果文件没有表头,可以将起始行改为 1。

删除操作使用 reversed(duplicate_rows),确保按照行号从大到小的顺序执行。例如,需要删除第 5 行和第 9 行时,如果先删除第 5 行,原来的第 9 行就会变成第 8 行,后续删除操作可能因此定位错误。倒序删除则不会影响尚未处理的行号。

方法二:只比较指定的关键列

按整行内容比较虽然严格,但有时并不符合实际需求。

例如,两条记录的姓名、电话号码和地址完全相同,只是导出时间不同。如果把所有列都纳入比较范围,脚本就会将它们识别为两条不同记录。

这种情况下,可以只选择关键列进行比较。下面的示例使用第 1 列和第 3 列作为判重依据:

from spire.xls import *
from spire.xls.common import *

inputFile = "records.xlsx"
outputFile = "records_by_key.xlsx"

workbook = Workbook()
workbook.LoadFromFile(inputFile)
sheet = workbook.Worksheets[0]

# 只比较第 1 列(姓名)和第 3 列(电话)
key_columns = (1, 3)

seen = set()
duplicate_rows = []

for row in range(2, sheet.LastRow + 1):
    key = tuple(
        sheet.Range[row, col].Text
        for col in key_columns
    )

    if key in seen:
        duplicate_rows.append(row)
    else:
        seen.add(key)

for row in reversed(duplicate_rows):
    sheet.DeleteRow(row)

workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

与方法一相比,主要变化是增加了 key_columns,用于指定参与比较的列。列号从 1 开始,可以根据实际数据调整,例如增加订单号、移除备注列等。

选择关键列时,应优先考虑能够识别一条业务记录的字段,例如订单号、客户编号,或者姓名与电话号码的组合。导出时间、自增序号和临时备注等字段通常不适合作为判重依据,因为这些内容可能在不同导出批次中发生变化。

方法三:删除前先标记重复值

如果不确定当前的判重规则是否合理,可以先预览重复值,再决定是否删除。

Spire.XLS 支持通过条件格式标记指定区域内重复出现的值。下面的示例会将 A 列中重复出现的值标记为红色:

from spire.xls import *
from spire.xls.common import *

inputFile = "records.xlsx"
outputFile = "records_preview.xlsx"

workbook = Workbook()
workbook.LoadFromFile(inputFile)
sheet = workbook.Worksheets[0]

# 为 A 列中重复出现的值添加条件格式
format = sheet.ConditionalFormats.Add()
format.AddRange(sheet.Range["A2:A" + str(sheet.LastRow)])

condition = format.AddCondition()
condition.FormatType = ConditionalFormatType.DuplicateValues
condition.BackColor = Color.get_IndianRed()

workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

ConditionalFormatType.DuplicateValues 用于识别指定区域内重复出现的值,BackColor 则设置重复值的背景颜色。示例将条件格式应用到 A2 到最后一行,跳过第一行表头。

需要注意,这种方式标记的是指定区域内重复的单元格值,而不是按照多列组合判断整行是否重复。例如,两条记录的姓名相同,但电话号码不同,A 列中的姓名仍可能被标记为重复值。因此,它更适合检查单列数据,不能直接替代方法一或方法二中的整行判重逻辑。

条件格式只会添加视觉标记,不会修改或删除原始数据。你可以先打开生成的文件检查结果,再根据实际需求调整比较范围,确认规则正确后执行删除操作。

方法四:将不重复的行复制到新工作簿

如果希望保留原始文件,可以不直接删除重复行,而是将不重复的记录逐行复制到另一个工作簿中。

下面的示例会从第一行开始遍历数据。每次遇到尚未出现过的行,就将其复制到新工作簿;如果已经出现过,则跳过该行。

from spire.xls import *
from spire.xls.common import *

inputFile = "records.xlsx"
outputFile = "records_new.xlsx"

sourceBook = Workbook()
sourceBook.LoadFromFile(inputFile)
sourceSheet = sourceBook.Worksheets[0]

resultBook = Workbook()
resultSheet = resultBook.Worksheets[0]

seen = set()
targetRow = 1

for row in range(1, sourceSheet.LastRow + 1):
    key = tuple(
        sourceSheet.Range[row, col].Text
        for col in range(1, sourceSheet.LastColumn + 1)
    )

    if key in seen:
        continue

    seen.add(key)

    sourceRange = sourceSheet.Range[
        row, 1, row, sourceSheet.LastColumn
    ]
    destRange = resultSheet.Range[
        targetRow, 1, targetRow, sourceSheet.LastColumn
    ]

    sourceSheet.Copy(sourceRange, destRange, True)
    targetRow += 1

resultBook.SaveToFile(outputFile, ExcelVersion.Version2013)

sourceBook.Dispose()
resultBook.Dispose()

这次从第 1 行开始遍历,是因为表头也需要复制到结果文件中。sourceSheet.Copy(sourceRange, destRange, True) 用于将指定区域复制到目标区域,第三个参数用于控制是否复制格式。

targetRow 表示结果工作表中的目标行号。只有成功复制一行后才会递增,因此被跳过的重复行不会在结果文件中留下空白行。

这种方式不会修改原始工作簿,适合需要保留源文件的场景。不过,上面的示例只复制了第一个工作表中的行数据,并不等于完整复制原工作簿。其他工作表、列宽、工作表设置等内容需要根据实际需求另外处理。

实战:封装成可复用的去重函数

前面的方法主要针对单张工作表。为了方便处理多个工作表,可以将判重和删除逻辑封装成一个函数,再遍历整个工作簿。

from spire.xls import *
from spire.xls.common import *

def remove_duplicate_rows(sheet, start_row=2, columns=None):
    """移除工作表中的重复行,返回删除的行数"""
    if columns is None:
        columns = range(1, sheet.LastColumn + 1)

    seen = set()
    duplicate_rows = []

    for row in range(start_row, sheet.LastRow + 1):
        key = tuple(
            sheet.Range[row, col].Text
            for col in columns
        )

        if key in seen:
            duplicate_rows.append(row)
        else:
            seen.add(key)

    for row in reversed(duplicate_rows):
        sheet.DeleteRow(row)

    return len(duplicate_rows)


inputFile = "workbook.xlsx"
outputFile = "workbook_unique.xlsx"

workbook = Workbook()
workbook.LoadFromFile(inputFile)

for sheet in workbook.Worksheets:
    removed = remove_duplicate_rows(sheet)
    print("工作表 {0}:移除 {1} 行".format(sheet.Name, removed))

workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

函数提供了两个参数,用于控制去重范围:

  • start_row:指定开始比较的行号。默认从第 2 行开始,适用于第一行为表头的工作表。如果没有表头,可以传入 start_row=1。
  • columns:指定参与比较的列。默认比较所有列,也可以传入 columns=(1, 3),只比较第 1 列和第 3 列。

函数返回实际删除的行数,调用方可以通过 print() 输出每张工作表的处理结果,方便检查。

for sheet in workbook.Worksheets 会依次遍历工作簿中的所有工作表。由于 seen 集合定义在函数内部,每张工作表都会独立判重,不会将不同工作表中的相同记录视为重复数据。

实践中的几个注意点

将去重脚本用于实际文件时,还需要留意以下细节:

  • 先备份原始文件:删除操作可能导致数据无法恢复,建议将处理结果保存到新文件,保留原始数据以便核对。
  • 跳过表头:如果第一行是字段名称,应将 start_row 设置为 2,避免把表头纳入比较范围。
  • 注意空白行:多个空白行读取到的单元格文本可能都是空字符串,因此会被判定为重复。可以结合 sheet.Rows[row - 1].IsBlank 判断并跳过空白行。
  • 注意 Text 的比较方式:Text 读取的是单元格显示文本,数字格式可能影响比较结果。例如,显示为 001 和 1 的两个单元格,文本可能不同,但实际数值相同。对于格式不统一的数据,应先确定比较规则。
  • 删除时使用倒序:先收集需要删除的行号,再从后往前删除,避免删除操作导致行号错位。
  • 排序辅助检查:如果需要人工核对重复记录,可以先按关键列排序,让相同值集中显示。Spire.XLS 提供了 DataSorter 相关接口,可用于对指定区域排序。

总结

本文介绍了使用 Python 和 Spire.XLS 移除 Excel 重复行的几种方式。通过读取单元格文本,将一行内容组合成元组,再利用集合判断是否重复,就可以灵活控制去重范围和保留规则。按整行比较适合清理完全相同的记录,按关键列比较则更适合根据业务字段识别重复数据。如果不希望直接修改原文件,还可以将不重复的行复制到新工作簿。对于正式的数据清理任务,建议先标记或检查重复结果,再执行删除,并保留原始文件。将判重逻辑封装成函数后,还可以进一步扩展到多工作表和批量文件处理。