Excle导出 多种复杂的表结构
excle表格样式图
重要的事情说三遍: 导出时接口的请求一定不是ajax !!!! 导出时接口的请求一定不是ajax !!!! 导出时接口的请求一定不是ajax !!!!
因为这样后台代码执行完,你会发现浏览器丝毫没有反应,会让你觉得是不是代码接口代码问题,其实是前端请求方式问题,那前端的请求方式是什么呢,下面我粘出自己的代码
1 //导出 2 $('#dc').click(function(){ 3 //console.log($($(".layui-laypage-em")[0]).next().text()); 4 var page = $($(".layui-laypage-em")[0]).next().text(); 5 //调用导出接口 6 window.location.href='${ctx}/exindustry/export?page='+page+'&limit=10'; 7 })
下面是后台的一些准备工作与代码
首先 先导入poi 依赖
1 <!-- poi excle导出模块 --> 2 <dependency> 3 <groupId>org.apache.poi</groupId> 4 <artifactId>poi</artifactId> 5 <version>3.15</version> 6 </dependency> 7 <dependency> 8 <groupId>org.apache.poi</groupId> 9 <artifactId>poi-ooxml-schemas</artifactId> 10 <version>3.15</version> 11 </dependency> 12 <dependency> 13 <groupId>org.apache.poi</groupId> 14 <artifactId>poi-ooxml</artifactId> 15 <version>3.15</version> 16 </dependency>
然后直接在业务层引用即可
1 package com.ecenter.excle.service.impl; 2 3 import java.util.ArrayList; 4 import java.util.HashMap; 5 import java.util.List; 6 import java.util.Map; 7 8 import javax.servlet.http.HttpServletRequest; 9 import javax.servlet.http.HttpServletResponse; 10 11 import org.apache.poi.ss.usermodel.Cell; 12 import org.apache.poi.ss.usermodel.CellStyle; 13 import org.apache.poi.ss.usermodel.Font; 14 import org.apache.poi.ss.usermodel.Row; 15 import org.apache.poi.ss.usermodel.Sheet; 16 import org.apache.poi.ss.util.CellRangeAddress; 17 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 18 import org.springframework.beans.factory.annotation.Autowired; 19 import org.springframework.stereotype.Service; 20 21 import com.ecenter.excle.service.ExportJtsgyyflbService; 22 import com.ecenter.excle.util.FontStyle; 23 import com.ecenter.njgl.domain.FReportInfo; 24 import com.ecenter.njgl.mapper.FStatisticsMapper; 25 import com.ecenter.njgl.service.impl.FStatisticsServiceImpl; 26 import com.ecenter.system.util.JSONUtil; 27 @Service 28 public class ExportJtsgyyflbServiceImpl implements ExportJtsgyyflbService { 29 @Autowired 30 private FStatisticsMapper fStatisticsMapper; 31 32 @Override 33 public void exportJtsgyyflb(HttpServletRequest request, HttpServletResponse response) { 34 // 创建excel文件对象 35 XSSFWorkbook wb = new XSSFWorkbook(); 36 // 创建sheet 37 Sheet sheet = wb.createSheet("sheet1"); 38 //表头字体 39 Font headerFont = wb.createFont(); 40 FontStyle.createFont(headerFont); 41 headerFont.setFontHeightInPoints((short)18);//字体大小 42 //表头字体 43 Font headerFont2 = wb.createFont(); 44 FontStyle.createFont(headerFont2); 45 headerFont2.setFontHeightInPoints((short)14);//字体大小 46 //正文字体 47 Font contextFont = wb.createFont(); 48 FontStyle.createFont(contextFont); 49 50 //表头样式，左右上下居中 51 CellStyle headerStyle = wb.createCellStyle(); 52 FontStyle.createNotBoderStyle(headerStyle,headerFont); 53 54 //表头样式，左右上下居中 55 CellStyle headerStyle2 = wb.createCellStyle(); 56 FontStyle.createNotBoderStyle(headerStyle2,headerFont2); 57 58 //单元格样式，左右上下居中 边框 59 CellStyle commonStyle = wb.createCellStyle(); 60 FontStyle.createBoderStyle(commonStyle,contextFont); 61 62 //单元格样式，左右上下居中 边框 63 CellStyle remarkStyle = wb.createCellStyle(); 64 FontStyle.createNotBoderStyle(remarkStyle,contextFont); 65 // 行号 66 int rowNum = 0; 67 //设置列宽 68 for (int i = 0; i < 36; i++) { 69 if(i==0) { 70 sheet.setColumnWidth(i, 4000); 71 }else { 72 sheet.setColumnWidth(i, 2600); 73 } 74 } 75 Map<String, Object> map = new HashMap<String, Object>(); 76 77 String data = request.getParameter("data"); 78 String year = "" + JSONUtil.getJSONString(data, "year"); 79 String jidu = "" + JSONUtil.getJSONString(data, "jidu"); 80 String regionCode = "" + JSONUtil.getJSONString(data, "regionCode"); 81 String regionCode2 = "" + JSONUtil.getJSONString(data, "regionCode"); 82 map.put("year", year); 83 map.put("jidu", jidu); 84 List<FReportInfo> info=new ArrayList<>(); 85 List<FReportInfo> infoCity=new ArrayList<>(); 86 if(regionCode.equals("330000")){ 93 List<String> list=new ArrayList<>(); 94 list.add("330100"); 95 list.add("330200"); 96 list.add("330300"); 97 list.add("330400"); 98 list.add("330500"); 99 list.add("330600"); 100 list.add("330700"); 101 list.add("330800"); 102 list.add("330900"); 103 list.add("331000"); 104 list.add("331100"); 105 for(int i=0;i<list.size();i++){ 106 String regionCodei=list.get(i); 107 map.put("code", regionCodei); 108 regionCodei=regionCodei.substring(0,4)+"__"; 109 map.put("regionCode", regionCodei); 110 map.put("condition", regionCodei.substring(0,4)+"00"); 111 map.put("regionCodeStr", "regionCode"); 112 infoCity=fStatisticsMapper.listCityTableSix(map); 113 if(infoCity==null || infoCity.size()<1){ 114 infoCity.add(new FReportInfo()); 115 } 116 infoCity.get(0).setDw(new FStatisticsServiceImpl().getRegionName(list.get(i))); 117 info.addAll(infoCity); 118 } 119 }else if(regionCode.substring(4,6).equals("00")){ 120 //查某市级下所有区县信息 121 map.put("code", regionCode); 122 regionCode=regionCode.substring(0,4)+"__"; 123 map.put("regionCode", regionCode); 124 map.put("condition", regionCode.substring(0,4)+"00"); 125 map.put("regionCodeStr", "regionCode"); 126 infoCity=fStatisticsMapper.listCityTableSix(map); 127 if(infoCity==null || infoCity.size()<1){ 128 infoCity.add(new FReportInfo()); 129 } 130 infoCity.get(0).setDw(new FStatisticsServiceImpl().getRegionName(regionCode.substring(0,4)+"00")); 131 info=fStatisticsMapper.listProvinceTableSix(map); 132 }else{ 133 map.put("regionCode", regionCode); 134 info = fStatisticsMapper.getTableSix(map); 135 } 136 137 //第一行 138 Row r0 = sheet.createRow(rowNum++); 139 r0.setHeight((short) 800); 140 Cell c00 = r0.createCell(0); 141 String title = "拖拉机道路交通甲方事故"; 142 c00.setCellValue(title); 143 c00.setCellStyle(headerStyle); 144 //合并单元格 145 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 35)); 146 147 //第二行 148 Row r2 = sheet.createRow(rowNum++); 149 r2.setHeight((short) 700); 150 Cell c01 = r2.createCell(0); 151 String value = year+"年第"+jidu+"季度"; 152 c01.setCellValue(value); 153 c01.setCellStyle(headerStyle2); 154 //合并单元格 155 sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 35)); 156 157 // 第三行 158 Row r3 = sheet.createRow(rowNum++); 159 r3.setHeight((short) 700); 160 String[] row_three = {"项目单位", "一、违章操作","", "","", "","", "","", "","","", "","", "","", "","", "","","","二、操作失误","", "","", "", "三、机件失灵、设施不全","","","","", "其它","","","",""}; 161 for (int i = 0; i < row_three.length; i++) { 162 Cell tempCell = r3.createCell(i); 163 tempCell.setCellValue(row_three[i]); 164 tempCell.setCellStyle(commonStyle); 165 } 166 sheet.addMergedRegion(new CellRangeAddress(2, 5, 0, 0)); 167 sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 20)); 168 sheet.addMergedRegion(new CellRangeAddress(2, 2, 21, 25)); 169 sheet.addMergedRegion(new CellRangeAddress(2, 2, 26, 30)); 170 sheet.addMergedRegion(new CellRangeAddress(2, 2, 31, 35)); 171 // 第四行 172 Row r4 = sheet.createRow(rowNum++); 173 r4.setHeight((short) 700); 174 String[] row_four = {"","次数（次）","","","", "受伤（人）","","","","死亡（人）","","","", "报废机具","","","","直接经济损失（万）","","","", "次数（次）", "受伤（人）","死亡（人）", "报废机具","直接经济损失（万）","次数（次）", "受伤（人）","死亡（人）", "报废机具","直接经济损失（万）","次数（次）", "受伤（人）","死亡（人）", "报废机具","直接经济损失（万）"}; 175 for (int i = 0; i < row_four.length; i++) { 176 Cell tempCell = r4.createCell(i); 177 tempCell.setCellValue(row_four[i]); 178 tempCell.setCellStyle(commonStyle); 179 } 180 sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 4)); 181 sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 8)); 182 sheet.addMergedRegion(new CellRangeAddress(3, 3, 9, 12)); 183 sheet.addMergedRegion(new CellRangeAddress(3, 3, 13, 16)); 184 sheet.addMergedRegion(new CellRangeAddress(3, 3, 17, 20)); 185 sheet.addMergedRegion(new CellRangeAddress(3, 5, 21, 21)); 186 sheet.addMergedRegion(new CellRangeAddress(3, 5, 22, 22)); 187 sheet.addMergedRegion(new CellRangeAddress(3, 5, 23, 23)); 188 sheet.addMergedRegion(new CellRangeAddress(3, 5, 24, 24)); 189 sheet.addMergedRegion(new CellRangeAddress(3, 5, 25, 25)); 190 sheet.addMergedRegion(new CellRangeAddress(3, 5, 26, 26)); 191 sheet.addMergedRegion(new CellRangeAddress(3, 5, 27, 27)); 192 sheet.addMergedRegion(new CellRangeAddress(3, 5, 28, 28)); 193 sheet.addMergedRegion(new CellRangeAddress(3, 5, 29, 29)); 194 sheet.addMergedRegion(new CellRangeAddress(3, 5, 30, 30)); 195 sheet.addMergedRegion(new CellRangeAddress(3, 5, 31, 31)); 196 sheet.addMergedRegion(new CellRangeAddress(3, 5, 32, 32)); 197 sheet.addMergedRegion(new CellRangeAddress(3, 5, 33, 33)); 198 sheet.addMergedRegion(new CellRangeAddress(3, 5, 34, 34)); 199 sheet.addMergedRegion(new CellRangeAddress(3, 5, 35, 35)); 200 // 第五行 201 Row r5 = sheet.createRow(rowNum++); 202 r5.setHeight((short) 700); 203 String[] row_five = {"","合计","其中:","","", "合计","其中:","","","合计","其中:","","","合计","其中:","","","合计","其中:","","","","","","","","","","","","","","","","",""}; 204 for (int i = 0; i < row_five.length; i++) { 205 Cell tempCell = r5.createCell(i); 206 tempCell.setCellValue(row_five[i]); 207 tempCell.setCellStyle(commonStyle); 208 } 209 sheet.addMergedRegion(new CellRangeAddress(4, 5, 1, 1)); 210 sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 4)); 211 sheet.addMergedRegion(new CellRangeAddress(4, 5, 5, 5)); 212 sheet.addMergedRegion(new CellRangeAddress(4, 4, 6, 8)); 213 sheet.addMergedRegion(new CellRangeAddress(4, 5, 9, 9)); 214 sheet.addMergedRegion(new CellRangeAddress(4, 4, 10, 12)); 215 sheet.addMergedRegion(new CellRangeAddress(4, 5, 13, 13)); 216 sheet.addMergedRegion(new CellRangeAddress(4, 4, 14, 16)); 217 sheet.addMergedRegion(new CellRangeAddress(4, 5, 17, 17)); 218 sheet.addMergedRegion(new CellRangeAddress(4, 4, 18, 20)); 219 220 // 第六行 221 Row r6 = sheet.createRow(rowNum++); 222 r6.setHeight((short) 700); 223 String[] row_six = {"","","无证驾驶", "酒后开车","超载超速","","无证驾驶", "酒后开车","超载超速","","无证驾驶", "酒后开车","超载超速","","无证驾驶", "酒后开车","超载超速","","无证驾驶", "酒后开车","超载超速","","","","","","","","","","","","","","",""}; 224 for (int i = 0; i < row_six.length; i++) { 225 Cell tempCell = r6.createCell(i); 226 tempCell.setCellValue(row_six[i]); 227 tempCell.setCellStyle(commonStyle); 228 } 229 // 第7行 230 Row r7 = sheet.createRow(rowNum++); 231 r7.setHeight((short) 700); 232 String[] row_7 = {"栏次","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"}; 233 for (int i = 0; i < row_7.length; i++) { 234 Cell tempCell = r7.createCell(i); 235 tempCell.setCellValue(row_7[i]); 236 tempCell.setCellStyle(commonStyle); 237 } 238 if(!"330000".equals(regionCode2) && "00".equals(regionCode2.substring(4,6))){ 239 if(infoCity.size()>0) { 240 FReportInfo fReportInfo = infoCity.get(0); 241 Row tempRow = sheet.createRow(rowNum++); 242 tempRow.setHeight((short) 500); 243 for (int j = 0; j < 36; j++) { 244 Cell tempCell = tempRow.createCell(j); 245 tempCell.setCellStyle(commonStyle); 246 String tempValue = "0"; 247 switch (j) { 248 case 0: 249 tempValue = fReportInfo.getDw(); 250 break; 251 case 1: 252 tempValue = ""+fReportInfo.getLc1(); 253 break; 254 case 2: 255 tempValue = ""+fReportInfo.getLc2(); 256 break; 257 case 3: 258 tempValue = ""+fReportInfo.getLc3(); 259 break; 260 case 4: 261 tempValue = ""+fReportInfo.getLc4(); 262 break; 263 case 5: 264 tempValue = ""+fReportInfo.getLc5(); 265 break; 266 case 6: 267 tempValue = ""+fReportInfo.getLc6(); 268 break; 269 case 7: 270 tempValue = ""+fReportInfo.getLc7(); 271 break; 272 case 8: 273 tempValue = ""+fReportInfo.getLc8(); 274 break; 275 case 9: 276 tempValue = ""+fReportInfo.getLc9(); 277 break; 278 case 10: 279 tempValue = ""+fReportInfo.getLc10(); 280 break; 281 case 11: 282 tempValue = ""+fReportInfo.getLc11(); 283 break; 284 case 12: 285 tempValue = ""+fReportInfo.getLc12(); 286 break; 287 case 13: 288 tempValue = ""+fReportInfo.getLc13(); 289 break; 290 case 14: 291 tempValue = ""+fReportInfo.getLc14(); 292 break; 293 case 15: 294 tempValue = ""+fReportInfo.getLc15(); 295 break; 296 case 16: 297 tempValue = ""+fReportInfo.getLc16(); 298 break; 299 case 17: 300 tempValue = ""+fReportInfo.getLc17(); 301 break; 302 case 18: 303 tempValue = ""+fReportInfo.getLc18(); 304 break; 305 case 19: 306 tempValue = ""+fReportInfo.getLc19(); 307 break; 308 case 20: 309 tempValue = ""+fReportInfo.getLc20(); 310 break; 311 case 21: 312 tempValue = ""+fReportInfo.getLc21(); 313 break; 314 case 22: 315 tempValue = ""+fReportInfo.getLc22(); 316 break; 317 case 23: 318 tempValue = ""+fReportInfo.getLc23(); 319 break; 320 case 24: 321 tempValue = ""+fReportInfo.getLc24(); 322 break; 323 case 25: 324 tempValue = ""+fReportInfo.getLc25(); 325 break; 326 case 26: 327 tempValue = ""+fReportInfo.getLc26(); 328 break; 329 case 27: 330 tempValue = ""+fReportInfo.getLc27(); 331 break; 332 case 28: 333 tempValue = ""+fReportInfo.getLc28(); 334 break; 335 case 29: 336 tempValue = ""+fReportInfo.getLc29(); 337 break; 338 case 30: 339 tempValue = ""+fReportInfo.getLc30(); 340 break; 341 case 31: 342 tempValue = ""+fReportInfo.getLc31(); 343 case 32: 344 tempValue = ""+fReportInfo.getLc32(); 345 break; 346 case 33: 347 tempValue = ""+fReportInfo.getLc33(); 348 break; 349 case 34: 350 tempValue = ""+fReportInfo.getLc34(); 351 break; 352 default: 353 tempValue = ""+fReportInfo.getLc35(); 354 break; 355 } 356 tempCell.setCellValue(tempValue); 357 } 358 } 359 } 360 int lc1s=0; 361 int lc2s=0; 362 int lc3s=0; 363 int lc4s=0; 364 int lc5s=0; 365 int lc6s=0; 366 int lc7s=0; 367 int lc8s=0; 368 int lc9s=0; 369 int lc10s=0; 370 int lc11s=0; 371 int lc12s=0; 372 int lc13s=0; 373 int lc14s=0; 374 int lc15s=0; 375 int lc16s=0; 376 int lc17s=0; 377 int lc18s=0; 378 int lc19s=0; 379 int lc20s=0; 380 int lc21s=0; 381 int lc22s=0; 382 int lc23s=0; 383 int lc24s=0; 384 int lc25s=0; 385 int lc26s=0; 386 int lc27s=0; 387 int lc28s=0; 388 int lc29s=0; 389 int lc30s=0; 390 int lc31s=0; 391 int lc32s=0; 392 int lc33s=0; 393 int lc34s=0; 394 int lc35s=0; 395 if(info.size()>0) { 396 for (int i = 0; i < info.size(); i++) { 397 FReportInfo fReportInfo = info.get(i); 398 Row tempRow = sheet.createRow(rowNum++); 399 tempRow.setHeight((short) 500); 400 for (int j = 0; j < 36; j++) { 401 Cell tempCell = tempRow.createCell(j); 402 tempCell.setCellStyle(commonStyle); 403 String tempValue = "0"; 404 switch (j) { 405 case 0: 406 tempValue = fReportInfo.getDw(); 407 break; 408 case 1: 409 int lc1 = fReportInfo.getLc1(); 410 lc1s += lc1; 411 tempValue = ""+lc1; 412 break; 413 case 2: 414 int lc2 = fReportInfo.getLc2(); 415 lc2s += lc2; 416 tempValue = ""+lc2; 417 break; 418 case 3: 419 int lc3 = fReportInfo.getLc3(); 420 lc3s += lc3; 421 tempValue = ""+lc3; 422 break; 423 case 4: 424 int lc4 = fReportInfo.getLc4(); 425 lc4s += lc4; 426 tempValue = ""+lc4; 427 break; 428 case 5: 429 int lc5 = fReportInfo.getLc5(); 430 lc5s += lc5; 431 tempValue = ""+lc5; 432 break; 433 case 6: 434 int lc6 = fReportInfo.getLc6(); 435 lc6s += lc6; 436 tempValue = ""+lc6; 437 break; 438 case 7: 439 int lc7 = fReportInfo.getLc7(); 440 lc7s += lc7; 441 tempValue = ""+lc7; 442 break; 443 case 8: 444 int lc8 = fReportInfo.getLc8(); 445 lc8s += lc8; 446 tempValue = ""+lc8; 447 break; 448 case 9: 449 int lc9 = fReportInfo.getLc9(); 450 lc9s += lc9; 451 tempValue = ""+lc9; 452 break; 453 case 10: 454 int lc10 = fReportInfo.getLc10(); 455 lc10s += lc10; 456 tempValue = ""+lc10; 457 break; 458 case 11: 459 int lc11 = fReportInfo.getLc11(); 460 lc11s += lc11; 461 tempValue = ""+lc11; 462 break; 463 case 12: 464 int lc12 = fReportInfo.getLc12(); 465 lc12s += lc12; 466 tempValue = ""+lc12; 467 break; 468 case 13: 469 int lc13 = fReportInfo.getLc13(); 470 lc13s += lc13; 471 tempValue = ""+lc13; 472 break; 473 case 14: 474 int lc14 = fReportInfo.getLc14(); 475 lc14s += lc14; 476 tempValue = ""+lc14; 477 break; 478 case 15: 479 int lc15 = fReportInfo.getLc15(); 480 lc15s += lc15; 481 tempValue = ""+lc15; 482 break; 483 case 16: 484 int lc16 = fReportInfo.getLc16(); 485 lc16s += lc16; 486 tempValue = ""+lc16; 487 break; 488 case 17: 489 int lc17 = fReportInfo.getLc17(); 490 lc17s += lc17; 491 tempValue = ""+lc17; 492 break; 493 case 18: 494 int lc18 = fReportInfo.getLc18(); 495 lc18s += lc18; 496 tempValue = ""+lc18; 497 break; 498 case 19: 499 int lc19 = fReportInfo.getLc19(); 500 lc19s += lc19; 501 tempValue = ""+lc19; 502 break; 503 case 20: 504 int lc20 = fReportInfo.getLc20(); 505 lc20s += lc20; 506 tempValue = ""+lc20; 507 break; 508 case 21: 509 int lc21 = fReportInfo.getLc21(); 510 lc21s += lc21; 511 tempValue = ""+lc21; 512 break; 513 case 22: 514 int lc22 = fReportInfo.getLc22(); 515 lc22s += lc22; 516 tempValue = ""+lc22; 517 break; 518 case 23: 519 int lc23 = fReportInfo.getLc23(); 520 lc23s += lc23; 521 tempValue = ""+lc23; 522 break; 523 case 24: 524 int lc24 = fReportInfo.getLc24(); 525 lc24s += lc24; 526 tempValue = ""+lc24; 527 break; 528 case 25: 529 int lc25 = fReportInfo.getLc25(); 530 lc25s += lc25; 531 tempValue = ""+lc25; 532 break; 533 case 26: 534 int lc26 = fReportInfo.getLc26(); 535 lc26s += lc26; 536 tempValue = ""+lc26; 537 break; 538 case 27: 539 int lc27 = fReportInfo.getLc27(); 540 lc27s += lc27; 541 tempValue = ""+lc27; 542 break; 543 case 28: 544 int lc28 = fReportInfo.getLc28(); 545 lc28s += lc28; 546 tempValue = ""+lc28; 547 break; 548 case 29: 549 int lc29 = fReportInfo.getLc29(); 550 lc29s += lc29; 551 tempValue = ""+lc29; 552 break; 553 case 30: 554 int lc30 = fReportInfo.getLc30(); 555 lc30s += lc30; 556 tempValue = ""+lc30; 557 break; 558 case 31: 559 int lc31 = fReportInfo.getLc31(); 560 lc31s += lc31; 561 tempValue = ""+lc31; 562 break; 563 case 32: 564 int lc32 = fReportInfo.getLc32(); 565 lc32s += lc32; 566 tempValue = ""+lc32; 567 break; 568 case 33: 569 int lc33 = fReportInfo.getLc33(); 570 lc33s += lc33; 571 tempValue = ""+lc33; 572 break; 573 case 34: 574 int lc34 = fReportInfo.getLc34(); 575 lc34s += lc34; 576 tempValue = ""+lc34; 577 break; 578 default: 579 int lc35 = fReportInfo.getLc35(); 580 lc35s += lc35; 581 tempValue = ""+lc35; 582 break; 583 } 584 tempCell.setCellValue(tempValue); 585 } 586 } 587 } 588 if("330000".equals(regionCode2)){ 589 Row tempRow = sheet.createRow(rowNum++); 590 tempRow.setHeight((short) 500); 591 for (int j = 0; j < 36; j++) { 592 Cell tempCell = tempRow.createCell(j); 593 tempCell.setCellStyle(commonStyle); 594 String tempValue = "0"; 595 switch (j) { 596 case 0: 597 tempValue = "合计"; 598 break; 599 case 1: 600 tempValue = ""+lc1s; 601 break; 602 case 2: 603 tempValue = ""+lc2s; 604 break; 605 case 3: 606 tempValue = ""+lc3s; 607 break; 608 case 4: 609 tempValue = ""+lc4s; 610 break; 611 case 5: 612 tempValue = ""+lc5s; 613 break; 614 case 6: 615 tempValue = ""+lc6s; 616 break; 617 case 7: 618 tempValue = ""+lc7s; 619 break; 620 case 8: 621 tempValue = ""+lc8s; 622 break; 623 case 9: 624 tempValue = ""+lc9s; 625 break; 626 case 10: 627 tempValue = ""+lc10s; 628 break; 629 case 11: 630 tempValue = ""+lc11s; 631 break; 632 case 12: 633 tempValue = ""+lc12s; 634 break; 635 case 13: 636 tempValue = ""+lc13s; 637 break; 638 case 14: 639 tempValue = ""+lc14s; 640 break; 641 case 15: 642 tempValue = ""+lc15s; 643 break; 644 case 16: 645 tempValue = ""+lc16s; 646 break; 647 case 17: 648 tempValue = ""+lc17s; 649 break; 650 case 18: 651 tempValue = ""+lc18s; 652 break; 653 case 19: 654 tempValue = ""+lc19s; 655 break; 656 case 20: 657 tempValue = ""+lc20s; 658 break; 659 case 21: 660 tempValue = ""+lc21s; 661 break; 662 case 22: 663 tempValue = ""+lc22s; 664 break; 665 case 23: 666 tempValue = ""+lc23s; 667 break; 668 case 24: 669 tempValue = ""+lc24s; 670 break; 671 case 25: 672 tempValue = ""+lc25s; 673 break; 674 case 26: 675 tempValue = ""+lc26s; 676 break; 677 case 27: 678 tempValue = ""+lc27s; 679 break; 680 case 28: 681 tempValue = ""+lc28s; 682 break; 683 case 29: 684 tempValue = ""+lc29s; 685 break; 686 case 30: 687 tempValue = ""+lc30s; 688 break; 689 case 31: 690 tempValue = ""+lc31s; 691 break; 692 case 32: 693 tempValue = ""+lc32s; 694 break; 695 case 33: 696 tempValue = ""+lc33s; 697 break; 698 case 34: 699 tempValue = ""+lc34s; 700 break; 701 default: 702 tempValue = ""+lc35s; 703 break; 704 } 705 tempCell.setCellValue(tempValue); 706 } 707 }else if("00".equals(regionCode2.substring(4,6))){ 708 if(infoCity.size()>0) { 709 FReportInfo fReportInfo = infoCity.get(0); 710 Row tempRow = sheet.createRow(rowNum++); 711 tempRow.setHeight((short) 500); 712 for (int j = 0; j < 36; j++) { 713 Cell tempCell = tempRow.createCell(j); 714 tempCell.setCellStyle(commonStyle); 715 String tempValue = "0"; 716 switch (j) { 717 case 0: 718 tempValue = "合计"; 719 break; 720 case 1: 721 tempValue = ""+fReportInfo.getLc1(); 722 break; 723 case 2: 724 tempValue = ""+fReportInfo.getLc2(); 725 break; 726 case 3: 727 tempValue = ""+fReportInfo.getLc3(); 728 break; 729 case 4: 730 tempValue = ""+fReportInfo.getLc4(); 731 break; 732 case 5: 733 tempValue = ""+fReportInfo.getLc5(); 734 break; 735 case 6: 736 tempValue = ""+fReportInfo.getLc6(); 737 break; 738 case 7: 739 tempValue = ""+fReportInfo.getLc7(); 740 break; 741 case 8: 742 tempValue = ""+fReportInfo.getLc8(); 743 break; 744 case 9: 745 tempValue = ""+fReportInfo.getLc9(); 746 break; 747 case 10: 748 tempValue = ""+fReportInfo.getLc10(); 749 break; 750 case 11: 751 tempValue = ""+fReportInfo.getLc11(); 752 break; 753 case 12: 754 tempValue = ""+fReportInfo.getLc12(); 755 break; 756 case 13: 757 tempValue = ""+fReportInfo.getLc13(); 758 break; 759 case 14: 760 tempValue = ""+fReportInfo.getLc14(); 761 break; 762 case 15: 763 tempValue = ""+fReportInfo.getLc15(); 764 break; 765 case 16: 766 tempValue = ""+fReportInfo.getLc16(); 767 break; 768 case 17: 769 tempValue = ""+fReportInfo.getLc17(); 770 break; 771 case 18: 772 tempValue = ""+fReportInfo.getLc18(); 773 break; 774 case 19: 775 tempValue = ""+fReportInfo.getLc19(); 776 break; 777 case 20: 778 tempValue = ""+fReportInfo.getLc20(); 779 break; 780 case 21: 781 tempValue = ""+fReportInfo.getLc21(); 782 break; 783 case 22: 784 tempValue = ""+fReportInfo.getLc22(); 785 break; 786 case 23: 787 tempValue = ""+fReportInfo.getLc23(); 788 break; 789 case 24: 790 tempValue = ""+fReportInfo.getLc24(); 791 break; 792 case 25: 793 tempValue = ""+fReportInfo.getLc25(); 794 break; 795 case 26: 796 tempValue = ""+fReportInfo.getLc26(); 797 break; 798 case 27: 799 tempValue = ""+fReportInfo.getLc27(); 800 break; 801 case 28: 802 tempValue = ""+fReportInfo.getLc28(); 803 break; 804 case 29: 805 tempValue = ""+fReportInfo.getLc29(); 806 break; 807 case 30: 808 tempValue = ""+fReportInfo.getLc30(); 809 break; 810 case 31: 811 tempValue = ""+fReportInfo.getLc31(); 812 case 32: 813 tempValue = ""+fReportInfo.getLc32(); 814 break; 815 case 33: 816 tempValue = ""+fReportInfo.getLc33(); 817 break; 818 case 34: 819 tempValue = ""+fReportInfo.getLc34(); 820 break; 821 default: 822 tempValue = ""+fReportInfo.getLc35(); 823 break; 824 } 825 tempCell.setCellValue(tempValue); 826 } 827 } 828 } 829 //注释行 830 Row r = sheet.createRow(rowNum++); 831 r.setHeight((short) 700); 832 Cell c = r.createCell(0); 833 String remark = "本表统计关系：栏1+21+26+31＝ 表五“栏2”数； 5+22+27+32＝表五“栏4”数； 9+23+28+33＝表五“栏6”数； 13+24+29+34＝表五“栏8”数； 17+25+30+35＝表七“栏10”数"; 834 c.setCellValue(remark); 835 c.setCellStyle(remarkStyle); 836 sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 0, 35)); 837 String fileName = "交通事故原因分类表.xlsx"; 838 // 进行导出 839 FontStyle.getResp(response, fileName, wb); 840 } 841 842 }
因为要导出的文件类型很多,所以将部分公用代码封装成了一个工具类
package com.ecenter.excle.util;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public final class FontStyle {
// 创建字体
public static void createFont(Font font) {
font.setFontName("微软雅黑");//字体样式
font.setFontHeightInPoints((short)12);//字体大小
font.setItalic(false);//设置字体是否为斜体
font.setStrikeout(false);//设置是否在文本中使用删除线水平线
font.setColor(HSSFColor.BLACK.index);//设置字体颜色
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
font.setBold(false);//设置字体是否加粗
}
//创建单元格无边框样式
public static void createNotBoderStyle(CellStyle style,Font font) {
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
style.setLocked(true);
style.setWrapText(true);//自动换行
}
//创建单元格有边框样式
public static void createBoderStyle(CellStyle style,Font font) {
createNotBoderStyle(style,font);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
}
//响应到客户端
public static void getResp(HttpServletResponse response, String fileName, XSSFWorkbook wb) {
try {
setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//发送响应流方法
public static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？