把多个系统导出的数据汇总到一张 Excel 表里时,重复记录几乎不可避免:同一条数据被重复抓取、分页导出时首尾重叠、人工复制时多粘贴了一段。这些重复行往往并不相邻,靠肉眼翻找既慢又容易漏。重复数据还会往下游传导:统计报表重复累加金额、导入数据库时撞上唯一键冲突、文件体积和处理耗时一起变大。
用 Python 写去重脚本的价值在于把规则写死:比较哪些列、保留哪一条、是删除还是另存,都能显式声明,同一个规则可以套用到多张工作表和多个文件上。本文以 Spire.XLS for Python 为例,演示按整行判重、按关键列判重、删除前先标记、把不重复的行复制到新工作簿四种做法,最后封装成一个可复用的函数。
一、环境准备
安装 Spire.XLS for Python 之后,Python 就能直接读写 Excel 文件,本机不需要安装 Microsoft Excel:
示例统一使用以下命名空间:
- 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()
复制代码
几个关键点:tuple() 把一行的多列文本聚合成一个整体,元组可哈希,因此能直接放进 set,用 in 判断是否出现过;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 决定重复值的底色;AddRange 从 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 行开始遍历,因为表头也要一并复制到结果文件。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 列。函数返回实际删除的行数,调用方可以打印每张表的处理结果便于核对。由于 seen 集合定义在函数内部,每张工作表各自独立判重,不同工作表中的相同内容不会被当成重复。
八、实践中的注意点
先备份原始文件。删除操作不可逆,建议把结果输出到新文件,保留原始数据以便复核。
跳过表头。首行是字段名时,start_row 要设为 2,否则表头会被拉进比较范围。
当心空白行。多个空白行读出的单元格文本都是空字符串,会被判定为互相重复。可以结合 sheet.Rows[row - 1].IsBlank 判断并跳过。
理解 Text 的比较口径。Text 拿到的是单元格显示文本,数字格式会影响比较结果:显示为 001 和 1 的两个单元格文本不同,但实际数值相同。格式不统一的数据要先确定比较规则。
删除必须倒序。先收集行号,再从后往前删,这是避免行号错位的唯一可靠做法。
人工核对可借助排序。想肉眼检查重复记录,可以先按关键列排序,让相同值集中显示,Spire.XLS 提供了 DataSorter 相关接口对指定区域排序。
小结
整体思路并不复杂:读取单元格文本,把一行参与比较的内容组成元组,用集合判断是否出现过,重复的记下行号后倒序删除。整行比较适合清理完全相同的记录,关键列比较适合按业务字段识别重复;不想改原文件时,把不重复的行复制到新工作簿即可。正式的数据清理任务建议先标记或预览重复结果,确认规则后再删除,并始终保留原始文件。把判重逻辑封装成函数后,扩展到多工作表、批量文件处理都只是外面再套一层循环的事。 |