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
|
#!/usr/bin/env python3
"""
Shared utilities for generating Shoplazza (店匠) product import Excel files
based on the provided template `docs/商品导入模板.xlsx`.
We keep this in `scripts/` to maximize reuse by existing ad-hoc pipeline scripts.
"""
from openpyxl import load_workbook
from openpyxl.styles import Alignment
def load_template_column_mapping(ws, header_row_idx=2):
"""
Read the header row in the template sheet and build a mapping:
header_name -> column_index (1-based).
"""
column_mapping = {}
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
return column_mapping
def create_excel_from_template(template_file, output_file, excel_rows, header_row_idx=2, data_start_row=4):
"""
Create an Excel file from the Shoplazza template and fill with data rows.
Args:
template_file: Path to Excel template file
output_file: Path to output Excel file
excel_rows: List[Dict[str, Any]] mapping template header -> value
header_row_idx: Header row index in template (default 2)
data_start_row: Data start row index in template (default 4)
"""
wb = load_workbook(template_file)
ws = wb.active
column_mapping = load_template_column_mapping(ws, header_row_idx=header_row_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
cell = ws.cell(row=excel_row_num, column=col_idx)
value = excel_row[field_name]
cell.value = value
if isinstance(value, str):
cell.alignment = Alignment(vertical='top', wrap_text=True)
else:
cell.alignment = Alignment(vertical='top')
wb.save(output_file)
print("Excel file created: {}".format(output_file))
print(" - Total rows: {}".format(len(excel_rows)))
|