name: excel-mapping-replace description: | Batch replace column values by mapping table. User provides mapping relationships (spoken in conversation / pasted list / mapping file), and the skill auto-matches and replaces corresponding values in the target column. Values not in the mapping table are preserved as-is. 按對映表批次替換某列的值。使用者提供對映關係（對話直說/貼上列表/對映檔案），技能自動匹配並替換目標列中對應的值。對映表中不存在的值保留原樣。 Trigger keywords: "mapping replace" "batch replace" "by lookup table" "convert codes" "corresponding replace" 觸發詞包括"對映替換""批次替換""按對照表""換成程式碼""對應替換"。

This skill follows [[excel-safe-workflow]] four-step method. Mapping matching uses pandas, value replacement uses openpyxl (small files) or XML (large files). 本技能遵循 [[excel-safe-workflow]] 四步法。對映匹配用 pandas，值替換用 openpyxl（小檔案）或 XML（大檔案）。

Excel Mapping Replace / Excel 對映替換

功能

給一張對映表，把目標列中匹配的值全部替換。

對映表:                          目標列替換前 → 替換後:
  中國 → CN                      中國 → CN
  日本 → JP                      中國 → CN
  美國 → US                      日本 → JP
  德國 → DE                      中國 → CN
  ...                            ...
7w4.net小蔥技能站收錄全網優質技能，值得收藏。

對映表中不存在的值保留原樣，不會丟失資料。

第零步：需求解析

對映關係格式

# 對話直說（幾個對映）
"中國換成CN，日本換成JP，美國換成US"

# 貼上列表（幾十個對映）
中國 → CN
日本 → JP
美國 → US
...

# 對映檔案（幾百個對映）
"用 國家程式碼表.xlsx 的 A列→B列 做對映"

第一步：勘察

import pandas as pd

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

df = pd.read_excel(FILE)
print(f'總行數: {len(df)}')

vc = df[TARGET_COL].value_counts()
print(f'唯一值: {len(vc)}')
for k, v in vc.head(20).items():
    print(f'  {k}: {v}')

第二步：規劃

• 確認目標列和對映表
• 統計有多少行會受影響（對映表 ∩ 列中的值）
• 列出對映表中不存在的值（不會被改動）
• 確認無誤後執行

第三步：執行

⚠️ 禁止在 sharedStrings 層做全域性替換。必須走 sheet 層 + 列號限定，只改目標列的 cell。

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

FILE = '目標檔案.xlsx'
TARGET_COL = '列名'
MAPPING = {'舊值1': '新值1', '舊值2': '新值2', ...}

# ====== 3.1 勘察 ======
df = pd.read_excel(FILE)
col_idx = list(df.columns).index(TARGET_COL) + 1  # 列號（1-based）
col_letter = get_column_letter(col_idx)

# 統計影響
affected = {k: v for k, v in df[TARGET_COL].value_counts().items() if k in MAPPING}
unmatched = {k: v for k, v in df[TARGET_COL].value_counts().items() if k not in MAPPING}

print(f'目標列: {TARGET_COL} ({col_letter}), 將替換:')
for k, v in affected.items():
    print(f'  {k} → {MAPPING[k]}: {v} 行')
if unmatched:
    print(f'\n不在對映表中（保留原值）:')
    for k, v in unmatched.items():
        print(f'  {k}: {v} 行')

# ====== 3.2 執行 ======
USE_XML = os.path.getsize(FILE) > 10 * 1024 * 1024  # >10MB

if USE_XML:
    # ====== XML 方案：sheet 層 + 列號限定 + inline 寫入 ======
    print('\n替換中（XML sheet 層方案）...')
    import zipfile
    from lxml import etree

    t0 = time.time()
    TMP = FILE.replace('.xlsx', '_mp_tmp')
    if os.path.exists(TMP): shutil.rmtree(TMP)
    os.makedirs(TMP)
    with zipfile.ZipFile(FILE, '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 對映（只讀，用於解析 t="s" 的 cell）
    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 ''

                if val is None or val not in MAPPING:
                    continue

                # 改為 inline 字串（不建立新的 sharedString 引用）
                new_val = MAPPING[val]
                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_val
                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(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:.0f}s')

else:
    # ====== openpyxl 方案（小檔案，簡單可靠）======
    print('\n替換中（openpyxl 方案）...')

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

    t0 = time.time()
    wb = load_workbook(FILE)
    ws = wb.active

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

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

第四步：驗證

df2 = pd.read_excel(FILE)
print(f'\n替換後 [{TARGET_COL}] 分佈:')
for k, v in df2[TARGET_COL].value_counts().items():
    marker = ' ← 新' if k in MAPPING.values() else ''
    print(f'  {k}: {v}{marker}')

# 確認未對映值沒被修改
for old_val in unmatched:
    still_there = (df2[TARGET_COL] == old_val).sum()
    if still_there != unmatched[old_val]:
        print(f'  ❌ {old_val}: 預期{unmatched[old_val]}行, 實際{still_there}行')

從對映檔案讀取

# 從另一個 xlsx/csv 讀取對映表
map_df = pd.read_excel('對映檔案.xlsx')
MAPPING = dict(zip(map_df.iloc[:, 0], map_df.iloc[:, 1]))
# 或從 csv
# map_df = pd.read_csv('對映檔案.csv')
# MAPPING = dict(zip(map_df['中文'], map_df['程式碼']))

注意事項

1. 對映表不匹配的值不動：只替換對映表中存在的值，其餘原樣保留
2. 精確匹配：不是包含匹配。中國 只匹配 中國，不匹配 中國北京
3. ⚠️ XML 方案只在目標列上改值：通過列號限定 cell.get('r').startswith(col_letter)，不會誤傷其他列。禁止在 sharedStrings 層做全域性替換
4. 改值後寫 inline string：替換後的值寫為 <is><t> 內聯字串，不產生新的 sharedString 引用
5. 操作前必備份：遵循 [[excel-safe-workflow]] 第零步——操作前自動備份（時間戳命名），成功後保留最新3份，失誤後立即刪除損壞檔案並從備份恢復
6. 檔案被佔用：如果目標檔案正在 Excel 中開啟，會儲存失敗。提示使用者關閉後重試
7. 大小寫敏感：China ≠ china，如需不敏感需預處理

這是一款實用的 Excel 值批次替換技能，操作流程規範，資料安全性有保障，支援多種對映來源，文件清晰易懂。主要優點是替換精準、操作安全、文件完善；不足之處是僅支援精確匹配、不支援模糊查詢，且缺乏批次處理和更友好的互動提示。對於日常簡單的程式碼替換場景足夠好用，但複雜場景功能有限。