easypoi 数据列动态导出

在数据导出时经常会出现需要动态导出数据的情况，尤其动态列，例如下图的数据1，数据2，....数据100等

 

1.定义实体类ParameterValueVo：

@Data
public class ParameterValueVo {
    @Excel(name = "标准值", width = 20)
    private String standardValue;

    @Excel(name = "最大值", width = 20)
    private String max;

    @Excel(name = "最小值", width = 20)
    private String min;

    @Excel(name = "实际值", width = 20)
    private String value;
}

 2.控制器调用导出：

控制器引入

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import org.apache.poi.ss.usermodel.Workbook;

    /**
     * 数据导出excel
     */
    @ApiOperation(value = "Get方式导出Excel")
    @RequestMapping(value = "/export", method = RequestMethod.GET, produces = "application/octet-stream")
    protected void exportExcel() {
        try {
            List<Map<String, Object>> list = new ArrayList<>();
            Map<String, Object> map = new HashMap<>();
            map.put("orderCode", "20230203044");
            map.put("productNames", "机械手");
            map.put("materialNames", "黑色色胶");
            map.put("injectionName", "BT37233");
            map.put("time", "2023-03-23 23:34:23");

            ParameterValueVo aValueVo = new ParameterValueVo();
            aValueVo.setStandardValue("30");
            aValueVo.setMax("50");
            aValueVo.setMin("10");
            aValueVo.setValue("20");
            List<ParameterValueVo> aList = new ArrayList<>();
            aList.add(aValueVo);
            map.put("a", aList);


            ParameterValueVo bValueVo = new ParameterValueVo();
            bValueVo.setStandardValue("30");
            bValueVo.setMax("50");
            bValueVo.setMin("10");
            bValueVo.setValue("20");
            List<ParameterValueVo> bList = new ArrayList<>();
            bList.add(bValueVo);
            map.put("b", bList);

            ParameterValueVo cValueVo = new ParameterValueVo();
            cValueVo.setStandardValue("30");
            cValueVo.setMax("50");
            cValueVo.setMin("10");
            cValueVo.setValue("20");
            List<ParameterValueVo> cList = new ArrayList<>();
            cList.add(cValueVo);
            map.put("c", cList);

            ParameterValueVo dValueVo = new ParameterValueVo();
            dValueVo.setStandardValue("30");
            dValueVo.setMax("50");
            dValueVo.setMin("10");
            dValueVo.setValue("20");
            List<ParameterValueVo> dList = new ArrayList<>();
            dList.add(dValueVo);
            map.put("d", dList);

            list.add(map);
            List<ExcelExportEntity> exportList = new ArrayList<>();
            ExcelExportEntity orderCode = new ExcelExportEntity("单号", "orderCode");
            orderCode.setWidth(20);

            ExcelExportEntity productNames = new ExcelExportEntity("产品", "productNames");
            productNames.setWidth(30);

            ExcelExportEntity materialNames = new ExcelExportEntity("原料", "materialNames");
            materialNames.setWidth(30);

            ExcelExportEntity injectionName = new ExcelExportEntity("机器", "injectionName");
            injectionName.setWidth(20);

            ExcelExportEntity time = new ExcelExportEntity("时间", "time");
            time.setWidth(30);
            // 创建最底部的一级表头10个
            ExcelExportEntity a1 = new ExcelExportEntity("标准值", "standardValue");
            ExcelExportEntity a2 = new ExcelExportEntity("最大值", "max");
            ExcelExportEntity a3 = new ExcelExportEntity("最小值", "min");
            ExcelExportEntity a4 = new ExcelExportEntity("实际值", "value");

            ExcelExportEntity b1 = new ExcelExportEntity("标准值", "standardValue");
            ExcelExportEntity b2 = new ExcelExportEntity("最大值", "max");
            ExcelExportEntity b3 = new ExcelExportEntity("最小值", "min");
            ExcelExportEntity b4 = new ExcelExportEntity("实际值", "value");

            ExcelExportEntity c1 = new ExcelExportEntity("标准值", "standardValue");
            ExcelExportEntity c2 = new ExcelExportEntity("最大值", "max");
            ExcelExportEntity c3 = new ExcelExportEntity("最小值", "min");
            ExcelExportEntity c4 = new ExcelExportEntity("实际值", "value");

            ExcelExportEntity d1 = new ExcelExportEntity("标准值", "standardValue");
            ExcelExportEntity d2 = new ExcelExportEntity("最大值", "max");
            ExcelExportEntity d3 = new ExcelExportEntity("最小值", "min");
            ExcelExportEntity d4 = new ExcelExportEntity("实际值", "value");

            // 创建二级表头，并将二级表头对应的下级一级表头放入其中，以此类推...
            ExcelExportEntity a = new ExcelExportEntity("数据1%", "a");
            a.setList(Arrays.asList(a1, a2, a3, a4));
            ExcelExportEntity b = new ExcelExportEntity("数据2%", "b");
            b.setList(Arrays.asList(b1, b2, b3, b4));
            ExcelExportEntity c = new ExcelExportEntity("数据3%", "c");
            c.setList(Arrays.asList(c1, c2, c3, c4));
            ExcelExportEntity d = new ExcelExportEntity("数据4%", "d");
            d.setList(Arrays.asList(d1, d2, d3, d4));
            exportList.add(orderCode);
            exportList.add(productNames);
            exportList.add(materialNames);
            exportList.add(injectionName);
            exportList.add(time);

            exportList.add(a);
            exportList.add(b);
            exportList.add(c);
            exportList.add(d);
            ExportParams params = new ExportParams("统计数据", "统计数据", ExcelType.XSSF);
            Workbook workbook = ExcelExportUtil.exportExcel(params, exportList, list);
            // 重置响应对象
            response.reset();
            // 指定下载的文件名--设置响应头
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("数据统计" + System.currentTimeMillis(), "UTF-8") + ".xlsx");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }