easy额xcel 生成Excel表并返回浏览器弹出下载的简单实现

佚名 6年前 (2019-05-15) 随笔 1133人围观抢沙发百度已收录

第一步：添加依赖

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

说明：阿里easyexcel导出excel文件的思路：

和管理后台普通的查询列表一样，设计Api需要具备按条件查询的功能特性
按条件查询出满足条件的records，封装成List集合
使用easyexcel构建的工具类通过字节流读取，搭档输出流将数据写入Excel

第二步：ExcelUtil工具类

import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.metadata.BaseRowModel; import com.alibaba.excel.metadata.Sheet; import com.alibaba.excel.support.ExcelTypeEnum; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; public class ExcelUtil { /** * 导出 Excel ：一个 sheet，带表头 * * @param response HttpServletResponse * @param list 数据 list，每个元素为一个 BaseRowModel * @param fileName 导出的文件名 * @param sheetName 导入文件的 sheet 名 * @param model 映射实体类，Excel 模型 */
    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel model)throws Exception { ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX); Sheet sheet = new Sheet(1, 0, model.getClass()); sheet.setSheetName(sheetName); writer.write(list, sheet); writer.finish(); } /** * 导出文件时为Writer生成OutputStream * * @param fileName * @param response * @return */
    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); } } }

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

第三步：封装model对象

public class exportDataModel extends BaseRowModel { @ExcelProperty(value = { "列1", "列1" }, index = 0) private String 属性1; @ExcelProperty(value = { "列2", "列2" }, index = 1) private String 属性2; @ExcelProperty(value = { "列3", "列4" }, index = 2) private String 属性3; @ExcelProperty(value = { "列3", "列5" }, index = 3) private String 属性4; @ExcelProperty(value = { "列6", "列7" }, index = 4) private String 属性5; @ExcelProperty(value = { "列6", "列8" }, index = 5) private String 属性6;

说明：作为映射实体类，需要继承 BaseRowModel 类，通过 @ExcelProperty 注解与 index 变量可以标注成员变量所映射的列，同时不可缺少 setter 方法

本文主要使用到@ExcelProperty注解的2个属性

value：用于指定Excel表头名称
index：用于指定表头所在列的索引值，从0开始

第四部：Controller代码演示

    try { String name = "演示导出模板.xlsx"; Date date = Calendar.getInstance().getTime(); SimpleDateFormat sdf_ymd = new SimpleDateFormat("yyyyMMddHHmmss"); String formatDate_ymd = sdf_ymd.format(date); // 设置文件名
            String fileName = formatDate_ymd + name; String sheetName = "数据展示"; // 按条件筛选records
            List<exportDataModel> list = this.getExportDataList(); // easyexcel工具类实现Excel文件导出
            ExcelUtil.writeExcel(response, list, fileName, sheetName, new exportDataModel()); } catch (Exception e) { e.printStackTrace(); }