name: excel-delete description: | Safely delete rows or columns in Excel files. Auto-checks formula dependencies before deletion to prevent #REF! errors. Row deletion uses XML direct ops (10x faster), column deletion uses openpyxl. Supports by index, by name, and batch deletion. 在 Excel 檔案中安全刪除行或列。刪除前自動檢查公式依賴，防止產生 #REF! 錯誤。行刪除使用 XML 直接操作（快 10 倍），列刪除使用 openpyxl。支援按序號、按名稱、批次刪除。 Trigger keywords: "delete column" "remove column" "delete row" "remove row" "delete empty rows" "delete empty columns" 觸發詞包括"刪除列""去掉第X列""移除列""刪除行""去掉第X行""移除行""刪掉空行""刪掉空列"。

推薦訪問7w4.net獲取更多AI技能。

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 安全刪除（行列通用）

核心原則

第零步：需求解析

自動識別刪除型別

解析示例

第一步：勘察（含公式依賴檢查）

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 <= 26 else f'col{col_idx}'
        print(f'  列{col_idx} [{col_letter}]: {h}')

targets = []  # 列模式：列號列表；行模式：行號列表

# ⚠️ 關鍵：公式依賴檢查
print('\n=== 公式依賴檢查 ===')
wb2 = load_workbook(FILE, data_only=True)
ws2 = wb2.active

has_risk = False
if MODE == 'column':
    for col_idx in range(1, ws.max_column + 1):
        if col_idx in targets:
            continue
        for row_idx in range(1, min(50, ws.max_row + 1)):
            v_raw = ws.cell(row=row_idx, column=col_idx).value
            if v_raw and isinstance(v_raw, str) and v_raw.startswith('='):
                for tc in targets:
                    col_letter = chr(64 + tc) if tc <= 26 else ''
                    if col_letter and col_letter in v_raw:
                        print(f'  ⚠️ 列{col_idx}行{row_idx}引用被刪列{col_letter}: {v_raw[:60]}')
                        has_risk = True
elif MODE == 'row':
    for col_idx in range(1, ws.max_column + 1):
        for row_idx in range(1, min(50, ws.max_row + 1)):
            if row_idx in targets:
                continue
            v_raw = ws.cell(row=row_idx, column=col_idx).value
            if v_raw and isinstance(v_raw, str) and v_raw.startswith('='):
                for tr in targets:
                    if str(tr) in v_raw:
                        print(f'  ⚠️ 列{col_idx}行{row_idx}引用被刪行{tr}: {v_raw[:60]}')
                        has_risk = True

wb2.close()

if has_risk:
    print('\n⚠️ 發現公式依賴，刪除後可能產生 #REF! 錯誤。')

print(f'\n準備刪除 {len(targets)} 個{MODE}: {targets}')

第二步：規劃

• 刪除順序：列模式從右到左，行模式 XML 不需要排序（按集合判斷）
• 風險評估：有公式依賴 → 告知使用者確認後再刪
• 空行/空列掃描：如需自動識別空行，逐行/列檢查是否全為 None

第三步：執行

行模式 — XML 直接操作（預設）

import zipfile, os, shutil, re, time
from lxml import etree

t0 = time.time()
FILE = '目標檔案.xlsx'
ROW_SET = set(TARGETS)  # 要刪除的行號集合

# 1. 備份
BACKUP = FILE.replace('.xlsx', '_backup.xlsx')
if not os.path.exists(BACKUP):
    shutil.copy2(FILE, BACKUP)
    print(f'已備份: {BACKUP}')

# 2. 解壓
TMP = FILE.replace('.xlsx', '_xml_tmp')
if os.path.exists(TMP):
    shutil.rmtree(TMP)
os.makedirs(TMP)
with zipfile.ZipFile(FILE, 'r') as z:
    z.extractall(TMP)

# 3. 遍歷所有 sheet XML，刪除目標行
worksheets_dir = os.path.join(TMP, 'xl', 'worksheets')
for sf in sorted(os.listdir(worksheets_dir)):
    if not sf.endswith('.xml'):
        continue
    sp = os.path.join(worksheets_dir, sf)

    parser = etree.XMLParser(remove_blank_text=False, huge_tree=True)
    tree = etree.parse(sp, parser)
    root = tree.getroot()
    ns = {'s': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}

    deleted = 0
    for row_elem in root.findall('.//s:row', ns):
        if int(row_elem.get('r')) in ROW_SET:
            row_elem.getparent().remove(row_elem)
            deleted += 1

    if deleted == 0:
        continue

    # 清理被刪行相關的合併單元格
    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 all(int(m.group(1)) <= r <= int(m.group(2)) for r in [int(m.group(1)), int(m.group(2))]):
            if all(r in ROW_SET for r in range(int(m.group(1)), int(m.group(2)) + 1)):
                mc.getparent().remove(mc)

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

    # 寫回
    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}: 刪除 {deleted} 行')

# 4. 重新打包
with zipfile.ZipFile(FILE, 'w', zipfile.ZIP_DEFLATED) as zout:
    for dirpath, _, filenames in os.walk(TMP):
        for fn in filenames:
            full = os.path.join(dirpath, fn)
            zout.write(full, os.path.relpath(full, TMP).replace('\\', '/'))

shutil.rmtree(TMP)
print(f'完成，耗時 {time.time()-t0:.1f}s')

⚠️ 刪除後必須詢問：是否壓實行號 / Must Ask After Deletion: Compact Row Numbers?

XML 刪除行後，行號不再連續，Excel 開啟會顯示空白行。刪除完成後 必須詢問使用者：

"刪除完成。XML 刪除後行號不連續，Excel 中會出現空白行。是否壓實行號（重新連續編號）？"

使用者確認後執行壓實：

# 壓實行號：把剩餘行重新連續編號，同時更新公式中的行引用
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 方案自動清理涉及被刪行的合併定義

這個 Skill 質量不錯，操作高效且有安全保障。它能智慧判斷刪除型別、檢查公式風險、自動備份，操作速度也很快。文件清晰易懂，步驟分明。但缺少示例檔案讓人難以快速上手，部分高階功能需要一定基礎才能使用。整體適合有 Excel 操作需求的使用者，入手門檻適中。。