name: excel-merge description: | Merge multiple same-structure Excel files into one — validates header consistency then appends rows. Format inherits from the first file. 合併多個同結構的 Excel 檔案為一個——驗證表頭一致後按行追加。格式繼承第一個檔案。 Trigger keywords: "merge" "combine" "concatenate" "consolidate" "merge multiple files" "append" 觸發詞包括"合併""拼接""合在一起""彙總""多個檔案合併""追加"。

This skill follows [[excel-safe-workflow]]. Small files use pandas concat + openpyxl write-back, large files use XML row append. 本技能遵循 [[excel-safe-workflow]]。小檔案用 pandas concat + openpyxl 寫回，大檔案用 XML 行追加。

Excel Merge / Excel 合併

功能

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

檔案1.xlsx (1000行)  ─┐
檔案2.xlsx (800行)   ─┤
檔案3.xlsx (1200行)  ─┼──→ 合併結果.xlsx (3000行)
...                  ─┘

第零步：需求解析

第一步：勘察——驗證表頭一致

import pandas as pd, os

FILES = ['檔案1.xlsx', '檔案2.xlsx', ...]

# 讀表頭
headers = {}
for fp in FILES:
    df = pd.read_excel(fp, nrows=0)
    headers[fp] = list(df.columns)

# 對比
base = headers[FILES[0]]
print(f'基準表頭 ({len(base)} 列): {FILES[0]}')
all_match = True
for fp in FILES[1:]:
    h = headers[fp]
    if h != base:
        print(f'  ❌ {fp}: 表頭不匹配!')
        # 列出差異
        only_base = set(base) - set(h)
        only_this = set(h) - set(base)
        if only_base: print(f'    缺少列: {only_base}')
        if only_this: print(f'    多餘列: {only_this}')
        all_match = False

if not all_match:
    print('請確認是否強制合併（缺失列填空）')

第二步：規劃

• 確認所有檔案表頭一致（不一致時詢問是否強制合併）
• 估算總行數
• 選引擎：總檔案 <10MB 用 pandas，否則用 XML

第三步：執行

小檔案 — pandas + openpyxl

import pandas as pd
from openpyxl import load_workbook
import shutil, os

FILES = ['檔案1.xlsx', ...]
OUTPUT = '合併結果.xlsx'

# 讀取並拼接
dfs = []
total = 0
for fp in FILES:
    df = pd.read_excel(fp)
    dfs.append(df)
    total += len(df)
    print(f'  {os.path.basename(fp)}: {len(df)} 行')

merged = pd.concat(dfs, ignore_index=True)
print(f'合併: {total} 行')

# 用第一個檔案做模板，寫回資料
shutil.copy2(FILES[0], OUTPUT)
wb = load_workbook(OUTPUT)
ws = wb.active

# 清空資料行（保留表頭）
for row in range(2, ws.max_row + 1):
    for col in range(1, ws.max_column + 1):
        ws.cell(row=row, column=col).value = None

# 寫入合併資料（從第2行開始）
for r_idx, row_data in merged.iterrows():
    for c_idx, val in enumerate(row_data):
        ws.cell(row=r_idx + 2, column=c_idx + 1).value = val
    if r_idx % 10000 == 0:
        print(f'  進度: {r_idx}/{total}')

wb.save(OUTPUT)
print(f'輸出: {OUTPUT} ({total} 行)')

大檔案 — XML 行追加（含 sharedStrings inline 化）

⚠️ 關鍵：不同檔案各自有獨立的 sharedStrings.xml，直接拼接 <row> 會導致引用斷裂。 合併時必須將 t="s" 單元格轉為內聯字串，最終輸出空 sharedStrings。

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

FILES = ['檔案1.xlsx', ...]
OUTPUT = '合併結果.xlsx'
S_NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'

# 以第一個檔案為基礎
shutil.copy2(FILES[0], OUTPUT)

# 解壓基礎檔案
TMP = OUTPUT.replace('.xlsx', '_merge_tmp')
if os.path.exists(TMP): shutil.rmtree(TMP)
os.makedirs(TMP)
with zipfile.ZipFile(OUTPUT, 'r') as z:
    z.extractall(TMP)

ws_dir = os.path.join(TMP, 'xl', 'worksheets')
parser = etree.XMLParser(remove_blank_text=False, huge_tree=True)

# 找到主 sheet
sheet_path = None
for sf in sorted(os.listdir(ws_dir)):
    if sf.endswith('.xml') and sf.startswith('sheet'):
        sheet_path = os.path.join(ws_dir, sf)
        break

tree = etree.parse(sheet_path, parser)
root = tree.getroot()
ns = {'s': S_NS}

# 獲取當前最大行號
existing_rows = [int(re.get('r')) for re in root.findall('.//s:row', ns)]
next_row = max(existing_rows) + 1 if existing_rows else 2

# ====== 處理第一個檔案的 inline 化 ======
# 第一個檔案作為基礎也需要 inline 化（它的 sharedStrings 仍指向原檔案）
# 先讀第一個檔案的 sharedStrings
src_ss_path = os.path.join(TMP, 'xl', 'sharedStrings.xml')
si_lookup_first = {}
if os.path.exists(src_ss_path):
    ss_tree = etree.parse(src_ss_path, parser)
    for idx, si in enumerate(ss_tree.findall('.//{'+S_NS+'}si')):
        t = si.find('{'+S_NS+'}t')
        si_lookup_first[idx] = t.text if t is not None else ''

# inline 化第一個檔案的已有行
for row_elem in root.findall('.//s:row', ns):
    for cell in row_elem.findall('s:c', ns):
        if cell.get('t') == 's':
            v_elem = cell.find('s:v', ns)
            if v_elem is not None and v_elem.text:
                si = int(v_elem.text)
                val = si_lookup_first.get(si, '')
                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 = val

# ====== 逐個追加其他檔案的資料行 ======
total_appended = 0
for fp in FILES[1:]:
    print(f'  追加: {os.path.basename(fp)}...')
    # 解壓原始檔
    src_tmp = fp.replace('.xlsx', '_src_tmp')
    if os.path.exists(src_tmp): shutil.rmtree(src_tmp)
    os.makedirs(src_tmp)
    with zipfile.ZipFile(fp, 'r') as z:
        z.extractall(src_tmp)

    # ====== 讀原始檔 sharedStrings ======
    si_lookup = {}
    src_ss_path = os.path.join(src_tmp, 'xl', 'sharedStrings.xml')
    if os.path.exists(src_ss_path):
        src_ss_tree = etree.parse(src_ss_path, parser)
        for idx, si_elem in enumerate(src_ss_tree.findall('.//{'+S_NS+'}si')):
            t_elem = si_elem.find('{'+S_NS+'}t')
            si_lookup[idx] = t_elem.text if t_elem is not None else ''

    src_sheet = os.path.join(src_tmp, 'xl', 'worksheets', 'sheet1.xml')
    src_tree = etree.parse(src_sheet, parser)
    src_root = src_tree.getroot()

    # 找到 <sheetData> 元素
    sheet_data = root.find('.//s:sheetData', ns)
    if sheet_data is None:
        sheet_data = etree.SubElement(root, '{'+S_NS+'}sheetData')

    appended = 0
    for row_elem in src_root.findall('.//s:row', ns):
        r = int(row_elem.get('r'))
        if r == 1:  # 跳過表頭
            continue

        # ====== 關鍵：inline 化所有 t="s" 的 cell ======
        for cell in row_elem.findall('s:c', ns):
            if cell.get('t') == 's':
                v_elem = cell.find('s:v', ns)
                if v_elem is not None and v_elem.text:
                    si = int(v_elem.text)
                    val = si_lookup.get(si, '')
                    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 = val

            # 更新行號引用
            old_ref = cell.get('r', '')
            m = re.match(r'([A-Z]+)(\d+)', old_ref)
            if m:
                cell.set('r', f'{m.group(1)}{next_row}')
            # 更新公式中的行引用
            f_elem = cell.find('s:f', ns)
            if f_elem is not None and f_elem.text:
                offset = next_row - r
                def shift_ref(m):
                    return f'{m.group(1)}{int(m.group(2)) + offset}'
                f_elem.text = re.sub(r'([A-Z]+)(\d+)', shift_ref, f_elem.text)

        row_elem.set('r', str(next_row))
        sheet_data.append(row_elem)
        next_row += 1
        appended += 1

    shutil.rmtree(src_tmp)
    total_appended += appended
    print(f'    追加 {appended} 行 (累計 {total_appended})')

# ====== 寫入空的 sharedStrings（openpyxl 需要它存在）======
empty_ss = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="0" uniqueCount="0"/>'
ss_path_out = os.path.join(TMP, 'xl', 'sharedStrings.xml')
with open(ss_path_out, 'wb') as f:
    f.write(empty_ss.encode('utf-8'))

# 更新 dimension
dim = root.find('.//s:dimension', ns)
if dim is not None:
    all_cols = set()
    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.add(m.group(1))
    if all_cols:
        max_col = max(all_cols, key=lambda x: (len(x), x))
        dim.set('ref', f'A1:{max_col}{next_row - 1}')

# 寫回 + 打包
sheet_xml = etree.tostring(root, xml_declaration=True, encoding='UTF-8', standalone=True)
with open(sheet_path, 'wb') as f:
    f.write(sheet_xml)

with zipfile.ZipFile(OUTPUT, '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'合併完成: {len(FILES)} 檔案 → {OUTPUT} ({total_appended + existing_rows - 1} 行)')

第四步：驗證

import pandas as pd
df = pd.read_excel(OUTPUT).dropna(how='all')
# 驗證總行數、表頭正確

注意事項

1. 表頭必須一致：不一致時先統一再合併
2. sharedStrings 自動 inline 化：合併過程中所有 t="s" 單元格自動轉為內聯字串，最終輸出空 sharedStrings.xml，確保資料不會因索引斷裂而錯亂
3. 公式行號偏移：XML 方案會自動調整追加行的公式引用
4. 大檔案用 XML：>10MB 自動走 XML 追加方案
5. 格式繼承首個檔案：樣式、列寬等取自第一個檔案
6. 操作前必備份：合併是高風險操作——務必在合併前備份所有原始檔。輸出檔案出錯時從備份恢復

這個 Excel 合併工具質量不錯，能自動處理表頭驗證，智慧選擇高效的合併方式，還能保護資料不會因內部引用問題而出錯。操作說明詳細，中英文都有。但遇到列順序不對齊、表格結構特殊的情況時，可能會出問題或需要你手動處理，使用前最好備份檔案。總體適合日常使用，但複雜場景需謹慎。