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(); }
