📘 SKU Excel 轉換程式使用說明

📖 簡介

此工具用於 電商訂單 Excel 檔案的轉換與處理，支援 SKU 對應、總金額與折扣計算、預交貨日自動填寫及溫層判斷。
程式以 Python 撰寫，操作時會先選取 Excel 檔，再進行自動處理與輸出。

🛠 功能特色

預交貨日設定
- 啟動程式時輸入「加幾天」，自動計算「新預交貨日」。
SKU 對應與數量換算
- 根據 sku_cb.json 的對照表，自動替換新 SKU 並調整數量與價格。
付款方式調整
- 例如將「全家冷凍C2C貨到付款」自動轉為「全冷C2C貨到付款」。
金額與折扣計算
- 若檔案內有「總額」欄位，直接引用。
- 否則以「數量 × 商品售價」計算。
- 輸出新總金額與「新折扣 = 原始總額 - 新總金額」。
溫層判斷
- 根據 plist.json 的 SKU 溫層資訊，決定「常溫」或「冷凍」。
- 若同訂單內產品全為「常溫」，則設定為「常溫」，否則為「冷凍」。
備註處理
- 自動合併「購買人名稱」與「收件人名稱」，加註 (寄)。
- 移除「如有送禮需求請幫忙註明送禮」字樣。
輸出 Excel 檔案
- 轉換後檔案以時間戳記命名，例如：

訂單資料_Sku轉換_20250801_143000.xls

📂 必要檔案

輸入檔案：
- 電商訂單 Excel（.xls / .xlsx）
SKU 對照表：
- \\nas-lianruey\office\sku\sku_cb.json
溫層資訊：
- \\nas-lianruey\office\sku\plist.json

📐 新增欄位

程式會在輸出檔案中自動新增以下欄位：

新SKU
新數量
新進價
新預交貨日
溫層
分錄備註
新總金額
新折扣
新備註

⚙️ 操作流程

執行程式。
輸入預交貨日加幾天（預設 1 天）。
選擇欲轉換的 Excel 訂單檔案。
程式自動進行 SKU 轉換、金額與折扣計算、溫層判斷與備註處理。
完成後在原始檔案目錄下輸出新檔案。

✅ 輸出結果

已完成 SKU 轉換、預交貨日、溫層、分錄備註、總金額與折扣計算。
結果會顯示於新檔案中，保留原始訂單資訊並新增計算欄位。

⚙️ 程式原始碼

import os

import json

import re

import pyexcel as pe

import requests

from tkinter import Tk, filedialog, simpledialog

from datetime import datetime, timedelta

from collections import defaultdict

root = Tk()

root.withdraw()

days_to_add = simpledialog.askinteger("預交貨日設定", "請輸入要加幾天（預設為1）", initialvalue=1)

if not days_to_add:
 
    days_to_add = 1

file_path = filedialog.askopenfilename(
 
    title="選擇 Excel 檔案",
 
    filetypes=[("Excel 檔案", "*.xls *.xlsx")]

)

if not file_path:
 
    print("❌ 未選擇檔案，程式結束。")
 
    exit()

sku_json_path = r"\\nas-lianruey\office\sku\sku_cb.json"

if not os.path.isfile(sku_json_path):
 
    print(f"❌ 找不到 SKU 對照表檔案：{sku_json_path}")
 
    exit()

with open(sku_json_path, "r", encoding="utf-8") as f:
 
    sku_map = json.load(f)

with open(file_path, "rb") as f:
 
    file_content = f.read()

file_ext = os.path.splitext(file_path)[1].lower()

file_type = "xlsx" if file_ext == ".xlsx" else "xls"

records = pe.get_array(file_type=file_type, file_content=file_content)

next_date = (datetime.today() + timedelta(days=days_to_add)).strftime("%Y%m%d")

header = records[0]

try:
 
    order_index = header.index("訂單編號")
 
    status_index = header.index("訂單狀態")
 
    item_index = header.index("SKU")
 
    qty_index = header.index("數量")
 
    price_index = header.index("商品售價")
 
    address_index = header.index("收件人地址")
 
    name_index = header.index("收件人名稱")

 

    if "轉單編號" not in header:
 
        header.append("轉單編號")
 
        for i in range(1, len(records)):
 
            records[i].append(str(records[i][order_index]))
 
    transfer_index = header.index("轉單編號")

except ValueError as e:
 
    print("❌ 缺少必要欄位：", e)
 
    exit()

# 🟡 替換付款方式

if "付款方式" in header:
 
    payment_index = header.index("付款方式")
 
    for i in range(1, len(records)):
 
        if str(records[i][payment_index]).strip() == "全家冷凍C2C貨到付款":
 
            records[i][payment_index] = "全冷C2C貨到付款"

total_amount_col_index = -1

try:
 
    total_amount_col_index = header.index("總額")
 
    print("✅ 找到原始「總額」欄位。")

except ValueError:
 
    print("ℹ️ 未找到原始「總額」欄位，將根據「數量」和「商品售價」計算。")

order_total_map = {}

if total_amount_col_index != -1:
 
    for row in records[1:]:
 
        order_no = str(row[order_index])
 
        try:
 
            total_value = float(row[total_amount_col_index])
 
            order_total_map[order_no] = total_value
 
        except (ValueError, TypeError):
 
            pass

original_total_amount_by_order = defaultdict(float)

unique_order_nos = {str(row[order_index]) for row in records[1:]}

for order_no in unique_order_nos:
 
    if order_no in order_total_map:
 
        original_total_amount_by_order[order_no] = order_total_map[order_no]
 
    else:
 
        order_total_calculated = 0
 
        for row in records[1:]:
 
            if str(row[order_index]) == order_no:
 
                try:
 
                    qty = int(row[qty_index])
 
                    price = float(row[price_index])
 
                    order_total_calculated += qty * price
 
                except (ValueError, TypeError):
 
                    pass
 
        original_total_amount_by_order[order_no] = order_total_calculated

total_amount_by_order = defaultdict(float)

for row in records[1:]:
 
    order_no = str(row[order_index])
 
    product_id = str(row[item_index])
 
    try:
 
        original_qty = int(row[qty_index])
 
    except:
 
        original_qty = 0

 

    if product_id in sku_map:
 
        for sku in sku_map[product_id]:
 
            try:
 
                mapped_qty = int(sku["新數量"])
 
            except:
 
                mapped_qty = 0
 
            final_qty = mapped_qty * original_qty
 
            try:
 
                new_price = float(sku["新進價"])
 
            except ValueError:
 
                new_price = 0.0
 
            item_total = final_qty * new_price
 
            total_amount_by_order[order_no] += item_total
 
    else:
 
        try:
 
            new_price = float(row[price_index])
 
        except ValueError:
 
            new_price = 0.0
 
        item_total = original_qty * new_price
 
        total_amount_by_order[order_no] += item_total

plist_url = r'\\nas-lianruey\office\sku\plist.json'



try:
 
    with open(plist_url, "r", encoding="utf-8") as f:
 
        temp_data = json.load(f)

except FileNotFoundError:
 
    print(f"❌ 找不到溫層資料檔案：{plist_url}")
 
    exit()

except json.JSONDecodeError as e:
 
    print(f"❌ 無法解析溫層資料檔案：{e}")
 
    exit()

sku_temp_map = {}

for item in temp_data:
 
    sku = item.get("sku")
 
    temp = item.get("temp", "").strip() or "冷凍"
 
    if sku:
 
        sku_temp_map[sku] = temp

fill_value_columns = [
 
    "收件人名稱", "收件人電話", "收件人地址", "收貨超商代號",
 
    "收件人國家", "收件人省份", "收件人城市", "收件人區",
 
    "購買人名稱", "購買人電話", "購買人地址", "時間", "取消時間",
 
    "訂單編號", "訂單狀態", "訂單來源", "備註", "收款方", "會員名稱",
 
    "Email", "會員手機號碼", "會員資料備註", "會員標籤", "會員生日", "會員ID"

]

fill_value_index = {}

for col in fill_value_columns:
 
    if col in header:
 
        fill_value_index[col] = header.index(col)

first_row_map = {}

for row in records[1:]:
 
    order_no = str(row[order_index])
 
    if order_no not in first_row_map:
 
        first_row_map[order_no] = {}
 
        for col, idx in fill_value_index.items():
 
            first_row_map[order_no][col] = row[idx]

# 確保備註欄位存在

if "備註" not in header:
 
    header.append("備註")
 
    for i in range(1, len(records)):
 
        records[i].append("")

remark_index = header.index("備註")

# 確認額外資訊欄位，不存在就新增空欄

if "額外資訊" not in header:
 
    header.append("額外資訊")
 
    for i in range(1, len(records)):
 
        records[i].append("")

extra_info_index = header.index("額外資訊")

# 新增欄位

header += ["新SKU", "新數量", "新進價", "新預交貨日", "溫層", "分錄備註", "新總金額", "新折扣"]

header.insert(header.index("新折扣") + 1, "新備註")

new_total_amount_index = header.index("新總金額")

new_discount_index = header.index("新折扣")

new_remark_index = header.index("新備註")

temp_col_index = header.index("溫層")

remark_col_index = header.index("分錄備註")

new_records = [header]

temp_by_order = defaultdict(list)

for row in records[1:]:
 
    row = row + ["" for _ in range(len(header) - len(records[0]))]

 

    if not row[item_index] or str(row[item_index]).strip() == "":
 
        row[item_index] = "A"

 

    order_no = str(row[order_index])
 
    for col, idx in fill_value_index.items():
 
        if not row[idx] or str(row[idx]).strip() == "":
 
            row[idx] = first_row_map[order_no][col]

 

    product_id = str(row[item_index])
 
    try:
 
        original_qty = int(row[qty_index])
 
    except:
 
        original_qty = 0

 

    transfer_no = row[transfer_index]
 
    address = str(row[address_index])
 
    name = str(row[name_index])
 
    cleaned_addr = re.sub(r"[0-9\s]", "", address)[:3]
 
    split_remark = cleaned_addr + name

 

    buyer_name = str(row[fill_value_index.get("購買人名稱", "")] if "購買人名稱" in fill_value_index else "")
 
    receiver_name = str(row[name_index])
 
    current_remark = str(row[remark_index]) if row[remark_index] else ""

 

    if buyer_name and receiver_name and buyer_name != receiver_name:
 
        row[remark_index] = current_remark + f"({buyer_name}寄)"
 
    else:
 
        row[remark_index] = current_remark

 

    original_remark = str(row[remark_index]) if row[remark_index] else ""
 
    extra_info = str(row[extra_info_index]) if row[extra_info_index] else ""
 
    new_remark = original_remark + extra_info
 
    new_remark = new_remark.replace("如有送禮需求請幫忙註明送禮", "").strip()

 

    if product_id in sku_map:
 
        for sku in sku_map[product_id]:
 
            try:
 
                mapped_qty = int(sku["新數量"])
 
            except:
 
                mapped_qty = 0
 
            final_qty = mapped_qty * original_qty
 
            new_sku = sku["新SKU"]
 
            try:
 
                new_price = float(sku["新進價"])
 
            except ValueError:
 
                new_price = 0.0
 
            temp = sku_temp_map.get(new_sku, "冷凍")
 
            temp_by_order[transfer_no].append(temp)
 
            new_row_data = [new_sku, str(final_qty), new_price, next_date, temp, split_remark, "", "", new_remark]
 
            new_row = row[:len(records[0])] + new_row_data
 
            new_records.append(new_row)
 
    else:
 
        new_sku = row[item_index]
 
        try:
 
            new_price = float(row[price_index])
 
        except ValueError:
 
            new_price = 0.0
 
        temp = sku_temp_map.get(new_sku, "冷凍")
 
        temp_by_order[transfer_no].append(temp)
 
        new_row_data = [new_sku, str(original_qty), new_price, next_date, temp, split_remark, "", "", new_remark]
 
        new_row = row[:len(records[0])] + new_row_data
 
        new_records.append(new_row)

final_records = [new_records[0]]

seen_transfers = set()

for row in new_records[1:]:
 
    transfer_no = row[transfer_index]
 
    order_no = str(row[order_index])
 
    new_total = total_amount_by_order.get(order_no, 0)
 
    original_total = original_total_amount_by_order.get(order_no, 0)

 

    row[new_total_amount_index] = new_total
 
    row[new_discount_index] = original_total - new_total

 

    print(f"Order: {order_no}, Original Total (總額): {original_total}, New Total (新總金額): {new_total})")
 
    print(f"Calculated New Discount (新折扣): {original_total - new_total})")

 

    temps = temp_by_order[transfer_no]
 
    row[temp_col_index] = "常溫" if all(t == "常溫" for t in temps) else "冷凍"
 
    if transfer_no in seen_transfers:
 
        row[remark_col_index] = ""
 
    else:
 
        seen_transfers.add(transfer_no)
 
    final_records.append(row)

base_dir = os.path.dirname(file_path)

name_part, ext_part = os.path.splitext(os.path.basename(file_path))

timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

new_filename = f"{name_part}_SKU轉換_{timestamp}.xls"

new_path = os.path.join(base_dir, new_filename)

pe.save_as(array=final_records, dest_file_name=new_path)

print(f"✅ SKU轉換、預交貨日、溫層、分錄備註、總金額與新折扣加總完成，檔案儲存為：{new_path}")

📘 SKU Excel 轉換程式 使用說明

📖 簡介

🛠 功能特色

📂 必要檔案

📐 新增欄位

⚙️ 操作流程

✅ 輸出結果

⚙️ 程式原始碼

📘 SKU Excel 轉換程式使用說明