利用poi数据导出到excel demo

jsp

<button type="button" id="exportExcel" onclick="eportExcel();" class="btn btn-success ">Excel</button>

js

function eportExcel(){var url="/authFlow/export.html";var channelCode = $("#channelCode option:selected").val();var dateTimeStart=$("#dateTimeStart input").val();var dateTimeEnd=$("#dateTimeEnd input").val();var param ={'authType':$("#authType").val(),'userName':$("#userName").val(),'channelCode':channelCode,'dateTimeStart':dateTimeStart,'dateTimeEnd':dateTimeEnd};$.post(url,param,function(data){var obj =  JSON.parse(data);if(obj.success == true){toastr["success"]("导出成功！", "成功");}else{toastr["fail"]("导出失败！", "失败");}});$("#exportForm").attr("action",url);$("#exportForm").attr("method","post");$("#exportForm").submit();}

controller层

@RequestMapping(value = "/export", method = RequestMethod.POST)public void ExportExcel(AuthFlowRequest request,HttpServletResponse response){HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("认证渠道流水表");//创建第一行        HSSFRow row0=sheet.createRow(0);                //HSSFCell cell = row0.createCell(0);                row0.createCell(0).setCellValue("用户名");        row0.createCell(1).setCellValue("认证类型");        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("响应时间");        List<AuthFlowResponse> list = authFlowService.queryAll(request);        Iterator it = list.listIterator();        int j =1;        while(it.hasNext()){        AuthFlowResponse resp = (AuthFlowResponse) it.next();        HSSFRow row=sheet.createRow(j);            row.createCell(0).setCellValue(resp.getUserName());            row.createCell(1).setCellValue(parse(resp.getAuthType().toString()));            row.createCell(2).setCellValue(resp.getFee().toString());            row.createCell(3).setCellValue(resp.getMerchantName());            row.createCell(4).setCellValue(resp.getOutTradeNo());                 row.createCell(5).setCellValue(resp.getIdNumber());            row.createCell(6).setCellValue(resp.getChannelSerialNo());            row.createCell(7).setCellValue(resp.isSuccessFlag()?"成功":"失败");            if(resp.getRequestTime()!=null){            row.createCell(8).setCellValue(                new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(resp.getRequestTime().getTime()));            }            if(resp.getResponseTime()!=null){            row.createCell(9).setCellValue(                new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(resp.getResponseTime().getTime()));            }                    j++;        }    OutputStream output;try {output = response.getOutputStream();response.setHeader("Content-disposition", "attachment; filename=download.xls");  response.setContentType("application/msexcel"); wb.write(output);output.flush();output.close();} catch (Exception e) {LOGGER.error("数据导出异常",e);}  }        //翻译private  String  parse(String  authType){if("1".equals(authType)){return "身份证实名认证（两要素）";}else if("2".equals(authType)){return "银行卡三要素实名认证";}else if("3".equals(authType)){return "银行卡四要素实名认证";}else if("4".equals(authType)){return "手机实名认证";}else if("5".equals(authType)){return "图片认证";}return null;}

serviceimpl层

@Overridepublic List<AuthFlowResponse> queryAll(AuthFlowRequest request) {List<AuthFlowEntity> entities = authFlowDao.queryAll(request.getChannelCode(),request.getUserName(),request.getAuthType(),request.getIdNumber(),request.getPhoneNo(),request.getOutTradeNo(),request.getDateTimeEnd(),request.getDateTimeStart());List<AuthFlowResponse> resp = new ArrayList<AuthFlowResponse>();AuthFlowResponse response = null;String merchantName = "";try {for(AuthFlowEntity entity : entities){response = new AuthFlowResponse();//entity对象转换成response对象BeanUtils.copyProperties(entity,response);response.setMerchantCode(entity.getBizCode());//根据bizCode查询出对应merchantName  再插入对象if(response.getMerchantCode()!=null){merchantName = authMerchantDao.selectByCode(entity.getBizCode());}response.setMerchantName(merchantName);resp.add(response);}} catch (Exception e) {LOGGER.error("对象转换异常", e);}return resp;}

mapper

List<AuthFlowEntity> queryAll(@Param(value="channelCode") Integer channelCode,@Param(value="userName") String userName,@Param(value="authType") Integer authType,@Param(value="idNumber") String  idNumber,@Param(value="phoneNo") String  phoneNo,@Param(value="outTradeNo") String  outTradeNo,@Param(value="dateTimeStart") String dateTimeStart,@Param(value="dateTimeEnd") String dateTimeEnd);

mapper.xml

<select id="queryAll"  resultMap="BaseResultMap">SELECT id,biz_code,auth_type,fee,out_trade_no,user_name,id_number, bank_card_num, phone_no, success_flag, channel_serial_no, response_code, response_msg, request_time, response_timeFROM auth_flow_${channelCode}where 1=1<if test="userName != null and userName != ''">and user_name = #{userName}</if><if test="authType != null and authType != ''">AND auth_type = #{authType}</if><if test="idNumber != null and idNumber != ''">AND id_number = #{idNumber}</if><if test="phoneNo != null and phoneNo != ''">AND phone_no = #{phoneNo}</if><if test="outTradeNo != null and outTradeNo != ''">AND out_trade_no = #{outTradeNo}</if><if test=" dateTimeStart != null and dateTimeStart !=''">  <![CDATA[  and request_time <= #{dateTimeStart} ]]>  </if>  <if test=" dateTimeEnd != null and dateTimeEnd!=''">  <![CDATA[  and request_time >= #{dateTimeEnd} ]]>  </if></select>