从系统导出的Excel文件经常夹带空行、空列:数据区域中间有空行会影响筛选和排序范围,空列会改变字段位置,按固定列号读取时容易字段错位,导入数据库时还可能产生无效记录。手工删除少量空白行列不难,但面对多个结构相似的工作簿,重复操作成本较高。本文围绕Spire.XLS for Python,介绍如何用脚本判断并删除空白行、空白列,同时区分删除整行整列与清空单元格内容。
环境准备方面,Spire.XLS for Python用于读取和修改Excel工作簿,不需要本机安装Microsoft Excel。安装命令如下:
导入模块:
- from spire.xls import *
- from spire.xls.common import *
复制代码
编写删除逻辑前要明确两个关键点。第一,判断空白行列使用IsBlank属性:sheet.Rows.IsBlank判断指定行是否为空,sheet.Columns[j].IsBlank判断指定列是否为空。这里判断的是整行或整列,只要行或列中仍有内容,就不会被视为空白。第二,索引规则不同:通过Rows、Columns[j]访问行列时索引从0开始,而DeleteRow()、DeleteColumn()的参数从1开始。例如删除第1行要调用DeleteRow(1)。删除行列后后续数据会上移或左移,如果从前往后遍历,删除当前行后原有索引变化,容易漏删,因此应采用倒序遍历。
方法一:删除工作表中的所有空白行和空白列。下面的示例加载工作簿,取第三张工作表,从后往前遍历所有行和列,遇到空白行或空白列分别调用DeleteRow()和DeleteColumn()。
- from spire.xls import *
- from spire.xls.common import *
- inputFile = '示例.xlsx'
- outputFile = 'cleaned.xlsx'
- workbook = Workbook()
- workbook.LoadFromFile(inputFile)
- sheet = workbook.Worksheets[2]
- for i in range(sheet.Rows.Length - 1, -1, -1):
- if sheet.Rows[i].IsBlank:
- sheet.DeleteRow(i + 1)
- for j in range(sheet.Columns.Length - 1, -1, -1):
- if sheet.Columns[j].IsBlank:
- sheet.DeleteColumn(j + 1)
- workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
- workbook.Dispose()
复制代码
这段代码用sheet.Rows.Length和sheet.Columns.Length取得行列数量,用range(..., -1, -1)实现倒序遍历。由于Rows、Columns[j]从0开始,而删除方法从1开始,因此分别传入i + 1和j + 1。该方式会检查工作表的全部行列,适合需要全面清理的场景;如果数据只集中在工作表的一小部分区域,遍历全部行列会产生不必要的开销。
方法二:只检查有数据的区域。如果工作表数据集中在左上角,可以用LastRow和LastColumn取得最后一个有内容的行号和列号,只遍历实际使用区域。
- from spire.xls import *
- from spire.xls.common import *
- inputFile = 'report.xlsx'
- outputFile = 'report_cleaned.xlsx'
- workbook = Workbook()
- workbook.LoadFromFile(inputFile)
- sheet = workbook.Worksheets[0]
- for row in range(sheet.LastRow, 0, -1):
- if sheet.Rows[row - 1].IsBlank:
- sheet.DeleteRow(row)
- for col in range(sheet.LastColumn, 0, -1):
- if sheet.Columns[col - 1].IsBlank:
- sheet.DeleteColumn(col)
- workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
- workbook.Dispose()
复制代码
这里LastRow和LastColumn分别表示最后一个有内容的行号和列号。循环变量row、col从1开始,因此访问行列对象时减1,调用删除方法时直接使用循环变量。该方法避免遍历大量未使用行列,适合数据范围相对集中的文件。需要注意,最后使用的区域与最后一个有内容的单元格不一定完全相同,例如工作表末尾可能存在只有格式、没有实际数据的单元格。对于这类文件,建议先检查LastRow和LastColumn返回的范围,再决定是否需要进一步清理。
方法三:删除指定位置的连续空白行或空白列。如果空白行列的位置和数量固定,就不必逐行逐列判断,可以直接指定起始位置和删除数量。例如模板每次从第5行开始出现连续4行空白区域,第2列开始有2列不需要的数据,可以这样处理:
- from spire.xls import *
- from spire.xls.common import *
- inputFile = 'template.xlsx'
- outputFile = 'template_cleaned.xlsx'
- workbook = Workbook()
- workbook.LoadFromFile(inputFile)
- sheet = workbook.Worksheets[0]
- sheet.DeleteRow(5, 4)
- sheet.DeleteColumn(2, 2)
- workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
- workbook.Dispose()
复制代码
DeleteRow(5, 4)表示从第5行开始连续删除4行,DeleteColumn(2, 2)表示从第2列开始连续删除2列。该方式不需要遍历和逐行判断,适合结构固定的Excel模板;如果不同文件中空白区域位置不一致,更适合使用IsBlank判断方式。
方法四:清空单元格内容,而不是删除整行或整列。有时只需要移除单元格中的内容,同时保留原有表格布局,这时应使用清空操作,而不是DeleteRow()或DeleteColumn()。Spire.XLS提供多种清空方式:
- from spire.xls import *
- from spire.xls.common import *
- inputFile = 'data.xlsx'
- outputFile = 'data_cleared.xlsx'
- workbook = Workbook()
- workbook.LoadFromFile(inputFile)
- sheet = workbook.Worksheets[0]
- sheet.Range['C6'].Value = ''
- sheet.Range['B6'].ClearContents()
- sheet.Range['D6'].ClearAll()
- workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
- workbook.Dispose()
复制代码
这几种操作的区别在于清除范围:Value = ''将单元格的值设置为空字符串;ClearContents()清除单元格内容并保留单元格位置;ClearAll()清除单元格内容及其格式。它们都不会删除整行或整列,因此不会导致后续数据上移或左移。如果需要移除整行或整列,应使用DeleteRow()或DeleteColumn();如果只是清除部分单元格数据并保留表格布局,则使用ClearContents()或ClearAll()更合适。
实战中可以将空白行列清理逻辑封装成函数,再遍历工作簿中的多张工作表。下面的clean_sheet()用于删除指定工作表中的空白行和空白列:
- from spire.xls import *
- from spire.xls.common import *
- def clean_sheet(sheet):
- for row in range(sheet.LastRow, 0, -1):
- if sheet.Rows[row - 1].IsBlank:
- sheet.DeleteRow(row)
- for col in range(sheet.LastColumn, 0, -1):
- if sheet.Columns[col - 1].IsBlank:
- sheet.DeleteColumn(col)
- inputFile = 'workbook.xlsx'
- outputFile = 'workbook_cleaned.xlsx'
- workbook = Workbook()
- workbook.LoadFromFile(inputFile)
- for sheet in workbook.Worksheets:
- if sheet.IsEmpty:
- continue
- clean_sheet(sheet)
- workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
- workbook.Dispose()
复制代码
这里通过for sheet in workbook.Worksheets遍历工作簿中的所有工作表,并用sheet.IsEmpty跳过空白工作表;对于包含数据的工作表,则调用clean_sheet()执行清理。封装成函数后,清理逻辑可重复用于同一工作簿中的不同工作表,也便于扩展到多个Excel文件:在外层增加文件遍历逻辑,依次加载工作簿、执行清理、保存结果并释放资源即可。
实际处理时还需要注意几点。删除操作应采用倒序遍历,避免删除后索引变化导致漏删;Rows、Columns[j]与DeleteRow()、DeleteColumn()的索引起始位置不同,循环中要正确转换;IsBlank判断的是整行或整列是否为空,若某些行只有格式没有实际数据,是否删除应结合文件情况判断;删除整行或整列可能影响公式引用和合并单元格布局,包含复杂公式或合并单元格的工作表应在清理后检查相关区域;删除会改变工作表结构,建议先另存为新文件,避免误删后无法恢复原始数据;批量处理多个文件时,完成操作后调用Dispose()释放工作簿资源,避免资源持续占用。
总体来看,使用Spire.XLS for Python删除Excel空白行列的核心是:用IsBlank判断整行整列是否为空,用DeleteRow()和DeleteColumn()执行删除;需要全面检查时遍历全部行列,数据集中时结合LastRow和LastColumn缩小范围,位置固定时直接指定起始位置和删除数量;同时区分删除整行整列与清空单元格内容,前者改变工作表结构,后者保留原有位置。将逻辑封装成函数后,可进一步用于多工作表和批量文件的数据清理流程。 |