导入Excel--前台和后台

业务背景：在前台页面点击导入excel，弹出系统盘框，选择文件，确定，开始导入

开发思路：在前台页面上选择要导入的文件，然后传向后台，在后台执行具体的导入逻辑

首先，此方法前台用的是layui，后台java代码都适用

前台：

var upload;
    layui.use(['form', 'upload'], function () {
        var form = layui.form;
        upload = layui.upload;//只有执行了这一步，部分表单元素才会修饰成功
        layui.use('upload', function () {
            var $ = layui.jquery
                , upload = layui.upload;

            var uploadInst = upload.render({
                elem: "#importExcel"
                , url: "http://localhost:90/dms/salesManageDepartmentInfo/importExcel"
                , accept:'file'
                , before: function (obj) {
                }
                , done: function (res) {
                    if(res.code=="1"){
                        setTimeout(function(){  // 这个方法是说在延迟两秒后执行大括号里的方法
                            layer.msg("导入数据成功");
                        },500)
                        // window.location.history(-1);
                        return;
                    }
                    layer.msg(res.code);

                }
                , error: function () {
                    layer.alert("导入数据失败");
                }
            });

        });
    });

后台：

	private static FormulaEvaluator evaluator;

	@RequestMapping("/importExcel")
    @ResponseBody
    @Transactional(rollbackFor = Exception.class)
    public Object importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd mm:dd:ss");
        Map<String, Object> arrMap = new HashMap<String, Object>();
        String arr = "1";// 导入成功
        Workbook wookbook = null;
        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        // 判断文件格式
        if (suffix.equals(".xls")) {
            try {
                wookbook = new HSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else if (suffix.equals(".xlsx") || suffix.equals(".xlsm")) {
            wookbook = new XSSFWorkbook(file.getInputStream());
        }
        evaluator = wookbook.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wookbook.getSheet("");
        if (sheet == null) {
            sheet = wookbook.getSheetAt(0);
        }
        // 判断表格中是否有数据
        if (sheet.getLastRowNum() > 0) {
            List<SalesShipmentPlanRow> dataList = new ArrayList<>();
            try{
                for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                    Row rowData = sheet.getRow(i);

                    SalesShipmentPlanRow salesShipmentPlanRow = new SalesShipmentPlanRow();
                    if(null != rowData.getCell(0) && !"".equals(rowData.getCell(0))){
                        boolean flag = isCellDateFormatted(rowData.getCell(0));
                        if(flag){
                            salesShipmentPlanRow.setBookTime(dateString(rowData.getCell(0)));
                        }else{
                            salesShipmentPlanRow.setBookTime(rowData.getCell(0).getStringCellValue());
                        }
                    }
                    if(null != rowData.getCell(1) && !"".equals(rowData.getCell(1))){
                        salesShipmentPlanRow.setLogisticsCompany(rowData.getCell(1).getStringCellValue());
                    }
                    if(null != rowData.getCell(2) && !"".equals(rowData.getCell(2))){
                        salesShipmentPlanRow.setCargoModel(rowData.getCell(2).getStringCellValue());
                    }
                    if(null != rowData.getCell(3) && !"".equals(rowData.getCell(3))){
                        salesShipmentPlanRow.setLocation(rowData.getCell(3).getStringCellValue());
                    }
                    if(null != rowData.getCell(4) && !"".equals(rowData.getCell(4))){
                        salesShipmentPlanRow.setBillNo(rowData.getCell(3).getStringCellValue());
                    }
                    if(null != rowData.getCell(5) && !"".equals(rowData.getCell(5))){
                        salesShipmentPlanRow.setBoxNo(rowData.getCell(5).getStringCellValue());
                    }
                    if(null != rowData.getCell(6) && !"".equals(rowData.getCell(6))){
                        salesShipmentPlanRow.setLeadSealNo(rowData.getCell(6).getStringCellValue());
                    }
                    if(null != rowData.getCell(7) && !"".equals(rowData.getCell(7))){
                        salesShipmentPlanRow.setDriverName(rowData.getCell(7).getStringCellValue());
                    }
                    if(null != rowData.getCell(8) && !"".equals(rowData.getCell(8))){
                        rowData.getCell(8).setCellType(CellType.STRING);
                        salesShipmentPlanRow.setTel(rowData.getCell(8).getStringCellValue());
                    }
                    if(null != rowData.getCell(9) && !"".equals(rowData.getCell(9))){
                        salesShipmentPlanRow.setVesselNameVoyage(rowData.getCell(9).getStringCellValue());
                    }
                    if(null != rowData.getCell(10) && !"".equals(rowData.getCell(10))){
                        salesShipmentPlanRow.setContainerTare(rowData.getCell(10).getStringCellValue());
                    }
                    if(null != rowData.getCell(11) && !"".equals(rowData.getCell(11))){
                        boolean flag = isCellDateFormatted(rowData.getCell(11));
                        if(flag){
                            salesShipmentPlanRow.setLeaveFctDate(dateString(rowData.getCell(11)));
                        }else{
                            salesShipmentPlanRow.setLeaveFctDate(rowData.getCell(11).getStringCellValue());
                        }
                    }
                    if(null != rowData.getCell(12) && !"".equals(rowData.getCell(12))){
                        boolean flag = isCellDateFormatted(rowData.getCell(12));
                        if(flag){
                            salesShipmentPlanRow.setLeavePortDate(dateString(rowData.getCell(12)));
                        }else{
                            salesShipmentPlanRow.setLeavePortDate(rowData.getCell(12).getStringCellValue());
                        }
                    }
                    if(null != rowData.getCell(13) && !"".equals(rowData.getCell(13))){
                        salesShipmentPlanRow.setCarNo(rowData.getCell(13).getStringCellValue());
                    }
                    if(null != rowData.getCell(14) && !"".equals(rowData.getCell(14))){
                        salesShipmentPlanRow.setCabinetType(rowData.getCell(14).getStringCellValue());
                    }
                    if(null != rowData.getCell(15) && !"".equals(rowData.getCell(15))){
                        salesShipmentPlanRow.setDischargePort(rowData.getCell(15).getStringCellValue());
                    }
                    if(null != rowData.getCell(16) && !"".equals(rowData.getCell(16))){
                        salesShipmentPlanRow.setPier(rowData.getCell(16).getStringCellValue());
                    }
                    salesShipmentPlanRow.setCreateTime(sdf1.parse(sdf1.format(new Date())));
                    dataList.add(salesShipmentPlanRow);
                }
                iSalesShipmentPlanRowServiceFeign.saveBatch(dataList);
                arr = "1";
            }catch (Exception e){
                e.printStackTrace();
                arr = "2";
            }

        }

        arrMap.put("code", arr);
        return arrMap;
    }