环境:
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;