java excel导入和模板下载

//模板下载
 @RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
    public void exportExcel(HttpServletRequest request, HttpServletResponse response, HttpSession session) {
        OutputStream ouputStream = null;
        try {
            // 创建新的Excel 工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 新建工作表，其语句为：
            HSSFSheet sheet = workbook.createSheet("sheet1");
            sheet.setColumnWidth(0, 8000);
            sheet.setColumnWidth(1, 5000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 5000);



            // 声明"Sheet1"工作表的第一行表头信息
            String[] tableHeader = new String[]{
                    "学号", "申请金额","审批金额","贷款年份"};

            // 创建第一行
            HSSFRow firstRow = sheet.createRow((short) 0);
            // 创建第一行里的格子
            for (int i = 1; i <= tableHeader.length; i++) {
                // 创建第i个格子
                HSSFCell cell = firstRow.createCell((short) (i - 1));
                if(cell.getCellType()!=1){
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                }


                //新增的四句话，设置CELL格式为文本格式
                HSSFCellStyle cellStyle2 = workbook.createCellStyle();
                HSSFDataFormat format = workbook.createDataFormat();
                cellStyle2.setDataFormat(format.getFormat("@"));
                cell.setCellStyle(cellStyle2);
                // 定义单元格为字符串类型
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(tableHeader[i - 1]);
            }

            //创建第二行里的例子
            String[] example = new String[]{
                  "1001011","1000","1000","2018"};
            HSSFRow secondRow = sheet.createRow((short) 1);
            for(int i = 1 ; i<=example.length;i++){
                // 创建第i个格子
                HSSFCell cell = secondRow.createCell((short) (i-1));
                if(cell.getCellType()!=1){
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                }
                //新增的四句话，设置CELL格式为文本格式
                HSSFCellStyle cellStyle2 = workbook.createCellStyle();
                HSSFDataFormat format = workbook.createDataFormat();
                cellStyle2.setDataFormat(format.getFormat("@"));
                cell.setCellStyle(cellStyle2);
                // 定义单元格为字符串类型
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(example[i-1]);
            }
            for (int i = 1;i<=4;i++){
                if (i<=4) {
                    HSSFCell cell3 = firstRow.createCell((short) (i - 1));
                    HSSFCellStyle style = workbook.createCellStyle();
                    style.setFillForegroundColor(HSSFColor.RED.index);
                    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    cell3.setCellStyle(style);
                    cell3.setCellValue(tableHeader[i - 1]);
                }
            }
            String fileName = "贷款导入模板.xls";// 设置下载时客户端Excel的名称
            response.setContentType("application/vnd.ms-excel");
            // 解决中文乱码
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes("gbk"), "iso-8859-1"));
            ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 操作结束，关闭文件
            IOUtils.closeQuietly(ouputStream);
        }

    }

   //上传

@RequestMapping(value = "/upload", method = RequestMethod.POST)
    public void upload(@RequestParam MultipartFile file,String pycc,
                       HttpServletRequest request,
                       HttpServletResponse response) {

        try {
            int flag = 0;
            String json;
            // 信息正常学生集合
            List<Loan> loanStudentList = new ArrayList<>();
            // 信息有误学生集合
            List<Loan> errorstudentList = new ArrayList<>();
            if (file.isEmpty()) {
                System.out.println("文件未上传");
            } else {
                Map<String, String> xhMap = new HashMap<>();
                Criteria c1 = new Criteria();
                List<Loan> studentLists= loanService.queryListForPage(c1);
                if (studentLists!=null) {
                    for (Loan stu : studentLists) {
                        if (stu!=null) {
                            xhMap.put(stu.getXh(), stu.getXh());
                        }

                    }
                }
                File xlsFile = FileDownLoadUtils.upload(file, request, null);


                Workbook workbook = ExcelUtils.getWorkbook(xlsFile);

                Sheet sheet = workbook.getSheetAt(0);

                Loan loan = null;
                Row row = null;
                Row titleRow = sheet.getRow(0);
                for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                    loan = new Loan();
                    row = sheet.getRow(i);

                    if (row != null){

                        setImport(titleRow, row, loan,sheet);
                        loan.setErrorMsg(validator(loan,xhMap));
                         // 学生信息存在错误
                        if (StringUtils.isNotBlank(loan.getErrorMsg())) {
                            errorstudentList.add(loan);
                        } else {
                            loanStudentList.add(loan);
                        }
                    }
                }
                xlsFile.delete();

            }
            response.setContentType("text/html;charset=utf-8");
            try {
                ExtReturn result = null;
                if(errorstudentList.size()>0){
                    result = new ExtReturn(false, "导入失败,数据错误!", errorstudentList);
                }else{

                    // 初次插入将数据插入相关表
                    int res = loanService.insertList(loanStudentList);
                    // 将错误学生信息返回到前台显示
                    if(res == 0){
                        result = new ExtReturn(false, "导入失败，数据已存在！", errorstudentList);
                    }else{
                        result = new ExtReturn(true, "导入成功！",errorstudentList);
                    }
                }

                json = JackJson.fromObjectToJson(result);
                System.out.println(json);
                response.getWriter().write(json);
                response.getWriter().flush();
                response.getWriter().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//上传读取excel时调用的方法

 private void setImport(Row titleRow, Row row,Loan loan,Sheet sheet){
        int num = 0;
        StringBuilder msg = new StringBuilder();
        int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();
        for(int i=0; i<coloumNum; i++){
            String title = ExcelUtils.getCellValue(titleRow.getCell(i));
            if(StringUtils.isBlank(title)){
                break;
            }else{
                num = i;
            }
        }
        String title = "";
        String value = "";
        for(int i=0; i<=num; i++){
            title = ExcelUtils.getCellValue(titleRow.getCell(i));
            value = ExcelUtils.getCellValue(row.getCell(i));
            switch (title){
                case "学号":
                    if (StringUtils.isNotBlank(value)){
                        loan.setXh(value);
                    }
                    break;
                case "申请金额":
                    if (StringUtils.isNotBlank(value)){
                        loan.setLoanMoney(Double.valueOf(value));
                    }
                    break;

                case "审批金额":
                    if (StringUtils.isNotBlank(value)){
                        loan.setApproveMoney(Double.valueOf(value));
                    }
                    break;

                case "贷款年份":
                    if (StringUtils.isNotBlank(value)){
                        loan.setPayYear(value);
                    }
                    break;
            }
        }


    }

//验证方法

public String validator( Loan loan,Map<String,String> xhMap){
        StringBuilder msg = new StringBuilder();
        boolean flag = false;
        if (StringUtils.isBlank(loan.getXh())){
            msg.append("学号不能为空");
        }
       if (StringUtils.isBlank(loan.getPayYear())){
           msg.append("贷款年份不能为空");
       }
        if(StringUtils.isNotBlank(xhMap.get(loan.getXh()))){
            msg.append("该学生已申请过贷款");
        }
        if (loan.getApproveMoney()==null){
            msg.append("审批金额不能为空");
        }
        if (loan.getLoanMoney()==null){
            msg.append("申请金额不能为空");
        }
        Criteria criteria = new Criteria();
        StudentInfo studentInfo = studentInfoService.getStudentInfoByXh(loan.getXh());
        loan.setStudentId(studentInfo.getId());
        if (StringUtils.isNotBlank(loan.getStudentId())){
            criteria.put("studentId",loan.getStudentId());
        }
        if (StringUtils.isNotBlank(loan.getPayYear())){
            criteria.put("payYear",loan.getPayYear());
        }
        List<PayPayment> list1= payPaymentService.queryListForPage(criteria);
        if (list1.size()==0){
            msg.append("请先设置缴费金额;");
        }else if(loan.getApproveMoney()>(list1.get(0).getNeedPay()-list1.get(0).getHadPay())){
            msg.append("审批金额超过待缴费金额");
        }
        return msg.toString();
    }