name: excel-regex-clean description: | Use regular expressions to clean Excel column content — extract, delete, or replace matched portions. Supports three modes: extract (capture matched content), remove (delete matched content), replace (substitute matched content). XML fast path for large files (4x faster). 用正規表示式清理 Excel 某列的內容——提取、刪除或替換匹配的部分。支援三種模式：extract（提取匹配內容）、remove（刪除匹配內容）、replace（替換匹配內容）。大檔案走 XML 快速路徑（快 4x）。 Trigger keywords: "regex" "clean" "extract" "remove numbers" "keep only" "clean up" 觸發詞包括"正則""清理""提取""刪除xxx部分""去掉數字""只保留""清洗"。

This skill follows [[excel-safe-workflow]]. Regex processing uses Python re module. Large files (>10MB) use XML direct ops on sheet XML (4x faster), small files use openpyxl. 本技能遵循 [[excel-safe-workflow]]。正則處理用 Python re 模組。大檔案（>10MB）用 XML 直接操作 sheet XML（快 4 倍），小檔案用 openpyxl。

Excel Regex Clean / Excel 正則清理

Three Modes / 三種模式

第零步：需求解析

常用正則速查 / Common Regex Quick Reference

第一步：勘察

import pandas as pd, re

FILE = '目標檔案.xlsx'
TARGET_COL = '列名'

df = pd.read_excel(FILE)
vc = df[TARGET_COL].value_counts()
print(f'列 [{TARGET_COL}] 唯一值: {len(vc)}')

# 展示前20行 + 變換預覽
MODE = 'extract'       # extract / remove / replace
PATTERN = r'\((.+)\)'  # 正則
REPLACE = ''           # replace 模式時的替換文本

print('\n變換預覽:')
count = 0
for idx, val in df[TARGET_COL].items():
    if pd.notna(val) and count < 20:
        old = str(val)
        if MODE == 'extract':
            m = re.search(PATTERN, old)
            new = m.group(1) if m else old
        elif MODE == 'remove':
            new = re.sub(PATTERN, '', old)
        else:  # replace
            new = re.sub(PATTERN, REPLACE, old)

        if new != old:
            print(f'  {old[:60]}  →  {new[:60]}')
            count += 1

第二步：規劃

• 確認模式和正則，預覽無誤後執行
• 正則不會的讓使用者直接描述需求，自動推斷

第三步：執行

⚠️ XML 方案必須在 sheet 層 + 列號限定，不碰 sharedStrings。

import re, os, shutil, time
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# ===== 使用者配置 =====
FILE = '目標檔案.xlsx'
TARGET_COL = '列名'
MODE = 'extract'       # extract / remove / replace
PATTERN = r'\((.+)\)'  # 正則
REPLACE = ''           # replace 模式時使用
# ====================

df = pd.read_excel(FILE)
col_idx = list(df.columns).index(TARGET_COL) + 1
col_letter = get_column_letter(col_idx)

# 副本（不修改原檔案）
OUT = FILE.replace('.xlsx', '_cleaned.xlsx')
shutil.copy2(FILE, OUT)

SIZE_MB = os.path.getsize(FILE) / 1024 / 1024
USE_XML = SIZE_MB > 10  # >10MB 走 XML 快速路徑

# ====== 正則處理函式 ======
def apply_regex(val):
    old = str(val) if val is not None else ''
    if MODE == 'extract':
        m = re.search(PATTERN, old)
        new = m.group(1) if m else old
    elif MODE == 'remove':
        new = re.sub(PATTERN, '', old)
    else:  # replace
        new = re.sub(PATTERN, REPLACE, old)
    return new, new != old

# ====== XML 快速路徑 ======
if USE_XML:
    print(f'\n替換中（XML sheet 層方案, {SIZE_MB:.0f}MB）...')
    import zipfile
    from lxml import etree

    t0 = time.time()
    TMP = OUT.replace('.xlsx', '_rgx_tmp')
    if os.path.exists(TMP): shutil.rmtree(TMP)
    os.makedirs(TMP)
    with zipfile.ZipFile(OUT, 'r') as z:
        z.extractall(TMP)

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

    # 讀 sharedStrings 建立 si→text 對映（只讀）
    ss_path = os.path.join(TMP, 'xl', 'sharedStrings.xml')
    si_lookup = {}
    if os.path.exists(ss_path):
        ss_tree = etree.parse(ss_path, parser)
        for idx, si_elem in enumerate(ss_tree.findall('.//s:si', ns)):
            t_elem = si_elem.find('s:t', ns)
            si_lookup[idx] = t_elem.text if t_elem is not None else ''

    # 處理 sheet XML — 只在目標列上改值
    ws_dir = os.path.join(TMP, 'xl', 'worksheets')
    replaced = 0
    for sf in sorted(os.listdir(ws_dir)):
        if not sf.endswith('.xml'): continue
        sp = os.path.join(ws_dir, sf)
        tree = etree.parse(sp, parser)
        root = tree.getroot()

        for row_elem in root.findall('.//s:row', ns):
            if row_elem.get('r') == '1': continue  # 跳過表頭
            for cell in row_elem.findall('s:c', ns):
                # 限定列號
                if not cell.get('r', '').startswith(col_letter):
                    continue

                # 獲取當前文本值
                cell_type = cell.get('t', '')
                val = None
                if cell_type == 's':
                    v_elem = cell.find('s:v', ns)
                    if v_elem is not None and v_elem.text:
                        val = si_lookup.get(int(v_elem.text), '')
                else:
                    is_elem = cell.find('s:is', ns)
                    if is_elem is not None:
                        t_elem = is_elem.find('s:t', ns)
                        val = t_elem.text if t_elem is not None else ''
                    else:
                        v_elem = cell.find('s:v', ns)
                        val = str(v_elem.text) if v_elem is not None and v_elem.text else ''

                if val is None:
                    continue

                new, changed = apply_regex(val)
                if not changed:
                    continue

                # 改為 inline 字串
                cell.set('t', 'inlineStr')
                for child in list(cell):
                    tag = child.tag.split('}')[-1]
                    if tag in ('v', 'f', 'is'): cell.remove(child)
                is_new = etree.SubElement(cell, '{'+S_NS+'}is')
                t_new = etree.SubElement(is_new, '{'+S_NS+'}t')
                t_new.text = new
                replaced += 1

        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'  替換 {replaced} 個單元格')

    # 打包
    with zipfile.ZipFile(OUT, '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:.0f}s')

# ====== openpyxl 方案（小檔案）======
else:
    print(f'\n替換中（openpyxl 方案, {SIZE_MB:.0f}MB）...')
    t0 = time.time()
    wb = load_workbook(OUT)
    ws = wb.active

    replaced = 0
    for row in range(2, ws.max_row + 1):
        cell = ws.cell(row=row, column=col_idx)
        new, changed = apply_regex(cell.value)
        if changed:
            cell.value = new
            replaced += 1
        if row % 50000 == 0:
            print(f'  進度: {row}/{ws.max_row}')

    wb.save(OUT)
    wb.close()
    print(f'  替換: {replaced} 個, 耗時: {time.time()-t0:.1f}s')

print(f'輸出: {OUT}')

第四步：驗證

df2 = pd.read_excel(OUT)
print(f'\n處理後 [{TARGET_COL}] 分佈:')
for k, v in df2[TARGET_COL].value_counts().items():
    print(f'  {k}: {v}')

注意事項

1. 副本操作：自動生成 _cleaned.xlsx，不修改原檔案
2. 正則只處理目標列：XML 方案通過列號限定，openpyxl 方案只遍歷目標列，不影響其他列
3. 匹配不到保留原值：extract 模式中正則不匹配的保留原樣
4. 改值後寫 inline string（XML 方案）：替換後的值寫為 <is><t> 內聯字串，不產生新的 sharedString 引用
5. 大檔案自動走 XML：>10MB 或 >5萬行自動使用 XML sheet 層方案，速度快 4 倍
6. 正則需轉義：. ( ) \ 等特殊字元前加 \

   小蔥技能有更好的技能skills外掛。

7. 建議先預覽：看到變換效果後再執行
8. 操作前必備份：遵循 [[excel-safe-workflow]] 第零步——操作前自動備份（時間戳命名），成功後保留最新3份，失誤後立即刪除損壞檔案並從備份恢復

這個 Skill 質量較好，操作步驟清晰明瞭，提供了預覽功能可以先看效果再執行，很貼心。自動備份設計讓人安心，不用擔心誤操作損壞原檔案。不過它需要填寫正規表示式，對技術背景薄弱的使用者有一定門檻，而且文件內容較長、功能較複雜，建議配合更多實際案例學習使用。