name: excel-replace description: | Safely replace content in Excel — entire columns, rows, or condition-matched cells. Supports replacing with fixed values, Excel formulas, or Python transform functions. 安全替換 Excel 中的內容——整列、整行、或指定條件的單元格。支援替換為固定值、Excel 公式、或 Python 轉換函式。 Trigger keywords: "replace column" "overwrite column" "entire column to" "replace row" "batch replace" "conditional replace" "cell replace" "clean" "change all to" 觸發詞包括"替換列""覆蓋列""整列改成""替換行""批次替換""條件替換""單元格替換""清洗""全部改為"。

This skill follows [[excel-safe-workflow]] four-step method. Must complete Requirement Parsing→Scout→Plan before execution, and Verify after. Must confirm before overwriting. 本技能遵循 [[excel-safe-workflow]] 四步法。執行前必須完成 需求解析→勘察→規劃，執行後必須驗證。替換覆蓋前必須確認。

Excel Safe Replace (Column/Row/Cell) / Excel 安全替換（列/行/單元格）

第零步：需求解析

自動識別替換範圍 / Auto-detect Replace Scope

解析要素

解析示例

第一步：勘察

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

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}')

# 定位目標
target_col = None  # 整列模式
target_row = None  # 整行模式
target_cell = None # 單格模式

# 整列模式：定位列號
if SCOPE == 'column':
    if isinstance(target_spec, str):
        for col_idx in range(1, ws.max_column + 1):
            if ws.cell(row=1, column=col_idx).value == target_spec:
                target_col = col_idx
                break
    else:
        target_col = int(target_spec)

    print(f'\n目標列: 列{target_col} "{ws.cell(row=1, column=target_col).value}"')
    # 抽樣展示
    for row in range(2, min(10, ws.max_row + 1)):
        v = ws.cell(row=row, column=target_col).value
        print(f'  行{row}: {repr(v)[:50]}')

# 條件模式：統計匹配數
if SCOPE == 'condition':
    match_count = 0
    for row in range(2, ws.max_row + 1):
        for col in range(1, ws.max_column + 1):
            v = ws.cell(row=row, column=col).value
            if CONDITION(v):  # 使用者定義的條件
                match_count += 1
    print(f'\n條件匹配: {match_count} 個單元格（共 {ws.max_row * ws.max_column} 個）')

# 雙重掃描
print('\n=== 雙重掃描 ===')
wb2 = load_workbook(FILE, data_only=True)
ws2 = wb2.active
# ... 對比 target 區域
wb2.close()

第二步：規劃

XML 方案限制：僅 整列 + value/formula 模式下可用。transform/condition 模式因需要執行 Python 邏輯判斷，不走 XML。

第三步：執行

⚠️ XML 方案必須在 sheet 層 + 列號限定，不碰 sharedStrings。僅整列+值/公式模式下可用。

import time, os, shutil
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# ===== 使用者配置 =====
SCOPE = 'column'       # 'column' / 'row' / 'condition' / 'cell'
TARGET_COL = None      # 整列模式：列號（1-based）
TARGET_ROW = None      # 整行模式
TARGET_CELL = None     # 單格模式: (row, col)
REPLACE_MODE = 'value' # 'value' / 'formula' / 'condition' / 'transform'
NEW_VALUE = None       # value/formula 模式下的新值
# ====================

SIZE_MB = os.path.getsize(FILE) / 1024 / 1024
USE_XML = (SCOPE == 'column' and REPLACE_MODE in ('value', 'formula') and SIZE_MB > 10)

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

    t0 = time.time()
    col_letter = get_column_letter(TARGET_COL)

    TMP = FILE.replace('.xlsx', '_rep_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}

    ws_dir = os.path.join(TMP, 'xl', 'worksheets')
    count = 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

                # 改為 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 = str(NEW_VALUE) if NEW_VALUE is not None else ''
                count += 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'  替換 {count} 個單元格')

    # 打包
    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 方案（預設）======
    t0 = time.time()
    wb = load_workbook(FILE)
    ws = wb.active

    count = 0

    if SCOPE == 'column':
        # 整列替換
        print(f'替換列{TARGET_COL}...')
        for row in range(2, ws.max_row + 1):
            if REPLACE_MODE == 'value':
                ws.cell(row=row, column=TARGET_COL).value = NEW_VALUE
            elif REPLACE_MODE == 'formula':
                ws.cell(row=row, column=TARGET_COL).value = NEW_VALUE  # 以 = 開頭
            elif REPLACE_MODE == 'transform':
                old = ws.cell(row=row, column=TARGET_COL).value
                ws.cell(row=row, column=TARGET_COL).value = transform(old)
            count += 1
            if row % 50000 == 0:
                print(f'  進度: {row}/{ws.max_row}')

    elif SCOPE == 'row':
        # 整行替換
        print(f'替換行{TARGET_ROW}...')
        for col in range(1, ws.max_column + 1):
            if REPLACE_MODE == 'value':
                ws.cell(row=TARGET_ROW, column=col).value = NEW_VALUE
            elif REPLACE_MODE == 'transform':
                old = ws.cell(row=TARGET_ROW, column=col).value
                ws.cell(row=TARGET_ROW, column=col).value = transform(old)
            count += 1

    elif SCOPE == 'condition':
        # 條件替換
        print(f'條件替換: {CONDITION_DESC}...')
        for row in range(1, ws.max_row + 1):
            for col in range(1, ws.max_column + 1):
                val = ws.cell(row=row, column=col).value
                if condition_match(val):
                    if REPLACE_MODE == 'value':
                        ws.cell(row=row, column=col).value = NEW_VALUE
                    elif REPLACE_MODE == 'transform':
                        ws.cell(row=row, column=col).value = transform(val)
                    count += 1
            if row % 50000 == 0:
                print(f'  進度: {row}/{ws.max_row}')

    elif SCOPE == 'cell':
        # 單格替換
        row, col = TARGET_CELL
        ws.cell(row=row, column=col).value = NEW_VALUE
        count = 1
        print(f'替換 {chr(64+col)}{row} → {NEW_VALUE}')

    print(f'替換 {count} 個單元格')
    print(f'\n儲存中...')
    wb.save(FILE)
    wb.close()
    print(f'完成，耗時 {time.time()-t0:.1f}s')

第四步：驗證

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

if SCOPE == 'column':
    # 抽查整列
    print(f'=== 列{TARGET_COL}替換後抽樣 ===')
    for row in [2, 3, 4, ws.max_row // 2, ws.max_row - 2, ws.max_row]:
        v = ws.cell(row=row, column=TARGET_COL).value
        print(f'  行{row}: {repr(v)[:40] if v else "(空)"}')

    # 全部校驗
    if REPLACE_MODE == 'value':
        ok = all(
            ws.cell(row=row, column=TARGET_COL).value == NEW_VALUE
            for row in range(2, ws.max_row + 1)
        )
        print(f'{"✅ 全部一致" if ok else "❌ 存在不一致"}')

elif SCOPE == 'condition':
    # 驗證不再有匹配舊條件的單元格
    remaining = 0
    for row in range(1, ws.max_row + 1):
        for col in range(1, ws.max_column + 1):
            if old_condition(ws.cell(row=row, column=col).value):
                remaining += 1
    print(f'剩餘匹配舊條件的單元格: {remaining} {"✅" if remaining == 0 else "⚠️"}')

wb.close()

注意事項

1. 大檔案整列替換自動走 XML：>10MB 的整列 value/formula 替換使用 sheet 層 XML 快速路徑，快 3-5x
2. XML 方案限定列號：通過 cell.get('r').startswith(col_letter) 限定，不會誤傷其他列
3. XML 方案寫 inline string：替換後的值寫為 <is><t> 內聯字串，不產生新的 sharedString 引用
4. transform 和 condition 模式走 openpyxl：需要 Python 邏輯判斷（自定義函式），XML 無法替代
5. 操作前必備份：遵循 [[excel-safe-workflow]] 第零步——操作前自動備份（時間戳命名），成功後保留最新3份，失誤後立即刪除損壞檔案並從備份恢復

常見場景速查 / Common Scenarios Quick Reference

整列填充固定值 / Entire Column Fill Fixed Value

SCOPE = 'column'; TARGET_COL = 5
REPLACE_MODE = 'value'; NEW_VALUE = '已確認'

整列填充公式 / Entire Column Fill Formula

SCOPE = 'column'; TARGET_COL = 7
REPLACE_MODE = 'formula'; NEW_VALUE = '=C2*D2'

條件替換（空值→0）/ Conditional Replace (Null→0)

SCOPE = 'condition'
def condition_match(val):
    return val is None or (isinstance(val, str) and val.strip() == '')
REPLACE_MODE = 'value'; NEW_VALUE = 0

條件替換（特定值→新值）/ Conditional Replace (Specific→New)

7w4.net收錄了海量優質技能外掛。

SCOPE = 'condition'
def condition_match(val):
    return val == '待審'
REPLACE_MODE = 'value'; NEW_VALUE = '已審'

整行清空 / Entire Row Clear

SCOPE = 'row'; TARGET_ROW = 5
REPLACE_MODE = 'value'; NEW_VALUE = None

單格修改 / Single Cell Edit

SCOPE = 'cell'; TARGET_CELL = (5, 2)  # B5
REPLACE_MODE = 'value'; NEW_VALUE = '總計'

這個 Skill 內容豐富、覆蓋面廣，提供了完整的中英文文件和多場景解決方案，安全性考慮也比較周到（操作前確認、備份恢復等）。但實際程式碼是半成品模板，需要使用者自行修改配置才能使用，對新手不夠友好。如果你是需要立即使用的使用者，可能會失望；如果你有一定基礎，把它當作參考指南還是不錯的。