crossborder/quanguo/commodity_trade.py

import re
import openpyxl
from decimal import Decimal
import pandas as pd

from quanguo.detail import parse_hscode


def chinese_class_to_number(class_str):
    """精确匹配中文类名"""
    cn_num_map = {
        '一': 1, '二': 2, '三': 3, '四': 4, '五': 5, '六': 6, '七': 7, '八': 8, '九': 9,
        '十': 10, '十一': 11, '十二': 12, '十三': 13, '十四': 14, '十五': 15, '十六': 16,
        '十七': 17, '十八': 18, '十九': 19, '二十': 20, '二十一': 21, '二十二': 22
    }
    match = re.match(r'^第([一二三四五六七八九十百千万]+)类$', class_str)
    return cn_num_map.get(match.group(1), 0) if match else 0


def parse_value(val):
    """数值处理（包含科学计数法处理）"""
    if val in ('-', None, 'None', 'null'):
        return 'NULL'
    try:
        # 处理科学计数法（如1.2E+5）
        if 'E' in str(val).upper():
            return Decimal(f"{float(val):.2f}").quantize(Decimal('0.00'))
        return Decimal(str(val).replace(',', '')).quantize(Decimal('0.00'))
    except Exception as e:
        print(f"数值解析错误：{val}，错误：{e}")
        return 'NULL'


# 数据预处理

df = pd.read_excel(r"D:/Downloads/2025051809313028789.xls", engine="xlrd")
df.to_excel("converted.xlsx", index=False)
wb = openpyxl.load_workbook('converted.xlsx')
sheet = wb.active

current_class = None
year_month = '2025-04'
sql_scripts = []

# 遍历所有数据行（示例数据从第7行开始）
for row_idx, row in enumerate(sheet.iter_rows(min_row=7, values_only=True), start=7):
    if not row[1]: continue

    hs_code = parse_hscode(row[1])
    if not hs_code:
        print(f"[行{row_idx}] 忽略无效行：{row[1]}")
        continue

    # 类行数据插入
    try:
        monthly_export = parse_value(row[2])
        ytd_export = parse_value(row[3])
        monthly_import = parse_value(row[4])
        ytd_import = parse_value(row[5])
        ytd_yoy_export = parse_value(row[6])
        ytd_yoy_import = parse_value(row[7])
    except IndexError as e:
        print(f"行{row_idx} 列索引错误：{e}")
        continue

    sql = f"""INSERT INTO `t_yujin_crossborder_commodity_trade` 
    (`year_month`, `hs_code`, `monthly_import`, `monthly_export`, 
    `ytd_import`, `ytd_export`, `ytd_yoy_import`, `ytd_yoy_export`)
    VALUES ('{year_month}', '{hs_code}', 
    {monthly_import}, {monthly_export}, 
    {ytd_import}, {ytd_export}, 
    {ytd_yoy_import}, {ytd_yoy_export})
    ON DUPLICATE KEY UPDATE 
    monthly_import = VALUES(monthly_import),
    monthly_export = VALUES(monthly_export),
    ytd_import = VALUES(ytd_import),
    ytd_export = VALUES(ytd_export),
    ytd_yoy_import = VALUES(ytd_yoy_import),
    ytd_yoy_export = VALUES(ytd_yoy_export);"""
    sql_scripts.append(sql)
    continue

# 结果输出
with open('../downloads/commodity_trade04.sql', 'w', encoding='utf-8') as f:
    f.write('\n'.join(sql_scripts))

print(f"生成完成，共处理 {len(sql_scripts)} 条有效记录")