Excel Delete

> This skill follows [[excel-safe-workflow]] four-step method. Must complete Requirement Parsing→Scout→Plan before execution, and Verify after. Must check formula dependencies before deletion. > 本技能遵循 [[excel-safe-workflow]] 四步法。执行前必须完成 需求解析→勘察→规划，执行后必须验证。删除前必须检查公式依赖。

Excel Safe Delete (Row & Column) / Excel 安全删除（行列通用）

核心原则

| 模式 | 引擎 | 原因 | |------|------|------| | 行删除 | XML 直接操作 | 快 10 倍，格式/公式无损 | | 列删除 | openpyxl delete_cols() | 列删除需逐行移除 cell，XML 太复杂 |

第零步：需求解析

自动识别删除类型

| 用户说 | 判定 | |--------|:--:| | "删除列""去掉列""移除列""E列""第3列""空列" | → 列模式 | | "删除行""去掉行""移除行""第5行""空行" | → 行模式 |

解析示例

| 用户说 | 提取 | |--------|------| | "把E列删掉" | 列模式, 目标=列E | | "删除第5行到第10行" | 行模式, 目标=[5,6,7,8,9,10] | | "清理所有空行" | 行模式, 自动扫描空行 | | "删掉申请日那一列" | 列模式, 目标=申请日(勘察定位) |

第一步：勘察（含公式依赖检查）

import os, sys
sys.stdout.reconfigure(encoding='utf-8')
from openpyxl import load_workbook

FILE = '目标文件.xlsx'
size_mb = os.path.getsize(FILE) / 1024 / 1024
print(f'文件大小: {size_mb:.1f} MB')

wb = load_workbook(FILE)
ws = wb.active
print(f'工作表: {ws.title}, 行: {ws.max_row}, 列: {ws.max_column}')

# 展示结构
print('\n=== 表头 ===')
for col_idx in range(1, ws.max_column + 1):
    h = ws.cell(row=1, column=col_idx).value
    if h:
        col_letter = chr(64 + col_idx) if col_idx  "删除完成。XML 删除后行号不连续，Excel 中会出现空白行。是否压实行号（重新连续编号）？"

用户确认后执行压实：

```python
# 压实行号：把剩余行重新连续编号，同时更新公式中的行引用
from compact_rows import compact_xlsx
# 或直接用内联版本（见下方）

import re
from lxml import etree

TMP2 = FILE.replace('.xlsx', '_compact_tmp')
os.makedirs(TMP2, exist_ok=True)
with zipfile.ZipFile(FILE, 'r') as z:
    z.extractall(TMP2)

worksheets_dir = os.path.join(TMP2, 'xl', 'worksheets')
parser = etree.XMLParser(remove_blank_text=False, huge_tree=True)

for sf in sorted(os.listdir(worksheets_dir)):
    if not sf.endswith('.xml'): continue
    sp = os.path.join(worksheets_dir, sf)
    tree = etree.parse(sp, parser)
    root = tree.getroot()
    ns = {'s': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}

    # 收集行并构建 old→new 映射
    rows_info = sorted(
        [(int(re.get('r')), re) for re in root.findall('.//s:row', ns)],
        key=lambda x: x[0]
    )
    old_to_new = {}
    next_new = 1
    for old_r, _ in rows_info:
        old_to_new[old_r] = next_new
        next_new += 1

    # 检查是否需要压实
    if all(o == n for o, n in old_to_new.items()):
        continue

    formulas_updated = 0
    for old_r, row_elem in rows_info:
        new_r = old_to_new[old_r]
        if old_r == new_r:
            continue
        row_elem.set('r', str(new_r))
        for cell in row_elem.findall('s:c', ns):
            old_ref = cell.get('r', '')
            m = re.match(r'([A-Z]+)(\d+)', old_ref)
            if m:
                cell.set('r', f'{m.group(1)}{new_r}')
            f_elem = cell.find('s:f', ns)
            if f_elem is not None and f_elem.text:
                new_f = re.sub(r'([A-Z]+)(\d+)',
                    lambda m: f'{m.group(1)}{old_to_new[int(m.group(2))]}' if int(m.group(2)) in old_to_new else m.group(0),
                    f_elem.text)
                if new_f != f_elem.text:
                    f_elem.text = new_f
                    formulas_updated += 1

    # 更新合并单元格
    for mc in root.findall('.//s:mergeCells/s:mergeCell', ns):
        m = re.match(r'([A-Z]+)(\d+):([A-Z]+)(\d+)', mc.get('ref', ''))
        if m and int(m.group(2)) in old_to_new and int(m.group(4)) in old_to_new:
            mc.set('ref', f'{m.group(1)}{old_to_new[int(m.group(2))]}:{m.group(3)}{old_to_new[int(m.group(4))]}')

    # 更新 dimension
    dim = root.find('.//s:dimension', ns)
    if dim is not None and rows_info:
        all_cols = []
        for _, re_elem in rows_info:
            for c in re_elem.findall('s:c', ns):
                m = re.match(r'([A-Z]+)', c.get('r', ''))
                if m: all_cols.append(m.group(1))
        if all_cols:
            max_col = max(all_cols, key=lambda x: (len(x), x))
            dim.set('ref', f'A1:{max_col}{max(old_to_new.values())}')

    sheet_xml = etree.tostring(root, xml_declaration=True, encoding='UTF-8', standalone=True)
    with open(sp, 'wb') as f:
        f.write(sheet_xml)
    print(f'  {sf}: {sum(1 for o,n in old_to_new.items() if o!=n)} 行压实, {formulas_updated} 公式更新')

with zipfile.ZipFile(FILE, 'w', zipfile.ZIP_DEFLATED) as zout:
    for dirpath, _, filenames in os.walk(TMP2):
        for fn in filenames:
            full = os.path.join(dirpath, fn)
            zout.write(full, os.path.relpath(full, TMP2).replace('\\', '/'))
shutil.rmtree(TMP2)
print('压实完成')

列模式 — openpyxl（保持不变）

import time
t0 = time.time()

wb = load_workbook(FILE)
ws = wb.active

# 从右到左删除
for col_idx in sorted(TARGETS, reverse=True):
    header = ws.cell(row=1, column=col_idx).value
    print(f'删除列{col_idx} "{header}"')
    ws.delete_cols(col_idx)

wb.save(FILE)
print(f'完成，耗时 {time.time()-t0:.1f}s，剩余: {ws.max_row}行 × {ws.max_column}列')

第四步：验证

wb = load_workbook(FILE, read_only=True, data_only=True)
ws = wb.active

print(f'当前: {ws.max_row}行 × {ws.max_column}列')

# 公式健康检查
print('\n=== 公式健康检查 ===')
ref_errors = 0
for row_idx in range(1, min(50, ws.max_row + 1)):
    for col_idx in range(1, ws.max_column + 1):
        v = ws.cell(row=row_idx, column=col_idx).value
        if v and isinstance(v, str) and '#REF!' in v:
            print(f'  ❌ 列{col_idx}行{row_idx}: {v}')
            ref_errors += 1
if ref_errors == 0:
    print('  ✅ 无 #REF! 错误')

# 验证被删行确实不存在
if MODE == 'row':
    for tr in TARGETS[:5]:  # 抽查前5个被删行
        v = ws.cell(row=tr, column=1).value
        print(f'  被删行{tr}: {v} (应为None表示已删除)')

wb.close()

特殊场景：自动扫描空行/空列

# 扫描空行（所有列该行值均为 None）
empty_rows = []
for row_idx in range(2, ws.max_row + 1):
    all_empty = True
    for col_idx in range(1, ws.max_column + 1):
        if ws.cell(row=row_idx, column=col_idx).value is not None:
            all_empty = False
            break
    if all_empty:
        empty_rows.append(row_idx)

# 扫描空列（所有数据行该列值均为 None）
empty_cols = []
for col_idx in range(1, ws.max_column + 1):
    all_empty = True
    for row_idx in range(2, ws.max_row + 1):
        if ws.cell(row=row_idx, column=col_idx).value is not None:
            all_empty = False
            break
    if all_empty:
        empty_cols.append(col_idx)

print(f'空行: {empty_rows}, 空列: {empty_cols}')

注意事项

1. 操作前必备份：遵循 [[excel-safe-workflow]] 第零步——删除前自动备份（时间戳命名），成功后保留最新3份，失误后立即删除损坏文件并从备份恢复
2. 行删除用 XML：不调用 delete_rows()，直接操作 sheet XML
3. 列删除用 openpyxl：XML 列删除太复杂，保持原方案
4. 大文件需 lxml：pip install lxml，配合 huge_tree=True
5. 间接引用：INDIRECT、OFFSET 不会被自动检测到
6. 合并单元格：XML 方案自动清理涉及被删行的合并定义

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: YuYY2004
• Source: YuYY2004/excel-skills
• License: MIT

Install and usage instructions live in the source repository linked above.