EasyExcel

---

前言

语雀地址：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();

---

总结

人生物语：很多时候，努力并不是为了得到奖赏，而是为了被认同。当有人说你变了的时候，不过是因为你不用再按他们的方式生活罢了。很多时候，我们视为刻骨铭心的记忆，而别人却早已忘记，与其纠结于心，不如看淡，看轻。