最近公司要求在项目中新增一个导入导出Excel的功能，在对比Apache poi、jxl和easyexcel后，最终选择了easyexcel。
alibaba.easyExcel
easyexcel重写了POI对07版的Excel的解析，能够原本一个3M的Excel的用POI sax依然需要100M左右内存降低到KB级别，并且再大的Excel中不会出现内存溢出，03版依赖POI的萨克斯模式。在上层做了模型转换的封装，让使用者更加简单方便。
Excel文件导入、导出
下面是我写的阿里easyexcel实现Excel导入、导出示例，使用网络下载的方法导出数据。
准备工作
先准备一个测试导入的Excel文档，后缀名必须是【.xlsx】
添加maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta4</version>
</dependency>
实体类
/**
* @author SJH
* @date 2020-06-10
*/
@Data
public class Atest extends BaseRowModel
{
private static final long serialVersionUID = 1L;
/**
* value: 表头名称
* index: 列的号, 0表示第一列
*/
/** 编号 */
private Integer id;
/** 姓名 */
@ExcelProperty(value ="姓名", index = 0)
private String aname;
/** 年龄 */
@ExcelProperty(value ="年龄", index = 1)
private Integer aage;
public void setId(Integer id)
{
this.id = id;
}
public Integer getId()
{
return id;
}
public void setAname(String aname)
{
this.aname = aname;
}
public String getAname()
{
return aname;
}
public void setAage(Integer aage)
{
this.aage = aage;
}
public Integer getAage()
{
return aage;
}
}
controller 控制层
/**
* @author SJH
* @date 2020-06-10
*/
@Controller
@RequestMapping("/system/atest")
public class AtestController {
@Autowired
private AtestServiceImpl atestService;
/**
* 导入文件
* @param file
* @throws ParseException
*/
@ApiOperation(value = "导入文件",httpMethod = "POST")
@PostMapping("/importExcelData")
@ResponseBody
public AjaxResult importExcelData(MultipartFile file,HttpServletRequest request, HttpServletResponse response) {
Cors cors=new Cors();
cors.corsInfo(response,request);
Map<String,Object> map=atestService.importExcelData(file);
if(map.get("code").equals(0)){
return success((String) map.get("msg"));
}
return error((String) map.get("msg"));
}
/**
* 下载模板
*/
@ApiOperation(value = "下载模板",httpMethod = "POST")
@GetMapping("/downloadTemplate")
public void downloadTemplate(HttpServletRequest request, HttpServletResponse response) {
Cors cors=new Cors();
cors.corsInfo(response,request);
atestService.downloadTemplate(request,response);
}
/**
* 导出全部
*/
@ApiOperation(value = "导出全部",httpMethod = "GET")
@GetMapping("/downloadAllExcel")
public void downloadAllExcel(HttpServletRequest request, HttpServletResponse response) {
Cors cors=new Cors();
cors.corsInfo(response,request);
atestService.downloadAllExcel(request,response);
}
/**
* 批量导出
*/
@ApiOperation(value = "批量导出",httpMethod = "GET")
@GetMapping("/downloadSomeExcel/{ids}")
public void downloadSomeExcel(@PathVariable("ids") String ids, HttpServletRequest request, HttpServletResponse response) {
Cors cors=new Cors();
cors.corsInfo(response,request);
atestService.downloadSomeExcel(ids,request,response);
}
}
service 接口类
/**
* @author SJH
* @date 2020-06-10
*/
public interface IAtestService
{
/**
* 查询信息
*
* @param id 编号
* @return 信息
*/
public Atest selectAtestById(Integer id);
/**
* 查询信息列表
*
* @param atest 信息
* @return 信息集合
*/
public List<Atest> selectAtestList(Atest atest);
/**
* 新增信息
*
* @param atest 信息
* @return 结果
*/
public int insertAtest(Atest atest);
/**
* 导入文件
* @param file
* @return
*/
public Map<String,Object> importExcelData(MultipartFile file);
/**
* 下载模板
*/
public void downloadTemplate(HttpServletRequest request, HttpServletResponse response);
/**
* 导出全部
*/
public void downloadAllExcel(HttpServletRequest request, HttpServletResponse response);
/**
* 批量导出
*/
public void downloadSomeExcel(String ids, HttpServletRequest request, HttpServletResponse response);
/**
* 根据多个编号查询对应信息列表
* @param ids 编号字符串
* @return
*/
public List<Atest> selectListForDownloadSomeExcel(String ids);
}
service 实现类
/**
* @author SJH
* @date 2020-06-10
*/
@Slf4j
@Service
public class AtestServiceImpl implements IAtestService
{
@Resource
private AtestMapper atestMapper;
/**
* 查询信息
*
* @param id 编号
* @return 信息
*/
@Override
public Atest selectAtestById(Integer id)
{
return atestMapper.selectAtestById(id);
}
/**
* 查询信息列表
*
* @param atest 信息
* @return 信息集合
*/
@Override
public List<Atest> selectAtestList(Atest atest)
{
return atestMapper.selectAtestList(atest);
}
/**
* 新增信息
*
* @param atest 信息
* @return 结果
*/
@Override
public int insertAtest(Atest atest)
{
return atestMapper.insertAtest(atest);
}
/**
* 导入文件
* @param file
* @return
*/
@Transactional
@Override
public Map<String,Object> importExcelData(MultipartFile file){
Map<String,Object> map=new HashMap<>();
//设置新增成功的数量和数组长度都为0
int num=0,size=0;
try {
String filename = file.getOriginalFilename();
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && (!filename.toLowerCase().endsWith(".xlsx")))) {
map.put("code",500);
log.error("导入文件 ---------- 导入文件格式错误");
map.put("msg","文件格式错误！");
return map;
}
List<Object> list= EasyExcelUtil.readExcel(file, Atest.class);
//将获取的到字符串数据转成JSON数组
JSONArray jsonArray = JSONArray.fromObject(list);
JSONArray new_jsonArray = JSONArray.fromObject(jsonArray.toArray());
//获取数组长度并赋值
size=new_jsonArray.size();
//使用foreach循环，将数据插入到数据库中
for(Object o:new_jsonArray){
//将字符串转成json数据
JSONObject jsonObject = (JSONObject) o;
Atest atest=new Atest();
atest.setAname((String) jsonObject.get("aname"));
atest.setAage((Integer) jsonObject.get("aage"));
//判断是否新增成功，新增成功则新增成功的数量加1
if(insertAtest(atest)>0){
num+=1;
}
}
}catch(Exception e){
//异常存入日志中
log.error("异常 ------------- " + e);
//进行事务回滚
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}
//判断新增成功的数量是否等于数组长度
if (num==size){
map.put("code",0);
log.info("导入文件 ---------- 导入成功");
map.put("msg","导入成功！");
return map;
}
map.put("code",500);
log.error("导入文件 ---------- 导入失败");
map.put("msg","导入失败！");
return map;
}
/**
* 下载模板
*/
@Override
public void downloadTemplate(HttpServletRequest request, HttpServletResponse response) {
ServletOutputStream out = null;
try {
//清除空白行
response.reset();
//设置下载类型
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//设置不缓存
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
//在代理服务器端防止缓冲
response.setDateHeader("Expires", 0);
//设置当前时间作为下载名称
// 设置头消息
response.setHeader("Content-Disposition", "attachment;filename=template.xlsx");
//下载到浏览器默认地址
out= response.getOutputStream();
List<Atest> list =null;
EasyExcelUtil easyExcelUtil=new EasyExcelUtil();
//将数据写入excel表中
Boolean flag = easyExcelUtil.writeExcel(out,Atest.class, list);
if(flag){
log.info("下载模板 ---------- 下载成功");
}else {
log.error("下载模板 ---------- 下载失败");
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (out != null){
try {
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 导出全部
*/
@Override
public void downloadAllExcel(HttpServletRequest request, HttpServletResponse response) {
ServletOutputStream out = null;
try {
//清除空白行
response.reset();
//设置下载类型
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//设置不缓存
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
//在代理服务器端防止缓冲
response.setDateHeader("Expires", 0);
//设置当前时间作为下载名称
String nowtimes=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
// 设置头消息
response.setHeader("Content-Disposition", "attachment;filename=" + nowtimes +".xlsx");
//下载到浏览器默认地址
out= response.getOutputStream();
Atest atest=new Atest();
List<Atest> list = selectAtestList(atest);
EasyExcelUtil easyExcelUtil=new EasyExcelUtil();
//将数据写入excel表中
Boolean flag = easyExcelUtil.writeExcel(out,Atest.class, list);
if(flag){
log.info("导出全部 ---------- 导出成功");
}else {
log.error("导出全部 ---------- 导出失败");
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (out != null){
try {
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 批量导出
*/
@Override
public void downloadSomeExcel(String ids, HttpServletRequest request, HttpServletResponse response){
ServletOutputStream out = null;
try {
//清除空白行
response.reset();
//设置下载类型
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//设置不缓存
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
//在代理服务器端防止缓冲
response.setDateHeader("Expires", 0);
//设置当前时间作为下载名称
String nowtimes=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
// 设置头消息
response.setHeader("Content-Disposition", "attachment;filename=" + nowtimes +".xlsx");
//下载到浏览器默认地址
out= response.getOutputStream();
List<Atest> list =selectListForDownloadSomeExcel(ids);
EasyExcelUtil easyExcelUtil=new EasyExcelUtil();
//将数据写入excel表中
Boolean flag = easyExcelUtil.writeExcel(out,Atest.class,list);
if(flag){
log.info("批量导出 ---------- 导出成功");
}else {
log.error("批量导出 ---------- 导出失败");
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (out != null){
try {
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 根据多个编号查询对应信息列表
*
* @return 编号字符串
*/
@Override
public List<Atest> selectListForDownloadSomeExcel(String ids) {
//创建数组
List<Atest> list = new ArrayList<Atest>();
//将字符串转换成数组
String[] a = Convert.toStrArray(ids);
for (int i = 0; i < a.length; i++) {
//将查询出来的数据添加到数组中
list.add(selectAtestById(Integer.parseInt(a[i])));
}
return list;
}
}
mapper 接口类
```java
/**
* @author SJH
* @date 2020-06-10
*/
public interface AtestMapper
{
/**
* 查询信息
*
* @param id 编号
* @return 信息
*/
public Atest selectAtestById(Integer id);
/**
* 查询信息列表
*
* @param atest
* @return 信息集合
*/
public List<Atest> selectAtestList(Atest atest);
/**
* 新增信息
*
* @param atest 信息
* @return 结果
*/
public int insertAtest(Atest atest);
}
mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aaa.project.system.atest.mapper.AtestMapper">
<resultMap type="com.aaa.project.system.atest.domain.Atest" id="AtestResult">
<result property="id" column="id" />
<result property="aname" column="aname" />
<result property="aage" column="aage" />
</resultMap>
<sql id="selectAtestVo">
select id, aname, aage from atest
</sql>
<select id="selectAtestList" parameterType="com.aaa.project.system.atest.domain.Atest" resultMap="AtestResult" useCache="true">
<include refid="selectAtestVo"/>
<where>
<if test="id != null "> and id = #{id}</if>
<if test="aname != null and aname != '' "> and aname = #{aname}</if>
<if test="aage != null "> and aage = #{aage}</if>
</where>
</select>
<select id="selectAtestById" parameterType="Long" resultMap="AtestResult">
<include refid="selectAtestVo"/>
where id = #{id}
</select>
<insert id="insertAtest" parameterType="com.aaa.project.system.atest.domain.Atest">
insert into atest
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="aname != null and aname != '' ">aname,</if>
<if test="aage != null ">aage</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="aname != null and aname != '' ">#{aname},</if>
<if test="aage != null ">#{aage}</if>
</trim>
</insert>
</mapper>
easyExcel导入监听类
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
/**
* easyExcel导入监听类
* @author SJH
* @create 2020/6/10
*/
public class EasyExcelListener extends AnalysisEventListener{
private static final Logger log = LoggerFactory.getLogger(EasyExcelListener.class);
private int count = 0;
//自定义用于暂时存储data。
//可以通过实例获取该值
private List<Object> datas = new ArrayList<Object>();
@Override
public void invoke(Object object, AnalysisContext context) {
doSomething(object);//根据自己业务做处理
}
public void doSomething(Object object) {
try {
/**
*@desc 总数增加
*/
count++;
datas.add(object);
} catch (Exception e) {
log.error("批量获取用户数据异常:{}", e.getMessage());
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// datas.clear();//解析结束销毁不用的资源
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
}
easyExcel工具类
/**
* @author SJH
* @create 2020/6/10
*/
public class EasyExcelUtil {
/**
*
* @param os 文件输出流
* @param clazz Excel实体映射类
* @param data 导出数据
* @return
*/
public boolean writeExcel(OutputStream os, Class clazz, List<? extends BaseRowModel> data){
BufferedOutputStream bos= null;
try {
bos = new BufferedOutputStream(os);
ExcelWriter writer = new ExcelWriter(bos, ExcelTypeEnum.XLSX);
//写第一个sheet, sheet1 数据全是List<String> 无模型映射关系
Sheet sheet1 = new Sheet(1, 0,clazz);
writer.write(data, sheet1);
writer.finish();
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
if (bos != null) {
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return true;
}
/**
* @param file 导入Excel文件输入流
* @param clazz Excel实体映射类
* @return
*/
public static List<Object> readExcel(MultipartFile file, Class clazz) {
InputStream inputStream = null;
List<Object> list=null;
try {
inputStream = new BufferedInputStream(file.getInputStream());
// 解析每行结果在listener中处理
AnalysisEventListener listener = new EasyExcelListener();
ExcelReader excelReader = EasyExcelFactory.getReader(inputStream, listener);
excelReader.read(new Sheet(1, 1, clazz));
list=((EasyExcelListener) listener).getDatas();
} catch (Exception e) {
e.printStackTrace();
return list;
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return list;
}
}
html页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<label for="uploadExcel" style="margin: 0;"><i class="fa fa-upload"></i> 导入文件（请使用模板导入）></label>
<input id="uploadExcel" type="file" style="display: none;" /><br><br>
<input type="button" onclick="downloadTemplate()" value="下载模板"/><br><br>
<input type="button" onclick="downloadAllExcel()" value="导出全部"/><br><br>
<input type="button" onclick="downloadSomeExcel()" value="批量导出"/>
<script>
//导入文件
$("#uploadExcel").change(function() {
importExcelData();
});
function importExcelData(){
var file = $("#uploadExcel")[0].files[0];
var fd = new FormData();
fd.append("file", file);
$.ajax({
type:"POST",
url:"http://localhost:107/system/atest/importExcelData",
data: fd,
cache: false,
contentType: false,
processData: false,
success: function (data) {
console.log(data)
}
});
}
//下载模板
function downloadTemplate(){
window.location.href="http://localhost:107/system/atest/downloadTemplate";
}
//导出全部
function downloadAllExcel(){
window.location.href="http://localhost:107/system/atest/downloadAllExcel";
}
//批量导出，批量导出暂时不可用，需要加入bootstrap插件
function downloadSomeExcel(){
var rows = $.common.isEmpty($.table._option.uniqueId) ? $.table.selectFirstColumns() : $.table.selectColumns($.table._option.uniqueId);
if (rows.length == 0) {
$.modal.alertWarning("请至少选择一条数据");
return;
}
$.modal.confirm("确认要导出选中的" + rows.length + "条数据吗?", function() {
window.location.href= "http://localhost:107/system/atest/downloadSomeExcel/"+rows.join() ;
});
}
</script>
</body>
</html>
最后
代码已给出，修改实体类后可直接使用。
请给作者点个赞，关注后续更多编程信息！
有任何问题都可以在下方留言，博主看到会回复！
268
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
