前言
本文大部分参考借鉴https://www.yuque.com/easyexcel/doc/read
一、导入jar
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
二、监听器
1.先实现一个保存到数据库父类(后面监听器调用),可以是dao层,也可以service层,看情况而定
ExcelDao
package com.example.demo.common;
import java.util.List;
/**
* @ProjectName: mydemo
* @PackageName: com.example.demo.common
* @ClassName: ExcelDao
* @User: JiaWei
* @Date: 2021/08/06 15:17
* @Week: 星期五
* @Description: Excel导入数据库dao层处理
*/
public class ExcelDao<T> {
//需要用到Excel导入数据库时,重写此方法
public int save(List<T> list) {
System.out.println("父类执行");
return 0;
}
}
2.监听器实现
package com.example.demo.utils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.demo.common.ExcelDao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
/**
* @ProjectName: mydemo
* @PackageName: com.example.demo.utils
* @ClassName: ExcelListener
* @User: JiaWei
* @Date: 2021/08/06 14:56
* @Week: 星期五
* @Description: Excel读取监听器
*/
//ExcelListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class ExcelListener<T> extends AnalysisEventListener<T> {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<T> list = new ArrayList<>();
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
* 数据库交互dao层
*/
private ExcelDao<T> excelDao;
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param excelDao
*/
public ExcelListener(ExcelDao excelDao) {
this.excelDao = excelDao;
}
/**
* 一行行读取表格内容
* 这个每一条数据解析都会来调用
*
* @param t one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(T t, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", t);
list.add(t);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 读取完成后的操作
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
//调用保存到数据库方法
excelDao.save(list);
LOGGER.info("存储数据库成功!");
}
}
三、Excel工具类
package com.example.demo.utils;
import com.alibaba.excel.EasyExcel;
import com.example.demo.common.ExcelDao;
import java.io.InputStream;
import java.util.List;
/**
* @ProjectName: mydemo
* @PackageName: com.example.demo.utils
* @ClassName: Excel
* @User: JiaWei
* @Date: 2021/08/06 9:54
* @Week: 星期五
* @Description: Excel工具类
*/
public class ExcelUtil<T> {
private Class<T> clazz;
//监听器
private ExcelListener<T> excelListener;// = new ExcelListener<T>(excelTestDao);
public ExcelUtil(Class<T> clazz) {
this.clazz = clazz;
}
public ExcelUtil(Class<T> clazz, ExcelDao excelDao) {
this.clazz = clazz;
this.excelListener = new ExcelListener(excelDao);
}
public List<T> importEasyExcel(InputStream is) throws Exception {
return EasyExcel.read(is, excelListener).head(clazz).sheet().doReadSync();
}
/**
* 对list数据源将其里面的数据导入到excel表单(EasyExcel)
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @return 结果
*/
public String exportEasyExcel(List<T> list, String sheetName) {
String fileName = "D:/excel_test/" + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(list);
return "OK";
}
}
四、调用读Excel方法,测试类
1.创建一个测试实体类
package com.example.demo.pojo;
import com.alibaba.excel.annotation.ExcelProperty;
/**
* @ProjectName: mydemo
* @PackageName: com.example.demo.pojo
* @ClassName: LogSys
* @User: JiaWei
* @Date: 2021/08/06 9:50
* @Week: 星期五
* @Description:
*/
public class LogSys {
private static final long serialVersionUID = 1L;
public LogSys() {
}
@ExcelProperty(value = "操作序号")
private int id;
@ExcelProperty(value = "状态码")
private String code;
@ExcelProperty(value = "信息描述")
private String msg;
public LogSys(int id, String code, String msg) {
this.id = id;
this.code = code;
this.msg = msg;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
@Override
public String toString() {
return "LogSys{" +
"id=" + id +
", code='" + code + '\'' +
", msg='" + msg + '\'' +
'}';
}
}
2.数据库交互(继承前面ExcelDao)
ExcelTestDao
package com.example.demo.test.dao;
import com.example.demo.common.ExcelDao;
import com.example.demo.pojo.LogSys;
import java.util.List;
/**
* @ProjectName: mydemo
* @PackageName: com.example.demo.test.dao
* @ClassName: ExcelTestDao
* @User: JiaWei
* @Date: 2021/08/06 15:28
* @Week: 星期五
* @Description: 数据库交互
*/
public class ExcelTestDao extends ExcelDao<LogSys> {
@Override
public int save(List<LogSys> list) {
//mybatis情况下,自己实现个批量插入方法
int count = batchInsert(list);
return count;
}
private int batchInsert(List<LogSys> list) {
System.out.println("重写父类方法,将数据保存到数据库,批量插入数据成功");
return 666;
}
}
3.最后调用测试
package com.example.demo.test;
import com.example.demo.pojo.LogSys;
import com.example.demo.test.dao.ExcelTestDao;
import com.example.demo.utils.ExcelUtil;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @ProjectName: mydemo
* @PackageName: com.example.demo.test
* @ClassName: ExcelImportController
* @User: JiaWei
* @Date: 2021/08/06 9:59
* @Week: 星期五
* @Description:
*/
@RestController
@RequestMapping("/file")
public class ExcelImportController {
@RequestMapping("/uploadExcel")
public List<LogSys> uploadExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
//spring中,使用@Autowired注入
ExcelTestDao excelTestDao = new ExcelTestDao();
try {
InputStream is = file.getInputStream();
ExcelUtil excelUtil = new ExcelUtil(LogSys.class, excelTestDao);
List<LogSys> data = excelUtil.importEasyExcel(is);
return data;
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
@RequestMapping("/exportExcel")
public void exportExcel(String sheetName) {
List<LogSys> list = new ArrayList<LogSys>() {{
add(new LogSys(1, "10", "测试1"));
add(new LogSys(2, "20", "测试2"));
add(new LogSys(3, "30", "测试3"));
add(new LogSys(4, "40", "测试4"));
add(new LogSys(5, "50", "测试5"));
add(new LogSys(6, "60", "测试6"));
add(new LogSys(7, "70", "测试7"));
add(new LogSys(8, "80", "测试8"));
}};
ExcelUtil excelUtil = new ExcelUtil(LogSys.class);
excelUtil.exportEasyExcel(list, sheetName);
}
}