87 lines
2.9 KiB
Python
87 lines
2.9 KiB
Python
# 解析 1 号药城「订单明细」xls。
|
|
|
|
from __future__ import annotations
|
|
|
|
from decimal import Decimal
|
|
from pathlib import Path
|
|
|
|
import xlrd
|
|
|
|
from settle import MallLine
|
|
|
|
_REQUIRED = {
|
|
"订单号": "order_no",
|
|
"父订单号": "parent_order_no",
|
|
"商家": "supplier_name",
|
|
"订单状态": "status",
|
|
"品名": "goods_name",
|
|
"产品编码": "product_code",
|
|
"产品条码": "bar_code",
|
|
"单价（元）": "unit_price",
|
|
"数量": "qty",
|
|
"商品金额（元）": "goods_amount",
|
|
"折后单价": "pay_price",
|
|
"订单实付总金额": "order_paid",
|
|
}
|
|
|
|
|
|
# 读 1 号药城订单明细表。缺列就失败。订单号为空的行丢掉。
|
|
def parse_order_xls(path: str | Path) -> list[MallLine]:
|
|
book = xlrd.open_workbook(str(path))
|
|
sheet = book.sheet_by_index(0)
|
|
headers = [str(sheet.cell_value(0, col)).strip() for col in range(sheet.ncols)]
|
|
missing = [name for name in _REQUIRED if name not in headers]
|
|
if missing:
|
|
raise ValueError("导出表缺列：" + "、".join(missing))
|
|
index = {field: headers.index(name) for name, field in _REQUIRED.items()}
|
|
lines: list[MallLine] = []
|
|
for row in range(1, sheet.nrows):
|
|
values = {field: sheet.cell_value(row, col) for field, col in index.items()}
|
|
order_no = _text(values["order_no"])
|
|
if not order_no:
|
|
continue
|
|
lines.append(
|
|
MallLine(
|
|
order_no=order_no,
|
|
parent_order_no=_text(values["parent_order_no"]),
|
|
supplier_name=_text(values["supplier_name"]),
|
|
status=_text(values["status"]),
|
|
goods_name=_text(values["goods_name"]),
|
|
product_code=_text(values["product_code"]),
|
|
bar_code=_barcode(values["bar_code"]),
|
|
unit_price=_decimal(values["unit_price"]),
|
|
pay_price=_decimal(values["pay_price"]),
|
|
qty=int(_decimal(values["qty"])),
|
|
goods_amount=_decimal(values["goods_amount"]),
|
|
order_paid=_decimal(values["order_paid"]),
|
|
)
|
|
)
|
|
return lines
|
|
|
|
|
|
# 单元格收成字符串。整数浮点去掉小数点，避免订单号变成 123.0。
|
|
def _text(value: object) -> str:
|
|
if value is None:
|
|
return ""
|
|
if isinstance(value, float) and value == int(value):
|
|
return str(int(value))
|
|
return str(value).strip()
|
|
|
|
|
|
# 条码只留第一段。逗号后的附加码丢掉，末尾 .0 去掉。
|
|
def _barcode(value: object) -> str:
|
|
text = _text(value)
|
|
if "," in text:
|
|
parts = [part.strip() for part in text.split(",") if part.strip()]
|
|
text = parts[0] if parts else ""
|
|
if text.endswith(".0") and text.replace(".", "", 1).isdigit():
|
|
return text[:-2]
|
|
return text
|
|
|
|
|
|
# 单元格收成金额。空值当 0。
|
|
def _decimal(value: object) -> Decimal:
|
|
if value is None or value == "":
|
|
return Decimal("0")
|
|
return Decimal(str(value))
|