Java使用EasyExcel将数据导出Excel表

一、添加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的使用

三、写一个导出工具类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;
    }