springboot整合阿里easyexcel2.x实现海量数据excel导入导出demo
springboot整合阿里easyexcel实现海量数据excel导入导出。实现demo如下：
1.pom.xml导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-core</artifactId>
<version>5.2.0</version>
</dependency>
2.导出实体类
package com.zwj.easyexcel.data;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
/**
* @Author: zhengwj
* @Description: 特殊号码 导出实体
* @Date: 2020/4/1 11:20
* @Version: 1.0
*/
@Data
public class ConfigFilterExport {
/**
* 特殊号码主键
*/
@ExcelIgnore
private String filterPk;
/**
* 用户号码
*/
@ExcelProperty("用户号码")
private String filterNumber;
/**
* 用户姓名
*/
@ExcelProperty("用户姓名")
private String filterName;
/**
* 归属地
*/
@ExcelProperty("归属地")
private String filterLocation;
/**
* 号码类型
*/
@ExcelProperty("号码类型")
private String filterType;
/**
* 申请人
*/
@ExcelIgnore
private String filterApplicant;
/**
* 申请时间
*/
@ExcelIgnore
private Date filterApptime;
/**
* 申请原因
*/
@ExcelIgnore
private String filterReason;
/**
* 审核状态
*/
@ExcelIgnore
private String filterStatus;
/**
* 是否有效
*/
@ExcelIgnore
private String filterIsenabled;
/**
* 创建人
*/
@ExcelIgnore
private String filterCreater;
/**
* 创建时间
*/
@ExcelIgnore
private Date filterCtime;
/**
* 更新人
*/
@ExcelIgnore
private String filterUpdater;
/**
* 更新时间
*/
@ExcelIgnore
private Date filterUtime;
}
3.导入实体类
package com.zwj.easyexcel.data;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
/**
* @Author: zhengwj
* @Description: 特殊号码 导入实体(自定义解析列)
*
* @Date: 2020/4/1 11:20
* @Version: 1.0
*/
@Data
public class ConfigFilterImport {
/**
* 特殊号码主键
*/
@ExcelIgnore
private String filterPk;
/**
* 用户号码
*/
@ExcelProperty("用户号码")
private String filterNumber;
/**
* 用户姓名
*/
@ExcelProperty("用户姓名")
private String filterName;
/**
* 归属地
*/
@ExcelProperty("归属地")
private String filterLocation;
/**
* 号码类型
*/
@ExcelProperty("号码类型")
private String filterType;
/**
* 申请人
*/
@ExcelIgnore
private String filterApplicant;
/**
* 申请时间
*/
@ExcelIgnore
private Date filterApptime;
/**
* 申请原因
*/
@ExcelIgnore
private String filterReason;
/**
* 审核状态
*/
@ExcelIgnore
private String filterStatus;
/**
* 是否有效
*/
@ExcelIgnore
private String filterIsenabled;
}
3.导入监听类
package com.zwj.easyexcel.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.zwj.easyexcel.dao.ConfigFilterDao;
import com.zwj.easyexcel.data.ConfigFilterImport;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
/**
* @Author: zhengwj
* @Description: 特殊号码导入监听类
* @Date: 2020/4/1 16:30
* @Version: 1.0
*/
// 不能被spring管理
public class ConfigFilterListener extends AnalysisEventListener<ConfigFilterImport> {
private static final Logger logger = LoggerFactory.getLogger(ConfigFilterListener.class);
private static final int BATCH_COUNT = 10000;
List<ConfigFilterImport> list = new ArrayList<>();
private ConfigFilterDao configFilterDao;
public ConfigFilterListener(ConfigFilterDao configFilterDao){
this.configFilterDao = configFilterDao;
}
/**
* 这个每一条数据解析都会来调用
*
* @param configFilter
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param analysisContext
*/
@Override
public void invoke(ConfigFilterImport configFilter, AnalysisContext analysisContext) {
list.add(configFilter);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
logger.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
logger.info("{}条数据，开始存储数据库！", list.size());
configFilterDao.save(list);
logger.info("存储数据库成功！");
}
}
4.导入导出实体的分页查询和批量新增方法
package com.zwj.easyexcel.dao;
import com.zwj.easyexcel.data.ConfigFilterExport;
import com.zwj.easyexcel.data.ConfigFilterImport;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;
import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.UUID;
/**
* @Author: zhengwj
* @Description: 导入导出实体的分页查询和批量新增方法
*
* @Date: 2020/4/1 16:43
* @Version: 1.0
*/
@Repository
public class ConfigFilterDao {
private static final Logger logger = LoggerFactory.getLogger(ConfigFilterDao.class);
@Value("${spring.datasource.url}")
private String url ;
@Value("${spring.datasource.username}")
private String user;
@Value("${spring.datasource.password}")
private String password ;
/**
* 批量增加
* @param list 大批量数据使用原生jdbc
*/
//TODO
public void save(List<ConfigFilterImport> list) {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO TBL_CONFIG_FILTER............"; //插入sql
pstm = conn.prepareStatement(sql);
conn.setAutoCommit(false);
Long startTime = System.currentTimeMillis();
for(ConfigFilterImport dr : list){
pstm.setString(1, UUID.randomUUID().toString().replace("-", ""));
pstm.setString(2,"");
pstm.setString(2,"");
//.........
pstm.addBatch();
}
pstm.executeBatch();
conn.commit();
Long endTime = System.currentTimeMillis();
logger.info("用时：" + (endTime - startTime));
} catch (Exception e) {
logger.error("执行出错{}",e.getMessage());
throw new RuntimeException(e);
} finally {
if (pstm != null) {
try {
pstm.close();
} catch (SQLException e) {
logger.error("执行出错{}",e.getMessage());
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
logger.error("执行出错{}",e.getMessage());
throw new RuntimeException(e);
}
}
}
}
//数据导出使用的分页查询方法 直接调用mybatis相应实体方法（sql优化）
public List<ConfigFilterExport> selectConfigFilterPage(Map<String, Object> param) {
//TODO
return null;
}
}
5.接口
package com.zwj.easyexcel.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.zwj.easyexcel.dao.ConfigFilterDao;
import com.zwj.easyexcel.data.ConfigFilterExport;
import com.zwj.easyexcel.data.ConfigFilterImport;
import com.zwj.easyexcel.listener.ConfigFilterListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 数据量: 多
读: 多
写: 多
* 注：对于大批量数据前端使用ajax异步请求
* @author zwj
* @since 2020-03-31 14:57:45
*/
@RestController
@RequestMapping("/excel")
public class EasyExcelController {
private static final Logger logger = LoggerFactory.getLogger(EasyExcelController.class);
@Resource
private ConfigFilterDao configFilterDao;
/**
* 获取特殊号码导入模板
* @param request
* @param response
* @throws Exception
*/
@RequestMapping(value = "/getConfigFilterTemplate")
public void getConfigFilterTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException{
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode防止中文乱码
String fileName = URLEncoder.encode("特殊号码导入模板", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
List<ConfigFilterImport> list = new ArrayList<>();
EasyExcel.write(response.getOutputStream(), ConfigFilterImport.class).sheet("模板").doWrite(list);
}
/**
* 特殊号码excel导入
* @param file
* @return
* @throws IOException
*/
@RequestMapping(value = { "/uploadConfigFilterExcel" }, method = { RequestMethod.POST })
public String uploadConfigFilterExcel(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), ConfigFilterImport.class, new ConfigFilterListener(configFilterDao)).sheet().doRead();
return "success";
}
/**
* 特殊号码excel导出
* @param param
* @param response
* @throws IOException
*/
@RequestMapping(value = { "/downloadConfigFilter" }, method = { RequestMethod.GET })
public void download(@RequestParam Map<String, Object> param, HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = "ConfigFilter" + System.currentTimeMillis() + ".xlsx";
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 这里 需要指定写用哪个class去写
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ConfigFilterExport.class).build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet("特殊号码").build();
int pageNumber = 1;
int pageSize = 10000;
int dataLength = pageSize;
List<ConfigFilterExport> data = null;
while (dataLength == pageSize){
// int startIndex = (pageNumber - 1) * pageSize;
param.put("pageNo", pageNumber);
param.put("pageSize", pageSize);
data=configFilterDao.selectConfigFilterPage(param); //分页查询
excelWriter.write(data, writeSheet);
if(null == data || data.isEmpty()){
break;
}
dataLength = data.size();
pageNumber++;
//写数据
excelWriter.write(data, writeSheet);
}
excelWriter.finish();
}
}
支持多版本excel，实现方便，直接替换demo中的实体类即可。
框架导出导出效率非常之快，且不会内存溢出，功能优化在于sql，耗时主要在于sql执行，所以大批量数据时一定要使用原生jdbc操作。
完整代码可在github上下载：https://github.com/wojozer/easyexcel-demo
