/*** 导出错误信息** @Title exportErrMsgQuery* @Description 【错误信息导出功能】* @Date 2018年6月5日* @Author wayne*/@RequestMapping(value = "/exportErrMsgQuery", method = RequestMethod.GET)public void exportErrMsgQuery(OrderChangeRequestSearchEntity param, Principal principal, HttpServletRequest request, HttpServletResponse response) {logger.info("----- 错误信息导出方法开始 -----");long loggerStart = System.currentTimeMillis();try (HSSFWorkbook workbook = new HSSFWorkbook()) {//根据条件使用分页方法查询所有需导出的数据 暂定默认5000条ExecuteResult<Pagination<JSONObject>> searchResult = getPage(5000, 1, param, principal);if (!searchResult.isSuccess()) {logger.error("导出错误信息数据时，查询数据出错:" + searchResult);return;}// 时间格式化SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");List<JSONObject> resultList = searchResult.getData().getRecords();// 声明一个工作薄// 生成一个表格HSSFSheet sheet = workbook.createSheet("错误信息信息");// 设置表格默认列宽度为15个字节sheet.setDefaultColumnWidth((short) 15);// 生成一个样式HSSFCellStyle style = workbook.createCellStyle();// 生成一个字体HSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 12);// 把字体应用到当前的样式style.setFont(font);// 生成并设置另一个样式HSSFCellStyle style2 = workbook.createCellStyle();// 生成另一个字体HSSFFont font2 = workbook.createFont();// 把字体应用到当前的样式style2.setFont(font2);// 产生表格标题行HSSFRow row = sheet.createRow(0);String[] title = {"工贸名称", "网点编码", "服务商名称", "工单编号", "手机号码", "错误街道", "正确街道", "错误服务区域", "正确服务区域","错误POI名称", "正确POI名称", "错误POI坐标（经度）", "错误POI坐标（纬度）", "正确POI坐标（经度）", "正确POI坐标（纬度）","提报时间", "创建时间", "错误类型", "网点申请描述", "系统返回描述", "审核结果", "系统状态", "错误服务类型", "正确服务类型","错误产品大类", "正确产品大类", "错误产品型号", "正确产品型号", "错误详细地址", "是否重新派工", "正确详细地址", "新工单号", "责任人","坐席编号", "坐席名称", "是否强结", "审核状态", "审批记录", "提报记录", "审核意见", "审核时间", "审核人", "审核来源"};for (short j = 0; j < title.length; j++) {HSSFCell cell = row.createCell(j);cell.setCellStyle(style);HSSFRichTextString text = new HSSFRichTextString(title[j]);cell.setCellValue(text);}if (CollectionUtils.isNotEmpty(resultList)) {for (int i = 0; i < resultList.size(); i++) {row = sheet.createRow(i + 1);JSONObject obj = resultList.get(i);int colNumber = -1;//工贸名称if (colNumber++ > -2 && StringUtils.isNotBlank(obj.getString("hsicrmRegionname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmRegionname"));}//网点编码if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmServicestationcode"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmServicestationcode"));}//服务商名称if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmServicestationname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmServicestationname"));}//工单编号if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmWorkorderid"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmWorkorderid"));}// 手机号码if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmMobilenumber"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmMobilenumber"));}// 街道if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPrevioustownname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPrevioustownname"));}// 正确街道if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmTownname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmTownname"));}// 服务区域if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPreviouspoiregion"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPreviouspoiregion"));}// 正确服务区域if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPoiregion"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPoiregion"));}// POI名称if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPreviouspoiname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPreviouspoiname"));}// 正确POI名称if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPoiname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPoiname"));}// POI坐标（经度）if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPreviouslongitude"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPreviouslongitude"));}// POI坐标（纬度）if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPreviouslatitude"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPreviouslatitude"));}// 正确POI坐标（经度）if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmLongitude"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmLongitude"));}// 正确POI坐标（纬度）if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmLatitude"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmLatitude"));}//提报时间if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmSubmittime"))) {row.createCell(colNumber).setCellValue(dateFormat.format(obj.getDate("hsicrmSubmittime")));}//创建时间if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmCreateddate"))) {row.createCell(colNumber).setCellValue(dateFormat.format(obj.getDate("hsicrmCreateddate")));}//错误类型if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmErrortypename"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmErrortypename"));}// 网点申请描述if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmDesc"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmDesc"));}// 系统返回描述审核结果if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmSystemreturnedmessage"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmSystemreturnedmessage"));}// 审核结果if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmOperateresult"))) {row.createCell(colNumber).setCellValue(StringUtils.equals("100000000", obj.getString("hsicrmOperateresult")) ? "通过" : "不通过");}// 系统状态if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmJobstate"))) {row.createCell(colNumber).setCellValue(StringUtils.equals("100000000", obj.getString("hsicrmJobstate")) ? "未处理" : (StringUtils.equals("100000001", obj.getString("hsicrmJobstate")) ? "成功" : (StringUtils.equals("100000002", obj.getString("hsicrmJobstate")) ? "失败" : "未处理")));}// 错误服务类型if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPreviousrequireservicetypename"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPreviousrequireservicetypename"));}// 正确服务类型if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmRequireservicetypename"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmRequireservicetypename"));}// 错误产品大类if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPreviousproductcategoryname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPreviousproductcategoryname"));}// 正确产品大类if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmProductcategoryname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmProductcategoryname"));}// 错误产品型号if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPreviousproductmodelname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPreviousproductmodelname"));}// 正确产品型号if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmProductmodelname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmProductmodelname"));}// 错误详细地址if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmPreviousconsumeraddr"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmPreviousconsumeraddr"));}// 是否重新派工if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmNeeddispatching"))) {row.createCell(colNumber).setCellValue(obj.getBoolean("hsicrmNeeddispatching") ? "是" : "否");}// 正确详细地址if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmConsumeraddr"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmConsumeraddr"));}// 新工单号if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmNewworkorderid"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmNewworkorderid"));}// 责任人if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmResponsibleperson"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmResponsibleperson"));}// 坐席编号if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmSeatsno"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmSeatsno"));}// 坐席名称if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmSeatsname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmSeatsname"));}// 是否强结if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmNeedforcecloseworkorder"))) {row.createCell(colNumber).setCellValue(obj.getBoolean("hsicrmNeedforcecloseworkorder") ? "是" : "否");}// 审核状态if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmStatusname"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmStatusname"));}// 审批记录if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmApprovallog"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmApprovallog"));}// 提报记录if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmSubmitlog"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmSubmitlog"));}// 审核意见if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmOperatecomment"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmOperatecomment"));}// 审核时间if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmOperatetime"))) {row.createCell(colNumber).setCellValue(dateFormat.format(obj.getDate("hsicrmOperatetime")));}// 审核人if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmOperateby"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmOperateby"));}// 审核来源if (colNumber++ > -2 && StringUtils.isNoneBlank(obj.getString("hsicrmOperatesource"))) {row.createCell(colNumber).setCellValue(obj.getString("hsicrmOperatesource"));}}}response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("错误信息", "UTF8") + ".xls");OutputStream ouputStream = response.getOutputStream();workbook.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (Exception e) {logger.error("导出错误信息数据失败:" + e.getMessage());}long loggerEnd = System.currentTimeMillis();logger.warn("----- 错误信息导出方法耗时：" + (loggerEnd - loggerStart) + "ms");logger.info("----- 错误信息导出方法结束 -----");}
依赖<**dependency**><br /> <**groupId**>org.apache.poi</**groupId**><br /> <**artifactId**>poi-ooxml</**artifactId**><br /> <**version**>3.16</**version**><br /></**dependency**>
若有收获，就点个赞吧
0 人点赞
