easyPoi导出表格(模板替换以及注解生成)

简化版

模板替换数据

    /**
     * 前端下载Excel
     *
     * @param response
     * @param activityQueryVo
     */
    @Override
    public void export(HttpServletResponse response, ActivityQueryVo activityQueryVo) {
   
        try {
   
            Map<String, Object> data = getData(activityQueryVo);
            Workbook workbook = export(templateUrl, data);
            String fileName = "返回给前端的文件名";
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
   
            e.printStackTrace();
        }
    }

工具类

    /**
     * 模板数据替换
     *
     * @param templateUtl 模板路径
     * @param map 数据集合
     * @return
     * @throws IOException
     */
    private Workbook export(String templateUtl, Map<String, Object> map) throws IOException {
   
        File file = new File(templateUtl);
        String canonicalPath = file.getCanonicalPath();
        // 默认为false, 设置sheetName为true, 开启多sheet，
        TemplateExportParams params = new TemplateExportParams(canonicalPath, true);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        return workbook;
    }

数据准备

        Map<String, Object> allMap = new HashMap<String, Object>();

        List<Map<String, Object>> list0 = Lists.newArrayList();
        Map<String, Object> map = new HashMap<String, Object>();
        // 添加多个map
        map.put("peopleNumber", 100);
        // 如果有多处需要循环输出的数据, 就添加多个list
        list0.add(map);
        allMap.put("list", list0);

数据样式

   {
   
    "msg": "操作成功",
    "code": 200,
    "data": {
   
        "list": [
            {
   
                "venueCategory": "",
                "peopleNumber": 100,
                "num": 1,
                "activityName": "非常",
                "organizers": "道济四等奖",
                "activityType": "",
                "holdTime": "2022-02-06"
            },
            {
   
                "venueCategory": "",
                "peopleNumber": 15,
                "num": 2,
                "activityName": "测试2",
                "organizers": "啊啊啊啊",
                "activityType": "",
                "holdTime": "2022-03-24"
            },
            {
   
                "venueCategory": "",
                "peopleNumber": 250,
                "num": 3,
                "activityName": "测试A",
                "organizers": "维众",
                "activityType": "",
                "holdTime": "2022-09-07"
            }
        ]
    }
}

模板说明:
循环数据用{ { KaTeX parse error: Expected 'EOF', got '}' at position 18: …: 集合名称 t.字段名 }̲} 包裹起来 其中 { { fe: }} 是不变的, t是easypoi默认的循环标识

注解生成Excel

为实体添加注解

    @Excel(name = "场馆类别", needMerge = true)
    private String venueCategory;

工具类

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.vxdata.activity.entity.CulturalActivity;
import lombok.extern.slf4j.Slf4j;
import