name: excel-split description: | Split a large table into multiple files by a specified column (one file per unique value). Uses pandas grouping + iterparse streaming fan-out (single XML parse, multi-output), with format fully preserved. Supports limiting split count (Top N + Others). 按指定列將一個大表拆分成多個檔案（每個值一個檔案）。用 pandas 分組 + iterparse 流式分流（一次解析XML，多路輸出），格式完整保留。支援限制拆分數量（Top N + 其他）。 Trigger keywords: "split" "separate" "split by" "split into files" "break apart by" 觸發詞包括"拆分""拆開""按xx分開""分表""拆成多個檔案"。

This skill follows [[excel-safe-workflow]] four-step method. Grouping uses pandas, fan-out uses lxml iterparse single-scan multi-output. 本技能遵循 [[excel-safe-workflow]] 四步法。分組用 pandas，分流用 lxml iterparse 一次掃描多路輸出。

Excel Split / Excel 拆分

功能

把一張大表按某列的值拆成 N 個獨立檔案。

總表 (33萬行)
  │
  │ 按"申請人"拆分 Top 10
  │
  ├── 上海諾基亞貝爾.xlsx    (1859行)
  ├── 上海泰康網路.xlsx      (1301行)
  ├── ... (8個)
  └── 其他.xlsx              (283145行)

第零步：需求解析

第一步：勘察

import pandas as pd

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

df = pd.read_excel(FILE)
counts = df[SPLIT_COL].value_counts()
print(f'總行數: {len(df)}, 唯一值: {len(counts)}')
print(f'Top 10:')
for k, v in counts.head(10).items():
    print(f'  {k}: {v} 行')

第二步：規劃

• Top N 限制：唯一值太多時（>50），只拆 Top N，其餘合併為"其他"
• 先壓實：如果檔案之前做過去重/篩選（有行號空隙），先壓實再拆分，否則 pandas 掃描行數會偏高
• 輸出目錄：{原檔名}_split/
• 檔案命名：{拆分值}.xlsx（自動清理非法字元）

第三步：執行

核心思路：一次 iterparse 流式解析 XML，按行分流到各輸出緩衝區，避免重複解析。

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

S_NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'

FILE = '目標檔案.xlsx'
SPLIT_COL = '列名'
TOP_N = 20
OUTPUT_DIR = FILE.replace('.xlsx', f'_split_{SPLIT_COL}')

# ====== 3.1 pandas 分組 ======
print(f'[1/4] pandas 分組...')
df = pd.read_excel(FILE).dropna(how='all')  # 去掉空行（如有間隙）
total = len(df)
counts = df[SPLIT_COL].value_counts()
top_keys = set(counts.head(TOP_N).index.tolist()) if len(counts) > TOP_N else set(counts.index)

row_to_file = {}
file_sizes = defaultdict(int)
for key in top_keys:
    safe = str(key).replace('/', '_').replace('\\', '_').replace(':', '_')[:80]
    indices = df.index[df[SPLIT_COL] == key].tolist()
    for i in indices:
        row_to_file[i + 2] = f'{safe}.xlsx'
    file_sizes[f'{safe}.xlsx'] = len(indices)

other = df.index[~df[SPLIT_COL].isin(top_keys)].tolist()
if other:
    for i in other:
        row_to_file[i + 2] = '其他.xlsx'
    file_sizes['其他.xlsx'] = len(other)

print(f'  將生成 {len(file_sizes)} 個檔案')

# ====== 3.2 解壓 ======
print(f'[2/4] 解壓...')
TMP = FILE.replace('.xlsx', '_split_tmp')
if os.path.exists(TMP): shutil.rmtree(TMP)
os.makedirs(TMP)
with zipfile.ZipFile(FILE, 'r') as z:
    z.extractall(TMP)

worksheets_dir = os.path.join(TMP, 'xl', 'worksheets')
orig_sheet = None
for sf in sorted(os.listdir(worksheets_dir)):
    if sf.endswith('.xml') and sf.startswith('sheet'):
        orig_sheet = os.path.join(worksheets_dir, sf)
        break

# ====== 3.3 iterparse 流式分流 ======
print(f'[3/4] 流式分流...')
row_xml = defaultdict(list)
header_xml = []

tag = f'{{{S_NS}}}row'
for event, elem in etree.iterparse(orig_sheet, tag=tag):
    r = int(elem.get('r'))
    row_str = etree.tostring(elem, encoding='unicode')

    if r == 1:  # 表頭行
        header_xml.append(row_str)
    elif r in row_to_file:
        row_xml[row_to_file[r]].append(row_str)

    elem.clear()
    while elem.getprevious() is not None:
        del elem.getparent()[0]

# ====== 3.4 生成輸出檔案 ======
print(f'[4/4] 生成輸出檔案...')
os.makedirs(OUTPUT_DIR, exist_ok=True)

# 構建 sheet XML 模板（<sheetData> 前後的結構）
tree_orig = etree.parse(orig_sheet, etree.XMLParser(huge_tree=True))
full_xml = etree.tostring(tree_orig.getroot(), encoding='unicode')
sd_start = full_xml.find('<sheetData')
sd_end = full_xml.find('</sheetData>')
prefix = full_xml[:sd_start]
suffix = full_xml[sd_end + len('</sheetData>'):]

for idx, (fname, rows) in enumerate(sorted(row_xml.items(), key=lambda x: -len(x[1]))):
    fpath = os.path.join(OUTPUT_DIR, fname)
    all_rows = ''.join(header_xml) + ''.join(rows)
    new_xml = f'{prefix}<sheetData>{all_rows}</sheetData>{suffix}'

    with open(orig_sheet, 'w', encoding='utf-8') as f:
        f.write(new_xml)

    with zipfile.ZipFile(fpath, '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'完成，輸出: {OUTPUT_DIR}/')

第四步：驗證

import pandas as pd, os

total_out = 0
for f in os.listdir(OUTPUT_DIR):
    if not f.endswith('.xlsx'): continue
    fp = os.path.join(OUTPUT_DIR, f)
    df = pd.read_excel(fp).dropna(how='all')
    total_out += len(df)

    if f != '其他.xlsx':
        key = f.replace('.xlsx', '')
        bad = df[df[SPLIT_COL] != key].shape[0]
        if bad: print(f'  ❌ {f}: {bad} 行錯配')

print(f'輸出總行: {total_out} (期望 {total})')

效能

生成打包階段耗時較長是因為每個輸出檔案都包含完整的 sharedStrings.xml（273MB），11 個檔案約 3GB 壓縮量。迭代次數越多，此階段越慢。

注意事項

1. 拆分前先壓實：如果檔案做過去重/篩選有行號空隙，先用 [[excel-delete]] 中的壓實功能
2. 共享字串膨脹：每個輸出檔案繼承完整的 sharedStrings，N 個檔案 = N × 原始大小
3. 大檔案建議限制 Top N：預設 Top 20，避免生成數百個檔案

   7w4.net小蔥技能站，你的AI助手技能庫。

4. iterparse 記憶體友好：一次只保有一個 row 元素，33萬行約佔用 100-200MB 記憶體
5. 操作前必備份：遵循 [[excel-safe-workflow]] 第零步——拆分前自動備份原檔案（時間戳命名），成功後保留最新3份

這是一個質量優秀的 Excel 拆分技能。文件清晰易懂，步驟分明，新手也能快速上手；功能描述準確，注意事項說明詳細，讓人用起來放心。唯一的不足是沒有提供示例檔案，新使用者可能需要自己準備測試資料才能更好地理解和使用。整體推薦度較高。