EasyExcel导出excel简单实现(单sheet页和多sheet页)

EasyExcel导出excel简单实现(单sheet页和多sheet页)

新手一枚,最近在做关于excel的导出,所以就记录下来,还望大家多多指教 ^-^ !

所用导出工具: EasyExcel

导入的依赖:

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>1.0.1</version>
</dependency>

单sheet页导出

controller层代码:

    @GetMapping("/exportExcel")
    public void exportUser(HttpServletResponse response) {
        String fileName = "excel文件名";
        String sheetName = "sheet表名";
        List<ExportDetailExcel> exportUserExcels = exportService.queryUser();
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //可以防止中文乱码,和easyexcel没有关系
        fileName = URLEncoder.encode(fileName,"utf-8");
        response.setHeader("Content-disposition","attachment;filename=" + fileName + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(0,sheetName).head(ExportUserExcel.class).build();
        excelWriter.write(exportDetailExcels,writeSheet);
        //千万别忘记关流,finish会帮忙关流
        excelWriter.finish();
    }

service层代码:

    @Override
    public List<ExportUserExcel> queryUser() {
        List<ExportUserDo> exportUserDos = exportMapper.queryUser();
        List<ExportUserExcel> exportUserExcels = new ArrayList<>();
        for (ExportUserDo exportuserDo : exportUserDos) {
            //将查询出来的结果添加到excel导出对象中
            ExportUserExcel exportUserExcel = ExportUserExcel.builder()
                    .userName(exportUserDo.getUserName())                           
                    .age(exportUserDo.getAge())                     
                    .gender(exportUserDo.getGender())                                                                 
                    .build();
            exportUserExcels.add(exportUserExcel);
        }
        return exportUserExcels;
    }

多sheet页导出

controller层代码:

    @GetMapping("/exportExcel")
    public void exportDetail(HttpServletResponse response) {
        String fileName = "excel文件名";
        String sheetName1 = "sheet表名";
        String sheetName2 = "sheet表名";
        List<ExportDetailExcel> exportUser1Excels = exportService.querySheet1();
        List<ExportStatisticsExcel> exportUser2Excels = exportService.querySheet2();
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //可以防止中文乱码,和easyexcel没有关系
        fileName = URLEncoder.encode(fileName,"utf-8");
        response.setHeader("Content-disposition","attachment;filename=" + fileName + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(0,sheetName1).head(ExportUser1Excel.class).build();
        excelWriter.write(exportDetailExcels,writeSheet);
        writeSheet = EasyExcel.writerSheet(1,sheetName2).head(ExportUser2Excel.class).build();
        excelWriter.write(exportStatisticsExcels,writeSheet);
        //千万别忘记关流,finish会帮忙关流
        excelWriter.finish();

service层代码:
和单sheet导出写法一样,这里就不再赘述.

创建excel表格实体类

@AllArgsConstructor
@NoArgsConstructorUser
@Builder
@HeadRowHeight(value = 20)
public class ExportUserExcel {

    @ExcelProperty(value = "姓名",index = 0)
    @ColumnWidth(value = 10)
    private String userName;

    @ExcelProperty(value = "年龄",index = 1)
    @ColumnWidth(value = 20)
    private String age;

    @ExcelProperty(value = "性别",index = 2)
    @ColumnWidth(value = 20)
    private String gender;
}

这里建议类型都写为String,可以减少不必要的类型转换.
用户实体类没写,到时候自己不要忘了加上.