java实现excel导入数据库

以下是一个示例代码，可以将Excel文件中的数据导入到MySQL数据库中：

import java.io.File;
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.ss.usermodel.WorkbookFactory;

public class ExcelToDatabase {
    public static void main(String[] args) {
        String jdbcURL = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "password";
        String excelFilePath = "data.xlsx";

        int batchSize = 20;

        Connection connection = null;

        try {
            long start = System.currentTimeMillis();

            FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

            Workbook workbook = WorkbookFactory.create(inputStream);

            Sheet sheet = workbook.getSheetAt(0);

            Iterator<Row> rowIterator = sheet.iterator();

            connection = DriverManager.getConnection(jdbcURL, username, password);
            connection.setAutoCommit(false);

            String sql = "INSERT INTO users (name, email, country_code) VALUES (?, ?, ?)";
            PreparedStatement statement = connection.prepareStatement(sql);

            int count = 0;

            rowIterator.next(); // skip the header row

            while (rowIterator.hasNext()) {
                Row nextRow = rowIterator.next();
                Iterator<Cell> cellIterator = nextRow.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell nextCell = cellIterator.next();
                    int columnIndex = nextCell.getColumnIndex();

                    switch (columnIndex) {
                    case 0:
                        String name = nextCell.getStringCellValue();
                        statement.setString(1, name);
                        break;
                    case 1:
                        String email = nextCell.getStringCellValue();
                        statement.setString(2, email);
                        break;
                    case 2:
                        String countryCode = nextCell.getStringCellValue();
                        statement.setString(3, countryCode);
                        break;
                    }

                }

                statement.addBatch();

                if (count % batchSize == 0) {
                    statement.executeBatch();
                }

            }

            workbook.close();

            // execute the remaining queries
            statement.executeBatch();

            connection.commit();
            connection.close();

            long end = System.currentTimeMillis();
            System.out.printf("Import done in %d ms\n", (end - start));

        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                System.out.println("Rollback failed " + e1.getMessage());
            }
        }

    }
}

这个例子使用了Apache POI库来读取Excel文件。在这个例子中，我们假设Excel文件的第一行是标题行，所以我们调用了`rowIterator.next()`来跳过它。然后，我们循环遍历每一行，并使用`cellIterator`来遍历每一列。我们使用`PreparedStatement`来构造SQL语句，然后使用`addBatch()`将其添加到批处理中。我们使用`executeBatch()`方法在提交之前执行批处理。最后，我们在`catch`块中回滚事务，以确保数据的一致性。