文件下载利用poi导出excel

jsp

<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-print" plain="true" onclick="downReslt()">下载</a>

js

//下载任务详情function downReslt(){$.ajax({            url:'/taskManager/queryFlagMap.html',            type:"post",            data:{'channelId':$('#resultChannelId').val()},            dataType:'json',            success:function(data){            var obj = {flag :$('#d-flag').combobox('getValue'),deleted :$("#d-deleted").combobox('getValue'),taskId :$("#resultTaskId").val(),channelId :$("#resultChannelId").val()};             for(var i=0;i<data.length;i++){            obj[""+data[i].field] = $("#"+data[i].field).val();            }            var url = '/taskManager/download.html?';            //格式化参数            param=$.param(obj);             //拼接            url=url+param;            $('#resultDownLoad').attr("action",url);            $('#resultDownLoad').attr("method","post");            $('#resultDownLoad').submit();           }        }); }

controller

    @RequestMapping("/download")    @ResponseBodypublic Result download(HttpServletResponse resp,HttpServletRequest request){try {log.info("对账结果导出开始"); // 根据查询条件及渠道Id获取对应表和结果数据 HSSFWorkbook wb  = taskDataService.loadResults(request); //创建输出流 OutputStream op = resp.getOutputStream(); resp.setHeader("Content-Disposition", "attachment; filename=download.xls");   resp.setContentType("application/msexcel"); wb.write(op); op.flush(); //关闭流 op.close(); wb.close();} catch (IOException e) {log.error("对账结果导出异常:"+ e);return Result.error("对账结果导出异常");}log.info("对账结果导出完成");return Result.success("下载对账结果成功");}    }

serivceImpl  实现方式1

public HSSFWorkbook loadResults(HttpServletRequest request) {//获取条件查询字段Integer taskId=Integer.valueOf( request.getParameter("taskId"));Integer channelId=Integer.valueOf(request.getParameter("channelId"));String flag=String.valueOf(request.getParameter("flag")).equals("3")?"":request.getParameter("flag");String deleted=String.valueOf(request.getParameter("deleted")).equals("2")?"":request.getParameter("deleted");ChannelEntity entity=channelService.selectById(DatasoureFlagEnum.DATA_R_CASH.getCode(),channelId);String schemaInfo=entity.getSchemaInfo();SchemaConfig config=JSON.parseObject(schemaInfo,SchemaConfig.class);String tableName=config.getTableName(); HSSFWorkbook wb = new HSSFWorkbook();try{StringBuffer selectSql=new StringBuffer("select * from " + tableName+ "_data  where task_id=" + taskId);for(int i=0;i<config.getFields().size();i++){if(String.valueOf(config.getFields().get(i).getQueryFlag()).equals("1")){String fieldValue = request.getParameter(config.getFields().get(i).getColumn());if(StringUtils.isNotBlank(fieldValue)){selectSql.append(" and "+config.getFields().get(i).getColumn()+"='"+fieldValue+"'");}}}if(StringUtils.isNotBlank(flag))selectSql.append(" and flag="+flag);if(StringUtils.isNotBlank(deleted))selectSql.append(" and deleted="+deleted);selectSql.append(" order by request_id desc ");final HSSFSheet sheet = wb.createSheet("对账结果表");//创建表头信息HSSFRow row0 = sheet.createRow(0);row0.createCell(0).setCellValue("比对结果");row0.createCell(1).setCellValue("处理状态");final List<FieldInfo> fields = config.getFields();int cursor = 2;for(FieldInfo f : fields){if(f.getColumn().endsWith("_amount")){row0.createCell(cursor++).setCellValue(StringUtils.replace(f.getLabel(),"分", "元"));} else{row0.createCell(cursor++).setCellValue(f.getLabel());}}row0.createCell(cursor).setCellValue("结果ID");final AtomicInteger counter = new AtomicInteger(1);baseJdbcDao.queryObjects(new String(selectSql),new org.springframework.jdbc.core.RowMapper<Void>() {@Overridepublic Void mapRow(ResultSet rs, int rowNum)  throws SQLException {HSSFRow row = sheet.createRow(counter.getAndIncrement());if(StringUtils.isNotEmpty(rs.getString("flag"))){if("0".equals(rs.getString("flag"))){row.createCell(0).setCellValue("后台失败记录");}else if("1".equals(rs.getString("flag"))){row.createCell(0).setCellValue("比对成功记录");}else if("2".equals(rs.getString("flag"))){row.createCell(0).setCellValue("网关失败记录");}}if(StringUtils.isNotEmpty(rs.getString("deleted"))){if("0".equals(rs.getString("deleted")))row.createCell(1).setCellValue("未处理");if("1".equals(rs.getString("deleted")))row.createCell(1).setCellValue("已处理");}int cursor = 2;for(FieldInfo f : fields){if(StringUtils.isNotEmpty(rs.getString(f.getColumn()))){//TODO if(f.getColumn().endsWith("_amount")){try {row.createCell(cursor).setCellValue(AmountUtils.changeF2Y(rs.getString(f.getColumn())));} catch (Exception e) {e.printStackTrace();}} else{row.createCell(cursor).setCellValue(rs.getString(f.getColumn()));}}cursor++;}if(StringUtils.isNotEmpty(rs.getString("request_id")))row.createCell(cursor).setCellValue(rs.getString("request_id"));return null;}});}catch(Exception e){logger.error("查询任务详情异常："+ e);}return wb;}

serviceimpl 实现方式2

public HSSFWorkbook loadResult(TaskDataRequest request) {Integer channelId=request.getChannelId();Integer taskId=request.getTaskId();String userName=request.getUserName();String bankName=request.getBankName();String flag=request.getFlag();String deleted=request.getDeleted();ChannelEntity entity=channelService.selectById(DatasoureFlagEnum.DATA_R_CASH.getCode(),channelId);String schemaInfo=entity.getSchemaInfo();SchemaConfig config=JSON.parseObject(schemaInfo,SchemaConfig.class);String tableName=config.getTableName(); HSSFWorkbook wb = new HSSFWorkbook();try{StringBuffer selectSql=new StringBuffer("select * from " + tableName+ "_data  where task_id=" + taskId);if(StringUtils.isNotBlank(userName))selectSql.append(" and user_name='"+userName+"'");if(StringUtils.isNotBlank(bankName))selectSql.append(" and bank_name='"+bankName+"'");if(StringUtils.isNotBlank(flag))selectSql.append(" and flag="+flag);if(StringUtils.isNotBlank(deleted))selectSql.append(" and deleted="+deleted);selectSql.append(" order by request_id desc ");final HSSFSheet sheet = wb.createSheet("对账结果表");//创建表头信息HSSFRow row0 = sheet.createRow(0);row0.createCell(0).setCellValue("比对结果");row0.createCell(1).setCellValue("处理状态");final List<FieldInfo> fields = config.getFields();int cursor = 2;for(FieldInfo f : fields){if(f.getColumn().endsWith("_amount")){row0.createCell(cursor++).setCellValue(StringUtils.replace(f.getLabel(),"分", "元"));} else{row0.createCell(cursor++).setCellValue(f.getLabel());}}///*row0.createCell(2).setCellValue("流水号");//row0.createCell(3).setCellValue("商户订单号");//row0.createCell(4).setCellValue("用户名称");//row0.createCell(5).setCellValue("支付金额(分)");//row0.createCell(6).setCellValue("渠道成本(分)");//row0.createCell(7).setCellValue("三方名称");//row0.createCell(8).setCellValue("银行名称");//row0.createCell(9).setCellValue("充值状态");//row0.createCell(10).setCellValue("支付状态");//row0.createCell(11).setCellValue("订单时间");*///row0.createCell(12).setCellValue("储蓄卡");//deprecatedrow0.createCell(cursor).setCellValue("结果ID");final AtomicInteger counter = new AtomicInteger(1);baseJdbcDao.queryObjects(new String(selectSql),new org.springframework.jdbc.core.RowMapper<Void>() {@Overridepublic Void mapRow(ResultSet rs, int rowNum)  throws SQLException {HSSFRow row = sheet.createRow(counter.getAndIncrement());if(StringUtils.isNotEmpty(rs.getString("flag"))){if("0".equals(rs.getString("flag"))){row.createCell(0).setCellValue("后台失败记录");}else if("1".equals(rs.getString("flag"))){row.createCell(0).setCellValue("比对成功记录");}else if("2".equals(rs.getString("flag"))){row.createCell(0).setCellValue("网关失败记录");}}if(StringUtils.isNotEmpty(rs.getString("deleted"))){if("0".equals(rs.getString("deleted")))row.createCell(1).setCellValue("未处理");if("1".equals(rs.getString("deleted")))row.createCell(1).setCellValue("已处理");}int cursor = 2;for(FieldInfo f : fields){if(StringUtils.isNotEmpty(rs.getString(f.getColumn()))){//TODO if(f.getColumn().endsWith("_amount")){try {row.createCell(cursor).setCellValue(AmountUtils.changeF2Y(rs.getString(f.getColumn())));} catch (Exception e) {e.printStackTrace();}} else{row.createCell(cursor).setCellValue(rs.getString(f.getColumn()));}}cursor++;}///*if(StringUtils.isNotEmpty(rs.getString("biz_workflow_id")))//row.createCell(2).setCellValue(rs.getString("biz_workflow_id"));//if(StringUtils.isNotEmpty(rs.getString("merchant_no")))//row.createCell(3).setCellValue(rs.getString("merchant_no"));//if(StringUtils.isNotEmpty(rs.getString("user_name")))//row.createCell(4).setCellValue(rs.getString("user_name"));//if(StringUtils.isNotEmpty(rs.getString("pay_amount")))//row.createCell(5).setCellValue(rs.getString("pay_amount"));//if(StringUtils.isNotEmpty(rs.getString("fee_amount")))//row.createCell(6).setCellValue(rs.getString("fee_amount"));//if(StringUtils.isNotEmpty(rs.getString("third_name")))//row.createCell(7).setCellValue(rs.getString("third_name"));//if(StringUtils.isNotEmpty(rs.getString("bank_name")))//row.createCell(8).setCellValue(rs.getString("bank_name"));//if(StringUtils.isNotEmpty(rs.getString("charge_des")))//row.createCell(9).setCellValue(rs.getString("charge_des"));//if(StringUtils.isNotEmpty(rs.getString("pay_des")))//row.createCell(10).setCellValue(rs.getString("pay_des"));//if(StringUtils.isNotEmpty(rs.getString("order_time")))//row.createCell(11).setCellValue(rs.getString("order_time"));*///if(StringUtils.isNotEmpty(rs.getString("card_type")))//row.createCell(12).setCellValue(rs.getString("card_type"));if(StringUtils.isNotEmpty(rs.getString("request_id")))row.createCell(cursor).setCellValue(rs.getString("request_id"));return null;}});}catch(Exception e){logger.error("查询任务详情异常："+ e);}return wb;}