1. 依赖

<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.6</version></dependency><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version></dependency> 

2. 表数据对象

import lombok.Getter;@Getterpublic class Person { private int id; private String name; private int age; private String sex; private String company; private String year; private int salary;}

3. listener

import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.blb.espro.pojo.Person;import com.blb.espro.utils.DBUtil;import java.util.ArrayList;import java.util.List;public class PersonData extends AnalysisEventListener<Person> { private int max=10000; private List<Person> userList=new ArrayList<Person>(); public void invoke(Person user, AnalysisContext analysisContext) { System.out.println(user); userList.add(user); if(userList.size()>=max) { DBUtil.batchSave(userList); userList.clear(); } } public void doAfterAllAnalysed(AnalysisContext analysisContext) { DBUtil.batchSave(userList); }}

4. 数据导入

import com.alibaba.excel.EasyExcel;import com.blb.espro.listener.PersonData;import com.blb.espro.pojo.Person;import lombok.Setter;import java.sql.*;import java.util.List;public class DBUtil { private static String filePath = "C:\\Users\\Administrator\\Desktop\\222.xls"; static{ try { Class.forName("com.mysql.jdbc.Driver"); System.out.println("加载驱动成功！！"); } catch (ClassNotFoundException e) { e.printStackTrace(); throw new RuntimeException("加载驱动失败！！！"); } } /*?? ? * 连接数据的方法?? ? */ public static Connection getConn(){ Connection conn = null; try { conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/es?useUnicode=true&characterEncoding=utf8", "root", "root"); System.out.println("数据库连接成功！！"); }catch (SQLException e){ e.printStackTrace(); throw new RuntimeException("数据库连接失败！！"); } return conn; } /*?? ? * 关闭数据库连接?? ? */ public static void closeConn(Connection conn){ try { if(conn != null){ conn.close(); } }catch (SQLException e){ e.printStackTrace(); } } public static void batchSave(List<Person> userList) { Connection conn=getConn(); String sql="insert into person values(?,?,?,?,?,?,?)"; PreparedStatement preparedStatement = null; try { preparedStatement = conn.prepareStatement(sql); int i=1; for(Person person:userList) { preparedStatement.setInt(1,person.getId()); preparedStatement.setString(2,person.getName()); preparedStatement.setInt(3,person.getAge()); preparedStatement.setString(4,person.getSex()); preparedStatement.setString(5,person.getCompany()); preparedStatement.setString(6,person.getYear()); preparedStatement.setInt(7,person.getSalary()); preparedStatement.addBatch(); } preparedStatement.executeBatch(); preparedStatement.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } closeConn(conn); } public static void main(String[] args) { EasyExcel.read(filePath, Person.class, new PersonData()).sheet().headRowNumber(0).doRead(); }}