使用openpyxl删除空行空列触发拆包错误，求解决方法

错误原因 #

你代码里的问题出在ws.calculate_dimension().split(':')：这个方法返回的是类似A1:E6的单元格范围字符串，分割后只会得到两个元素（比如['A1', 'E6']），但你试图直接把它拆成4个变量（min_col, min_row, max_col, max_row），这就导致了“unpack expected 4 got 2”的错误。

修复原代码 #

先把分割后的起始、结束单元格分别拆分，提取对应的行和列：

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# set some values in cells A1 to D4 and E6
ws['A1'] = 'Hello'
ws['B1'] = ','
ws['C1'] = 'world'
ws['D1'] = '!'
ws['E6'] = 'Goodbye'

# 修复维度解析逻辑
dim_parts = ws.calculate_dimension().split(':')
start_cell = dim_parts[0]
end_cell = dim_parts[1]

# 提取起始单元格的列和行
min_col = ord(start_cell[0]) - 64
min_row = int(start_cell[1:])
# 提取结束单元格的列和行
max_col = ord(end_cell[0]) - 64
max_row = int(end_cell[1:])

# 删除空行（原逻辑没问题）
for row in reversed(range(1, max_row + 1)):
    if all(cell.value is None for cell in ws[row]):
        ws.delete_rows(row)

# 删除空列：修复列范围的写法，数字转列字母
from openpyxl.utils import get_column_letter

for col in reversed(range(1, max_col + 1)):
    col_letter = get_column_letter(col)
    if all(cell.value is None for cell in ws[f"{col_letter}:{col_letter}"]):
        ws.delete_cols(col)

wb.save('example.xlsx')

更简洁的替代方案 #

不用纠结calculate_dimension，直接利用ws.max_row和ws.max_column获取当前范围，反向遍历避免删除后索引错乱：

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active

# 测试数据
ws['A1'] = 'Hello'
ws['B1'] = ','
ws['C1'] = 'world'
ws['D1'] = '!'
ws['E6'] = 'Goodbye'

# 删除空行：反向遍历所有行
for row in reversed(range(1, ws.max_row + 1)):
    if all(cell.value is None for cell in ws[row]):
        ws.delete_rows(row)

# 删除空列：反向遍历所有列
for col in reversed(range(1, ws.max_column + 1)):
    col_letter = get_column_letter(col)
    if all(cell.value is None for cell in ws[col_letter]):
        ws.delete_cols(col)

wb.save('example_clean.xlsx')

这个方案逻辑更直接，也能准确删除所有无值的空行和空列。

内容的提问来源于stack exchange，提问作者Ahmed Faisal