Java使用EasyExcel将数据导出Excel表

以你之姓@ 2022-11-25 04:13 296阅读 0赞

# 一、添加EasyExcel依赖 #

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

# 二、准备一个Excel模板类，例如创建`ExcelModalJobBean.class` #

@Data
    public class ExcelModelJobBean {
    
         @ExcelProperty(value = "序号",index = 0)
         private String id;
    
         @ExcelProperty(value = "用人单位名称",index = 1)
         private String companyName;
    
         @ExcelProperty(value = "岗位名称",index = 2)
         private String name;
    
         @ExcelProperty(value = "招聘人数",index = 3)
         private String number;
    
         @ExcelProperty(value = "学历要求",index = 4)
         private String education;
    
         @ExcelProperty(value = "学位要求",index = 5)
         private String degree;
    
         @ExcelProperty(value = "需求专业",index = 6)
         private String major;
    
         @ExcelProperty(value = "专业技术职称要求",index = 7)
         private String theTitleFor;
    
         @ExcelProperty(value = "专业职业资格要求",index = 8)
         private String theQualification;
    
         @ExcelProperty(value = "意向高校",index = 9)
         private String university;
    
         @ExcelProperty(value = "薪酬待遇(月薪)",index = 10)
         private String salary;
    
         @ExcelProperty(value = "发布时间",index = 11)
         private String createTime;
    
         @ExcelProperty(value = "应聘人数",index = 12)
         private String  forTheNumber;
    
    }

#### 注意：其中get、set方法我使用lombok的注解@Data，需要先安装lombok以及添加相关依赖：[lombok的使用][lombok] ####

# 三、写一个导出工具类`ExportExcelUtil .class` #

/**
     * 导出excel工具类
     * @author cy
     */
    public class ExportExcelUtil {
    
        /**
         *
          * @param response 响应体
         * @param list 数据
         * @param fileName 导出文件名
         * @param sheetName 导出文件的sheet名
         * @param object 对应的excel模板类
         */
           public static  void  writeExcel(HttpServletResponse response, List list,
                                          String fileName, String sheetName, Object object) throws Exception {
                  EasyExcel.write(getOutputStream(fileName,response),object.getClass())
                           .sheet(sheetName)
                           .doWrite(list);
    
           }
    
           private static OutputStream getOutputStream(String fileName,HttpServletResponse response) throws Exception{
               try {
                   fileName = URLEncoder.encode(fileName, "UTF-8");
                   response.setContentType("application/vnd.ms-excel");
                   response.setCharacterEncoding("utf8");
                   response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
                   response.setHeader("Pragma", "public");
                   response.setHeader("Cache-Control", "no-store");
                   response.addHeader("Cache-Control", "max-age=0");
                   return response.getOutputStream();
               } catch (IOException e) {
                   throw new Exception("导出excel表格失败!", e);
               }
           }
    }

# 四、调用方式： #

List jobList = mapToJob(jobRemote.listCompanyJob()); //设置模板类与对象属性的对应关系
    ExportExcelUtil.writeExcel(response, jobList, "岗位列表" + FastDateFormat.getInstance("yyyy-MM-dd").format(new Date()), "第一页", new ExcelModelJobBean());

#### 注意：需要设置好对象与Excel模板类属性的对应，例如：手动设置模板的属性 ####

//处理招聘岗位数据
        private List<ExcelModelJobBean> mapToJob(List<JobDo> list){
            List<ExcelModelJobBean> excelModelJobBeans = new ArrayList<>();
            list.forEach(vo  ->{
                ExcelModelJobBean excelModelJobBean  = new ExcelModelJobBean();
                excelModelJobBean.setId(vo.getId() != null ? vo.getId().toString() : "");
                excelModelJobBean.setCompanyName(vo.getCompanyName());
                excelModelJobBean.setName(vo.getName());
                excelModelJobBean.setMajor(vo.getMajor());
                excelModelJobBean.setNumber(vo.getNumber() != null ? vo.getNumber().toString() : "");
                excelModelJobBean.setEducation(DictionaryUtil.educationValueName.get(vo.getEducation()));
                excelModelJobBean.setDegree(DictionaryUtil.degreeValueName.get(vo.getDegree()));
                excelModelJobBean.setTheTitleFor(DictionaryUtil.theTitleForValueName.get(vo.getTheTitleFor()));
                excelModelJobBean.setTheQualification(DictionaryUtil.theQualificationValueName.get(vo.getTheQualification()));
                excelModelJobBean.setUniversity(vo.getUniversity());
                excelModelJobBean.setSalary(DictionaryUtil.salaryValueName.get(vo.getSalary()));
                excelModelJobBean.setCreateTime(fastDateFormat.format(vo.getCreateTime()));
                excelModelJobBean.setForTheNumber(vo.getForTheNumber() != null ? vo.getForTheNumber().toString() : "");
                excelModelJobBeans.add(excelModelJobBean);
            });
            return  excelModelJobBeans;
        }

[lombok]: https://blog.csdn.net/longloveqing/article/details/81539749