name: excel-filter description: | Filter Excel data rows by condition — keep or delete rows matching criteria. First uses pandas read-only scan to identify target row numbers, then uses XML direct operations to remove unwanted rows (format-preserving). Supports equals, contains, range, date comparison, and other common filters. 按條件篩選 Excel 資料行——保留或刪除符合條件的行。先用 pandas 只讀掃描確定目標行號，再用 XML 直接操作移除不需要的行（格式無損）。支援等值、包含、範圍、日期比較等常見篩選。 Trigger keywords: "filter" "keep only" "extract" "keep rows matching" "delete rows matching" "by condition" 觸發詞包括"篩選""過濾""只要""提取""保留滿足條件的""刪除滿足條件的""按條件"。

This skill follows [[excel-safe-workflow]] four-step method. Filtering logic uses pandas (fast), deletion uses XML direct ops (fast + format-preserving). 本技能遵循 [[excel-safe-workflow]] 四步法。篩選邏輯用 pandas（快），刪除用 XML 直接操作（快+格式無損）。

Excel Filter / Excel 篩選

Two Modes / 兩種模式

預設是 keep 模式。

第零步：需求解析

條件型別識別

解析示例

第一步：勘察

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, read_only=True)
ws = wb.active
print(f'工作表: {ws.title}, 行: {ws.max_row}, 列: {ws.max_column}')

# 表頭
print('\n=== 表頭 ===')
for col_idx in range(1, min(ws.max_column + 1, 30)):
    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}')

# 資料樣本
print('\n=== 資料樣本(前5行) ===')
for row_idx in range(2, min(7, ws.max_row + 1)):
    vals = []
    for col_idx in range(1, min(6, ws.max_column + 1)):
        v = str(ws.cell(row=row_idx, column=col_idx).value or '')[:40]
        vals.append(v)
    print(f'  行{row_idx}: {" | ".join(vals)}')

wb.close()

第二步：規劃

• 確認篩選列、條件、模式（keep/remove）
• 確認表頭行數（第 1 行是表頭還是有多行表頭）
• keep 模式 → 標記不匹配的行為刪除目標
• remove 模式 → 標記匹配的行為刪除目標

第三步：執行

import pandas as pd
import zipfile, os, shutil, re, time
from lxml import etree

FILE = '目標檔案.xlsx'
HEADER_ROW = 0      # pandas 表頭行索引（通常第1行=0），如果有多行表頭需調整
MODE = 'keep'       # 'keep'=保留匹配行 / 'remove'=刪除匹配行

# ====== 3.1 用 pandas 確定目標行 ======
print(f'① pandas 掃描 ({MODE} 模式)...')
t0 = time.time()

df = pd.read_excel(FILE, header=HEADER_ROW)
total = len(df)
print(f'  總資料行: {total}')

# ══════════════════════════════════════
# 條件配置區 —— 根據需求修改
# ══════════════════════════════════════
COL = '列名'         # 篩選列名
OPERATOR = 'contains'  # eq / ne / gt / gte / lt / lte / contains / isna / between
VALUE = '篩選值'      # 比較值（between 時用 (min, max)；isna 時忽略）

if OPERATOR == 'eq':
    mask = df[COL] == VALUE
elif OPERATOR == 'ne':
    mask = df[COL] != VALUE
elif OPERATOR == 'gt':
    mask = df[COL] > VALUE
elif OPERATOR == 'gte':
    mask = df[COL] >= VALUE
elif OPERATOR == 'lt':
    mask = df[COL] < VALUE
elif OPERATOR == 'lte':
    mask = df[COL] <= VALUE
elif OPERATOR == 'contains':
    mask = df[COL].astype(str).str.contains(VALUE, na=False)
elif OPERATOR == 'isna':
    mask = df[COL].isna()
elif OPERATOR == 'notna':
    mask = df[COL].notna()
elif OPERATOR == 'between':
    mask = (df[COL] >= VALUE[0]) & (df[COL] <= VALUE[1])
# 多條件示例（按需組合）:
# mask = (df['申請人'].str.contains('華為', na=False)) & (df['當前法律狀態'] == '授權')
# ══════════════════════════════════════

match_count = mask.sum()
print(f'  匹配行數: {match_count} ({match_count/total*100:.1f}%)')

if MODE == 'keep':
    delete_count = total - match_count
    delete_indices = df.index[~mask].tolist()
else:  # remove
    delete_count = match_count
    delete_indices = df.index[mask].tolist()

# pandas索引 → Excel行號（+2 = +1表頭 +1 pandas 0-index）
delete_rows = [i + 2 + HEADER_ROW for i in delete_indices]
print(f'  將刪除: {delete_count} 行')
print(f'  將保留: {total - delete_count} 行')
print(f'  掃描耗時: {time.time()-t0:.1f}s')

if delete_count == 0:
    print('無需要刪除的行，結束。')
    exit()

# ====== 3.2 XML 直接刪除 ======
print(f'\n② XML 刪除...')
t0 = time.time()
dup_set = set(delete_rows)

# 備份
BACKUP = FILE.replace('.xlsx', '_backup.xlsx')
if not os.path.exists(BACKUP):
    shutil.copy2(FILE, BACKUP)

# 解壓
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)

# 遍歷 sheet XML
worksheets_dir = os.path.join(TMP, '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'}

    deleted = 0
    for row_elem in root.findall('.//s:row', ns):
        if int(row_elem.get('r')) in dup_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:
            r1, r2 = int(m.group(1)), int(m.group(2))
            if all(r in dup_set for r in range(r1, r2 + 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_elem in root.findall('.//s:row', ns):
            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 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} 行')

# 重新打包
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)

elapsed = time.time() - t0
old_sz = os.path.getsize(BACKUP) / 1024 / 1024
new_sz = os.path.getsize(FILE) / 1024 / 1024
print(f'  刪除耗時: {elapsed:.0f}s, {old_sz:.1f}MB → {new_sz:.1f}MB')

⚠️ 刪除後必須詢問：是否壓實空白行 / Must Ask After Deletion: Compact Blank Rows?

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

"篩選完成，共刪除 X 行。XML 刪除後行號不連續，Excel 開啟會看到空白行。是否壓實行號讓資料連續？"

使用者確認後，執行 [[excel-delete]] 中的壓實步驟（解壓 → 行號重新連續編號 → 公式引用同步更新 → 打包）。

第四步：驗證

print(f'\n③ 驗證...')

# 用 openpyxl 驗證
from openpyxl import load_workbook
wb = load_workbook(FILE, read_only=True, data_only=True)
ws = wb.active
print(f'  當前: {ws.max_row}行 × {ws.max_column}列')

# 公式健康檢查
print(f'  公式健康檢查...')
ref_errors = 0
for row_idx in range(1, min(50, ws.max_row + 1)):
    for col_idx in range(1, min(10, 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'  ❌ {ws.cell(row=row_idx, column=col_idx).coordinate}: {v}')
            ref_errors += 1
if ref_errors == 0:
    print(f'  ✅ 無 #REF!')

wb.close()

# 用 pandas 驗證篩選結果
df2 = pd.read_excel(FILE)
print(f'  結果行數: {len(df2)}')
if MODE == 'keep':
    # 檢查留下的都滿足條件
    if OPERATOR == 'contains':
        not_match = df2[~df2[COL].astype(str).str.contains(VALUE, na=False)]
    elif OPERATOR == 'eq':
        not_match = df2[df2[COL] != VALUE]
    print(f'  不符合條件殘留: {len(not_match)} {"✅" if len(not_match)==0 else "❌"}')

常用條件速查

本技能來自小蔥技能站7w4.net。

# 單條件
df['申請人'] == '華為技術有限公司'                            # 等於
df['申請人'].str.contains('華為', na=False)                   # 包含
df['申請日'] >= '2020-01-01'                                  # 大於等於
df['申請日'].between('2020-01-01', '2023-12-31')              # 範圍
df['申請人'].isna()                                           # 為空

# 多條件
(df['申請人'].str.contains('華為', na=False)) & (df['法律狀態'] == '授權')   # 與
(df['申請人'].str.contains('華為', na=False)) | (df['申請人'].str.contains('騰訊', na=False))  # 或
~(df['申請人'].str.contains('華為', na=False))                               # 非

注意事項

1. 條件用 pandas：pandas 的字串/日期比較比 openpyxl 逐格判斷快幾個數量級
2. 刪除用 XML：格式無損，比 openpyxl 快 10 倍
3. 操作前必備份：遵循 [[excel-safe-workflow]] 第零步——操作前自動備份（時間戳命名），成功後保留最新3份，失誤後立即刪除損壞檔案並從備份恢復
4. 日期列：如果是 Excel 日期（datetime 型別），用 pd.Timestamp('2020-01-01') 比較
5. 空值：isna() 匹配 None/NaN，不會匹配空字串，如需匹配空字串用 df[col] == ''

這個技能功能完善、邏輯清晰，能較好地處理 Excel 篩選需求。技術實現紮實，有備份和驗證機制保證操作安全，中英文雙語支援也很友好。主要不足是缺少示例和引導說明，純文字呈現略顯單調，對於初次使用的使用者來說不夠友好。總體質量良好，但使用者引導和輔助內容還有提升空間。