EasyExcel - Alibaba
前言
语雀地址:https://www.yuque.com/easyexcel/doc/easyexcel
1.EasyExcel快速入门
导入Maven相关依赖:
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
1.实例
首先需要创建一个实例,用来封装数据,在我这边的话,使用了一个Lombok插件,记得下载一下。
在字段中需要添加EasyExcel相关注解。
package com.hh.userservice.pojo;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Excel {
@ExcelProperty("ID")
private String id;
@ExcelProperty("用户名")
private String name;
@ExcelProperty("状态")
private String state;
@ExcelProperty("手机号码")
private String phone;
}
2.接口测试模板
@RequestMapping(value = "exportUserBeanData", method = RequestMethod.GET)
public void exportUserBeanData(HttpServletRequest request, HttpServletResponse response) {
String fileName = null;
try {
fileName = new String("测试EasyExcel文件输出数据.xlsx".getBytes(), "ISO-8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
List<Excel> data = getExcelData();
response.addHeader("Content-Disposition", "filename=" + fileName);
ServletOutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
EasyExcel.write(outputStream, Excel.class).sheet("Test").doWrite(data);
}
3.编写单元测试
将数据导出到成Excel文件中,大家可以简单尝试一下,很容易和方便。
@Test
public void exportUserBeanData() {
String fileName = "测试EasyExcel文件输出数据";
List<Excel> data = getExcelData();
EasyExcel.write(fileName, Excel.class).sheet("DATA").doWrite(data);
}
private static List<Excel> getExcelData() {
List<Excel> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
list.add(new Excel(UUID.randomUUID().toString().replaceAll("-",""),"name"+i,"0","13006257601"));
}
return list;
}
导入数据,当前是将数据全部读取到内存中。如果数据量非常大的话,建议拆分,然后进行读取。
@Test
public void readUserBeanData() {
String filePath = "E:\\workspace\\user\\user-service\\测试EasyExcel文件输出数据";
try {
List<Excel> excels = EasyExcel.read(filePath).head(Excel.class).sheet().doReadSync();
System.out.println();
excels.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
}
}
2.第一种自定义表头方式,导出一个Excel文件,多个Sheet工作溥。
1.导出多个工作溥数据
@Test
public void exportExcelData() {
List<List<String>> outDataList = new ArrayList<>();
List<String> dataList = new ArrayList<>();
dataList.add("1");
dataList.add("zhangsan");
dataList.add("17879303721");
outDataList.add(dataList);
ExcelWriter excelWriter = EasyExcel.write("测试多Sheet工作溥.xlsx").build();
WriteSheet writeSheet = EasyExcel.writerSheet(0,"工作溥1").head(getHead()).build();
excelWriter.write(outDataList,writeSheet);
WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "工作溥2").head(getHead()).build();
excelWriter.write(outDataList,writeSheet1);
excelWriter.finish();
}
public static List<List<String>> getHead() {
List<List<String>> arrayList = new ArrayList<>();
List<String> names1 = new ArrayList<>();
List<String> names2 = new ArrayList<>();
List<String> names3 = new ArrayList<>();
names1.add("id");
names2.add("name");
names3.add("telphone");
arrayList.add(names1);
arrayList.add(names2);
arrayList.add(names3);
return arrayList;
}
2.导入多个Sheet工作溥数据,但是需要创建一个监听器,用来接收数据。
@Test
public void testReadMultiWork() {
ExcelReader excelReader = EasyExcel.read("测试多Sheet工作溥.xlsx").build();
ReadSheet readSheet = EasyExcel.readSheet(0).head(getHead()).registerReadListener(new CustomAnalysisEventListener()).build();
ReadSheet readSheet1 = EasyExcel.readSheet(1).head(getHead()).registerReadListener(new CustomAnalysisEventListener()).build();
ExcelReader read = excelReader.read(readSheet,readSheet1);
read.finish();
}
第一种方式创建监听器读取数据
package com.hh.userservice.listen;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.LinkedHashMap;
public class CustomAnalysisEventListener extends AnalysisEventListener<LinkedHashMap> {
@Override
public void invoke(LinkedHashMap linkedHashMaps, AnalysisContext analysisContext) {
System.out.println(linkedHashMaps);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
第二种方式,可以直接读取0号工作溥中的数据,放到集合数组中
List<LinkedHashMap> string = EasyExcel.read("测试多Sheet工作溥.xlsx").sheet(0).head(getHead()).doReadSync();
List<LinkedHashMap> string = EasyExcel.read("测试多Sheet工作溥.xlsx").sheet(1).head(getHead()).doReadSync();
3.第二种使用实体表,导出一个Excel文件,多个Sheet工作溥。
1.导出多个工作溥数据
@Test
public void testExportExcelData() {
List<TestExportVO> arrayList1 = new ArrayList<>();
List<TestExportVO> arrayList2 = new ArrayList<>();
arrayList1.add(new TestExportVO("1","zhangsan","17879303721"));
arrayList1.add(new TestExportVO("2","lisi","17879303721"));
arrayList2.add(new TestExportVO("3","wangwu","17879303721"));
ExcelWriter exportVO = EasyExcel.write("ExportVO.xlsx").build();
WriteSheet sheet1 = EasyExcel.writerSheet(0, "1").head(TestExportVO.class).build();
exportVO.write(arrayList1,sheet1);
WriteSheet sheet2 = EasyExcel.writerSheet(1, "2").head(TestExportVO.class).build();
exportVO.write(arrayList2,sheet2);
exportVO.finish();
}
2.读取多个Sheet工作溥
@Test
public void testImportExcelData() {
ExcelReader excelReader = EasyExcel.read("ExportVO.xlsx").build();
ReadSheet readSheet1 = EasyExcel.readSheet(0, "1").registerReadListener(new ObjectAnalysisEventListener()).build();
ReadSheet readSheet2 = EasyExcel.readSheet(0, "2").registerReadListener(new ObjectAnalysisEventListener()).build();
excelReader.read(readSheet1,readSheet2);
excelReader.finish();
}
第一种方式创建监听器读取数据
package com.hh.userservice.listen;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.LinkedHashMap;
public class ObjectAnalysisEventListener extends AnalysisEventListener<LinkedHashMap> {
@Override
public void invoke(LinkedHashMap linkedHashMap, AnalysisContext analysisContext) {
System.out.println(linkedHashMap);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
第二种方式,可以直接读取0号工作溥中的数据,放到集合数组中
List<TestExportVO> list = EasyExcel.read("ExportVO.xlsx").head(TestExportVO.class).sheet(0).doReadSync();
List<TestExportVO> list1 = EasyExcel.read("ExportVO.xlsx").head(TestExportVO.class).sheet(1).doReadSync();
总结
人生物语:很多时候,努力并不是为了得到奖赏,而是为了被认同。当有人说你变了的时候,不过是因为你不用再按他们的方式生活罢了。很多时候,我们视为刻骨铭心的记忆,而别人却早已忘记,与其纠结于心,不如看淡,看轻。