【EasyExcel】 background development how to use EasyExcel elegant operation Excel

Background interfaces that read documents typically use the type MultipartFile from which an InputStream can be constructed. To read Excel with EasyExcel, you need to construct a listener first. Luffy has prepared it for you:

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;

import java.util.ArrayList;
import java.util.List;

/ * * *@author Carson
 * @date2020/6/4 21:26 * /
public class ExcelReaderListener<T> extends AnalysisEventListener<T> {
    /** * The data set returned */
    private List<T> list = new ArrayList<>(1 << 6);

    /** * If spring is used, use this constructor. Each time you create a Listener, you need to pass in the Spring-managed classes */
    public ExcelReaderListener(a) {}/ * * * the each data parsing would call for * *@paramThe data data *@paramContext Excel reads the context */
    @Override
    public void invoke(T data, AnalysisContext context) {
        System.out.printf("Parse to a single data :{%s}", JSON.toJSONString(data));
        System.out.println();
        list.add(data);
    }

    /** * when all data is parsed, call **@paramContext Excel reads the context */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {}public List<T> getList(a) {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list; }}Copy the code

After creating the listener, you need to manually write a tool class, specially used to read Excel, Shaoxia here prepared two reading methods:

import com.alibaba.excel.EasyExcel;

import java.io.InputStream;
import java.util.List;

/ * * *@author Carson
 * @date 2020/6/4 21:31
 */
public class ExcelReaderUtil<T> {
    private Class<T> clazz;

    public ExcelReaderUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    public List<T> readByStream(InputStream inputStream) {
        ExcelReaderListener<T> readerListener = new ExcelReaderListener<>();
        EasyExcel.read(inputStream, clazz, readerListener).sheet().doRead();
        return readerListener.getList();
    }

    public List<T> readByPath(String fileName) {
        ExcelReaderListener<T> readerListener = new ExcelReaderListener<>();
        EasyExcel.read(fileName, clazz, readerListener).sheet().doRead();
        returnreaderListener.getList(); }}Copy the code

Note: the code here is generic, feel don’t want to look at the past can be directly copied to use.

Now that the basic reading utility class has been implemented, the template class has been created. What is a template class? It is a class that has different parameters for each Excel header, and each parameter is qualified with the @ExcelProperty annotation.

@Data
public class GoodsReaderModel {
    @ ExcelProperty (SKU "goods")
    public Long skuId;

    @ExcelProperty(" Event Start Time ")
    public String startTime;

    @ExcelProperty(" Event End Time ")
    public String finishTime;
}
Copy the code

Then read from the interface:

    @RequestMapping(value = "/import", method = RequestMethod.POST, produces = {"application/json; charset=UTF-8"})
    @apiOperation (value = "import commodity information ", httpMethod = "POST", notes =" import commodity information ")
    @ResponseBody
    public List<GoodsReaderModel > importGoodsFromExcel(@RequestParam("excel") MultipartFile multipartFile, HttpServletRequest request) {
        if (multipartFile == null) {
            LOGGER.info("Entered Excel information is empty");
            return null;
        }
        ExcelReaderUtil<GoodsReaderModel> excelReader = new ExcelReaderUtil<>(GoodsReaderModel.class);
        List<GoodsReaderModel> modelList = Lists.newArrayList();
        try {
            InputStream inputStream = multipartFile.getInputStream();
            modelList = excelReader.readByStream(inputStream);
        } catch (IOException e) {
            LOGGER.info("Sorry, no file found.");
            return null;
        }
        // Follow up data processing logic
    }
Copy the code

EasyExcel is just a tool class, which I feel is much less important than thread pools, message queues, etc., but it’s nice to be able to skillfully use it in development. Personally, this is the most useful and efficient Excel tool class we’ve ever used. Shaoxia has done a test, EasyExcel only needs about 60 seconds to export one million pieces of data (currently Excel can hold up to 1.04 million pieces of data), while the traditional POI technology takes several hours, and the memory optimization is poor, large objects can not be recycled in time, it is easy to cause the JVM FULL GC.