使用 Python 删除 Excel 中的空白行或空白列

0 阅读10分钟

从系统导出的 Excel 文件往往包含一些无用的空白行和空白列。例如,数据区域中间夹杂着空行,表格末尾保留了多余的空白行,或者右侧存在没有内容的辅助列。这些空白区域不仅影响表格的整洁度,还可能导致筛选范围不连续、数据读取异常,甚至在导入数据库时产生无效记录。

手动删除少量空白行列并不困难,但如果需要处理多个结构相似的工作簿,重复操作就会耗费不少时间。使用 Python 编写脚本,可以按照统一规则自动检查和删除空白行列,也方便将清理过程集成到日常数据处理流程中。

本文将使用 Spire.XLS for Python 演示如何删除 Excel 中的空白行和空白列,包括遍历工作表、限定检查范围、删除指定位置的连续行列,以及清空单元格内容等操作。最后,还会将清理逻辑封装成函数,方便在多个工作表中重复使用。

为什么需要删除 Excel 中的空白行和空白列?

Excel 中多余的空白行列可能带来以下问题:

  • 影响数据筛选和排序: 数据区域中间存在空行时,Excel 可能无法正确识别连续的数据范围,影响筛选和排序操作。
  • 增加数据处理难度: 空列会改变字段所在的位置,如果程序按照固定列号读取数据,可能导致字段对应错误。
  • 产生无效记录: 将 Excel 导入数据库或其他系统时,空白行可能被识别为无效记录,增加后续清理工作。
  • 影响数据核对: 多余的空白行列会扩大工作表的使用范围,给数据浏览和核对带来不便。

需要注意,删除空白行列与清空单元格内容是两种不同的操作。删除整行或整列会改变工作表结构,后续数据会相应上移或左移;清空内容则会保留原有行列位置。本文主要介绍如何删除整行和整列,并在后续单独说明清空内容的用法。

环境准备

本文使用 Spire.XLS for Python 读取和修改 Excel 工作簿,无需在计算机上安装 Microsoft Excel。

首先,通过 pip 安装:

pip install Spire.XLS

然后导入所需模块:

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

了解空白行列的判断方式和索引规则

在编写删除逻辑之前,需要了解两个关键点:如何判断空白行列,以及删除时如何处理索引变化。

Spire.XLS 提供了 IsBlank 属性,可以判断整行或整列是否为空:

  • sheet.Rows[i].IsBlank:判断指定行是否为空。
  • sheet.Columns[j].IsBlank:判断指定列是否为空。

这里判断的是整行或整列,而不是单个单元格。只要行或列中仍有内容,就不会被视为空白。

另一个需要注意的是索引规则。通过 Rows[i] 或 Columns[j] 访问行列时,索引从 0 开始;而 DeleteRow() 和 DeleteColumn() 的参数从 1 开始。例如,删除第 1 行需要调用 DeleteRow(1)。

此外,删除行列会改变后续数据的位置。如果从前往后遍历,删除当前行后,后面的行会向上移动,原有索引随之变化,容易造成漏删。因此,删除空白行列时应采用倒序遍历,从最后一行或最后一列开始处理。

方法一:删除工作表中的所有空白行和空白列

首先来看如何检查整张工作表,并删除其中所有空白行和空白列。

下面的示例加载 Excel 文件,获取第三张工作表,然后从后往前遍历所有行和列。遇到空白行或空白列时,分别调用 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()

移除 Excel 中的空白行或列.png

这里使用 sheet.Rows.Length 和 sheet.Columns.Length 获取工作表的行列数量,并通过 range(..., -1, -1) 实现倒序遍历。

由于 Rows[i] 和 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 返回的范围,再确定是否需要进一步清理。

方法三:删除指定位置的连续空白行或空白列

如果空白行列的位置和数量是固定的,就不必逐行逐列判断,可以直接指定起始位置和删除数量。

例如,某个 Excel 模板每次都会在第 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]

# 从第 5 行开始删除 4 行
sheet.DeleteRow(5, 4)

# 从第 2 列开始删除 2 列
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()。

实战:封装空白行列清理函数

前面的示例主要针对工作簿中的第一张工作表。如果一个 Excel 文件包含多个工作表,可以将删除逻辑封装成函数,再遍历整个工作簿,逐张清理。

下面的示例定义了 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 文件。只需在外层增加文件遍历逻辑,依次加载工作簿、执行清理、保存结果并释放资源即可。

实用技巧与注意事项

在实际清理 Excel 文件时,还需要注意以下几点:

删除操作应采用倒序遍历。 删除一行或一列后,后续数据的位置会发生变化。从后往前删除可以避免索引变化导致漏删。

注意行列索引的起始位置。 Rows[i] 和 Columns[j] 使用从 0 开始的索引,而 DeleteRow() 和 DeleteColumn() 使用从 1 开始的编号。编写循环时需要正确转换。

明确空白行列的判断标准。 IsBlank 用于判断整行或整列是否为空。如果某些行只有格式、没有实际数据,是否需要删除应根据文件的具体情况判断。

检查公式和合并单元格。 删除整行或整列可能影响公式引用和表格布局。对于包含复杂公式或合并单元格的工作表,建议在清理后检查相关区域。

先保存副本再清理。 删除操作会改变工作表结构,建议将结果另存为新文件,避免误删后无法恢复原始数据。

及时释放工作簿资源。 完成操作后调用 Dispose(),尤其是在批量处理多个文件时,可以避免资源持续占用。

总结

使用 Spire.XLS for Python 删除 Excel 中的空白行和空白列,可以通过 IsBlank 判断整行或整列是否为空,再调用 DeleteRow() 和 DeleteColumn() 执行删除。对于需要全面检查的工作表,可以遍历全部行列;如果只需处理实际使用区域,则可以结合 LastRow 和 LastColumn 缩小范围;如果空白区域的位置固定,也可以直接指定起始位置和删除数量。此外,还需要区分删除整行整列与清空单元格内容:前者会改变工作表结构,后者则保留原有位置。将这些操作封装成函数后,还可以进一步扩展到多工作表和批量文件处理,让 Excel 数据清理更加高效。