Excel 转sql

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelMain {
    public static void main(String[] args) {
        String excelPath = "D:\\desktop\\111.xlsx";
        String tableName = "bms_cost_ledger";

        try {
            List<String> sqls = generateSQL(excelPath, tableName);
            for (String sql : sqls) {
                System.out.println("成功生成:" + sql);
            }
        } catch (Exception e) {
            System.err.println("处理失败: " + e.getMessage());
        }
    }

    public static List<String> generateSQL(String filePath, String tableName)
            throws IOException {
        List<String> sqlList = new ArrayList<>();
        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);

        // 获取列头
        Row headerRow = sheet.getRow(0);
        int colCount = headerRow.getLastCellNum();
        String[] columns = new String[colCount];
        for (int i = 0; i < colCount; i++) {
            columns[i] = headerRow.getCell(i).getStringCellValue();
        }

        // 处理数据行
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if(row == null) continue;

            StringBuilder sql = new StringBuilder("INSERT INTO ")
                    .append(tableName).append(" (")
                    .append(String.join(", ", columns)).append(") VALUES (");

            List<String> values = new ArrayList<>();
            for (int colNum = 0; colNum < colCount; colNum++) {
                Cell cell = row.getCell(colNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                values.add(convertCellValue(cell));
            }
            sql.append(String.join(", ", values)).append(");");
            sqlList.add(sql.toString());
        }

        workbook.close();
        fis.close();
        return sqlList;
    }

    private static String convertCellValue(Cell cell) {
        if (cell == null) return "NULL";

        switch (cell.getCellType()) {
            case STRING:
                return "'" + cell.getStringCellValue().replace("'", "''") + "'";
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    return "'" + format.format(cell.getDateCellValue()) + "'";
                }
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return cell.getBooleanCellValue() ? "1" : "0";
            case BLANK:
                return "NULL";
            default:
                return "NULL";
        }
    }
}

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
</dependencies>