EasyExcel实现Excel导出

EasyExcel

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。
他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。

引入依赖

 	<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.1.3</version> 
    </dependency>
```# 表结构
```java
CREATE TABLE `student`  (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生表ID',
  `sname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学生姓名',
  `sno` bigint NOT NULL COMMENT '学号',
  `sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '性别',
  `age` int NOT NULL COMMENT '年龄',
  `is_deleted` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
  PRIMARY KEY (`id`)
);

之前在上传的文章中已经导入过一些数据，所以现在表中有数据，可直接导出

项目结构

导出模板类

@Data
public class StudentVo {

    /**学生姓名**/
    @ExcelProperty(value = "姓名",index = 0)
    private String sname;
    /**学号**/
    @ExcelProperty(value = "学号",index = 1)
    private Long sno;
    /**性别**/
    @ExcelProperty(value = "性别",index = 2)
    private String sex;
    /**年龄**/
    @ExcelProperty(value = "年龄",index = 3)
    private Integer age;

}

Controller

@RestController
@RequestMapping("/easyExcel")
public class EasyExcelController {

    @Resource
    private IStudentService studentService;

    @PostMapping("/studentExport")
    public void studentExport(HttpServletResponse response){
        studentService.studentExport(response);
    }

}

Service

public interface IStudentService extends IService<Student> {
    void studentExport(HttpServletResponse response);
}

ServiceImpl

@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements IStudentService {
    
    @Resource
    private StudentMapper studentMapper;
    
    @Override
    public void studentExport(HttpServletResponse response) {
        try {
            String fileName = "student";
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet studentInfo = EasyExcel.writerSheet(0,"student")
                    .head(StudentVo.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .build();
            List<Student> studentList = studentMapper.selectList(null);
            excelWriter.write(studentList,studentInfo);

            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
            excelWriter.finish();
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}

mapper

public interface StudentMapper extends BaseMapper<Student> {
}

启动项目

PostMan测试

导出成功