SpringBoot 整合easyexcel导出Excel

官方文档#

添加依赖#

　　这里SpringBoot项目就不带领大家创建了，直接在pom.xml中添加esayexcel依赖即可

        <!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

创建导出实体类#

Student.java

package net.ybclass.online_ybclass.exceldemo;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;

/**
 * @ClassName：Student
 * @Description：Excel导出实体类
 * @Author：chenyb
 * @Date：2020/11/11 10:30 上午
 * @Versiion：1.0
 */
@ExcelIgnoreUnannotated()
public class Student {

    private Integer myId;

    @ExcelProperty(value = "学号",index = 0) //0 对应导出Excel表格的第一列
    private Integer id;

    @ExcelProperty(value = "姓名",index = 1)
    private String name;

    @ExcelProperty(value = "性别",index = 2)
    private String sex;

    @ExcelProperty(value = "班级",index = 3)
    private String grade;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", grade='" + grade + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getGrade() {
        return grade;
    }

    public void setGrade(String grade) {
        this.grade = grade;
    }
}

控制层#

package net.ybclass.online_ybclass.exceldemo;

import com.alibaba.excel.EasyExcel;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @ClassName：ExcelController
 * @Description：excel导出控制层
 * @Author：chenyb
 * @Date：2020/11/11 10:31 上午
 * @Versiion：1.0
 */
@RestController
public class ExcelController {
    /**
     * 导出
     */
    @RequestMapping("/export")
    public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {


        //模拟需要导出的数据
        List<Student> list = new ArrayList();
        Student student = new Student();
        student.setId(1);
        student.setName("1321");
        student.setSex("男");
        student.setGrade("一年级");
        for (int i = 0; i < 5; i++) {
            list.add(student);
        }

        //设置并导出
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");

        //设置文件名
        SimpleDateFormat fDate = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
        String fileName = "导出测试表" + fDate.format(new Date()) + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        EasyExcel.write(response.getOutputStream(), Student.class).sheet("sheet1").doWrite(list);
    }
}

导出Excel的数据#

导入Excel数据#

创建Student读取类

StudentListener.java

package net.ybclass.online_ybclass.exceldemo;

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

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

/**
 * @ClassName：StudentListener
 * @Description：学生读取类
 * @Author：chenyb
 * @Date：2020/11/20 1:49 下午
 * @Versiion：1.0
 */
public class StudentListener extends AnalysisEventListener<Student> {
    private List<Student> studentList = new ArrayList<Student>();
    public StudentListener() {
        super();
        studentList.clear();
    }
    /**
     * 每一条数据解析都会调用
     */
    @Override
    public void invoke(Student student, AnalysisContext analysisContext) {
        //此处，可以做数据的校验
        studentList.add(student);
    }
    /**
     * 所有数据解析完成都会调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        studentList.forEach(System.out::println);
    }

    public List<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }
}

Student.java

package net.ybclass.online_ybclass.exceldemo;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;

/**
 * @ClassName：Student
 * @Description：Excel导出实体类
 * @Author：chenyb
 * @Date：2020/11/11 10:30 上午
 * @Versiion：1.0
 */
@ExcelIgnoreUnannotated()
public class Student {

    private Integer myId;

    @ExcelProperty(value = "学号",index = 0) //0 对应导出Excel表格的第一列
    private Integer id;

    @ExcelProperty(value = "姓名",index = 1)
    private String name;

    @ExcelProperty(value = "性别",index = 2)
    private String sex;

    @ExcelProperty(value = "班级",index = 3)
    private String grade;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", grade='" + grade + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getGrade() {
        return grade;
    }

    public void setGrade(String grade) {
        this.grade = grade;
    }


}

控制层#

    /**
     * 导入学生信息
     *
     * @param file
     * @throws IOException
     */
    @RequestMapping(value = "import")
    public List<Student> importStudentInfos(MultipartFile file) throws IOException {
        StudentListener studentListener = new StudentListener();
        EasyExcel.read(file.getInputStream(), Student.class, studentListener).sheet().doRead();
        return studentListener.getStudentList();
    }