import gradio as gr
import pandas as pd
from datetime import datetime
import pytz
import re
# Updated Gradio app for YunExpress:
# - New output headers (as provided)
# - Email -> Email mapping
# - FOBPrice1 = 2 (replaces UnitPrice1)
# - ZIP padding fix for US 3- and 4-digit ZIPs (no leading apostrophe; sanitize non [A-Za-z0-9 ] chars)
# - RoutingCode logic: US/NO/FR -> HK-ASS-PF; others -> HKTHZXR
# - Save Excel with ZipCode column set to Text (@) to preserve leading zeros without apostrophes
def process_file(file):
file_name = file.name.lower()
try:
if file_name.endswith(('.xls', '.xlsx', '.xlsm')):
# Read data from the "YunExpress" sheet
df = pd.read_excel(file, sheet_name="YunExpress")
else:
return f"Unsupported file format: {file_name}", None
except Exception as e:
return f"Error reading file: {e}", None
# === New output headers (exact order) ===
output_headers = [
"CustomerOrderNo.", "RoutingCode", "Trackingnumber", "AdditionalServices",
"ShipmentProtectionPlusService", "CustomDeclaredValue", "SignatureService",
"VatNumber", "EoriNumber", "IossCode", "ManufactureSalesName",
"UnifiedSocialCreditCode", "CountryCode", "Name", "CertificateCode", "Company",
"Street", "City", "Province/State", "ZipCode", "phone", "HouseNumber", "Email",
"PackageNumber", "PackageWeight", "SenderFiastName", "SenderCompany",
"SenderStreet", "SenderCity", "SenderProvince", "SenderPostalCode",
"SenderCountry", "SenderTelephone", "SenderEmail", "SenderUSCI", "PlatformName",
"PlatformProvince", "PlatformAddress", "PlatformPostalCode", "PlatformPhoneNumber",
"PlatformEmail", "EcommercePlatformCode", "SalesPlatformLink", "CurrencyCode",
"SKU1", "ItemDescription1", "ForeignItemDescription1", "DeclaredQuantity1",
"FOBPrice1", "SellingPrice1", "UnitWeight1", "HsCode1", "Remarks1", "SalesLink1",
"Materials1", "Use1", "Brand1", "ModelType1", "Specs1", "FabricCreationMethod1",
"ManufacturerID1", "ManufacturerName1", "ManufacturerCountry1",
"ManufacturerState1", "ManufacturerCity1", "ManufacturerPostalCode1",
"ManufacturerAddress1", "CargoCategory", "PaymentPlatform", "PaymentAccount",
"PaymentTransactionNumber"
]
# Initialize empty output DataFrame
output_df = pd.DataFrame("", index=range(len(df)), columns=output_headers)
# 1) Order Number -> CustomerOrderNo. (prefix "LB")
if "Order Number" in df.columns:
output_df["CustomerOrderNo."] = "LB" + df["Order Number"].astype(str)
# 2) Name: First + Last + Company -> Name
if {"Shipping First Name", "Shipping Last Name", "Shipping Company"}.issubset(df.columns):
first = df["Shipping First Name"].fillna("").astype(str).str.strip()
last = df["Shipping Last Name"].fillna("").astype(str).str.strip()
comp = df["Shipping Company"].fillna("").astype(str).str.strip()
output_df["Name"] = (first + " " + last + " " + comp).str.replace(r"\s+", " ", regex=True).str.strip()
# 3) Street: Address 1 + Address 2 -> Street
addr1 = df.get("Shipping Address 1", pd.Series([""] * len(df), index=df.index)).fillna("").astype(str).str.strip()
addr2 = df.get("Shipping Address 2", pd.Series([""] * len(df), index=df.index)).fillna("").astype(str).str.strip()
output_df["Street"] = (addr1 + " " + addr2).str.replace(r"\s+", " ", regex=True).str.strip()
# 4) City
if "Shipping City" in df.columns:
output_df["City"] = df["Shipping City"]
# 5) Province/State
if "Shipping Province" in df.columns:
output_df["Province/State"] = df["Shipping Province"]
# 6) CountryCode (before ZIP handling)
if "Shipping Country Code" in df.columns:
output_df["CountryCode"] = df["Shipping Country Code"]
# 7) ZipCode (sanitize; US 3/4-digit -> pad to 5; do NOT add apostrophe)
if "Shipping ZIP" in df.columns:
zip_raw = (
df["Shipping ZIP"]
.astype(str)
.str.strip()
.str.replace(r"\.0$", "", regex=True) # clean "1234.0"
)
# Keep only letters, digits, spaces (avoid "only letters, numbers and spaces" API error)
zip_clean = zip_raw.str.replace(r"[^A-Za-z0-9 ]+", "", regex=True)
mask_us = output_df["CountryCode"].eq("US")
# For US: if 3 or 4 numeric digits -> zero-fill to 5
mask_3_4_digits = zip_clean.str.fullmatch(r"\d{3,4}")
zip_padded = zip_clean.where(~(mask_us & mask_3_4_digits), zip_clean.str.zfill(5))
# Do NOT add any leading apostrophe
output_df["ZipCode"] = zip_padded
# 8) phone
if "Shipping Address Phone" in df.columns:
output_df["phone"] = df["Shipping Address Phone"]
# 9) NEW: Email -> Email (direct mapping)
if "Email" in df.columns:
output_df["Email"] = df["Email"]
# 10) PackageWeight: Total Weight (g) -> kg
if "Total Weight" in df.columns:
output_df["PackageWeight"] = df["Total Weight"] / 1000
# 11) DeclaredQuantity1: sum Quantity per Order Number
if "Order Number" in df.columns and "Quantity" in df.columns:
output_df["DeclaredQuantity1"] = df.groupby("Order Number")["Quantity"].transform("sum")
# Fixed defaults & RoutingCode
mask = output_df["CustomerOrderNo."].astype(str).str.len() > 0
# RoutingCode: HK-ASS-PF if US/NO/FR else HKTHZXR
mask_us_no_fr = mask & output_df["CountryCode"].isin(["US", "NO", "FR"])
mask_other = mask & ~output_df["CountryCode"].isin(["US", "NO", "FR"])
output_df.loc[mask_us_no_fr, "RoutingCode"] = "HK-ASS-PF"
output_df.loc[mask_other, "RoutingCode"] = "HKTHZXR"
# Pricing / descriptions / weights
output_df.loc[mask, "FOBPrice1"] = 2 # CHANGED from UnitPrice1=2
output_df.loc[mask, "CurrencyCode"] = "USD"
output_df.loc[mask, "ItemDescription1"] = "Eye Cosmetic Accessories"
output_df.loc[mask, "ForeignItemDescription1"] = "Eye Cosmetic Accessories"
output_df.loc[mask, "UnitWeight1"] = 0.02
# EU AdditionalServices = v1
EU_COUNTRIES = {"AT","BE","BG","CY","CZ","DE","DK","EE","ES","FI",
"FR","HR","HU","IE","IT","LT","LU","LV","MT","NL",
"PL","PT","RO","SE","SI","SK","GR"}
mask_eu = mask & output_df["CountryCode"].isin(EU_COUNTRIES)
output_df.loc[mask_eu, "AdditionalServices"] = "v1"
# Remove duplicates by CustomerOrderNo.
output_df = output_df.drop_duplicates(subset=["CustomerOrderNo."], keep="first")
# Output filename (HK date)
hk_tz = pytz.timezone("Asia/Hong_Kong")
today_hk = datetime.now(hk_tz).strftime("%y%m%d")
output_file_name = f"yunexpress {today_hk}.xlsx"
# Save to Excel with ZipCode column forced to Text (@) using xlsxwriter
try:
import xlsxwriter
from xlsxwriter.utility import xl_col_to_name
with pd.ExcelWriter(output_file_name, engine="xlsxwriter") as writer:
output_df.to_excel(writer, index=False, sheet_name="Sheet1")
workbook = writer.book
worksheet = writer.sheets["Sheet1"]
# Text format to preserve leading zeros without adding apostrophes
text_fmt = workbook.add_format({"num_format": "@"})
# Locate ZipCode column and set entire column to text
zip_col_idx = output_df.columns.get_loc("ZipCode") # 0-based index
col_letter = xl_col_to_name(zip_col_idx)
worksheet.set_column(f"{col_letter}:{col_letter}", None, text_fmt)
except Exception:
# Fallback to default writer if xlsxwriter not available
output_df.to_excel(output_file_name, index=False)
return output_df, output_file_name
# Gradio interface
with gr.Blocks(title="Shipping - YunExpress") as demo:
gr.Markdown("# Shipping - YunExpress")
with gr.Row():
file_input = gr.File(label="Upload Excel File with a YunExpress sheet")
process_button = gr.Button("Process Data")
with gr.Row():
output_data = gr.DataFrame()
output_file_component = gr.File(label="Download Processed File")
process_button.click(fn=process_file, inputs=[file_input], outputs=[output_data, output_file_component])
# Links to other tools
gr.HTML(
"""
"""
)
demo.launch()