查看: 99|回复: 3

Python使用Spire.XLS批量清理Excel空白行列

[复制链接]
发表于 半小时前 | 显示全部楼层 |阅读模式
从系统导出的Excel文件经常夹带空行、空列:数据区域中间有空行会影响筛选和排序范围,空列会改变字段位置,按固定列号读取时容易字段错位,导入数据库时还可能产生无效记录。手工删除少量空白行列不难,但面对多个结构相似的工作簿,重复操作成本较高。本文围绕Spire.XLS for Python,介绍如何用脚本判断并删除空白行、空白列,同时区分删除整行整列与清空单元格内容。

环境准备方面,Spire.XLS for Python用于读取和修改Excel工作簿,不需要本机安装Microsoft Excel。安装命令如下:
  1. pip install Spire.XLS
复制代码

导入模块:
  1. from spire.xls import *
  2. 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()。
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. inputFile = '示例.xlsx'
  4. outputFile = 'cleaned.xlsx'
  5. workbook = Workbook()
  6. workbook.LoadFromFile(inputFile)
  7. sheet = workbook.Worksheets[2]
  8. for i in range(sheet.Rows.Length - 1, -1, -1):
  9.     if sheet.Rows[i].IsBlank:
  10.         sheet.DeleteRow(i + 1)
  11. for j in range(sheet.Columns.Length - 1, -1, -1):
  12.     if sheet.Columns[j].IsBlank:
  13.         sheet.DeleteColumn(j + 1)
  14. workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
  15. workbook.Dispose()
复制代码

这段代码用sheet.Rows.Length和sheet.Columns.Length取得行列数量,用range(..., -1, -1)实现倒序遍历。由于Rows、Columns[j]从0开始,而删除方法从1开始,因此分别传入i + 1和j + 1。该方式会检查工作表的全部行列,适合需要全面清理的场景;如果数据只集中在工作表的一小部分区域,遍历全部行列会产生不必要的开销。

方法二:只检查有数据的区域。如果工作表数据集中在左上角,可以用LastRow和LastColumn取得最后一个有内容的行号和列号,只遍历实际使用区域。
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. inputFile = 'report.xlsx'
  4. outputFile = 'report_cleaned.xlsx'
  5. workbook = Workbook()
  6. workbook.LoadFromFile(inputFile)
  7. sheet = workbook.Worksheets[0]
  8. for row in range(sheet.LastRow, 0, -1):
  9.     if sheet.Rows[row - 1].IsBlank:
  10.         sheet.DeleteRow(row)
  11. for col in range(sheet.LastColumn, 0, -1):
  12.     if sheet.Columns[col - 1].IsBlank:
  13.         sheet.DeleteColumn(col)
  14. workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
  15. workbook.Dispose()
复制代码

这里LastRow和LastColumn分别表示最后一个有内容的行号和列号。循环变量row、col从1开始,因此访问行列对象时减1,调用删除方法时直接使用循环变量。该方法避免遍历大量未使用行列,适合数据范围相对集中的文件。需要注意,最后使用的区域与最后一个有内容的单元格不一定完全相同,例如工作表末尾可能存在只有格式、没有实际数据的单元格。对于这类文件,建议先检查LastRow和LastColumn返回的范围,再决定是否需要进一步清理。

方法三:删除指定位置的连续空白行或空白列。如果空白行列的位置和数量固定,就不必逐行逐列判断,可以直接指定起始位置和删除数量。例如模板每次从第5行开始出现连续4行空白区域,第2列开始有2列不需要的数据,可以这样处理:
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. inputFile = 'template.xlsx'
  4. outputFile = 'template_cleaned.xlsx'
  5. workbook = Workbook()
  6. workbook.LoadFromFile(inputFile)
  7. sheet = workbook.Worksheets[0]
  8. sheet.DeleteRow(5, 4)
  9. sheet.DeleteColumn(2, 2)
  10. workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
  11. workbook.Dispose()
复制代码

DeleteRow(5, 4)表示从第5行开始连续删除4行,DeleteColumn(2, 2)表示从第2列开始连续删除2列。该方式不需要遍历和逐行判断,适合结构固定的Excel模板;如果不同文件中空白区域位置不一致,更适合使用IsBlank判断方式。

方法四:清空单元格内容,而不是删除整行或整列。有时只需要移除单元格中的内容,同时保留原有表格布局,这时应使用清空操作,而不是DeleteRow()或DeleteColumn()。Spire.XLS提供多种清空方式:
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. inputFile = 'data.xlsx'
  4. outputFile = 'data_cleared.xlsx'
  5. workbook = Workbook()
  6. workbook.LoadFromFile(inputFile)
  7. sheet = workbook.Worksheets[0]
  8. sheet.Range['C6'].Value = ''
  9. sheet.Range['B6'].ClearContents()
  10. sheet.Range['D6'].ClearAll()
  11. workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
  12. workbook.Dispose()
复制代码

这几种操作的区别在于清除范围:Value = ''将单元格的值设置为空字符串;ClearContents()清除单元格内容并保留单元格位置;ClearAll()清除单元格内容及其格式。它们都不会删除整行或整列,因此不会导致后续数据上移或左移。如果需要移除整行或整列,应使用DeleteRow()或DeleteColumn();如果只是清除部分单元格数据并保留表格布局,则使用ClearContents()或ClearAll()更合适。

实战中可以将空白行列清理逻辑封装成函数,再遍历工作簿中的多张工作表。下面的clean_sheet()用于删除指定工作表中的空白行和空白列:
  1. from spire.xls import *
  2. from spire.xls.common import *
  3. def clean_sheet(sheet):
  4.     for row in range(sheet.LastRow, 0, -1):
  5.         if sheet.Rows[row - 1].IsBlank:
  6.             sheet.DeleteRow(row)
  7.     for col in range(sheet.LastColumn, 0, -1):
  8.         if sheet.Columns[col - 1].IsBlank:
  9.             sheet.DeleteColumn(col)
  10. inputFile = 'workbook.xlsx'
  11. outputFile = 'workbook_cleaned.xlsx'
  12. workbook = Workbook()
  13. workbook.LoadFromFile(inputFile)
  14. for sheet in workbook.Worksheets:
  15.     if sheet.IsEmpty:
  16.         continue
  17.     clean_sheet(sheet)
  18. workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
  19. 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缩小范围,位置固定时直接指定起始位置和删除数量;同时区分删除整行整列与清空单元格内容,前者改变工作表结构,后者保留原有位置。将逻辑封装成函数后,可进一步用于多工作表和批量文件的数据清理流程。
回复

使用道具 举报

发表于 半小时前 | 显示全部楼层

Re: Python使用Spire.XLS批量清理Excel空白行列

感谢分享,整理得很系统,尤其是“倒序遍历”和“Rows/Columns 从 0 开始、DeleteRow/DeleteColumn 从 1 开始”这两个坑,确实是写这类脚本最容易翻车的地方。 不过方法一的代码有个小问题想提醒一下:循环里判断条件写的是 `sheet.Rows.IsBlank`,没有带上索引 `i`,这样每一轮判断的其实是同一个对象,而不是当前遍历到的那一行,实际跑起来结果可能不对。列的部分同理。应该写成 `sheet.Rows.IsBlank` 和 `sheet.Columns.IsBlank` 才符合注释里说的逻辑。方法二里用 `sheet.Rows[row - 1].IsBlank` 就是对的,可以对照着改一下。 另外两点觉得挺实用:一是方法二用 LastRow/LastColumn 限定范围,数据集中在左上角时确实能省不少遍历;二是关于“只有格式没有实际数据的单元格会影响最后使用区域”的提醒,实际处理从系统导出的表时经常遇到,建议先在脚本里打印一下这两个值再决定用哪种清理策略。 方法四好像被截断了,清空单元格内容那部分只显示到 import 就没了,方便的话可以补全一下,正好和“删除整行整列”做个对比,对需要保留表格布局的场景会很有帮助。
回复 支持 反对

使用道具 举报

发表于 半小时前 | 显示全部楼层

Re: Python使用Spire.XLS批量清理Excel空白行列

这篇整理挺实用的,把删除空白行列时最容易踩的两个点讲清楚了:Rows、Columns 的索引从 0 开始,而 DeleteRow、DeleteColumn 从 1 开始;删除后索引会变化,所以要倒序遍历。方法一和方法二的取舍也说得很明白,全面清理适合结构不定的文件,只遍历 LastRow 和 LastColumn 更省开销,但确实要注意末尾仅有格式的单元格可能影响范围判断。方法三对固定模板很直接,方法四区分“清空内容”和“删除整行整列”也很关键。不过方法四的代码好像没贴完整,后面几种清空方式如果能补充完就更好了。
回复 支持 反对

使用道具 举报

发表于 半小时前 | 显示全部楼层

Re: Python使用Spire.XLS批量清理Excel空白行列

这篇挺实用的,尤其是把“倒序遍历”和 Rows/Columns 从 0 开始、DeleteRow/DeleteColumn 从 1 开始这两个容易踩坑的点讲清楚了,方法二用 LastRow、LastColumn 限定范围也比全表遍历更合理。 我有几个小建议:批量处理前最好先备份原文件,并对每个工作簿加异常处理,避免某个文件结构异常导致整批中断;方法二里 LastRow 和 LastColumn 可能受格式影响,正式删除前可以先输出范围确认;方法三适合固定模板,但最好加一句“位置变化时不要用固定删除”;方法四正文好像没发完,期待补全清空单元格和删除整行整列的区别,以及清空时是否保留格式的说明。整体思路很清晰,适合拿来改成批量处理脚本。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

指导单位

江苏省公安厅

江苏省通信管理局

浙江省台州刑侦支队

DEFCON GROUP 86025

Hacking Group 021A

旗下站点

态势感知中心

应急响应中心

红盟安全

联系我们

官方QQ群:112851260

官方邮箱:security#ihonker.org(#改成@)

官方核心成员

关注微信公众号

Archiver|手机版|小黑屋| ( 沪ICP备2021026908号 )

GMT+8, 2026-11-19 02:47 , Processed in 0.020143 second(s), 18 queries , Gzip On, Redis On.

Powered by ihonker.com

Copyright © 2015-现在.

  • 返回顶部