一,导包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
二, 创建listener
创建了2种,方便使用。
①,json类型listener
package com.leadtrans.report.common;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author: Tyler
* @createDate: 2021/11/17
*/
public class ExcelJsonListener extends AnalysisEventListener<Object> {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelJsonListener.class);
/**
* 自定义用于暂时存储data
*/
private List<JSONObject> dataList = new ArrayList<>();
/**
* 导入表头
*/
private Map<String, Integer> importHeads = new HashMap<>(16);
public List<JSONObject> getDataList() {
return dataList;
}
public void setDataList(List<JSONObject> dataList) {
this.dataList = dataList;
}
public Map<String, Integer> getImportHeads() {
return importHeads;
}
public void setImportHeads(Map<String, Integer> importHeads) {
this.importHeads = importHeads;
}
/**
* 这个每一条数据解析都会来调用
*/
@Override
public void invoke(Object data, AnalysisContext context) {
String headStr = JSON.toJSONString(data);
dataList.add(JSONObject.parseObject(headStr));
}
/**
* 这里会一行行的返回头
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
for (Integer key : headMap.keySet()) {
if (importHeads.containsKey(headMap.get(key))) {
continue;
}
importHeads.put(headMap.get(key), key);
}
}
/**
* 所有数据解析完成了 都会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
LOGGER.info("Excel解析完毕");
}
}
②,泛型listener
package com.leadtrans.report.common;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.apache.commons.compress.utils.Lists;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author: Tyler
* @createDate: 2021/11/18
*/
public class ExcelModelListener<T> extends AnalysisEventListener<T> {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelModelListenner.class);
//数据
List<T> dataList = new ArrayList<T>();
//List<Map> 列名作为key
List<Map<String,String>> dataListMap=new ArrayList<>();
//表头
private Map<String, Integer> importHeads = new HashMap<>(16);
private List<String> headsList = Lists.newArrayList();
public List<T> getDataList() {
return dataList;
}
public List<Map<String,String>> getDataListMap() {
return dataListMap;
}
public void setDataList(List<T> dataList) {
this.dataList = dataList;
}
public Map<String, Integer> getImportHeads() {
return importHeads;
}
public void setImportHeads(Map<String, Integer> importHeads) {
this.importHeads = importHeads;
}
public List<String> getHeadsList() {
return headsList;
}
public void setHeadsList(List<String> headsList) {
this.headsList = headsList;
}
/**
* 这里会一行行的返回头
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
for (Integer key : headMap.keySet()) {
if (importHeads.containsKey(headMap.get(key))) {
continue;
}
importHeads.put(headMap.get(key), key);
if(null != headMap.get(key)){
headsList.add(headMap.get(key));
}
}
}
/**
* 这个每一条数据解析都会来调用
*
* @param t one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(T t, AnalysisContext context) {
dataList.add(t);
if(t instanceof Map) {
Map<String, String> map = new HashMap<>();
Map<String, String> tMap=(Map<String, String>)t;
getImportHeads().forEach((k,v)->{
map.put(k,tMap.get(v));
});
dataListMap.add(map);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
LOGGER.info("所有数据解析完成!");
}
}
三,使用
@SpringBootTest
public class ReportApplicationTests {
@Autowired
ExcelUtil excelUtil;
/**
* EasyExcel 写入xls
*/
@Test
public void writeXls() {
List<List<String>> headList = new ArrayList<>();
List<String> head0 = new ArrayList<>();
head0.add("姓名");
List<String> head1 = new ArrayList<>();
head1.add("年龄");
List<String> head2 = new ArrayList<>();
head2.add("生日");
headList.add(head0);
headList.add(head1);
headList.add(head2);
List<List<Object>> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<Object> data = new ArrayList<>();
data.add("张三");
data.add(25);
data.add(new Date());
list.add(data);
}
String fileName="C:\\Users\\pc\\Desktop\\test.xls";
List<String> myList=new ArrayList<>();
myList.add("t1");
myList.add("t2");
myList.add("t3");
excelUtil.write(fileName,myList,list);
// EasyExcel.write(fileName).head(headList).sheet("模板").doWrite(list);
}
/**
* EasyExcel 读取xlsx(ExcelJsonListener:Json类型监听器)
*/
@Test
public void readXlsx() throws FileNotFoundException {
String fileName="C:\\Users\\pc\\Desktop\\test.xlsx";
ExcelJsonListener excelListener = new ExcelJsonListener();
EasyExcel.read(fileName, excelListener).sheet().doRead();
//read xls head
Map<String, Integer> importHeads = excelListener.getImportHeads();
//read xls data
List<JSONObject> dataList = excelListener.getDataList();
}
/**
* EasyExcel 读取xlsx(ExcelModelListenner: 泛型监听器)
*/
@Test
public void simpleRead() {
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
// 写法1:
String fileName = "C:\\Users\\pc\\Desktop\\demo.xlsx";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
ExcelModelListenner<DemoData> excelListener = new ExcelModelListenner<DemoData>();
EasyExcel.read(fileName, DemoData.class, excelListener).sheet().doRead();
//read xls head
Map<String, Integer> importHeads = excelListener.getImportHeads();
//read xls data
List<DemoData> dataList = excelListener.getDataList();
}
/**
* EasyExcel 读取
*/
@Test
public void simpleWrite() {
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);
}
// 写法1
String fileName = "C:\\Users\\pc\\Desktop\\demo.xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(list);
}
@Test
public void readMap()
{
String fileName="";
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
ExcelModelListenner<Map<String,String>> excelListener = new ExcelModelListenner<>();
EasyExcel.read(fileName, ReportShipmentDto.class, excelListener).sheet().doRead();
//read xls head
Map<String, Integer> dataHeads = excelListener.getImportHeads();
//read xls data
List<Map<String,String>> dataList = excelListener.getDataList();
}