SpringBoot整合EasyExcel实现Excel导出功能

àì夳堔傛蜴生んèń 2023-06-12 04:17 22阅读 0赞

## SpringBoot整合EasyExcel实现Excel导出功能 ##

### 导入需要的依赖 ###

<dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>easyexcel</artifactId>
       <version>1.1.2-beta5</version>
    </dependency>

### 编写实体类，映射表头 ###

import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.metadata.BaseRowModel;
    import lombok.Data;
    
    import java.io.Serializable;
    import java.util.Date;
    
    @Data
    public class Student extends BaseRowModel implements Serializable {
        public static final long serialVersionUID = 1L;
        @ExcelProperty(value = {"学号"}, index = 0)
        private Long studentId;
    
        @ExcelProperty(value = {"姓名"}, index = 1)
        private String studentName;
    
        @ExcelProperty(value = {"准考证号"}, index = 2)
        private long examId;
    
        @ExcelProperty(value = {"身份证号"}, index = 3)
        private String idNumber;
    
        @ExcelProperty(value = {"生源地"}, index = 4)
        private String cradle;
    
        @ExcelProperty(value = {"家庭住址"}, index = 5)
        private String address;
    
        @ExcelProperty(value = {"入学年份"}, index = 6)
        private Integer enroll_year;
    
        @ExcelProperty(value = {"班级Id"}, index = 7)
        private Integer classId;
    
        @ExcelProperty(value = {"房间Id"}, index = 8)
        private Integer roomId;
    
        @ExcelProperty(value = {"性别"}, index = 9)
        private String gender;
    
        @ExcelProperty(value = {"出生日期"}, index = 10)
        private Date birthday;
    
        @ExcelProperty(value = {"手机号"}, index = 11)
        private String phone;
    
        @ExcelProperty(value = {"邮箱"}, index = 12)
        private String mail;
    
        @ExcelProperty(value = {"激活状态"}, index = 13)
        private Integer infoStatus;
    
        @ExcelProperty(value = {"报到状态"}, index = 14)
        private Integer payStatus;
    
        @ExcelProperty(value = {"注册状态"}, index = 15)
        private Integer registerStatus;
    
        @ExcelProperty(value = {"交通工具"}, index = 16)
        private String transportation;
    
        @ExcelProperty(value = {"到达日期"}, index = 16)
        private Date date;
    }

### 控制器使用 ###

package org.graduation.freshmanwelcome.handler;
    
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.metadata.Sheet;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import org.graduation.freshmanwelcome.entity.Student;
    import org.graduation.freshmanwelcome.service.StudentAdminService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.List;
    
    @RestController
    public class ExportHandler {
    
        @Autowired
        private StudentAdminService studentAdminService;
    
        @GetMapping("/exportExcel")
        public void export(HttpServletResponse response) throws IOException {
            List<Student> list = studentAdminService.queryAllStudent();
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            String fileName = "学生信息表";
            Sheet sheet = new Sheet(1, 0,Student.class);
            //设置自适应宽度
            sheet.setAutoWidth(Boolean.TRUE);
            // 第一个 sheet 名称
            sheet.setSheetName("学生信息");
            writer.write(list, sheet);
            //通知浏览器以附件的形式下载处理，设置返回头要注意文件名有中文
            response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx");
            writer.finish();
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            out.flush();
        }
    }

### 测试 ###

![测试][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDUxMDMw_size_16_color_FFFFFF_t_70]  
![excel][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDUxMDMw_size_16_color_FFFFFF_t_70]: /images/20230531/d142c65e1ed0463cb9df80e4661e74c7.png
[excel]: /images/20230531/974f7ecdcfca4e1380253e94354267b7.png