117 lines
4.2 KiB
Python
117 lines
4.2 KiB
Python
import os
|
||
import json
|
||
import openpyxl
|
||
import re
|
||
|
||
excel_dir = 'excel'
|
||
output_dir = 'src/ReplicatedStorage/Json'
|
||
|
||
if not os.path.exists(output_dir):
|
||
os.makedirs(output_dir)
|
||
|
||
def parse_array_field(value, elem_type):
|
||
"""
|
||
解析一维数组类型字段，支持[1,2,3]或1,2,3写法。
|
||
"""
|
||
if value is None:
|
||
return []
|
||
s = str(value).strip()
|
||
if s.startswith("[") and s.endswith("]"):
|
||
s = s[1:-1]
|
||
items = re.split(r'[，,]', s)
|
||
result = []
|
||
for v in items:
|
||
v = v.strip()
|
||
if v == "":
|
||
continue
|
||
if elem_type == "int":
|
||
try:
|
||
result.append(int(v))
|
||
except Exception:
|
||
pass
|
||
elif elem_type == "float":
|
||
try:
|
||
result.append(float(v))
|
||
except Exception:
|
||
pass
|
||
else:
|
||
result.append(v)
|
||
return result
|
||
|
||
def parse_2d_array_field(value, elem_type):
|
||
"""
|
||
解析二维数组类型字段，支持[1,2],[3,4]或[[1,2],[3,4]]等写法。
|
||
保证导出始终为双数组结构。
|
||
"""
|
||
if value is None or str(value).strip() == "":
|
||
return []
|
||
s = str(value).strip()
|
||
# 去除最外层中括号
|
||
if s.startswith("[[") and s.endswith("]]"):
|
||
s = s[1:-1]
|
||
# 按 '],[' 拆分
|
||
parts = re.split(r'\]\s*,\s*\[', s)
|
||
result = []
|
||
for part in parts:
|
||
part = part.strip("[] ")
|
||
arr = parse_array_field(part, elem_type)
|
||
result.append(arr)
|
||
# 如果内容其实是一维数组（如单元格内容为1,2,3），也包一层
|
||
if len(result) == 1 and not isinstance(result[0], list):
|
||
result = [parse_array_field(s, elem_type)]
|
||
# 如果内容为空但原始字符串非空，也包一层
|
||
if not result and s:
|
||
result = [parse_array_field(s, elem_type)]
|
||
return result
|
||
|
||
for filename in os.listdir(excel_dir):
|
||
if filename.endswith('.xlsx'):
|
||
filepath = os.path.join(excel_dir, filename)
|
||
# 用 data_only=True 读取公式的值
|
||
wb = openpyxl.load_workbook(filepath, data_only=True)
|
||
for sheet_name in wb.sheetnames:
|
||
ws = wb[sheet_name]
|
||
rows = list(ws.iter_rows(values_only=True))
|
||
if len(rows) < 2:
|
||
continue
|
||
headers = rows[0]
|
||
types = rows[1]
|
||
# 只保留字段名和类型都不为空的字段
|
||
valid_indices = [
|
||
i for i, (h, t) in enumerate(zip(headers, types))
|
||
if h is not None and str(h).strip() != "" and t is not None and str(t).strip() != ""
|
||
]
|
||
valid_headers = [headers[i] for i in valid_indices]
|
||
valid_types = [types[i] for i in valid_indices]
|
||
data = []
|
||
for row in rows[2:]:
|
||
filtered_row = [row[i] if i < len(row) else None for i in valid_indices]
|
||
row_dict = {}
|
||
for h, t, v in zip(valid_headers, valid_types, filtered_row):
|
||
if t.endswith("[][]"):
|
||
elem_type = t[:-4]
|
||
row_dict[h] = parse_2d_array_field(v, elem_type)
|
||
elif t.endswith("[]"):
|
||
elem_type = t[:-2]
|
||
row_dict[h] = parse_array_field(v, elem_type)
|
||
else:
|
||
row_dict[h] = v
|
||
id_value = row_dict.get("id")
|
||
# 只导出id为数字且不为空的行
|
||
if id_value is not None and isinstance(id_value, (int, float)) and str(int(id_value)).isdigit():
|
||
data.append(row_dict)
|
||
if not data:
|
||
continue
|
||
out_name = f"{sheet_name}.json"
|
||
out_path = os.path.join(output_dir, out_name)
|
||
# 写入json，每个对象单独一行
|
||
with open(out_path, 'w', encoding='utf-8') as f:
|
||
f.write('[\n')
|
||
for i, obj in enumerate(data):
|
||
line = json.dumps(obj, ensure_ascii=False, separators=(',', ':'))
|
||
if i < len(data) - 1:
|
||
f.write(line + ',\n')
|
||
else:
|
||
f.write(line + '\n')
|
||
f.write(']')
|
||
print(f"导出: {out_path}") |