EasyExcel Manual

一：引入依赖

<!--excel操作工具-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.3</version>
</dependency>

<!--日期格式化工具-->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.12.1</version>
</dependency>

<!--实体工具-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

<!--测试工具-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
</dependency>

<!--json工具-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>2.0.19</version>
</dependency>

<!--spring web-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

二：代码模板

（1）实体类

package cn.aiyingke.easyexcel.domain;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

@Data
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

（2）核心类

package cn.aiyingke.easyexcel.common;

import cn.aiyingke.easyexcel.domain.DemoData;
import cn.aiyingke.easyexcel.dto.DemoDAO;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;

import java.util.List;

/**
 * Author: Rupert Tears
 * Date: Created in 17:15 2022/11/20
 * Description: Thought is already is late, exactly is the earliest time.
 */
public class DemoDataListener implements ReadListener<DemoData> {

    private static final int BATCH_COUNT = 100;
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private final DemoDAO demoDAO;

    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }

    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data));
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
    }

    private void saveData() {
        demoDAO.save(cachedDataList);
    }
}

（3）传输层

package cn.aiyingke.easyexcel.dto;

import cn.aiyingke.easyexcel.domain.DemoData;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * Author: Rupert Tears
 * Date: Created in 17:16 2022/11/20
 * Description: Thought is already is late, exactly is the earliest time.
 */
@Component
public class DemoDAO {
    public void save(List<DemoData> list) {
        System.out.println("insert" + list);
    }
}

（4）本地读写

package cn.aiyingke.easyexcel.test;

import cn.aiyingke.easyexcel.common.DemoDataListener;
import cn.aiyingke.easyexcel.domain.DemoData;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import org.junit.Test;

import java.util.Date;
import java.util.List;

/**
 * Author: Rupert Tears
 * Date: Created in 16:55 2022/11/20
 * Description: Thought is already is late, exactly is the earliest time.
 */
public class EasyTest {
    private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        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);
        }
        return list;
    }

    @Test
    public void getPath() {
        String relativelyPath = System.getProperty("user.dir");
        System.out.println(relativelyPath);
    }

    String PATH = System.getProperty("user.dir") + "\\data\\";

    @Test
    public void simpleWrite() {
        String fileName = PATH + "EasyTest.xlsx";
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(this::data);
    }

    @Test
    public void simpleRead() {
        String fileName = PATH + "EasyTest.xlsx";
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

}

（5）Web读写

package cn.aiyingke.easyexcel.web;

import java.io.IOException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import cn.aiyingke.easyexcel.common.DemoDataListener;
import cn.aiyingke.easyexcel.domain.DemoData;
import cn.aiyingke.easyexcel.dto.DemoDAO;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

@Controller
public class WebTest {

    @Resource
    private DemoDAO demoDao;

    private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + 0);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        EasyExcel.write(response.getOutputStream(), DemoData.class).sheet("模板").doWrite(data());
    }


    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), DemoData.class, new DemoDataListener(demoDao)).sheet().doRead();
        return "success";
    }
}