根据EXCEL生成SQL代码文件

const xlsx = require("xlsx");
const fs = require("fs");
const { Client } = require("pg");

// 研发库
const client = new Client({
  host: "",
  port: 5432,
  user: "postgres",
  password: "123",
  database: "testtwo",
});

// 目标表字段列表
const tableFields = [
  "supplier_name", "supplier_short_name", "communication_address", "postal_code",
  "company_website", "industry", "establishment_date", "legal_representative",
  "registered_capital", "business_registration_number", "business_license",
  "unified_social_credit_code", "registered_address", "supply_category",
  "company_category", "company_type", "company_size", "contact_person", "position",
  "contact_information", "is_enabled", "user_id", "user_info", "editDate",
  "is_delete", "created_at", "pay_condition", "pay_method", "deposit_bank",
  "account_title", "account_number", "business_picture"
];

// 读取 Excel 文件
const workbook = xlsx.readFile("清单.xlsx");
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

// 转换为 JSON 数据
const jsonData = xlsx.utils.sheet_to_json(worksheet);

// 按字段对齐 JSON 数据
const processJsonData = (data, fields) => {
  return data.map(row => {
    const processedRow = {};
    fields.forEach(field => {
      processedRow[field] = row[field] || null; // JSON 中不存在的字段设置为 null
    });
    return processedRow;
  });
};

const processedData = processJsonData(jsonData, tableFields);

// SQL 文件路径
const sqlFilePath = "insert_t_supplier.sql";

(async () => {
  try {
    await client.connect();

    // 清空并初始化 SQL 文件
    fs.writeFileSync(sqlFilePath, ""); // 清空文件内容

    for (const row of processedData) {
      try {
        // 构造字段和值
        const keys = Object.keys(row).map(key => `"${key}"`).join(", ");
        const values = Object.values(row)
        .map(value => (value === null ? "NULL" : '${value}'))
          .join(", ");
        
        const query = `INSERT INTO t_supplier (${keys}) VALUES (${values});\n`;

        // 写入 SQL 文件
        fs.appendFileSync(sqlFilePath, query);

        console.log(`Executing Query: ${query.trim()}`); // 可选，调试用
        await client.query(query);
      } catch (err) {
        console.error(`插入失败，错误行: ${JSON.stringify(row)}，原因: ${err.message}`);
      }
    }

    console.log("所有数据插入完成，SQL 文件已保存！");
  } catch (error) {
    console.error("数据库连接或处理失败：", error.message);
  } finally {
    await client.end();
  }
})();