本文共 21911 字，大约阅读时间需要 73 分钟。
1 package com.sides.operation.exportExcel; 2 3 4 import java.io.IOException; 5 import java.io.OutputStream; 6 import java.io.UnsupportedEncodingException; 7 import java.util.List; 8 9 import javax.servlet.http.HttpServletResponse; 10 11 import org.apache.poi.xssf.usermodel.XSSFCell; 12 import org.apache.poi.xssf.usermodel.XSSFCellStyle; 13 import org.apache.poi.xssf.usermodel.XSSFFont; 14 import org.apache.poi.xssf.usermodel.XSSFRow; 15 import org.apache.poi.xssf.usermodel.XSSFSheet; 16 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 17 import org.apache.struts2.interceptor.ServletResponseAware; 18 19 import com.opensymphony.xwork2.ActionSupport; 20 import com.sides.operation.hmsorderinfo.HmsOrderInfo; 21 import com.sides.operation.hmsorderinfo.HmsOrderInfoService; 22 import com.sides.operation.hmsserviceorder.HmsServiceOrder; 23 import com.sides.operation.hmsserviceorder.HmsServiceOrderService; 24 import com.sides.operation.orderTotal.OrderTotal; 25 import com.sides.operation.orderTotal.OrderTotalService; 26 import com.sides.operation.serviceOrder.ServiceOrder; 27 import com.sides.operation.serviceOrder.ServiceOrderAction; 28 import com.sides.operation.serviceOrder.ServiceOrderService; 29 import com.sides.pub.utils.ApplicationUtil; 30 import com.sun.istack.logging.Logger; 31 32 public class OutAction extends ActionSupport implements ServletResponseAware{ 33 private OrderTotal orderTotal; 34 private HmsOrderInfo hmsOrderInfo ; 35 private ServiceOrder serviceOrder; 36 private HmsServiceOrder hmsServiceOrder ; 37 private HttpServletResponse response; 38 private String fileName; 39 private static final long serialVersionUID = 1L; 40 private Logger logger = Logger.getLogger(OutAction.class); 41 42 /** 设置响应头*/ 43 public void setResponseHeader(){ 44 try{ 45 // response.setContentType("application/msexcel;charset=UTF-8"); //两种方法都可以 46 response.setContentType("application/octet-stream;charset=UTF-8"); 47 // response.setContentType("application/octet-stream;charset=iso-8859-1"); 48 // response.setHeader("Content-Disposition", "attachment;filename=" 49 // +java.net.URLEncoder.encode(this.fileName, "utf-8")); 50 51 response.setHeader("Content-Disposition", "attachment;filename=" 52 +new String( this.fileName.getBytes("gb2312"), "ISO8859-1") ); 53 54 //客户端不缓存 55 response.addHeader("Pargam", "no-cache"); 56 response.addHeader("Cache-Control", "no-cache"); 57 }catch(Exception ex){ 58 ex.printStackTrace(); 59 } 60 } 61 62 /** 63 * 导出订单excel 64 */ 65 public String orderExportExcel(){ 66 logger.info("订单汇总表"); 67 this.setFileName("订单汇总表.xlsx"); 68 setResponseHeader(); 69 try { 70 orderExportExcelData(response.getOutputStream()); 71 response.getOutputStream().flush(); 72 response.getOutputStream().close(); 73 } catch (IOException e) { 74 e.printStackTrace(); 75 } 76 logger.info("订单汇总表导出完毕"); 77 return null; 78 } 79 80 /** 81 * 导出订单Excel数据 82 * @param os 83 * @throws IOException 84 */ 85 private void orderExportExcelData(OutputStream os) throws IOException{ 86 87 logger.info("订单汇总表 Excel数据处理"); 88 89 XSSFWorkbook wb = new XSSFWorkbook(); 90 91 // 创建标题单元格样式 92 XSSFCellStyle cellStyleTitle = wb.createCellStyle(); 93 // 指定单元格居中对齐 94 cellStyleTitle.setAlignment(XSSFCellStyle.ALIGN_CENTER); 95 // 指定单元格垂直居中对齐 96 cellStyleTitle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); 97 // 指定当单元格内容显示不下时自动换行 98 cellStyleTitle.setWrapText(true); 99 100 XSSFCellStyle cellStyle = wb.createCellStyle(); 101 // 指定单元格居中对齐 102 cellStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT); 103 // 指定单元格垂直居中对齐 104 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); 105 // 指定当单元格内容显示不下时自动换行 106 cellStyle.setWrapText(true); 107 // ------------------------------------------------------------------ 108 // 设置单元格字体 109 XSSFFont titleFont = wb.createFont(); 110 titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); 111 titleFont.setFontName("宋体"); 112 titleFont.setFontHeight((short) 200); 113 114 XSSFFont font = wb.createFont(); 115 font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); 116 font.setFontName("宋体"); 117 font.setFontHeight((short) 200); 118 119 cellStyleTitle.setFont(titleFont); 120 cellStyle.setFont(font);121 122 XSSFSheet sheet = wb.createSheet("订单统计信息"); 123 XSSFRow row = sheet.createRow(0);124 125 XSSFCell cell0 = row.createCell(0) ;126 cell0.setCellValue("订单编号");127 cell0.setCellStyle(cellStyleTitle);128 129 XSSFCell cell1 = row.createCell(1) ;130 cell1.setCellValue("商品名称");131 cell1.setCellStyle(cellStyleTitle);132 133 134 // XSSFCell cell2 = row.createCell(2) ;135 // cell2.setCellValue("数量");136 // cell2.setCellStyle(cellStyleTitle);137 138 XSSFCell cell3 = row.createCell(2) ;139 cell3.setCellValue("产生日期");140 cell3.setCellStyle(cellStyleTitle);141 142 /*XSSFCell cell4 = row.createCell(4) ;143 cell4.setCellValue("接收人姓名");144 cell4.setCellStyle(cellStyleTitle);145 146 XSSFCell cell5 = row.createCell(5) ;147 cell5.setCellValue("联系电话");148 cell5.setCellStyle(cellStyleTitle);149 150 XSSFCell cell6 = row.createCell(6) ;151 cell6.setCellValue("收货方式");152 cell6.setCellStyle(cellStyleTitle);153 154 XSSFCell cell7 = row.createCell(7) ;155 cell7.setCellValue("地址");156 cell7.setCellStyle(cellStyleTitle);157 158 XSSFCell cell8 = row.createCell(8) ;159 cell8.setCellValue("配送时间 ");160 cell8.setCellStyle(cellStyleTitle);*/161 162 XSSFCell cell9 = row.createCell(3) ;163 cell9.setCellValue("订单总价");164 cell9.setCellStyle(cellStyleTitle);165 166 XSSFCell cell10 = row.createCell(4) ;167 cell10.setCellValue("订单状态");168 cell10.setCellStyle(cellStyleTitle);169 170 final HmsOrderInfoService hmsOrderInfoService = (HmsOrderInfoService)ApplicationUtil.getBean("hmsOrderInfoServiceImpl");171 List list = hmsOrderInfoService.queryListHmsOrderInfo(hmsOrderInfo) ;172 for(int i=1;i<=list.size();i++){173 HmsOrderInfo el = list.get(i-1);174 175 //String orderDeliver = el.getOrderDeliverStr() == null?"":el.getOrderDeliverStr();176 177 row = sheet.createRow(i);178 cell0 = row.createCell(0) ;179 cell0.setCellValue(el.getOrderNumber());180 cell0.setCellStyle(cellStyle);181 182 // cell1 = row.createCell(1) ;183 // cell1.setCellValue(el.getProductName()+"("+el.getProductType()+"L)");184 // cell1.setCellStyle(cellStyle);185 186 cell1 = row.createCell(1) ;187 cell1.setCellValue(el.getMainPackage());188 cell1.setCellStyle(cellStyle);189 190 // cell2 = row.createCell(2) ;191 // cell2.setCellValue(el.getQuantity()+"*"+el.getProType()+"L");192 // cell2.setCellStyle(cellStyle);193 194 cell3 = row.createCell(2) ;195 cell3.setCellValue(el.getOrderTimeStr());196 cell3.setCellStyle(cellStyle);197 198 /*cell4 = row.createCell(4) ;199 cell4.setCellValue(el.getOrderRecName());200 cell4.setCellStyle(cellStyle);201 202 cell5 = row.createCell(5) ;203 cell5.setCellValue(el.getOrderPhone());204 cell5.setCellStyle(cellStyle);205 206 cell6 = row.createCell(6) ;207 cell6.setCellValue(el.getDeliverTypeStr());208 cell6.setCellStyle(cellStyle);209 210 cell7 = row.createCell(7) ;211 cell7.setCellValue(el.getOrderRecAddress());212 cell7.setCellStyle(cellStyle);213 214 cell8 = row.createCell(8) ;215 cell8.setCellValue(orderDeliver);216 cell8.setCellStyle(cellStyle);*/217 218 cell9 = row.createCell(3) ;219 cell9.setCellValue(el.getOrderTotalPrice());220 cell9.setCellStyle(cellStyle);221 222 cell10 = row.createCell(4) ;223 cell10.setCellValue(el.getOrderStatusName());224 cell10.setCellStyle(cellStyle);225 226 }227 228 for(int j=0;j<11;j++){229 sheet.autoSizeColumn((short)j); //调整第一列宽度230 }231 232 try{ 233 wb.write(os); 234 }catch(Exception ex){ 235 ex.printStackTrace(); 236 } 237 logger.info("订单汇总表 Excel数据处理完毕");238 } 239 240 /**241 * 导出服务预约excel242 */243 public String appointServiceExportExcel(){244 logger.info("进入服务预约");245 this.setFileName("服务预约汇总表.xlsx");246 setResponseHeader(); 247 try { 248 appointServiceExportExcelData(response.getOutputStream()); 249 response.getOutputStream().flush(); 250 response.getOutputStream().close(); 251 } catch (IOException e) { 252 e.printStackTrace(); 253 } 254 logger.info("服务预约完毕");255 return null;256 }257 258 /**259 * 导出订单Excel数据 260 * @param os261 * @throws IOException262 */263 private void appointServiceExportExcelData(OutputStream os) throws IOException{ 264 265 logger.info("进入服务预约 Excel处理");266 267 XSSFWorkbook wb = new XSSFWorkbook();268 269 // 创建标题单元格样式 270 XSSFCellStyle cellStyleTitle = wb.createCellStyle(); 271 // 指定单元格居中对齐 272 cellStyleTitle.setAlignment(XSSFCellStyle.ALIGN_CENTER); 273 // 指定单元格垂直居中对齐 274 cellStyleTitle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); 275 // 指定当单元格内容显示不下时自动换行 276 cellStyleTitle.setWrapText(true); 277 278 XSSFCellStyle cellStyle = wb.createCellStyle(); 279 // 指定单元格居中对齐 280 cellStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT); 281 // 指定单元格垂直居中对齐 282 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); 283 // 指定当单元格内容显示不下时自动换行 284 cellStyle.setWrapText(true); 285 // ------------------------------------------------------------------ 286 // 设置单元格字体 287 XSSFFont titleFont = wb.createFont(); 288 titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); 289 titleFont.setFontName("宋体"); 290 titleFont.setFontHeight((short) 200); 291 292 XSSFFont font = wb.createFont(); 293 font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); 294 font.setFontName("宋体"); 295 font.setFontHeight((short) 200); 296 297 cellStyleTitle.setFont(titleFont); 298 cellStyle.setFont(font);299 300 XSSFSheet sheet = wb.createSheet("服务预约统计信息"); 301 XSSFRow row = sheet.createRow(0);302 303 XSSFCell cell0 = row.createCell(0) ;304 cell0.setCellValue("订单编号");305 cell0.setCellStyle(cellStyleTitle);306 307 308 XSSFCell cell1 = row.createCell(1) ;309 cell1.setCellValue("姓名");310 cell1.setCellStyle(cellStyleTitle);311 312 XSSFCell cell2 = row.createCell(2) ;313 cell2.setCellValue("联系电话");314 cell2.setCellStyle(cellStyleTitle);315 316 XSSFCell cell3 = row.createCell(3) ;317 cell3.setCellValue("预约时间");318 cell3.setCellStyle(cellStyleTitle);319 320 XSSFCell cell4 = row.createCell(4) ;321 cell4.setCellValue("到店时间");322 cell4.setCellStyle(cellStyleTitle);323 324 XSSFCell cell5 = row.createCell(5) ;325 cell5.setCellValue("服务完成时间");326 cell5.setCellStyle(cellStyleTitle);327 328 XSSFCell cell6 = row.createCell(6) ;329 cell6.setCellValue("网点名称");330 cell6.setCellStyle(cellStyleTitle);331 332 XSSFCell cell7 = row.createCell(7) ;333 cell7.setCellValue("网点地址");334 cell7.setCellStyle(cellStyleTitle);335 336 XSSFCell cell8 = row.createCell(8) ;337 cell8.setCellValue("网点电话");338 cell8.setCellStyle(cellStyleTitle);339 340 XSSFCell cell9 = row.createCell(9) ;341 cell9.setCellValue("服务状态");342 cell9.setCellStyle(cellStyleTitle);343 344 XSSFCell cell10 = row.createCell(10) ;345 cell10.setCellValue("主套餐");346 cell10.setCellStyle(cellStyleTitle);347 348 XSSFCell cell11 = row.createCell(11) ;349 cell11.setCellValue("附加套餐");350 cell11.setCellStyle(cellStyleTitle);351 352 XSSFCell cell12 = row.createCell(12) ;353 cell12.setCellValue("车辆品牌");354 cell12.setCellStyle(cellStyleTitle);355 356 XSSFCell cell13 = row.createCell(13) ;357 cell13.setCellValue("车辆型号");358 cell13.setCellStyle(cellStyleTitle);359 360 XSSFCell cell14 = row.createCell(14) ;361 cell14.setCellValue("车辆年款");362 cell14.setCellStyle(cellStyleTitle);363 364 XSSFCell cell15 = row.createCell(15) ;365 cell15.setCellValue("车牌号");366 cell15.setCellStyle(cellStyleTitle);367 368 XSSFCell cell16 = row.createCell(16) ;369 cell16.setCellValue("取车地址");370 cell16.setCellStyle(cellStyleTitle);371 372 XSSFCell cell17 = row.createCell(17) ;373 cell17.setCellValue("送车地址");374 cell17.setCellStyle(cellStyleTitle);375 376 377 final HmsServiceOrderService hmsServiceOrderService = (HmsServiceOrderService)ApplicationUtil.getBean("hmsServiceOrderServiceImpl");378 List list = hmsServiceOrderService.queryPTListHmsServiceOrder(hmsServiceOrder);379 380 //已预约数381 int yycount = 0 ;382 //已到店数383 int ddcount = 0 ;384 //已服务完数385 int fwcount = 0 ;386 387 logger.info("servicestate:"+hmsServiceOrder.getServiceState()+"list.size():"+list.size());388 389 if(null==hmsServiceOrder.getServiceState()||"".equals(hmsServiceOrder.getServiceState())){390 hmsServiceOrder.setServiceState(1);391 List yylist = hmsServiceOrderService.queryPTListHmsServiceOrder(hmsServiceOrder);392 yycount = yylist.size() ;393 hmsServiceOrder.setServiceState(2);394 List ddlist = hmsServiceOrderService.queryPTListHmsServiceOrder(hmsServiceOrder);395 ddcount = ddlist.size() ;396 hmsServiceOrder.setServiceState(3);397 List fwlist = hmsServiceOrderService.queryPTListHmsServiceOrder(hmsServiceOrder);398 fwcount = fwlist.size() ;399 }else if(1==hmsServiceOrder.getServiceState()){400 yycount = list.size() ;401 }else if(2==hmsServiceOrder.getServiceState()){402 ddcount = list.size() ;403 }else if(3==hmsServiceOrder.getServiceState()){404 fwcount = list.size() ;405 }406 407 408 409 410 for(int i=1;i<=list.size();i++){411 412 HmsServiceOrder el = list.get(i-1);413 String shoptime = el.getStrShopTime() == null?"":el.getStrShopTime();414 String completeTime = el.getStrCompleteTime()==null?"":el.getStrCompleteTime() ;415 416 row = sheet.createRow(i);417 cell0 = row.createCell(0) ;418 cell0.setCellValue(el.getOrderNumber());419 cell0.setCellStyle(cellStyle);420 421 cell1 = row.createCell(1) ;422 cell1.setCellValue(el.getReserveName());423 cell1.setCellStyle(cellStyle);424 425 cell2 = row.createCell(2) ;426 cell2.setCellValue(el.getReservePhone());427 cell2.setCellStyle(cellStyle);428 429 cell3 = row.createCell(3) ;430 cell3.setCellValue(el.getStrReserveTime());431 cell3.setCellStyle(cellStyle);432 433 cell4 = row.createCell(4) ;434 cell4.setCellValue(shoptime);435 cell4.setCellStyle(cellStyle);436 437 cell5 = row.createCell(5) ;438 cell5.setCellValue(completeTime);439 cell5.setCellStyle(cellStyle);440 441 cell6 = row.createCell(6) ;442 cell6.setCellValue(el.getServiceName());443 cell6.setCellStyle(cellStyle);444 445 cell7 = row.createCell(7) ;446 cell7.setCellValue(el.getServicePlaceAddress());447 cell7.setCellStyle(cellStyle);448 449 cell8 = row.createCell(8) ;450 cell8.setCellValue(el.getServicePlacePhone());451 cell8.setCellStyle(cellStyle);452 453 cell9 = row.createCell(9) ;454 cell9.setCellValue(el.getServiceStateStr());455 cell9.setCellStyle(cellStyle);456 457 cell10 = row.createCell(10) ;458 cell10.setCellValue(el.getMainPackages());459 cell10.setCellStyle(cellStyle);460 461 cell11 = row.createCell(11) ;462 cell11.setCellValue(el.getAdditPackages()==null?"":el.getAdditPackages());463 cell11.setCellStyle(cellStyle);464 465 466 cell12 = row.createCell(12) ;467 cell12.setCellValue(el.getVehicleBrandStr()==null?"":el.getVehicleBrandStr());468 cell12.setCellStyle(cellStyle);469 470 cell13 = row.createCell(13) ;471 cell13.setCellValue(el.getVehicleSeriesStr()==null?"":el.getVehicleSeriesStr());472 cell13.setCellStyle(cellStyle);473 474 cell14 = row.createCell(14) ;475 cell14.setCellValue(el.getVehicleYearStr()==null?"":el.getVehicleYearStr());476 cell14.setCellStyle(cellStyle);477 478 cell15 = row.createCell(15) ;479 cell15.setCellValue(el.getPlateNo());480 cell15.setCellStyle(cellStyle);481 482 cell16 = row.createCell(16) ;483 cell16.setCellValue(el.getGetcarAddr()==null?"":el.getGetcarAddr());484 cell16.setCellStyle(cellStyle);485 486 cell17 = row.createCell(17) ;487 cell17.setCellValue(el.getSendcarAddr()==null?"":el.getSendcarAddr());488 cell17.setCellStyle(cellStyle);489 490 }491 492 row = sheet.createRow(list.size()+1);493 cell0 = row.createCell(0) ;494 cell0.setCellValue("数据统计：");495 cell0.setCellStyle(cellStyleTitle);496 497 cell1 = row.createCell(1) ;498 cell1.setCellValue("预约数："+yycount);499 cell1.setCellStyle(cellStyleTitle);500 501 cell2 = row.createCell(2) ;502 cell2.setCellValue("服务中数："+ddcount);503 cell2.setCellStyle(cellStyleTitle);504 505 cell3 = row.createCell(3) ;506 cell3.setCellValue("已服务数："+fwcount);507 cell3.setCellStyle(cellStyleTitle);508 509 510 for(int j=0;j<18;j++){511 sheet.autoSizeColumn((short)j); //调整第一列宽度512 }513 514 try{ 515 wb.write(os); 516 }catch(Exception ex){ 517 ex.printStackTrace(); 518 } 519 logger.info("服务预约 Excel处理完毕");520 } 521 522 /**记住一定有该属性的set方法*/ 523 public void setServletResponse(HttpServletResponse response) { 524 this.response = response; 525 }526 527 public OrderTotal getOrderTotal() {528 return orderTotal;529 }530 531 public void setOrderTotal(OrderTotal orderTotal) {532 this.orderTotal = orderTotal;533 }534 535 public ServiceOrder getServiceOrder() {536 return serviceOrder;537 }538 539 public void setServiceOrder(ServiceOrder serviceOrder) {540 this.serviceOrder = serviceOrder;541 }542 543 public String getFileName() {544 return fileName;545 }546 547 public void setFileName(String fileName) {548 this.fileName = fileName;549 }550 551 public HmsOrderInfo getHmsOrderInfo() {552 return hmsOrderInfo;553 }554 555 public void setHmsOrderInfo(HmsOrderInfo hmsOrderInfo) {556 this.hmsOrderInfo = hmsOrderInfo;557 }558 559 public HmsServiceOrder getHmsServiceOrder() {560 return hmsServiceOrder;561 }562 563 public void setHmsServiceOrder(HmsServiceOrder hmsServiceOrder) {564 this.hmsServiceOrder = hmsServiceOrder;565 } 566 567 } 转载地址：http://hladi.baihongyu.com/