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); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
主要是在exportAssignModelByTemplate方法内创建map将数据动态地塞进模板里，数据实体类AssginModelRes 为
package cn.com.reformer.model.comandestimate.assignmodel;import java.io.Serializable;import java.util.List;import lombok.Data;@Datapublic 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; }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
@Datapublic class Table1 implements Serializable { /** * */ private static final long serialVersionUID = 1L; /* * 表头 最近的三个仓库 */ private List<String> t1header; /** * 表格数据 */ private List<Table1Data> data; }@Datapublic class Table2 implements Serializable { /** * */ private static final long serialVersionUID = 1L; /** * 表头：事件和次生事件的地点. * * */ private List<String> t2header; private List<Map<String, Object>> data; }@Datapublic class Table3 implements Serializable { /** * */ private static final long serialVersionUID = 1L; /* * 表头 */ private List<String> t3header; private List<Map<String, Object>> data;}/* * 表4：分配结果 */@Datapublic class Table4 implements Serializable { /** * */ private static final long serialVersionUID = 1L; /* * 表头：仓库名、集散地 */ private Table4Header header; private List<Map<String, Object>> data;}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
数据格式示例：
{ "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 } ] } ] }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
最终导出的Excel 为：