CyberBiz

📖 簡介 

 此工具用於 電商訂單 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}")