name: excel-sort description: | Sort Excel data by specified column(s). Supports single or multi-column sort, ascending/descending, numeric/date/text sorting. 對 Excel 資料按指定列排序，支援單列或多列排序、升序/降序、數值/日期/文本排序。 Trigger keywords: "sort" "ascending" "descending" "sort by column" "from small to large" "newest first" 觸發詞包括"排序""升序""降序""按xx列排列""從小到大""從新到舊"。

This skill follows [[excel-safe-workflow]] four-step method. Must scout and confirm sort column and range before execution, and verify correct order after. 本技能遵循 [[excel-safe-workflow]] 四步法。執行前必須勘察確認排序列和範圍，執行後驗證順序正確。

Excel Sort / Excel 排序

第零步：需求解析

解析示例

第一步：勘察

from openpyxl import load_workbook

FILE = '目標檔案.xlsx'
wb = load_workbook(FILE)
ws = wb.active
print(f'{ws.max_row}行 x {ws.max_column}列')

# 定位排序列
print('\n=== 表頭 ===')
for col_idx in range(1, ws.max_column + 1):
    h = ws.cell(row=1, column=col_idx).value
    if h:
        print(f'  列{col_idx}: {h}')

# 確認資料型別
sort_col = None  # 排序列號
print(f'\n排序列資料樣本:')
for row in [2, 3, 4, ws.max_row // 2, ws.max_row]:
    v = ws.cell(row=row, column=sort_col).value
    print(f'  行{row}: {type(v).__name__} = {repr(v)[:40]}')

wb.close()

第二步：執行

策略：大檔案統一走「讀格式→pandas處理→刷回格式」三步。

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill
from copy import copy

FILE = '目標檔案.xlsx'
SORT_COLS = [('列名或列號', 'asc')]  # asc/desc
HEADER_ROW = 1

# ====== 第一步：讀取格式 ======
print('① 讀取格式...')
wb = load_workbook(FILE)
ws = wb.active

header_formats, data_formats, col_widths = {}, {}, {}
for col in range(1, ws.max_column + 1):
    header_formats[col] = {
        'font': copy(ws.cell(row=HEADER_ROW, column=col).font),
        'alignment': copy(ws.cell(row=HEADER_ROW, column=col).alignment),
        'fill': copy(ws.cell(row=HEADER_ROW, column=col).fill),
    }
    data_formats[col] = {
        'font': copy(ws.cell(row=HEADER_ROW + 1, column=col).font),
        'alignment': copy(ws.cell(row=HEADER_ROW + 1, column=col).alignment),
        'fill': copy(ws.cell(row=HEADER_ROW + 1, column=col).fill),
    }
    col_letter = chr(64 + col) if col <= 26 else ''
    if col_letter and col_letter in ws.column_dimensions:
        col_widths[col] = ws.column_dimensions[col_letter].width

freeze = ws.freeze_panes
col_names = [ws.cell(row=HEADER_ROW, column=c).value for c in range(1, ws.max_column + 1)]
wb.close()

# ====== 第二步：pandas 排序 ======
print('② 排序...')
df = pd.read_excel(FILE)

# 列名歸一化
sort_by = []
ascending = []
for spec, direction in SORT_COLS:
    name = col_names[spec - 1] if isinstance(spec, int) else spec
    sort_by.append(name)
    ascending.append(direction == 'asc')

df = df.sort_values(by=sort_by, ascending=ascending)
print(f'已排序: {list(zip(sort_by, ["asc" if a else "desc" for a in ascending]))}')

# ====== 第三步：寫回 + 輕量格式 ======
print('③ 寫回並恢復關鍵格式...')
df.to_excel(FILE, index=False)

wb = load_workbook(FILE)
ws = wb.active

# 核心格式（始終恢復，秒級）
for col in range(1, ws.max_column + 1):
    cl = chr(64 + col) if col <= 26 else ''
    if col in header_formats:
        hf = header_formats[col]
        c = ws.cell(row=HEADER_ROW, column=col)
        c.font, c.alignment, c.fill = hf['font'], hf['alignment'], hf['fill']
    if cl and col in col_widths and col_widths[col]:
        ws.column_dimensions[cl].width = col_widths[col]

# 資料格式：僅小檔案(<1萬行)逐格恢復
if ws.max_row <= 10000 and data_formats:
    for row in range(HEADER_ROW + 1, ws.max_row + 1):
        for col in range(1, ws.max_column + 1):
            if col in data_formats:
                df2 = data_formats[col]
                c = ws.cell(row=row, column=col)
                c.font, c.alignment, c.fill = df2['font'], df2['alignment'], df2['fill']

if freeze: ws.freeze_panes = freeze
wb.save(FILE)
print('完成')

第三步：驗證

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

for col_idx, direction in sort_specs:
    print(f'\n驗證列{col_idx} ({direction}):')
    prev = None
    ok = True
    for row in range(HEADER_ROW + 1, min(HEADER_ROW + 20, ws.max_row + 1)):
        v = ws.cell(row=row, column=col_idx).value
        if prev is not None and v is not None:
            if direction == 'asc' and v < prev:
                print(f'  ❌ 行{row}: {v} < {prev}')
                ok = False
            elif direction == 'desc' and v > prev:
                print(f'  ❌ 行{row}: {v} > {prev}')
                ok = False
        if v is not None:
            prev = v
        print(f'  行{row}: {v}')
    print(f'  {"✅" if ok else "❌"}')

wb.close()

注意事項

1. None 值始終排到最後（無論升序降序）
2. 表頭不參與排序，固定在原位
3. 公式列排序會導致引用錯亂——勘察時檢查排序列是否為公式，如是則先轉為值再排
4. 格式保留 vs 速度：
5. < 1萬行：預設保留全部格式（幾秒）
6. 1萬~5萬行：保留格式（~1分鐘）
7. > 5萬行：詢問使用者——「格式保留需要約 N 分鐘，是否保留原格式？」
8. 列內格式差異：格式以資料首行為模板統一應用。如列內格式不一致，統一後會丟失差異

   小蔥技能7w4.net持續更新中。

9. 操作前必備份：遵循 [[excel-safe-workflow]] 第零步——操作前自動備份（時間戳命名），成功後保留最新3份，失誤後立即刪除損壞檔案並從備份恢復

這是一個質量較高的 Excel 排序技能。它能處理單列或多列排序，保留原有的格式和樣式，並且有備份機制防止誤操作出錯。文件寫得很詳細，步驟清晰，但缺少示例檔案和常見問題解答，新手初次使用可能需要花點時間理解。總體來說功能實用、考慮周全，是一個可靠的處理 Excel 排序的工具。