import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
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;

public class ExcelToMysql {
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/db_name";
    private static final String JDBC_USERNAME = "username";
    private static final String JDBC_PASSWORD = "password";
    
    public static void main(String[] args) {
        try {
            // 加载MySQL JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 连接MySQL数据库
            Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
            
            // 读取Excel文件
            FileInputStream inputStream = new FileInputStream("path_to_excel_file.xlsx");
            Workbook workbook = new XSSFWorkbook(inputStream);
            
            // 获取Excel的第一个表格
            Sheet sheet = workbook.getSheetAt(0);
            
            // 获取列头
            Row headerRow = sheet.getRow(0);
            
            // 创建MySQL表
            String createTableSql = "CREATE TABLE IF NOT EXISTS table_name (";
            Iterator<Cell> headerCells = headerRow.cellIterator();
            while (headerCells.hasNext()) {
                Cell cell = headerCells.next();
                String columnName = cell.getStringCellValue();
                createTableSql += columnName + " VARCHAR(255), ";
            }
            createTableSql = createTableSql.substring(0, createTableSql.length() - 2) + ")";
            
            PreparedStatement createTableStatement = connection.prepareStatement(createTableSql);
            createTableStatement.execute();
            
            // 插入数据
            String insertSql = "INSERT INTO table_name VALUES (";
            Iterator<Row> rows = sheet.iterator();
            rows.next(); // 跳过列头
            while (rows.hasNext()) {
                Row row = rows.next();
                Iterator<Cell> cells = row.cellIterator();
                
                insertSql = "INSERT INTO table_name VALUES (";
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    String cellValue = "";
                    switch (cell.getCellType()) {
                        case STRING:
                            cellValue = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            cellValue = Double.toString(cell.getNumericCellValue());
                            break;
                        case BOOLEAN:
                            cellValue = Boolean.toString(cell.getBooleanCellValue());
                            break;
                        default:
                            break;
                    }
                    insertSql += "'" + cellValue + "', ";
                }
                insertSql = insertSql.substring(0, insertSql.length() - 2) + ")";
                
                PreparedStatement insertStatement = connection.prepareStatement(insertSql);
                insertStatement.execute();
            }
            
            // 关闭连接
            connection.close();
            inputStream.close();
            
            System.out.println("数据导入成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}