f3c11fef
 tangwang
亚马逊格式数据 导入店匠
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
#!/usr/bin/env python3
"""
Shared helpers for generating Shoplazza product import Excel files from the
official template `docs/商品导入模板.xlsx`.
We keep this module small and dependency-light (openpyxl only) so other scripts
can reuse the same template-writing behavior (header row mapping, data start
row, alignment).
"""
import re
from datetime import datetime
from typing import Dict, Iterable, List, Optional
from openpyxl import load_workbook
from openpyxl.styles import Alignment
def generate_handle(title: str) -> str:
"""
Generate URL-friendly handle from title (ASCII only), suitable for Shoplazza
`SEO URL Handle` field. Caller may prepend `products/`.
"""
if not title:
return "product"
handle = str(title).lower()
handle = re.sub(r"[^a-z0-9\s-]", "", handle)
handle = re.sub(r"[-\s]+", "-", handle).strip("-")
if len(handle) > 255:
handle = handle[:255]
return handle or "product"
def parse_date_to_datetime_str(value) -> str:
"""
Parse common date strings into Shoplazza template datetime string:
`YYYY-MM-DD HH:MM:SS`. If parsing fails, returns empty string.
"""
if value is None:
return ""
if isinstance(value, datetime):
return value.strftime("%Y-%m-%d %H:%M:%S")
s = str(value).strip()
if not s:
return ""
# Most competitor sheets use YYYY-MM-DD
for fmt in ("%Y-%m-%d %H:%M:%S", "%Y-%m-%d", "%Y/%m/%d", "%m/%d/%Y"):
try:
dt = datetime.strptime(s, fmt)
if fmt in ("%Y-%m-%d", "%Y/%m/%d", "%m/%d/%Y"):
dt = dt.replace(hour=0, minute=0, second=0)
return dt.strftime("%Y-%m-%d %H:%M:%S")
except Exception:
pass
return ""
def create_excel_from_template(
template_file: str,
output_file: str,
excel_rows: List[Dict[str, object]],
*,
header_row_idx: int = 2,
data_start_row: int = 4,
sheet_name: Optional[str] = None,
) -> None:
"""
Create an Excel file from Shoplazza import template and fill rows.
- Header row is expected at row 2 (1-based) in the official template.
- Data starts at row 4 (1-based), after the instruction row(s).
"""
wb = load_workbook(template_file)
ws = wb[sheet_name] if sheet_name else wb.active
column_mapping: Dict[str, int] = {}
for col_idx in range(1, ws.max_column + 1):
cell_value = ws.cell(row=header_row_idx, column=col_idx).value
if cell_value:
column_mapping[str(cell_value).strip()] = col_idx
# Clear existing data rows
last_template_row = ws.max_row
if last_template_row >= data_start_row:
for row in range(data_start_row, last_template_row + 1):
for col in range(1, ws.max_column + 1):
ws.cell(row=row, column=col).value = None
# Write data rows
for row_idx, excel_row in enumerate(excel_rows):
excel_row_num = data_start_row + row_idx
for field_name, col_idx in column_mapping.items():
if field_name not in excel_row:
continue
value = excel_row[field_name]
cell = ws.cell(row=excel_row_num, column=col_idx)
cell.value = value
if isinstance(value, str):
cell.alignment = Alignment(vertical="top", wrap_text=True)
elif isinstance(value, (int, float)):
cell.alignment = Alignment(vertical="top")
wb.save(output_file)
|