试问将Excel导入数据库

步骤为:前端选中excel表格,后台进行判断表格的模板是否符合要求,并进行导入数据库

 public String importCoefficientExcel(MultipartFile file, HttpServletRequest request, HttpSession session, String page) {
        String result=null;
        if (file.isEmpty()) {
            return Result.customErrorByMsg("文件不存在");
        }
        try {
            //获取文件名称
            String fileName = file.getOriginalFilename();
            // 获取文件后缀名
            Integer indexNum = fileName.lastIndexOf(".") + 1;
            String lastName = fileName.substring(indexNum);
            if (!(lastName.equals(EXCEL_TYPE_2003) || lastName.equals(EXCEL_TYPE_2007))) {
                return Result.customErrorByMsg("仅支持导入Excel文件");
            }
            BosUserModel userModel = (BosUserModel) session.getAttribute("user");
            //开始导入  根据页面
           result= readCoefficientExcel(file.getInputStream(), lastName, userModel);     
        } catch (Exception e) {
            e.printStackTrace();
            return Result.customError("导入失败");
        }
        return result;
    }

public String readCoefficientExcel(InputStream inputStream, String fileName, BosUserModel user) {
        Workbook wb = null;
        try {
            if (EXCEL_TYPE_2007.equals(fileName)) {
                wb = new XSSFWorkbook(inputStream);
            } else {
                wb = new HSSFWorkbook(inputStream);
            }
            //获取第一个表格
            Sheet sheet = wb.getSheetAt(0);
            String[] arr = {"设施种类", "雨水径流系数范围", "雨水径流系数"};
            //获取第一行表头
            Row row = sheet.getRow(0);
            Boolean isOK = false;
            //判断模板是否错误
            for (int i = 0; i < arr.length; i++) {
                if (!toStringValue(row.getCell(i)).equals(arr[i])) {
                    isOK = true;
                }
            }
            if (isOK) {
                return Result.customErrorByMsg("导入的模板不符合要求");
            }
            //开始导入  考虑原有在数据库中的数据....已存在的数据是否覆盖 不存在的数据直接导入
            int hang = 0;
            List<CoefficientModel> list = new ArrayList<>();
            for (Row cells : sheet) {
                //第一行表头不导入
                if (hang == 0) {
                    hang++;
                    continue;
                }
                if(Strings.isNullOrEmpty( toStringValue(cells.getCell(0)).trim())){
                    continue;
                }
                CoefficientModel coefficientModel = new CoefficientModel();
                coefficientModel.setTypesFacilities(toStringValue(cells.getCell(0)).trim());
                coefficientModel.setRunoffCoefficientRange(toStringValue(cells.getCell(1)).trim());
                coefficientModel.setRunoffCoefficient(toStringValue(cells.getCell(2)).trim());
                coefficientModel.setCreateTime(new Timestamp(System.currentTimeMillis()));
                coefficientModel.setUpdateTime(new Timestamp(System.currentTimeMillis()));
                coefficientModel.setUpdateUser(user.getName());
                list.add(coefficientModel);
            }
            String sql = "insert into coefficient(types_facilities,runoff_coefficient_range,runoff_coefficient,create_time,update_time,update_user,is_del) values(?,?,?,?,?,?,?)";
            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) {
                    try {
                        ps.setString(1, list.get(i).getTypesFacilities());
                        ps.setString(2, list.get(i).getRunoffCoefficientRange());
                        ps.setString(3, list.get(i).getRunoffCoefficient());
                        ps.setTimestamp(4, list.get(i).getCreateTime());
                        ps.setTimestamp(5, list.get(i).getUpdateTime());
                        ps.setString(6, list.get(i).getUpdateUser());
                        ps.setInt(7, list.get(i).getIsDel());
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                @Override
                public int getBatchSize() {
                    return list.size();
                }
            });
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            logger.error("coefficientExcel导入失败", e);
            return Result.defeatedResult();
        }
        return Result.succeedResult();
    }

第一篇博客,记录美好时刻