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

第一步：添加依赖

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

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

1. 和管理后台普通的查询列表一样，设计Api需要具备按条件查询的功能特性
2. 按条件查询出满足条件的records，封装成List集合
3. 使用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);
        }
    }

}

 

第三步：封装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();
        }

说明：fileName，sheetName 分别是导出文件的文件名和 sheet 名，new exportDataModel() 为导出数据的映射实体对象，list 为导出数据。

第五步：效果展示