Excel多表格合併工具

簡介

將工作簿中的多個表格合併為一個表格，基於指定的兩列（專案名稱及特徵、生產規格）進行匹配彙總，合計件數和數量。

功能特性

• 自動識別各工作表的表頭結構
• 基於兩列完全一致的資料進行合併彙總
• 支援按關鍵字（梁、柱、檔、板、椽、枋、機、戧）排序
• 自動過濾支架、膨脹、螺絲、鍍鋅等無關專案
• 讀取公式計算結果（而非公式本身）

使用場景

當用戶要求將Excel工作簿中的多個發貨清單/統計表合併時使用。

核心程式碼

import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
from collections import defaultdict

def merge_excel_tables(input_file, output_file, ignore_keywords=None, sort_keywords=None):
    """
    合併Excel多表格

    引數:
        input_file: 輸入Excel檔案路徑
        output_file: 輸出Excel檔案路徑
        ignore_keywords: 需要忽略的關鍵詞列表
        sort_keywords: 排序關鍵字優先順序列表
    """
    if ignore_keywords is None:
        ignore_keywords = ['支架', '膨脹', '螺絲', '鍍鋅']

    if sort_keywords is None:
        sort_keywords = ['梁', '柱', '檔', '板', '椽', '枋', '機', '戧']

    # 1. 讀取資料 - 必須使用 data_only=True 獲取計算後的值
    wb = openpyxl.load_workbook(input_file, data_only=True)

    # 2. 用於儲存合併資料
    data_dict = defaultdict(lambda: {'count': [], 'qty': []})

    # 3. 遍歷每個工作表
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]

        # 找到表頭行
        header_row = None
        for row_idx in range(1, min(15, ws.max_row + 1)):
            row_values = [ws.cell(row=row_idx, column=col).value for col in range(1, 12)]
            if '序號' in str(row_values[0]) or '序號' in str(row_values):
                header_row = row_idx
                break

        if header_row is None:
            continue

        # 識別關鍵列
        name_col, spec_col, count_col, qty_col = identify_columns(ws, header_row)

        # 遍歷資料行
        process_data_rows(ws, header_row, name_col, spec_col, count_col, qty_col,
                         data_dict, ignore_keywords)

    wb.close()

    # 4. 排序
    sorted_items = sorted(data_dict.items(), key=lambda x: get_sort_key(x, sort_keywords))

    # 5. 建立新工作簿並寫入
    write_output(sorted_items, output_file)

def identify_columns(ws, header_row):
    """識別關鍵列索引"""
    name_col = spec_col = count_col = qty_col = None

    for col in range(1, ws.max_column + 1):
        header = ws.cell(row=header_row, column=col).value
        if header:
            header_str = str(header).strip()
            if '專案名稱' in header_str or '部件名稱' in header_str:
                name_col = col
            elif '生產規格' in header_str:
                spec_col = col
            elif header_str == '件':
                count_col = col
            elif '數量' in header_str and ('M)' in header_str or 'm)' in header_str):
                qty_col = col

    # 備用方案
    if name_col is None: name_col = 2
    if spec_col is None: spec_col = 4

    return name_col, spec_col, count_col, qty_col

def process_data_rows(ws, header_row, name_col, spec_col, count_col, qty_col,
                      data_dict, ignore_keywords):
    """處理資料行"""
    skip_keywords = ['包裝', '合計', '稅金', '總計', '說明', '甲方',
                     '現場', '施工', '制單人']

    start_row = header_row + 1
    for row_idx in range(start_row, ws.max_row + 1):
        name = ws.cell(row=row_idx, column=name_col).value
        spec = ws.cell(row=row_idx, column=spec_col).value

        name_str = str(name).strip() if name else ''
        spec_str = str(spec).strip() if spec else ''

        # 跳過空行
        if not name_str and not spec_str:
            continue

        # 過濾關鍵詞
        if name_str and any(kw in name_str for kw in ignore_keywords):
            continue
        if spec_str and any(kw in spec_str for kw in ignore_keywords):
            continue

        # 跳過無效行
        if name_str and any(kw in name_str for kw in skip_keywords):
            continue

        # 獲取件數和數量
        count = get_numeric_value(ws.cell(row=row_idx, column=count_col).value) if count_col else 0
        qty = get_numeric_value(ws.cell(row=row_idx, column=qty_col).value) if qty_col else 0

        if count > 0 or qty > 0:
            key = (name_str if name_str else spec_str, spec_str if spec_str else name_str)
            if count > 0:
                data_dict[key]['count'].append(count)
            if qty > 0:
                data_dict[key]['qty'].append(qty)

def get_numeric_value(value):
    """獲取數值"""
    if isinstance(value, (int, float)):
        return value
    if value is None or value == '':
        return 0
    return 0

def get_sort_key(item, sort_keywords):
    """排序鍵"""
    name = item[0][0].lower()

    for i, keyword in enumerate(sort_keywords):
        if keyword in name:
            return (i, name, item[0][1])

    return (len(sort_keywords), name, item[0][1])

def write_output(sorted_items, output_file):
    """寫入輸出檔案"""
    new_wb = openpyxl.Workbook()
    ws = new_wb.active
    ws.title = "合併清單"

    # 表頭
    headers = ['序號', '專案名稱及特徵', '生產規格', '合計件數', '合計數量']
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center', vertical='center')

    # 邊框樣式
    thin_border = Border(
        left=Side(style='thin'), right=Side(style='thin'),
        top=Side(style='thin'), bottom=Side(style='thin')
    )

    # 寫入資料
    for idx, (key, data) in enumerate(sorted_items, 1):
        total_count = sum(data['count'])
        total_qty = sum(data['qty'])

        ws.cell(row=idx+1, column=1, value=idx)
        ws.cell(row=idx+1, column=2, value=key[0])
        ws.cell(row=idx+1, column=3, value=key[1])
        ws.cell(row=idx+1, column=4, value=int(total_count) if total_count == int(total_count) else round(total_count, 2) if total_count > 0 else '')
        ws.cell(row=idx+1, column=5, value=round(total_qty, 2) if total_qty > 0 else '')

        for col in range(1, 6):
            ws.cell(row=idx+1, column=col).border = thin_border

    # 調整列寬
    ws.column_dimensions['A'].width = 8
    ws.column_dimensions['B'].width = 35
    ws.column_dimensions['C'].width = 30
    ws.column_dimensions['D'].width = 15
    ws.column_dimensions['E'].width = 15

    new_wb.save(output_file)

# 使用示例
if __name__ == "__main__":
    input_file = "user_input_files/發貨清單.xlsx"
    output_file = "/workspace/合併發貨清單彙總.xlsx"
    merge_excel_tables(input_file, output_file)
    print(f"已合併 {len(data_dict)} 個不同專案")
小蔥技能有更好的技能skills外掛。

關鍵注意事項

⚠️ 重要：data_only=True

必須使用 data_only=True 讀取Excel檔案，否則會讀到公式字串（如 =F5*3）而非計算結果。

# ✅ 正確：讀取計算後的值
wb = openpyxl.load_workbook(file, data_only=True)

# ❌ 錯誤：讀到公式字串
wb = openpyxl.load_workbook(file)

列識別

不同工作表的列位置可能不同，需要根據表頭動態識別：

• 專案名稱列：表頭包含"專案名稱"或"部件名稱"
• 生產規格列：表頭包含"生產規格"
• 件數列：表頭為"件"
• 數量列：表頭包含"數量"和"M)"或"m)"

資料過濾

自動過濾：

• 忽略詞：支架、膨脹、螺絲、鍍鋅
• 跳過行：包裝、合計、稅金、總計、說明、甲方、現場、施工

輸出檔案

這個工具能較好地完成Excel多表格合併任務，輸出格式整潔，智慧過濾無關專案。但最大的問題是使用不夠方便——使用者需要手動修改程式碼中的檔案路徑才能使用，而不是通過簡單的引數傳入；此外當檔案格式不匹配時可能出錯而沒有提示。適合有程式設計基礎的使用者直接使用或二次開發，對普通使用者不太友好。質量中等偏上，核心功能紮實但易用性有待提升。