EasyExcel 轻松导出Excel

风尘博客 2019-12-10

4508

关于导出 Excel
文件，可以说是大多数服务中都需要集成的功能。那么，要如何优雅快速地去实现这个功能呢？

一、项目准备

1.1 步骤

1.创建Excel
导出对应的数据模型对象(本文的ExportModel.java
)；2.将数据写入到数据模型对象中；3.导出Excel
。

1.2 导出数据模型对象

该类必须也要继承自 BaseRowModel.java

@Data
public class ExportModel extends BaseRowModel {


    /**
     * 通过 @ExcelProperty 的value 指定每个字段的列名称，index 为列的序号。
     */
    @ExcelProperty(value = "姓名", index = 0)
    private String studentName;


    @ExcelProperty(value = "年级", index = 1)
    private String grade;


    @ExcelProperty(value = "学科", index = 2)
    private String subject;


    @ExcelProperty(value = "分数", index = 3)
    private Integer fraction;


    public ExportModel() {


    }


    public ExportModel(String studentName, String grade, String subject, Integer fraction) {
        this.studentName = studentName;
        this.grade = grade;
        this.subject = subject;
        this.fraction = fraction;
    }
}

1.3 根据时间戳生产文件名

private static String createFileName() {
    Long time = System.currentTimeMillis();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    return sdf.format(new Date()) + time;
}

1.4 导出文件时为`Writer`
生成`OutputStream`

private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
    response.reset();
    response.setContentType("application/vnd.ms-excel");
    try {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName  + ".xlsx");
        return response.getOutputStream();
    } catch (IOException e) {
        throw new ExcelException("创建文件失败！");
    }
}

1.5 导出文件时为`File`
生成`OutputStream`

private static OutputStream getFileOutputStream(String fileName) {
    try {
        String filePath = fileName + ".xlsx";
        File dbfFile = new File(filePath);
        if (!dbfFile.exists() || dbfFile.isDirectory()) {
            dbfFile.createNewFile();
        }
        OutputStream out = new FileOutputStream(filePath);
        return out;
    } catch (Exception e) {
        throw new RuntimeException("创建文件失败！");
    }
}

1.6 造数据的方法

实际情况：从数据库查询动态数据。

public static List<ExportModel> getList() {
    List<ExportModel> list = new ArrayList<>();
    ExportModel model1 = new ExportModel("张三", "高三", "语文", 130);
    ExportModel model2 = new ExportModel("张三", "高三", "数学", 140);
    ExportModel model3 = new ExportModel("张三", "高三", "英语", 125);
    ExportModel model4 = new ExportModel("张三", "高三", "化学", 90);
    list.add(model1);
    list.add(model2);
    list.add(model3);
    list.add(model4);
    return list;
}
public static List<ExportModel> getAnotherList() {
    List<ExportModel> list = new ArrayList<>();
    ExportModel model1 = new ExportModel("李四", "高二", "语文", 120);
    ExportModel model2 = new ExportModel("李四", "高二", "数学", 125);
    ExportModel model3 = new ExportModel("李四", "高二", "英语", 140);
    ExportModel model4 = new ExportModel("李四", "高二", "化学", 85);
    list.add(model1);
    list.add(model2);
    list.add(model3);
    list.add(model4);
    return list;
}

二、核心实现

2.1 导出的到一个 `sheet`
的 `Excel`

public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                                  String fileName, String sheetName, BaseRowModel object) {
    // WriteModel 是 写入 Excel 的数据模型对象
    ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0, object.getClass());
    sheet.setSheetName(sheetName);
    // 异常处理
    writer.write(list, sheet);
    writer.finish();
}

2.2 导出的到多个 `sheet`
的 `Excel`

•重写导出方法

public class ExcelWriterFactory extends ExcelWriter {
    private OutputStream outputStream;
    private int sheetNo = 1;


    public ExcelWriterFactory(OutputStream outputStream, ExcelTypeEnum typeEnum) {
        super(outputStream, typeEnum);
        this.outputStream = outputStream;
    }


    public ExcelWriterFactory write(List<? extends BaseRowModel> list, String sheetName,
                                    BaseRowModel object) {
        this.sheetNo++;
        try {
            Sheet sheet = new Sheet(sheetNo, 0, object.getClass());
            sheet.setSheetName(sheetName);
            this.write(list, sheet);
        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                outputStream.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return this;
    }


    @Override
    public void finish() {
        super.finish();
        try {
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

•导出两个 sheet
示例

public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list,
                                                      String fileName, String sheetName, BaseRowModel object) {
    ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0, object.getClass());
    sheet.setSheetName(sheetName);
    writer.write(list, sheet);
    return writer;
}

2.3 异步导出

1.如果数据量大，可能导出等待时间较长；2.同步导出必须实时下载，异步导出可以保存到系统，需要时再进行下载。

public static String asyWriteExcel(List<? extends BaseRowModel> list,
                                    String sheetName, BaseRowModel object) {
    // 先将数据导出excel到本地
    try {
        String fileName = URLEncoder.encode(createFileName(), "UTF-8");
        ExcelWriter writer = new ExcelWriter(getFileOutputStream(fileName), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
        // 读取该excel,并上传到oss，返回下载链接
        // File file = readFileByLines(fileName + ".xlsx");
        // return FileUploadUtil.upload(file, fileName + ".xlsx");
    } catch (UnsupportedEncodingException e) {
        throw new RuntimeException("创建excel失败!");
    }
    return null;
}

三、测试

导出的测试比较简单，这里直接放出接口，具体业务实现见文末源码

@RestController
@Api(tags = "EasyExcel 导出")
@RequestMapping("/export")
public class ExportExcelController {


    @Resource
    ExportExcelService exportExcelService;


    /**
     * 导出 Excel（一个 sheet）
     * @param response
     * @throws IOException
     */
    @ApiOperation(value = "导出 Excel", httpMethod = "GET")
    @GetMapping(value = "/exportWithOneSheet")
    public void exportWithOneSheet(HttpServletResponse response) {
        exportExcelService.exportWithOneSheet(response);
    }


    /**
     * 导出 Excel（多个 sheet）
     */
    @ApiOperation(value = "导出 Excel（多个 sheet）", httpMethod = "GET")
    @GetMapping(value = "/exportWithSheets")
    public void exportWithSheets(HttpServletResponse response) {
        exportExcelService.exportWithSheets(response);
    }


    /**
     * 异步导出 Excel（一个 sheet）
     * @param
     * @throws IOException
     */
    @ApiOperation(value = "异步导出 Excel", httpMethod = "GET")
    @GetMapping(value = "/asyExportWithOneSheet")
    public void asyExportWithOneSheet() {
        exportExcelService.asyExportWithOneSheet();
    }
}

四、待优化

本次分享的读取和导出只是基本的使用，面对复杂需求的时候还需要继续加工，更多内容，下次分享，大概包括：

1.导出合并单元格，单元格格式自定义；2.读取和导出自定义转换器；3.读取时指定表头行数、读取表头数据；4.转换异常处理。

Github 示例代码^[1]

文中链接

[1]
Github 示例代码: https://github.com/vanDusty/SpringBoot-Home/tree/master/springboot-demo-excel

数据库

文章转载自风尘博客，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。