EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
需求前提 业务需要做一个导入导出功能，可以实现数据的导入导出功能，且不能固定导入导出模板，所以采用了不创建对象的读、写方式。
数据库表的字段和字段名称都有关系表做存储。能知道每个表格导出时需要查询的字段和列名，以及每次导入时需要拼接的sql 添加语句。
通过以下四张表就能动态获取数据库中所有字段的数据关系，以及系统中所有列表页面显示的字段，而根据调整列表页面显示字段来实现一个可以灵活调整的导出功能。
文章图片
文章图片
文章图片
文章图片
文章图片
pom.xml 在pom.xml中加入 com.alibaba.easyexcel 的依赖
com.alibaba
easyexcel
2.1.3
org.apache.poi
poi
3.17
org.apache.poi
poi-ooxml
3.17
导出示例： 1.获取需要导出的列表和列表数据
2.解析列头数据和列表数据
目前需要导出的列表数据。
文章图片
参考官方示例 不创建对象的写
/**
* 不创建对象的写
*/
@Test
public void noModelWrite() {
// 写法1
String fileName = TestFileUtil.getPath() + "noModelWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName).head(head()).sheet("模板").doWrite(dataList());
} private List> head() {
List> list = new ArrayList>();
List> head0 = new ArrayList>();
head0.add("字符串" + System.currentTimeMillis());
List> head1 = new ArrayList>();
head1.add("数字" + System.currentTimeMillis());
List> head2 = new ArrayList>();
head2.add("日期" + System.currentTimeMillis());
list.add(head0);
list.add(head1);
list.add(head2);
return list;
}private List> dataList() {
List> list = new ArrayList>();
for (int i = 0;
i < 10;
i++) {
List
通过逻辑处理获得这个列表的数据，以及显示的字段集合。
ExportController .java
public class ExportController {
EasyExcelUtils.downloadFailedUsingJson(response,listLayoutFieldList,list,listLayout.getName());
}
EasyExcelUtils.java
package com.ac.hdx.base.custom.importexport;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import com.ac.hdx.base.custom.layout.ListLayoutField;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
public class EasyExcelUtils { /**
*
* @param response
* @param listLayoutFieldList 显示的字段名,excel的列头
* @param list 数据内容
* @param fileName 导出的文件名
* @throws IOException
*/
public static void downloadFailedUsingJson(HttpServletResponse response,
List listLayoutFieldList,List> list,String fileName) throws IOException {response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
response.setHeader("Content-disposition", "attachment;
filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream()).head(head(listLayoutFieldList))
.autoCloseStream(Boolean.FALSE).sheet("模板")
.doWrite(dataList(list));
} /**
* 通过解析显示字段集合得到列头
* @param listLayoutFieldList
* @return
*/
private static List> head(List listLayoutFieldList) {
List> list = new ArrayList>();
List> id = new ArrayList>();
id.add("ID");
list.add(id);
//上面两行是为了导出数据ID，不需要的可以不写//下面是解析字段集合获取列头
for (ListLayoutField layoutField : listLayoutFieldList) {
List> head = new ArrayList>();
head.add(layoutField.getInchinese());
list.add(head);
}return list;
} /**
* 通过解析所有数据将数据写入excel中，有特殊业务处理也可在这里进行
* @param listLayoutFieldList
* @return
*/
private static List> dataList(List> list) {
List> excellist = new ArrayList>();
for (Map, Object> map : list) {
List
以上就实现了不创建对象写功能，实现了通过的数据导出功能。
导入示例：
- 实体表进行导入，将表中所有的数据库字段显示在。
- 选择对应的excel文件，使用前端js工具类获取excel的列头。(我使用的是 sheetjs)
- 让用户来将列头与数据库字段关系绑定
- 在上传文件到后台执行导入方法时，将列头与数据库字段的对应对应关系一起提交，用做数据解析。
文章图片
导入通用页面 - 锐客网
th:src="https://www.it610.com/article/@{/static/script/xlsx.full.min.js}">
选择导入的数据源文件
1.你可以把excel(*.xls),excel(*.xlsx)格式的文件导入系统
2.请确认文件格式,所选文件格式与文件的格式要相符
3.选择数据列名与系统字段名的匹配关系
4.如果需要导入数据量过于庞大且数据自身关系复杂时,请拆分多表格分批导入(建议超过3W数据时分批导入)
配置属性对应关系
系统属性
导入属性
系统属性
导入属性
th:fieldid="${m.id}" th:dbname="${m.ename}" name="sheetSelect">
th:inline="javascript">var jsonArr = "";
//使用sheetjs导入文件，获取文件列头，示例只能获取第一个sheet页面的内容
function importFile(obj) {//导入if(validateMethod()){
if(!obj.files) {
return;
}
var f = obj.files[0];
var reader = new FileReader();
reader.onload = function(e) {
var data = https://www.it610.com/article/e.target.result;
var wb = XLSX.read(data, {
type:'binary' //以二进制的方式读取
});
var sheet0=wb.Sheets[wb.SheetNames[0]];
//sheet0代表excel表格中的第一页jsonArr=XLSX.utils.sheet_to_json(sheet0,{header:1,blankrows:false});
//利用接口实现转换。
if(jsonArr.length > 30000){
alert('单表数据超过30000条，请分多次导入！');
return;
}//获取表头 jsonArr[0] 拼接到下拉框中
$("[name=sheetSelect]").empty();
var optionNull = ''
$("[name=sheetSelect]").append(optionNull);
var colIndex = 0;
for (var col in jsonArr[0]) {var option = '';
colIndex++;
$("[name=sheetSelect]").append(option);
}}
reader.readAsBinaryString(f);
}
}//提交数据
function importData(){
if(validateMethod()){
getSelectAttribute();
}
}//获取数据属性绑定,传递至后台进行解析
function getSelectAttribute(){
var propertyMap = '{';
$("[name='sheetSelect']").each(function(){
if(this.value != '空'){
propertyMap+= '"'+$(this).attr('dbname')+'":"'+this.value+'",';
}
});
propertyMap = propertyMap.substring(0,propertyMap.length-1)+'}';
$("[name='propertyMap']").val(propertyMap);
$("#importData").hide();
$("#exportform").ajaxForm(function(data){
processJsonResponse(data);
}).submit();
}//验证文件类型
function validateMethod(){
var fileType = $("select[name='fileType']").val();
var path = $("input[name='file']").val();
var attr = path.substring(path.lastIndexOf(".")+1);
//判断文件选择类型是否相同
if(path==''){
alert("文件不能为空，请选择文件!","warning");
return false;
}else{
if(attr != fileType){
alert("文件类型错误，请重新选择!","warning");
return false;
}
}
return true;
}//选择文件类型时，过滤文件选择框的默认文件格式
$("select[name='fileType']").change(function(){
var fileType = $("select[name='fileType']").val();
$("input[name='file']").attr("accept","."+fileType);
});
参考官方的 不创建对象的读
/**
* 直接用map接收数据
*
* @author Jiaju Zhuang
*/
public class NoModelDataListener extends AnalysisEventListener> {
private static final Logger LOGGER = LoggerFactory.getLogger(NoModelDataListener.class);
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 5;
List> list = new ArrayList>();
@Override
public void invoke(Map data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
LOGGER.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据，开始存储数据库！", list.size());
LOGGER.info("存储数据库成功！");
}
}/**
* 不创建对象的读
*/
@Test
public void noModelRead() {
String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
// 这里 只要，然后读取第一个sheet 同步读取会自动finish
EasyExcel.read(fileName, new NoModelDataListener()).sheet().doRead();
}
ImportController.java
package com.ac.hdx.base.custom.importexport;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import com.ac.hdx.base.custom.role.AuthorizeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.ac.hdx.base.custom.entity.FieldRecordService;
import com.ac.hdx.base.custom.layout.ListLayout;
import com.ac.hdx.base.custom.layout.ListLayoutService;
import com.ac.hdx.base.custom.universal.UniversalService;
import com.ac.hdx.base.framework.utils.JsonResponse;
import com.alibaba.excel.EasyExcel;
/**
* 导入控制器
*
* @author liushao
* @date 2020-02-12
*/
@Controller
@RequestMapping(value = "https://www.it610.com/import")
public class ImportController { @Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
ListLayoutService listLayoutService;
@Autowired
private FieldRecordService fieldRecordService;
@Autowired
private ImportService importService;
@Autowired
private UniversalService universalService;
@Value("${base.txt.uri}")
private String txturi;
@Autowired
private AuthorizeService authorizeService;
/**
* 导入数据
*
* @throws Exception
*/
@RequestMapping(value = "https://www.it610.com/article/importData", method = RequestMethod.POST)
@ResponseBody
public JsonResponse importData(HttpServletRequest request,String entityId, String propertyMap) throws Exception {MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartHttpServletRequest.getFile("file");
EasyExcel.read(file.getInputStream(),new NoModleDataListener(entityId,propertyMap,jdbcTemplate,txturi,universalService)).sheet().doRead();
return JsonResponse.reload("导入成功");
}}NoModleDataListener.java 类中无法注入所以很多类都是实例化NoModleDataListener时传入的
package com.ac.hdx.base.custom.importexport;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.UUID;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import com.ac.hdx.base.custom.universal.UniversalService;
import com.ac.hdx.base.dylan.base.core.DBRule;
import com.ac.hdx.base.dylan.crud.create.IdGenerator;
import com.ac.hdx.base.dylan.crud.retrieve.sqlparse.DicMap;
import com.ac.hdx.base.framework.utils.FileUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONObject;
public class NoModleDataListener extends AnalysisEventListener> { private JdbcTemplate jdbcTemplate;
private UniversalService universalService;
private String txturi;
//字段映射关系
public String propertyStr;
//主表
public String entityId;
//字段映射Map
public Map,String> propertyMap = null;
//字段IDMap
public Map,String> fieldIdMap = new HashMap<>();
//pk字段缓存
public Map,String> pkMap = new HashMap, String>();
public String importSql = "insert into t_";
publicNoModleDataListener(String entityId,String propertyStr,
JdbcTemplate jdbcTemplate,String txturi,UniversalService universalService) {
this.propertyStr = propertyStr;
this.entityId = entityId;
this.jdbcTemplate = jdbcTemplate;
this.universalService = universalService;
this.txturi = txturi;
} /**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 3000;
List> excelList = new ArrayList>();
@Override
public void invoke(Map data, AnalysisContext context) {
excelList.add(data);
if (excelList.size() >= BATCH_COUNT) {
saveData();
}
}@Override
public void doAfterAllAnalysed(AnalysisContext context) {saveData();
pkMap.clear();
}/**
*加上存储数据库
*/
private void saveData() {
try {
//如果字段映射Map为空，则获取映射关系，遍历字段映射拼接批处理语句
if( propertyMap == null) {
propertyMap = JSONObject.parseObject(propertyStr,Map.class);
importSql += entityId +" (";
for (Entry, String> a : propertyMap.entrySet()) {
importSql += a.getKey() +",";
}importSql = importSql.substring(0,importSql.length()-1)+",id,create_datetime) VALUES (";
for (Entry, String> a : propertyMap.entrySet()) {
importSql += "?,";
}
importSql = importSql.substring(0,importSql.length()-1)+",?,now())";
}jdbcTemplate.batchUpdate(importSql, new BatchPreparedStatementSetter() {@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
// TODO Auto-generated method stubint sqlIndex = 1;
//生成Id
String id = IdGenerator.generator(Integer.parseInt(entityId));
/**
* 遍历字段，处理字段类型，依照每个类型的不同进行单独的业务处理
* 如果是pk引用字段则获取列中文字在数据库中对应的ID值，并缓存对应关系
* 如果是下拉字段，则从数据字段缓存中获取对应的ID值
* 如果是文本域字段，则将数据内容写入到文件中,将文件名写入数据库
*/
for (Entry, String> a : propertyMap.entrySet()) {String fieldName = excelList.get(i).get(Integer.parseInt(a.getValue()));
if(fieldName != null) {
if(a.getKey().startsWith("pk_t")) {//PK字段String tableName = DBRule.getTableNameByPkField(a.getKey());
String pkMapKey = tableName+a.getKey()+fieldName;
if(pkMap.get(pkMapKey) ==null){
try {
String sql = "select id,name name from " + tableName +" where name = ?";
Map,Object> resertMap = jdbcTemplate.queryForMap(sql,fieldName);
pkMap.put(pkMapKey, resertMap.get("id").toString());
}catch (Exception e) {
// TODO: handle exception
pkMap.put(pkMapKey, "0");
}
}if(!"0".equals(pkMap.get(pkMapKey))) {
ps.setObject(sqlIndex, pkMap.get(pkMapKey));
}else {
ps.setObject(sqlIndex, null);
}}else if(a.getKey().endsWith("_select")) {//下拉字段if(DicMap.getId(fieldName)!=null) {
ps.setObject(sqlIndex, DicMap.getId(fieldName));
}else {
ps.setObject(sqlIndex, null);
}}else if(a.getKey().endsWith("_text")) {//文本域String code = UUID.randomUUID().toString();
FileUtil.writerString2Uri(fieldName, txturi + "/" + code + ".txt");
Integer fieldId = 0;
if(fieldIdMap.get("t_"+entityId+"_"+a.getKey()+"_fieldName") !=null) {
fieldId = Integer.parseInt(fieldIdMap.get("t_"+entityId+"_"+a.getKey()+"_fieldName"));
}else {
fieldId = selectFiledId(entityId, a.getKey(), jdbcTemplate);
fieldIdMap.put("t_"+entityId+"_"+a.getKey()+"_fieldName", fieldId+"");
}
//插入富文本记录
universalService.textareaSave(Integer.parseInt(entityId), id, fieldId, code);
ps.setObject(sqlIndex, code);
}else {
ps.setObject(sqlIndex, fieldName);
}
}else {
ps.setObject(sqlIndex, null);
}
sqlIndex ++;
}
ps.setObject(sqlIndex, id);
}@Override
public int getBatchSize() {
// TODO Auto-generated method stubreturn excelList.size();
}
});
} catch (Exception e) {
System.out.println(e);
}finally {
//清空集合，回收内存
excelList.clear();
}
}public Integer selectFiledId (String entityId,String fieldName,JdbcTemplate jdbcTemplate) {
String sql = "select id from s_field where name = ? and pk_s_entityrecord_1 = ?";
return jdbcTemplate.queryForObject(sql, Integer.class,fieldName,entityId);
}
}
【插件工具|alibaba Easyexcel 不创建对象的读、写实现通用的管理系统导入，导出功能】NoModleDataListener.java中 saveData()方法主要是系统的具体业务逻辑处理，可根据自身不同做调整。
