读取Excel生成insert sql

package com.yoc.rxk.saas.dis.server;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelToSqlGenerator {

    public static void main(String[] args) {
        String excelFilePath = "E:\\yoc\\文档\\副本成都(1).xlsx";
        String outputSqlPath = "E:\\yoc\\文档\\output1.sql";
        int startRow = 1; // Excel数据起始行（0-based，第2行）
        int endRow = 50; // Excel数据结束行（0-based，第259行）

        try {
            List<String> sqlStatements = generateSqlFromExcel(excelFilePath, startRow, endRow);
            writeSqlToFile(sqlStatements, outputSqlPath);
            System.out.println("SQL文件生成成功，共生成 " + sqlStatements.size() + " 条记录");
        } catch (IOException e) {
            System.err.println("处理文件时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static List<String> generateSqlFromExcel(String filePath, int startRow, int endRow) throws IOException {
        List<String> sqlList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 第一个工作表

            for (int rowNum = startRow; rowNum <= endRow; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) break;

                // 解析各列数据（0-based）
                String name = getCellValue(row.getCell(1));     // B列 姓名
                String mobile = getCellValue(row.getCell(8));   // I列 电话
                String wechat = getCellValue(row.getCell(9));   // J列 微信
                String company = getCellValue(row.getCell(0)); // A列 公司名
                String position = getCellValue(row.getCell(2));// C列 岗位
                String expertise = getCellValue(row.getCell(7));// H列 业务介绍

                // 生成avatar路径（行号从1开始）
                String avatar = String.format("/dis/dis%d.png", rowNum - startRow + 1);
                Integer channelId = rowNum - startRow + 1;

                // 构建SQL语句
                String sql = String.format(
                    "(1238531052562489344, -1, -1, '%s', '%s', '%s', '%s', '{\"province\":\"510000\",\"pName\":\"四川省\",\"city\":\"510100\",\"cName\":\"成都市\",\"detail\":\"\",\"full\":\"四川省成都市\"}', '%s', %s, NULL, '%s', %s)",
                    escapeSql(avatar),
                    escapeSql(name),
                    escapeSql(mobile),
                    escapeSql(wechat),
                    escapeSql(company),
                    position.isEmpty() ? "NULL" : "'" + escapeSql(position) + "'",
                    escapeSql(expertise),
                        channelId
                );

                sqlList.add(sql);
            }
        }

        return sqlList;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                return String.format("%.0f", cell.getNumericCellValue());
            default:
                return "";
        }
    }

    private static String escapeSql(String input) {
        return input.replace("'", "''"); // 处理单引号
    }

    private static void writeSqlToFile(List<String> sqlList, String filePath) throws IOException {
        try (FileWriter writer = new FileWriter(filePath)) {
            writer.write("INSERT INTO `dis_business_card_effective` " +
                    "(`tenant_id`, `create_by`, `update_by`, `avatar`, `name`, `mobile`, `wechat`, " +
                    "`city`, `company`, `position`, `expertise_products`, `main_products`, `channel_id`) VALUES\n");

            for (int i = 0; i < sqlList.size(); i++) {
                writer.write(sqlList.get(i));
                writer.write(i == sqlList.size() - 1 ? ";" : ",\n");
            }
        }
    }
}