二、阿里开源 EasyExcel
使用可以直接看(点击)官方文档
github地址:(https://github.com/alibaba/easyexcel
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。easyexcel重写了poi对07版Excel的解析,能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M,并且再大的excel不会出现内存溢出,03版依赖POI的sax模式。在上层做了模型转换的封装,让使用者更加简单方便
**
pom中引入xml相关依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
</dependencies>
写excel(导出)
**
通用数据生成 后面不会重复写
//封装好的数据
private List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
对照excel写实体类
// @ExcelProperty("字符串标题") 表头名
@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
/**
* 最简单的写
* <p>1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>2. 直接写即可
*/
@Test
public void simpleWrite() {
// 写法1
String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去读,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03版本的excel 则 传入excelType参数即可xlsx改成xls
// 参数一:写入excel文件路径
// 参数二:写入的数据类型是TbDemo
//data()方法是写入的数据,结果是List<DemoData>集合,查询全部数据的方法
// 根据用户传入字段 假设我们要忽略 date
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}
public static void uploadExcel(HttpServletResponse response, String name, Class cla, List<?> list) {
//防止中文件名,在浏览器消失
String fileName = new String(name.getBytes(), StandardCharsets.ISO_8859_1);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
try {
EasyExcel.write(response.getOutputStream(), cla).sheet(name).doWrite(list);
} catch (IOException e) {
e.printStackTrace();
}
}
读Excel(导入)
对象
@Data
public class DemoData {
private String string;
private Date date;
private Double doubleData;
}
持久层
/**
* 假设这个是你的DAO存储。当然还要这个类让spring管理,当然你不用需要存储,也不需要这个类。
**/
public class DemoDAO {
public void save(List<DemoData> list) {
// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
}
}
监听器
/ 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<DemoData> list = new ArrayList<DemoData>();
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private DemoDAO demoDAO;
public DemoDataListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
demoDAO = new DemoDAO();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param demoDAO
*/
public DemoDataListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
* 该方法不会读取表头
* 一行一行读取excel内容
*/
@Override
public void invoke(DemoData data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));//使用需要导入json依赖
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*读取完毕后操作
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
demoDAO.save(list);
LOGGER.info("存储数据库成功!");
}
}
最简单的读
@Test
public void simpleRead() {
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
// 写法1:
String fileName = "D:\\IDEA_Project\\easy-excel\\src\\main\\resources\\excel\\demo.xlsx";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
// 参数一:读取的excel文件路径
// 参数二:读取sheet的一行,将参数封装在DemoData实体类中
// 参数三:读取每一行的时候会执行DemoDataListener监听器
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
案例:
1.Controller层
@RestController
@RequestMapping("/subject")
@CrossOrigin//跨域
public class SubjectController {
@Autowired
private SubjectService subjectService;
@PostMapping("upload/excel")
public Result uploadExcel(MultipartFile file) {
subjectService.batchImport(file, subjectService);
return Result.success();
}
}
2.service接口层
public interface SubjectService {
/**
* excel导入
* @param file
* @param subjectService
*/
void batchImport(MultipartFile file,SubjectService subjectService);
}
3. SubjectServiceImpl 实现类
@Service
public class SubjectServiceImpl implements SubjectService {
/**
* excel 上传
* @param file
* @param subjectService
*/
@Override
public void batchImport(MultipartFile file ,SubjectService subjectService) {
//1 获取文件输入流
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(inputStream, ExcelSubjectData.class, new SubjectExcelListener(subjectService)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
}
}
4.监听器
package com.yzh.edu.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.yzh.edu.entity.Subject;
import com.yzh.edu.excel.ExcelSubjectData;
import com.yzh.edu.service.SubjectService;
import java.util.Map;
/**
* @author yzh
*/
public class SubjectExcelListener extends AnalysisEventListener<ExcelSubjectData> {
public SubjectService subjectService;
/**
* 无参构造器
*/
public SubjectExcelListener() {}
/**
* 创建有参数构造,传递subjectService用于操作数据库
* @param subjectService
*/
public SubjectExcelListener(SubjectService subjectService) {
this.subjectService = subjectService;
}
/**
* 一行一行去读取excle内容
*/
@Override
public void invoke(ExcelSubjectData excelSubjectData, AnalysisContext analysisContext) {
if (null == excelSubjectData) {
System.out.println("excel导入失败");
}
//添加一级分类
Subject oneSubject = getOneSubject(excelSubjectData.getOneSubjectName());
//没有相同的
if (null == oneSubject) {
oneSubject = new Subject();
oneSubject.setTitle(excelSubjectData.getOneSubjectName());
oneSubject.setParentId("0");
subjectService.save(oneSubject);
}
//获取一级分类的id
String pid = oneSubject.getId();
//添加二级分类
Subject twoSubject = getTwoSubject(excelSubjectData.getTwoSubjectName(), pid);
if (null == twoSubject) {
twoSubject = new Subject();
twoSubject.setTitle(excelSubjectData.getTwoSubjectName());
twoSubject.setParentId(pid);
subjectService.save(twoSubject);
}
}
//读取excel表头信息
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头信息:"+headMap);
}
//读取完成后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
//判断一级分类是否重复
private Subject getOneSubject(String name) {
QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("parent_id", "0");
queryWrapper.eq("title", name);
Subject serviceOne = subjectService.getOne(queryWrapper);
return serviceOne;
}
//判断二级分类是否重复
private Subject getTwoSubject(String name, String pid) {
QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("title", "name");
queryWrapper.eq("parent_id", "pid");
Subject serviceOne = subjectService.getOne(queryWrapper);
return serviceOne;
}
}
模板导出
/**
* 根据模板,导出重点河流来水情况
*/
@Override
public void downLoadExcel(Map<String, Object> parameterSet) {
// 塔城市
List<WrRiverRVo> riverTCAll = wrRiverRMapper.getRiverTCAll(parameterSet);
// 额敏县
List<WrRiverRVo> emAll = wrRiverRMapper.getEMAll(parameterSet);
// 乌苏市
List<WrRiverRVo> wsAll = wrRiverRMapper.getWSAll(parameterSet);
// 沙湾县
List<WrRiverRVo> swAll = wrRiverRMapper.getSWAll(parameterSet);
// 托里县
List<WrRiverRVo> tlAll = wrRiverRMapper.getTLAll(parameterSet);
// 裕民县
List<WrRiverRVo> ymAll = wrRiverRMapper.getYMAll(parameterSet);
// 和丰县
List<WrRiverRVo> hfAll = wrRiverRMapper.getHFAll(parameterSet);
// 模板
String templateFileName = "D:\\work\\新需求20200603\\20200507徐洁整理新需求\\数据库建表\\河流\\河流监测信息表模板.xlsx";
// 新的
String newFileName = "D:\\work\\新需求20200603\\20200507徐洁整理新需求\\数据库建表\\河流\\河流监测信息表" + System.currentTimeMillis()
+ ".xlsx";
try {
ExcelWriter excelWriter = EasyExcel.write(newFileName).withTemplate(templateFileName).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
// 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 data1,然后多个list必须用 FillWrapper包裹
excelWriter.fill(new FillWrapper("tc", riverTCAll), writeSheet);
excelWriter.fill(new FillWrapper("em", emAll), writeSheet);
excelWriter.fill(new FillWrapper("ws", wsAll), writeSheet);
excelWriter.fill(new FillWrapper("sw", swAll), writeSheet);
excelWriter.fill(new FillWrapper("tl", tlAll), writeSheet);
excelWriter.fill(new FillWrapper("ym", ymAll), writeSheet);
excelWriter.fill(new FillWrapper("hf", hfAll), writeSheet);
// 千万别忘记关闭流
excelWriter.finish();
File file = new File(newFileName);
Workbook wb = WorkbookFactory.create(new FileInputStream(file));
// 刷新excel公式(否则公式不会重新计算)
wb.setForceFormulaRecalculation(true);
wb.write(new FileOutputStream(newFileName));
} catch (Exception e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
}