springboot+easypoi模板导出Excel 动态表头+多表格(一个sheet)

1.需求:将此页面的几个表格导出
在这里插入图片描述
其中表头中的仓库 集散地是是动态生成的。
首先制作Excel模板:
在这里插入图片描述
代码:

@Resource
	private RedisService redisService;
	@Override
	public void assignModel(EventInfo info, HttpServletResponse response) {
		AssginModelRes res=new AssginModelRes();
    	Object obj = redisService.getObj("modelExport-"+info.getId());
		if (null == obj) {
			throw new CustomException("找不到指定的事件!");
		}
		res = (AssginModelRes) obj;
    	try {
            // 获取workbook对象
            // 单sheet或多sheet 只需要更改此处即可
            Workbook workbook = exportAssignModelByTemplate(res) ;
            // 设置excel的文件名称
            String excelName = "资源分配模型" ;
            // 重置响应对象
            response.reset();
            // 当前日期,用于导出文件名称
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String dateStr = "["+excelName+"-"+sdf.format(new Date())+"]";
            // 指定下载的文件名--设置响应头
            response.setHeader("Content-Disposition", "attachment;filename=" +dateStr+".xls");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            // 写出数据输出流到页面
            OutputStream output = response.getOutputStream();
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
            workbook.write(bufferedOutPut);
            bufferedOutPut.flush();
            bufferedOutPut.close();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

	}
private Workbook exportAssignModelByTemplate(AssginModelRes res) throws IOException {
	        // 手动创建导出数据,需根据具体业务进行查询 
	        // sheet1内容
	        Map<String,Object> map = new HashMap<String, Object>() ;
	        map.put("w1", res.getT1().getT1header().get(0)); 
	        map.put("w2", res.getT1().getT1header().get(1)); 
	        map.put("w3", res.getT1().getT1header().get(2));    
	        map.put("resultList1",res.getT1().getData());
	        for(int i=0;i<res.getT2().getT2header().size();i++) {
	        	 map.put("p"+(i+1), res.getT2().getT2header().get(i));
	        }
	        map.put("resultList2",res.getT2().getData());
	        map.put("resultList3",res.getT3().getData());
	        map.put("resultList4_1",res.getT4().get(0).getData());
	        map.put("resultList4_2",res.getT4().get(1).getData());
	        map.put("resultList4_3",res.getT4().get(2).getData());
	        // 设置导出配置
	        // 获取导出excel指定模版
			TemplateExportParams template = new TemplateExportParams("templates/model.xls", true);
	        // 导出excel
	        return ExcelExportUtil.exportExcel(template , map);
	    }

主要是在exportAssignModelByTemplate方法内创建map将数据动态地塞进模板里,数据实体类AssginModelRes 为

package cn.com.reformer.model.comandestimate.assignmodel;

import java.io.Serializable;
import java.util.List;

import lombok.Data;
@Data
public class AssginModelRes implements Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	/**
	 * 表格1
	 */
	private Table1 t1;
	/**
	 * 表格2
	 */
	private Table2 t2;
	/**
	 * 表格3
	 */
	private Table3 t3;
	/**
	 * 表格4
	 */
	private List<Table4> t4;
	
}

@Data
public class Table1 implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	/*
	 * 表头 最近的三个仓库
	 */
	private List<String> t1header;
	/**
	 * 表格数据
	 */
	private List<Table1Data> data;
	
}

@Data
public class Table2 implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	/**
	 * 表头:事件和次生事件的地点.
	 * 
	 * 
	 */
	private  List<String> t2header;
	private List<Map<String, Object>> data;
	
}
@Data
public class Table3 implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	/*
	 * 表头
	 */
	private List<String> t3header;
	private List<Map<String, Object>> data;

}
/*
 * 表4:分配结果
 */
@Data
public class Table4 implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	/*
	 * 表头:仓库名、集散地
	 */
	private Table4Header header;
	private List<Map<String, Object>> data;
}

数据格式示例:

{
    "msg": "操作成功",
    "code": 200,
    "data": {
        "t1": {
            "t1header": [
                "1209测试仓库",
                "研判测试仓库",
                "测试仓库"
            ],
            "data": [
                {
                    "time": "2020-09-03 19:27:17",
                    "w1": 20,
                    "w2": 14,
                    "w3": 29,
                    "sum": 63
                },
                {
                    "time": "2020-09-03 20:27:17",
                    "w1": 15,
                    "w2": 11,
                    "w3": 20,
                    "sum": 46
                },
                {
                    "time": "2020-09-03 21:27:17",
                    "w1": 27,
                    "w2": 21,
                    "w3": 43,
                    "sum": 91
                }
            ]
        },
        "t2": {
            "t2header": [
                "湖北省武汉市洪山区东湖风景区街道花城大道",
                "湖北省武汉市洪山区和平街道悠贝·菁华幼儿园花样年·花郡东区",
                "湖北省武汉市武昌区徐家棚街街道油料所社区",
                "湖北省武汉市武昌区水果湖街街道万达御湖世家",
                "湖北省武汉市武昌区水果湖街街道观星传媒万达尊"
            ],
            "data": [
                {
                    "time": "2020-09-03 19:27:17",
                    "p1": 21,
                    "p2": 17,
                    "p3": 12,
                    "p4": 15,
                    "p5": 28,
                    "sum": 93
                },
                {
                    "time": "2020-09-03 20:27:17",
                    "p1": 16,
                    "p2": 28,
                    "p3": 9,
                    "p4": 17,
                    "p5": 23,
                    "sum": 93
                },
                {
                    "time": "2020-09-03 21:27:17",
                    "p1": 28,
                    "p2": 30,
                    "p3": 17,
                    "p4": 29,
                    "p5": 21,
                    "sum": 125
                }
            ]
        },
        "t3": {
            "t3header": [
                "湖北省武汉市洪山区东湖风景区街道花城大道",
                "湖北省武汉市洪山区和平街道悠贝·菁华幼儿园花样年·花郡东区",
                "湖北省武汉市武昌区徐家棚街街道油料所社区",
                "湖北省武汉市武昌区水果湖街街道万达御湖世家",
                "湖北省武汉市武昌区水果湖街街道观星传媒万达尊"
            ],
            "data": [
                {
                    "warename": "1209测试仓库",
                    "p1": 1.26,
                    "p2": 1.05,
                    "p3": 1.37,
                    "p4": 1.5,
                    "p5": 1.31
                },
                {
                    "warename": "研判测试仓库",
                    "p1": 1.16,
                    "p2": 1.25,
                    "p3": 1.02,
                    "p4": 1.7,
                    "p5": 1.57
                },
                {
                    "warename": "测试仓库",
                    "p1": 1.53,
                    "p2": 1.29,
                    "p3": 1.07,
                    "p4": 1.15,
                    "p5": 1.08
                }
            ]
        },
        "t4": [
            {
                "header": {
                    "ware1": "1209测试仓库",
                    "places": [
                        "湖北省武汉市洪山区东湖风景区街道花城大道",
                        "湖北省武汉市洪山区和平街道悠贝·菁华幼儿园花样年·花郡东区",
                        "湖北省武汉市武昌区徐家棚街街道油料所社区",
                        "湖北省武汉市武昌区水果湖街街道万达御湖世家",
                        "湖北省武汉市武昌区水果湖街街道观星传媒万达尊"
                    ]
                },
                "data": [
                    {
                        "p0": "实际需求dj(t)",
                        "p1": 21,
                        "p2": 17,
                        "p3": 12,
                        "p4": 15,
                        "p5": 28
                    },
                    {
                        "p0": "实际需求zj(t)",
                        "p1": 21,
                        "p2": 17,
                        "p3": 12,
                        "p4": 15,
                        "p5": 28
                    },
                    {
                        "p0": "分配量∑χφ(t)",
                        "p1": 21,
                        "p2": 17,
                        "p3": 12,
                        "p4": 15,
                        "p5": 28
                    },
                    {
                        "p0": "满意度Rj(t)",
                        "p1": 0.62,
                        "p2": 0.59,
                        "p3": 0.68,
                        "p4": 0.6,
                        "p5": 0.61
                    }
                ]
            },
            {
                "header": {
                    "ware1": "研判测试仓库",
                    "places": [
                        "湖北省武汉市洪山区东湖风景区街道花城大道",
                        "湖北省武汉市洪山区和平街道悠贝·菁华幼儿园花样年·花郡东区",
                        "湖北省武汉市武昌区徐家棚街街道油料所社区",
                        "湖北省武汉市武昌区水果湖街街道万达御湖世家",
                        "湖北省武汉市武昌区水果湖街街道观星传媒万达尊"
                    ]
                },
                "data": [
                    {
                        "p0": "实际需求dj(t)",
                        "p1": 21,
                        "p2": 17,
                        "p3": 12,
                        "p4": 15,
                        "p5": 28
                    },
                    {
                        "p0": "实际需求zj(t)",
                        "p1": 21,
                        "p2": 17,
                        "p3": 12,
                        "p4": 15,
                        "p5": 28
                    },
                    {
                        "p0": "分配量∑χφ(t)",
                        "p1": 21,
                        "p2": 17,
                        "p3": 12,
                        "p4": 15,
                        "p5": 28
                    },
                    {
                        "p0": "满意度Rj(t)",
                        "p1": 0.62,
                        "p2": 0.59,
                        "p3": 0.68,
                        "p4": 0.6,
                        "p5": 0.61
                    }
                ]
            },
            {
                "header": {
                    "ware1": "测试仓库",
                    "places": [
                        "湖北省武汉市洪山区东湖风景区街道花城大道",
                        "湖北省武汉市洪山区和平街道悠贝·菁华幼儿园花样年·花郡东区",
                        "湖北省武汉市武昌区徐家棚街街道油料所社区",
                        "湖北省武汉市武昌区水果湖街街道万达御湖世家",
                        "湖北省武汉市武昌区水果湖街街道观星传媒万达尊"
                    ]
                },
                "data": [
                    {
                        "p0": "实际需求dj(t)",
                        "p1": 21,
                        "p2": 17,
                        "p3": 12,
                        "p4": 15,
                        "p5": 28
                    },
                    {
                        "p0": "实际需求zj(t)",
                        "p1": 21,
                        "p2": 17,
                        "p3": 12,
                        "p4": 15,
                        "p5": 28
                    },
                    {
                        "p0": "分配量∑χφ(t)",
                        "p1": 21,
                        "p2": 17,
                        "p3": 12,
                        "p4": 15,
                        "p5": 28
                    },
                    {
                        "p0": "满意度Rj(t)",
                        "p1": 0.62,
                        "p2": 0.59,
                        "p3": 0.68,
                        "p4": 0.6,
                        "p5": 0.61
                    }
                ]
            }
        ]
    }
}

最终导出的Excel 为:
在这里插入图片描述

  • 5
    点赞
  • 22
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
好的,关于你的问题,我会为你详细介绍SpringBootEasyPoi如何实现数据导出。下面是实现步骤及代码示例: 步骤1:在pom.xml添加EasyPoi依赖 ```xml <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>4.2.0</version> </dependency> ``` 步骤2:定义导出的实体类,并使用注解配置导出的列名和顺序 ```java public class User{ @Excel(name = "用户名", orderNum = "1") private String username; @Excel(name = "密码", orderNum = "2") private String password; // ...其他属性及getter/setter方法 } ``` 步骤3:编写导出Excel数据的控制器 ```java @RestController @RequestMapping("/api/user") public class UserController { @Autowired private UserService userService; @GetMapping("/export") public void exportData(@RequestParam(name = "fields") String fields, HttpServletResponse response) { // 处理表头列名 String[] titles = fields.split(","); // 查询数据库所有用户信息 List<User> userList = userService.findAll(); // 根据用户选择的字段过滤掉不需要导出的列 for (int i = 0; i < titles.length; i++) { boolean isMatch = false; for (Field field : User.class.getDeclaredFields()) { if (field.isAnnotationPresent(Excel.class)) { Excel annotation = field.getAnnotation(Excel.class); if (annotation.name().equals(titles[i])) { isMatch = true; break; } } } if (!isMatch) { titles[i] = null; } } // 利用EasyPoi导出Excel ExportParams exportParams = new ExportParams("", "用户信息"); exportParams.setCreateHeadRows(true); exportParams.setHeadRows(1); Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, userList, null, titles, null); try (OutputStream outputStream = response.getOutputStream()) { response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode("用户信息.xlsx")); workbook.write(outputStream); } catch (IOException e) { e.printStackTrace(); } } } ``` 以上就是导出Excel的完整代码,其针对用户选择需要导出的字段进行校验的部分,使用了反射机制获取注解信息进行匹配。为保证Excel格式的一致性,我们还使用了EasyPoi提供的ExportParams配置Excel表头等属性。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值