环境：

    jdk1.8+poi-3.17+mysql-5.6+excel2010(xlsx)

工具类：

    1.excel解析工具类，使用poi sax模式解析excel。生成数据格式为List<LinkedHashMap<String,String>>

private List
    
     
      > results=new ArrayList<>();public void process(LinkedHashMap
      
        dataMap, int curRow) {        if(headerMap.isEmpty()){            for(Map.Entry
       
         e: dataMap.entrySet()){                if(null != e.getKey() && null != e.getValue()){                    headerMap.put(e.getKey(), e.getValue().toLowerCase());                }            }        }else{            LinkedHashMap
        
          data = new LinkedHashMap<>();            for (Map.Entry
         
           e : headerMap.entrySet()) { String key = e.getValue(); String value = null==dataMap.get(e.getKey())?"":dataMap.get(e.getKey()); data.put(key, value); } count.getAndIncrement(); results.add(data); } }
         
        
       
      
     
    

2.数据库的excel的配置信息t_fields--可以配置多个excel

fname--excel标题字段

fcode--标题字段对应的数据库字段

data_type--建表字段信息

ftable--excel对应的数据表

3.excel配置表实体类---jpa

package com.fdrx.model;import lombok.Data;import javax.persistence.*;import java.io.Serializable;/** * Created by shea on 2019-06-05. */@Data@Entity@Table(name ="t_fields")public class ExcelBean implements Serializable {    @Id    @GeneratedValue(strategy= GenerationType.IDENTITY)    private Integer id;    @Column(length = 10)    private String fcode;    @Column(length = 10)    private String fname;    @Column(length = 20)    private String dataType;    @Column(length = 10)    private String ftable;}package com.fdrx.dao;import com.fdrx.model.ExcelBean;import org.springframework.data.jpa.repository.JpaRepository;/** * Created by shea on 2019-06-05. */@Repositorypublic interface ExcelBeanDao extends JpaRepository
    
      {}
    

4.excel工具导入服务类

package com.fdrx.service;import com.fdrx.dao.ExcelBeanDao;import com.fdrx.model.ExcelBean;import lombok.extern.slf4j.Slf4j;import org.apache.commons.lang.StringUtils;import org.springframework.stereotype.Service;import javax.annotation.Resource;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.*;import java.util.stream.Collectors;/** * Created by shea on 2019-06-06. */@Service@Slf4jpublic class ExcelPipeline {    @Resource    private ExcelBeanDao excelBeanDao;    private HashMap
    
      allFields= new HashMap<>();    /**     * 写入数据库     * @param datas excel解析数据     * @param table 目标表     * @return     */    public void saveData(List
     
      
       > datas,String table){        //1.获取配置文件        List
       
         all = excelBeanDao.findAll();        //2.根据表 获取对应字段映射关系        allFields=all.stream().filter(m -> table.equals(m.getFtable()))                .collect(Collectors.toMap(ExcelBean::getFname,ExcelBean::getFcode,(k1, k2)->k2,LinkedHashMap::new));        //3.根据数据库配置信息,生成建表sql        String collect = all.stream().filter(m -> table.equals(m.getFtable()))                .map(m -> String.format("`%s` %s comment '%s'",m.getFcode(), StringUtils.isEmpty(m.getDataType())?"text":m.getDataType(),m.getFname()))                .collect(Collectors.joining(","));        String createSql = String.format("create table IF NOT Exists %s (%s)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", table,collect);        //4.根据excel标题 获取导入字段        List
        
          title = createTitle(datas.get(0));        //5.根据导入字段，生成对应的导入数据列表        List
         
          
           > importLists = convert2List(datas,title); //6.根据导入字段，生成导入语句 String inserSql = createSql(title, table); //7.开始msql导入 Connection con=null; try { con = getConnector();//jdbc链接 con.setAutoCommit(false);//不自动提交 //指定建表语句，存在则不创建 executeUpdate(createSql,new ArrayList<>(),con); for (List
           
             strings : importLists) { try { executeUpdate(inserSql, strings, con); } catch (SQLException e) { log.error("{}=》写入失败！",strings.get(0)); } } } catch (Exception e) { log.error(e.getMessage()); }finally { try { if(con!=null){ con.setAutoCommit(true); con.close(); } } catch (SQLException e) { log.error(e.getMessage()); } } log.info("导入完成"); } /** * 执行 sql 更新 * @param sql sql * @param params params * @param conn conn * @return * @throws SQLException */ private void executeUpdate(String sql, List
            
              params, Connection conn ) throws SQLException { try (PreparedStatement ps = conn.prepareStatement(sql)){ int paramIdx = 1; for(Object obj: params){ ps.setObject(paramIdx, obj); paramIdx ++; } ps.executeUpdate(); } } /** * 根据excel数据生成插入语句 */ private String createSql( List
             
               title,String table){ Optional
              
                sqlField = title.stream() .map(str -> String.format("`%s`", allFields.get(str.trim()))) .filter(s->!"".equals(s)&& null!=s) .reduce((a, b) -> String.format("%s,%s", a, b)); Optional
               
                 sqlValue=title.stream() .map(str -> String.format("`%s`", allFields.get(str.trim()))) .filter(s->!"".equals(s)&& null!=s) .map(str->"?").reduce((a, b) -> String.format("%s,%s", a, b)); String sql = String.format("replace into `%s`(%s) values(%s)",table, sqlField.orElse(""), sqlValue.orElse("")); return sql; } /** * 映射excel标题行 与 数据库配置信息===》确定要导入的数据列 * @param headers * @return */ private List
                
                  createTitle(LinkedHashMap
                 
                   headers){ return headers.keySet().stream() .filter(str -> allFields.get(str.trim())!=null) .collect(Collectors.toList()); } /** * 转换数据list * @param datas excel数据 * @param title 导入字段列表 * @return */ private List
                  
                   
                    > convert2List(List
                    
                     
                      > datas,List
                      
                        title){ List
                       
                        
                         > collect = datas.stream().map(data -> { List
                         
                           single = title.stream().map(data::get).collect(Collectors.toList()); return single; }).collect(Collectors.toList()); return collect; } /** * jdbc * @return * @throws SQLException */ public Connection getConnector() throws SQLException { Connection conn = null; String url = "jdbc:mysql://localhost:3306/weixin?useUnicode=true&characterEncoding=utf-8"; String user = "root"; String passwd = "root"; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, user, passwd); } catch (ClassNotFoundException e) { e.printStackTrace(); } return conn; }}
                         
                        
                       
                      
                     
                    
                   
                  
                 
                
               
              
             
            
           
          
         
        
       
      
     
    

5.测试

package com.fdrx.demo;import com.fdrx.Application;import com.fdrx.service.ExcelPipeline;import com.fdrx.service.JdbcPipeline;import com.fdrx.util.excel.Excel2007Parser;import lombok.extern.slf4j.Slf4j;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringRunner;import javax.annotation.Resource;import java.io.FileInputStream;import java.io.InputStream;import java.util.LinkedHashMap;import java.util.List;/** * Created by shea on 2019-06-05. */@RunWith(SpringRunner.class)@SpringBootTest(classes = Application.class)//如果需要注入服务，则要启@Slf4jpublic class TestDemo {    @Resource    private JdbcPipeline jdbcPipeline;    @Resource    private ExcelPipeline excelPipeline;    @Test    public void test1()throws Exception{        String table ="t_excel2";        InputStream in = new FileInputStream("C:\\Users\\shea\\Desktop\\excel2.xlsx");        //调用事件驱动解析excel        Excel2007Parser parser = new Excel2007Parser(in);        parser.parse();        //解析结果        List
    
     
      > datas = parser.getResults();        excelPipeline.saveData(datas,table);    }}
     
    

excel导入都可以通过只配置mysql数据表中的excel配置表t_fields即可。

再导入excel的服务类中指定导入excel对应的ftable 即可，程序会自动创建对应的excel表，excel导入就可以实现快捷导入。

当导入的excel模板发生变化的时候，也能通过t_fields配置，实现标题的动态配置。

 

 

修改为批量插入：不能使用replace into 进行主键覆盖策略，所有需要根据自己业务情况选择合适的插入方法

    

#生成插入语句--修改/**     * 根据第一行生成插入语句     */    private String createSql( List
    
      title,String table){        Optional
     
       sqlField = title.stream()                .map(str -> String.format("`%s`", allFields.get(str.trim())))                .filter(s->!"".equals(s)&& null!=s)                .reduce((a, b) -> String.format("%s,%s", a, b));        String sql = String.format("insert into `%s`(%s) values ",table, sqlField.get());        return sql;    }#生成批量插入private int batch = 1000; String inserSql = createSql(title, table);        try {            ArrayList
      
        res = new ArrayList<>();            //使用批量插入---每N条生成一个插入语句            for (List
       
         strings : lists) {               String s = strings.stream().map(m -> String.format("'%s'", m)).reduce((x,y)->String.join(",",x,y))                        .map(m -> String.format("(%s)", m)).get();                res.add(s);                if(res.size() % batch ==0){                    String join = String.join(",", res);                    executeUpdateBatch(inserSql+join, con);                    res.clear();                }            }            String join = String.join(",", res);            executeUpdateBatch(inserSql+join, con);            try {                if(con!=null){                    con.setAutoCommit(true);                    con.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        } catch (Exception e) {            e.printStackTrace();        }/**     * 执行 sql批量更新     * @param sql sql     * @param conn conn     * @return     * @throws SQLException     */    private void executeUpdateBatch(String sql, Connection conn ) throws SQLException {        AtomicInteger count = new AtomicInteger(1);        PreparedStatement ps = conn.prepareStatement(sql);       ps.executeUpdate();       log.info("第{}次提交！",count.getAndIncrement());    }
       
      
     
    

 

耗时统计：

 

 

    测试数据：35个字段，共65330条数据

    1.使用单条导入耗时：导入完成,共导入65330条数据,一共耗时24.816秒。

    2.使用批量导入耗时：导入完成,共导入65330条数据,一共耗时7.359秒。

 

新增excel快速导入配置信息到t_fields:

前两行为标题行，第三行为fcode，第四行为data_type.

你也可以直接用poi解析，生成 List<ArrayList<String>>的数据即可：第一行为标题fname，第二行为字段fcode，第三行为建表数据格式data_type.

String fieldsTable = "t_fields";//excel字段配置表        String ftable ="t_excel4";//excel对应的table名称        //todo Excel2007Parser解析默认第一行为标题好，数据从第二行开始计算的,所以excel中多定义一行标题        InputStream in = new FileInputStream("C:\\Users\\shea\\Desktop\\t4.xlsx");        Excel2007Parser parser = new Excel2007Parser(in);        parser.parse();        //解析结果        List
    
     
      > datas = parser.getResults();//直接转换为需要的数据格式 List
      
       
        > values= datas.get(0).keySet().stream().map(k -> {            return IntStream.range(0, 3).boxed().map(i -> datas.get(i).get(k)).collect(Collectors.toList());        }).collect(Collectors.toList());/**        //获取解析结果转为list        List
        
         
          > collect = datas.stream().map(m -> { return new ArrayList<>(m.values()); }).collect(Collectors.toList()); //实现行转列--生成插入需要的数据结构 List
          
           
            > values = IntStream.range(0, collect.get(0).size()).boxed().map(i -> { return IntStream.range(0, 3).boxed().map(index -> collect.get(index).get(i)).collect(Collectors.toList()); }).collect(Collectors.toList());*/ String sql = String.format("insert into %s (`fname`,`fcode`,`data_type`,`ftable`) values (?,?,?,'%s')",fieldsTable,ftable); Connection conn = getcon(); values.forEach(v-> { try { executeUpdate(sql,v,conn); } catch (SQLException e) { e.printStackTrace(); } });
           
          
         
        
       
      
     
    

根据标题自动生成slq字段---中文缩写

1.引入拼音处理包

        
     
      com.hankcs
         
     
      hanlp
         
     
      portable-1.7.2
     
    

2.根据中文标题拼音首字母缩写生成字段

@Test    public void createTable()throws Exception{        String fieldsTable = "t_fields";//excel字段配置表        String ftable ="t_excel6";//excel对应的table名称        //todo Excel2007Parser解析默认第一行为标题好，数据从第二行开始计算的,所以excel中多定义一行标题        InputStream in = new FileInputStream("C:\\Users\\shea\\Desktop\\t6.xlsx");        Excel2007Parser parser = new Excel2007Parser(in);        parser.parse();        //解析结果        List
    
     
      > datas = parser.getResults();        LinkedHashMap
      
        map = new LinkedHashMap<>();        datas.get(0).keySet().forEach(k->{            String fcode = HanLP.convertToPinyinFirstCharString(k, "", false);            if(map.containsKey(fcode)){                String code = assertLive(map, fcode, 1);                map.put(code,k);            }else {                map.put(fcode,k);            }        });        List
       
        
         > values = map.entrySet().stream()                .map(m -> Stream.of(m.getKey(), m.getValue(), "text").collect(Collectors.toList()))                .collect(Collectors.toList());        String sql = String.format("insert into %s (`fcode`,`fname`,`data_type`,`ftable`) values (?,?,?,'%s')",fieldsTable,ftable);        Connection conn = getcon();        values.forEach(v-> {            try {                executeUpdate(sql,v,conn);            } catch (SQLException e) {                e.printStackTrace();            }        });        conn.close();    }    //递归查询，避免重复字段    private String assertLive(Map
         
           map,String code,int index){ String suffix = String.valueOf(index); if(map.containsKey(code.concat(suffix))){ index++; return assertLive(map,code,index); } return code.concat(suffix); }
         
        
       
      
     
    

结果：