spring 处理excel 工具类

package com.mobanker.tkj.cw.web.v1_0_0;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.dianping.cat.message.Transaction;
import com.mobanker.framework.dto.PageQueryCondition;
import com.mobanker.framework.dto.ResponseEntity;
import com.mobanker.framework.tracking.EE;
import com.mobanker.tkj.cw.biz.AccountRepayManager;
import com.mobanker.tkj.cw.core.constant.errorcode.CWConstants;
import com.mobanker.tkj.cw.core.util.ExcelUtils;
import com.mobanker.tkj.cw.repay.dto.AccountRepayDto;
import com.mobanker.tkj.cw.repay.service.AccountRepayService;

/**
 * Created by qiuyangjun on 2014/12/3.
 */
@Controller
@RequestMapping("accountRepay")
public class AccountRepayController
{

    protected final Logger      logger = LoggerFactory.getLogger(getClass());

    @Resource
    private AccountRepayManager accountRepayManager;
    @Resource
    private AccountRepayService accountRepayService;

    /**
     * Description: 根据userId查询：借款单号，还款方式，还款金额，还款状态[仅成功失败两种状态]，微信支付手续费，还款时间。
     * 并以借款单号作为key值，其他相同借款单号对应的集合作为value返回的map <br/>
     * @param userId 用户ID
     * @return ResponseEntity
     * @author hongchaoMao <br/>
     * Create date: 2015年7月28日
     */
    @ResponseBody
    @RequestMapping(value = "findPayWayByUserId/{userId}")
    public ResponseEntity<Map<String, List<AccountRepayDto>>> findPayWayByBorrowNid(@PathVariable("userId") String userId)
    {
        ResponseEntity<Map<String, List<AccountRepayDto>>> res = new ResponseEntity<Map<String, List<AccountRepayDto>>>();
        Map<String, List<AccountRepayDto>> map = accountRepayService.findPayWayByUserId(userId);
        if (MapUtils.isEmpty(map))
        {
            res.setMsg("没有找到借款单号对应相关还款信息!");
            res.setError(CWConstants.CW.PARAMS_INVALID_NULL_CODE);
            res.setStatus(CWConstants.CW.FAIL);
            return res;
        }
        res.setStatus(CWConstants.CW.OK);
        res.setData(map);
        return res;
    }

    @ResponseBody
    @RequestMapping(value = "findDtoByBorrowNid/{borrowNid}")
    public ResponseEntity findDtoByBorrowNid(@PathVariable("borrowNid") String borrowNid)
    {
        return accountRepayService.findDtoByBorrowNid(borrowNid);

    }

    /**
     * 根据参数查询还款状态（手机贷）
     *
     * @param request
     * @date 2015/06/02 14:37
     * @return
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "getAccountRepaySelectStatusListWithShoujidai/{realName}_{phone}_{billNo}_{transSerialsid}_{channelId}_{startDate}_{endDate}_{currentPage}_{pageSize}_{repayStatus}", method = RequestMethod.GET)
    public ResponseEntity getAccountRepaySelectStatusListWithShoujidai(
          
            @PathVariable("realName") String realName,
            @PathVariable("startDate") String startDate, @PathVariable("endDate") String endDate,
            @PathVariable("phone") String phone, @PathVariable("billNo") String billNo,
            @PathVariable("channelId") String channelId,@PathVariable("repayStatus") String repayStatus,
            @PathVariable("transSerialsid") String transSerialsid,PageQueryCondition condition)
    {
        logger.debug("enter getAccountRepaySelectStatusList method");
        // 设定局部变量
        Map<String, Object> map = new HashMap<String, Object>();
        if(realName!=null&&realName.length()>0){
            map.put("realName", realName);
        }
        if(phone!=null&&phone.length()>0){
            map.put("phone", phone);
        }
        if(billNo!=null&&billNo.length()>0){
            map.put("billNo", billNo);
        }
        if(transSerialsid!=null&&transSerialsid.length()>0){
            map.put("transSerialsid", transSerialsid);
        }
        map.put("startDate", startDate);
        map.put("endDate", endDate);
        map.put("productType", "shoujidai");
        map.put("channelId", channelId);
        map.put("repayStatus", repayStatus);
        return accountRepayService.getAccountRepaySelectStatusListByParams(map,condition);
    }

    /**
     * 根据参数查询还款状态（学生贷）
     * @date 2015/06/02 14:37
     * @return
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "getAccountRepaySelectStatusListWithUZone/{realName}_{phone}_{billNo}_{transSerialsid}_{channelId}_{startDate}_{endDate}_{currentPage}_{pageSize}_{repayStatus}", method = RequestMethod.GET)
    public ResponseEntity getAccountRepaySelectStatusListWithUZone(
            @PathVariable("realName") String realName,
            @PathVariable("startDate") String startDate, @PathVariable("endDate") String endDate,
            @PathVariable("phone") String phone, @PathVariable("billNo") String billNo,
            @PathVariable("channelId") String channelId, @PathVariable("repayStatus") String repayStatus,
            @PathVariable("transSerialsid") String transSerialsid,PageQueryCondition condition)
    {
        logger.debug("enter getAccountRepaySelectStatusList method");

        // 设定局部变量
        // 设定局部变量
        Map<String, Object> map = new HashMap<String, Object>();
        if(realName!=null&&realName.length()>0){
            map.put("realName", realName+"%");
        }
        if(phone!=null&&phone.length()>0){
            map.put("phone", phone+"%");
        }
        if(billNo!=null&&billNo.length()>0){
            map.put("billNo", billNo+"%");
        }
        if(transSerialsid!=null&&transSerialsid.length()>0){
            map.put("transSerialsid", transSerialsid+"%");
        }
        map.put("startDate", startDate);
        map.put("endDate", endDate);
        map.put("productType", "uzone");
        map.put("channelId", channelId);
        map.put("repayStatus", repayStatus);
        return accountRepayService.getAccountRepaySelectStatusListByParams(map,condition);
    }

    /**
     * 查询指定transserialsid的还款个数
     * @author yangxiaoming
     * @date 2015/06/02 14:37
     * @return
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "getNumTransserialsid/{transserialsid}", method = RequestMethod.GET)
    public ResponseEntity<String> getNumTransserialsid(@PathVariable("transserialsid") String transserialsid)
    {
        ResponseEntity<String> res = new ResponseEntity<String>();
        if (StringUtils.isBlank(transserialsid))
        {
            logger.error("查询指定transserialsid的还款个数失败--transserialsid不存在!");
            res.setMsg("查询指定transserialsid的还款个数失败--transserialsid不存在!");
            res.setStatus(CWConstants.CW.FAIL);
            res.setError(CWConstants.CW.PARAMS_INVALID_NULL_CODE);
            return res;
        }
        String num = accountRepayService.getNumTransserialsid(transserialsid);
        res.setStatus(CWConstants.CW.OK);
        res.setMsg("查询指定transserialsid的还款个数成功");
        res.setData(num);
        return res;
    }

    /**
     *  回调xml
     * @author taojin
     * @date 2015/06/02 14:37
     * @return
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "getHuiDiaoXml", method = RequestMethod.GET)
    public void getHuiDiaoXml()
    {
        accountRepayManager.getHuiDiaoXml();
    }

    /**
     * 查询当日还款明细uzone
     * @author taojin
     * @date 2015/07/15 16:10
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "getDailyRefundDetailU/{startTime}_{endTime}")
    public ResponseEntity getDailyRefundDetailU(@PathVariable("startTime") Date startTime,
            @PathVariable("endTime") Date endTime)
    {
        logger.debug("enter getDailyRefundDetail method,startTime{},endTime{}", startTime, endTime);
        return accountRepayService.getDailyRefundDetailU(startTime, endTime);
    }

    /**
     * 查投诉单
     * @param date
     * @author taojin
     * @date 2015/07/20 17:37
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "getDkComplaint/{accoutNo}_{date}_{amount}_{transserialsid}")
    public ResponseEntity getDkComplaint(@PathVariable("accoutNo") String accoutNo,
            @PathVariable("date") String date, @PathVariable("amount") String amount,
            @PathVariable("transserialsid") String transserialsid)
    {
        logger.debug("enter getDkComplaint method,accoutNo{},date{},amount{},transserialsid{}",
                accoutNo, date, amount, transserialsid);
        return accountRepayService.getDkComplaint(accoutNo, date, amount, transserialsid);
    }

    /**
     *
     * <b>Description:查询分期-每期还款记录只要某笔还款有一部分销该期的张就全部显示出来</b>〈方法详细描述〉<br/>
     * @return
     * @author gaohailiang <br/>
     * Create date: 2016年4月14日
     */
    @ResponseBody
    @RequestMapping(value = "queryRepayByInstallment/{borrowNid}_{period}")
    public ResponseEntity queryRepayByInstallment(@PathVariable("borrowNid") String borrowNid,@PathVariable("period") String period)
    {
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("borrowNid",borrowNid);
        if(period ==  null || period.equals("") || period.equals("0")){
            map.put("period",null);
        }else{
            map.put("period",period);
        }

        return accountRepayManager.queryRepayByInstallment(map);
    }

    /**
     * <b>Description:</b>〈用于修改还款渠道〉<br/>
     * @param channel
     * @param uid
     * @return
     * @author luxingguo <br/>
     * Create date: 2016年5月10日
     */
    @ResponseBody
    @RequestMapping(value = "updateAccountRepay/{billNo}_{uid}_{remark}")
    public ResponseEntity updateAccountRepay(@PathVariable("billNo") String billNo,@PathVariable("uid") Integer uid,@PathVariable("remark") String remark)
    {
        ResponseEntity rse = new ResponseEntity();
        if(StringUtils.isBlank(billNo)){
            rse.setStatus(CWConstants.CW.FAIL);
            rse.setMsg("billNo不能为空");
            return rse;
        }
        if(null==uid){
            rse.setStatus(CWConstants.CW.FAIL);
            rse.setMsg("uid不能为空");
            return rse;
        }
        return accountRepayManager.updateAccountRepayAndBills(billNo,uid,remark,rse);
    }


    /**
     * <b>Description:</b>〈用于修改还款状态〉<br/>
     * @param channel
     * @param uid
     * @return
     * @author luxingguo <br/>
     * Create date: 2016年5月10日
     */
    @ResponseBody
    @RequestMapping(value = "updateAccountRepayAndBills/{billNo}_{status}_{remark}")
    public ResponseEntity updateAccountRepayAndBills(@PathVariable("billNo") String billNo,@PathVariable("status") String status,@PathVariable("remark") String remark)
    {
        ResponseEntity rse = new ResponseEntity();
        if(StringUtils.isBlank(billNo)){
            rse.setStatus(CWConstants.CW.FAIL);
            rse.setMsg("billNo不能为空");
            return rse;
        }
        if(StringUtils.isBlank(status)){
            rse.setStatus(CWConstants.CW.FAIL);
            rse.setMsg("status不能为空");
            return rse;
        }
        /*if(StringUtils.isBlank(remark)){
            rse.setStatus(CWConstants.CW.FAIL);
            rse.setMsg("remark不能为空");
            return rse;
        }*/
        return accountRepayManager.updateAccountRepayAndBills(billNo,status,remark,rse);
    }
   
    /**
     * 置为失败操作接口
     * @param borrowNid
     * @param billNo
     * @param reverseStatus
     * @param userName
     * @author mengjiguang
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "operateFailure", method = RequestMethod.POST)
    public ResponseEntity operateFailure(String borrowNid, String billNo, String reverseStatus, String userName)
    {
        Map<String, String> map = new HashMap<String, String>();
        map.put("borrowNid", borrowNid);
        map.put("billNo", billNo);
        map.put("status", reverseStatus);
        map.put("userName", userName);
        return accountRepayManager.operateFailure(map);
    }
   
    /**
     * 置为失败 审核通过
     * @param borrowNid
     * @param billNo
     * @param reverseStatus
     * @param userName
     * @author mengjiguang
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "operateAuditPass", method = RequestMethod.POST)
    public ResponseEntity operateAuditPass(String borrowNid, String billNo, String reverseStatus, String userName)
    {
        Map<String, String> map = new HashMap<String, String>();
        map.put("borrowNid", borrowNid);
        map.put("billNo", billNo);
        map.put("status", reverseStatus);
        map.put("userName", userName);
        return accountRepayManager.operateAuditPass(map);
    }
   
    /**
     * 置为失败 审核拒绝
     * @param borrowNid
     * @param billNo
     * @param reverseStatus
     * @param userName
     * @author mengjiguang
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "operateAuditRefuse", method = RequestMethod.POST)
    public ResponseEntity operateAuditRefuse(String borrowNid, String billNo, String reverseStatus, String userName)
    {
        Map<String, String> map = new HashMap<String, String>();
        map.put("borrowNid", borrowNid);
        map.put("billNo", billNo);
        map.put("status", reverseStatus);
        map.put("userName", userName);
        return accountRepayManager.operateAuditRefuse(map);
    }
   
    /**
     * 冲正审核界面查看接口
     * @param borrowNid
     * @author mengjiguang
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "findDtoReverseByBorrowNid/{borrowNid}")
    public ResponseEntity findDtoReverseByBorrowNid(@PathVariable("borrowNid") String borrowNid)
    {
        return accountRepayManager.findDtoReverseByBorrowNid(borrowNid);
    }
   
   
    @ResponseBody
    @RequestMapping(value = "updateAccountRepayChannelFee/{billNo}")
    public ResponseEntity updateAccountRepayChannelFee(@PathVariable("billNo") String billNo)
    {
        ResponseEntity rse = new ResponseEntity();
        if(StringUtils.isBlank(billNo)){
            rse.setStatus(CWConstants.CW.FAIL);
            rse.setMsg("billNo不能为空");
            return rse;
        }
       
        return accountRepayManager.updateAccountRepayChannelFee(billNo);
    }
    /**
     *
     * <b>Description:</b>〈方法详细描述〉<br/>
     * @param request
     * @return
     * @throws Exception
     * @author gaohailiang <br/>
     * Create date: 2016年10月13日
     */
    @SuppressWarnings("rawtypes")
    @RequestMapping(value = "importRepayData", method = RequestMethod.POST)
    @ResponseBody
    public ResponseEntity importRepayData(MultipartHttpServletRequest request) throws Exception {
        ResponseEntity entity=null;
        Transaction trans = EE.newTransaction("URL", "AccountRepayController.importRepayData");
        MultipartHttpServletRequest multipartReq = (MultipartHttpServletRequest) request;
        MultipartFile file = multipartReq.getFile("excelFile");
        try {
           
            // 校验excel格式，不同格式对应不同方法
            if (file.getOriginalFilename().endsWith(".xlsx")) {
                XSSFSheet sheet=ExcelUtils.getXSSFSheetSheet(file, 0);
                for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                   
                }
            } else if (file.getOriginalFilename().endsWith(".xls")) {
                HSSFSheet sheet=ExcelUtils.getHSSFSheetSheet(file, 0);
                for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                   
                }
            }
    
        } finally {
            trans.complete();
        }
        return entity;
    }
}