pom引入
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.75</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
实体
package com.example.easy.excle.data.req.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.ToString;
/**
* @author lbb
* 不能使用bulid模式会报错
* @Accessors(chain = true) 也不能使用,数据映射不上了
*/
@Data
@ToString
public class ExcelTemplate {
/**
* 第一列,第二列
* x0,x1...
*/
@ExcelProperty(index = 0)
private String x0;
@ExcelProperty(index = 1)
private String x1;
@ExcelProperty(index = 2)
private String x2;
@ExcelProperty(index = 3)
private String x3;
@ExcelProperty(index = 4)
private String x4;
@ExcelProperty(index = 5)
private String x5;
@ExcelProperty(index = 6)
private String x6;
@ExcelProperty(index = 7)
private String x7;
@ExcelProperty(index = 8)
private String x8;
@ExcelProperty(index = 9)
private String x9;
}
实体对应监听器
package com.example.easy.excle.listener.easyexcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.easy.excle.data.req.excel.ExcelTemplate;
import lombok.Getter;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
/**
* @author lbb
* 方法执行顺序,从上到下
*/
@Getter
public class ExcelListener extends AnalysisEventListener<ExcelTemplate> {
/**
* 读到到数据
*/
List<ExcelTemplate> excelTemplateList = new LinkedList<>();
/**
* 读取表头,如果设置了 headRowNumber(0) ,则改方法不会被调用
* 空实现即可,或者根据headMap.size()验证列数,context.readSheetHolder().getApproximateTotalRowNumber()验证行数
*
* @param headMap
* @param context
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头信息:"+headMap+"共多少列"+headMap.size()+"共多少行"+context.readSheetHolder().getApproximateTotalRowNumber());
}
/**
* 一行一行的去读
* 这里可以根据 list 的size去分批入库,防止OOM
* list 的size每够3000条,存一次数据库,之后清空list,
* 但:1.如果导入失败需要事务回滚就不能分批入库
* 2.本来解析excel是在controller,存库在service,为防止OOM,这里分层混了
* 所以推荐一次入库,控制文件流的大小即可
* @param excelTemplate
* @param analysisContext
*/
@Override
public void invoke(ExcelTemplate excelTemplate, AnalysisContext analysisContext) {
excelTemplateList.add(excelTemplate);
//读取第一行时,获取总行数
if (excelTemplateList.size() == 1) {
System.out.println("共多少行" + analysisContext.readSheetHolder().getApproximateTotalRowNumber());
}
}
/**
* 读完之后执行,空实现即可,或者日志记录size
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("ok了"+excelTemplateList);
}
}
main方法测试
//headRowNumber(0)从第一行读,默认是不读表头的,看需求
public static void main(String[] args) {
String filePath = "/Users/lbb/Downloads/1.xlS";
ExcelListener excelListener = new ExcelListener();
EasyExcel.read(filePath, ExcelTemplate.class, excelListener)
.sheet()
.headRowNumber(0)
.doRead();
List<ExcelTemplate> excelTemplateList = excelListener.getExcelTemplateList();
System.out.println("结果" + excelTemplateList);
String s = JSON.toJSONString(excelTemplateList);
System.out.println("JSON" + s);
//向外写,needHead(Boolean.FALSE)可有可无,我们的表头也在数据里面,就象征性的设置一下不需要表头
String fileName = "/Users/lbb/Downloads/" + System.currentTimeMillis() + ".xls";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
List<ExcelTemplate> excelTemplates = JSONArray.parseArray(s, ExcelTemplate.class);
EasyExcel.write(fileName).needHead(Boolean.FALSE).sheet("模板").doWrite(excelTemplates);
//只导出表头,即对用户来说就是下载模板
fileName = "/Users/lbb/Downloads/模板" + System.currentTimeMillis() + ".xls";
EasyExcel.write(fileName).sheet("模板").doWrite(Arrays.asList(excelTemplates.get(0)));
}
但还是有实体耦合度
easyExcel还提供了,无实体的,美滋滋
package com.example.easy.excle.listener.easyexcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 直接用list接收数据
* https://www.yuque.com/easyexcel/doc/read#f14acd88
*
* @author
*/
@Slf4j
@Getter
public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
/**
* 结果集
*/
List<List<String>> list = new ArrayList<>();
/**
* 一行一行的读
* @param data
* @param context
*/
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
List<String> listData = new ArrayList<>();
for (int i = 0; i < data.size(); i++) {
listData.add(data.get(i));
}
list.add(listData);
}
/**
* 读完之后的操作
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("共多少行" + list.size());
}
}
public static void main(String[] args) {
//读
String fileName = "/Users/lbb/Downloads/3.xlS";
NoModelDataListener noModelDataListener = new NoModelDataListener();
EasyExcel.read(fileName, noModelDataListener).headRowNumber(0).sheet().doRead();
List<List<String>> list = noModelDataListener.getList();
String s = JSON.toJSONString(list);
System.out.println(s);
// 写
List<List> lists = JSON.parseArray(s, List.class);
fileName = "/Users/lbb/Downloads/" + System.currentTimeMillis() + ".xls";
EasyExcel.write(fileName).needHead(false).sheet("模板").doWrite(lists);
}