环境：

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

工具类：

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

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.

*/

@Repository

public 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

@Slf4j

public 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",m.getFcode(), StringUtils.isEmpty(m.getDataType())?"text":m.getDataType()))

.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)//如果需要注入服务，则要启

@Slf4j

public 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配置，实现标题的动态配置。