Python Excel删除空行空列：清理脏数据一键搞定

用openpyxl扫描工作表，整行全空就删、整列全空就删，从下往上删避免行号错乱，附完整代码。

场景痛点

从系统导出的表，中间夹着好多空行，末尾还拖着几列全是空的辅助列；直接发给别人不专业，手动删又怕删到数据。用 openpyxl 扫一遍，整行所有单元格都空就删、整列全空就删，从下往上操作，干净利落。

用到的库

pip install openpyxl

完整代码

from openpyxl import load_workbook


def is_row_empty(ws, row_idx: int, max_col: int) -> bool:
for col in range(1, max_col + 1):
v = ws.cell(row=row_idx, column=col).value
if v is not None and str(v).strip() != "":
return False
return True


def is_col_empty(ws, col_idx: int, max_row: int) -> bool:
for row in range(2, max_row + 1):  # 跳过表头，表头有列名不算空
v = ws.cell(row=row, column=col_idx).value
if v is not None and str(v).strip() != "":
return False
return True


def clean_empty(file_path: str) -> None:
wb = load_workbook(file_path)

for ws in wb.worksheets:
max_row, max_col = ws.max_row, ws.max_column

# 1) 从下往上删空行（保留第 1 行表头）
for row_idx in range(max_row, 1, -1):
if is_row_empty(ws, row_idx, max_col):
ws.delete_rows(idx=row_idx, amount=1)

# 2) 从右往左删空列
for col_idx in range(max_col, 0, -1):
if is_col_empty(ws, col_idx, ws.max_row):
ws.delete_cols(idx=col_idx, amount=1)

print(f"[{ws.title}] 清理完成，剩余 {ws.max_row} 行 {ws.max_column} 列")

wb.save(file_path)
print("空行空列已清理")


if __name__ == "__main__":
clean_empty(r"D:\reports\销售明细.xlsx")