java实现excel导入

EXCEL导入就是文件导入，操作代码是一样的，我们先上导入模块代码

@SneakyThrows
    @ApiOperation(value = "文件导入")
    @ResponseBody
    @ApiImplicitParam(paramType = "form", name = "file", value = "文件对象", required = true, dataType = "__file")
    @PostMapping(value = "/importCAListFile")
    public RtMsg importCAListFile(HttpServletRequest request, HttpServletResponse response, @RequestParam(value = "file", required = true) MultipartFile filein) {
        //将文件写入到本地
        String filePath = fileUtils.writeFile(request, response, filein);
        RtMsg rtMsg = dmCaContinuationPageListService.excelImport(filePath);
        return rtMsg;
    }

   @SneakyThrows
    public String writeFile(HttpServletRequest request, HttpServletResponse response, MultipartFile filein) {
        //将文件从swagger写入到本地，再从本地读取数据到数据库中
        InputStream input = null;
        FileOutputStream fos = null;
        String fileName = filein.getOriginalFilename().replace(".xlsx","-");
        String path = "";
        try {
            input = filein.getInputStream();
            File file = new File("E:/Xiolift-Export-Excel/");
            if (!file.exists()) {
                file.mkdirs();
            }
            String time = DateUtils.format(new Date(), DateUtils.DATE_ALL);
            //将路径返回出去，方便接口调用
            path = "E:/Xiolift-Export-Excel/" + fileName + time + ".xlsx";
            fos = new FileOutputStream(path);
            int size = 0;
            byte[] buffer = new byte[1024];
            while ((size = input.read(buffer, 0, 1024)) != -1) {
                fos.write(buffer, 0, size);
            }
            //响应信息 json字符串格式
            Map<String, Object> responseMap = new HashMap<String, Object>();
            responseMap.put("flag", true);
            //生成响应的json字符串
            String jsonResponse = JSONObject.toJSONString(responseMap);
//            sendResponse(jsonResponse, response);
        } catch (IOException e) {
            //响应信息 json字符串格式
            Map<String, Object> responseMap = new HashMap<String, Object>();
            responseMap.put("flag", false);
            responseMap.put("errorMsg", e.getMessage());
            String jsonResponse = JSONObject.toJSONString(responseMap);
//            sendResponse(jsonResponse, response);

        } finally {
            if (input != null) {
                input.close();
            }
            if (fos != null) {
                fos.close();
            }
        }
        return path;
    }

导入后需要将本地存放的文件路径返回出来，给excel导入使用

 @SneakyThrows
    @Override
    public RtMsg excelImport(String filepath) {
        //原模板
        FileInputStream inputStream = new FileInputStream(new File(filepath));
        try {
            return getListByExcel(inputStream, filepath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return RtMsg.ok();
    }



@Transactional
    public RtMsg getListByExcel(InputStream in, String fileName) throws Exception {
        //创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        //页数
        Sheet sheet = null;
        //行数
        Row row = null;
        //列数
        Cell cell = null;

        int insertNum = 0;
        String nullDrawingNoLine = "";
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            //根据导入模板单独取出cano存入数据库
            String cano = changeStringType(sheet.getRow(0).getCell(11));
            String insertCano = cano.replace("CA号：", "");
            //编制
            String prepareduserin = changeStringType(sheet.getRow(1).getCell(11));
            String prepareduser = prepareduserin.replace("编制：", "");

            List<DmCaContinuationPageListDO> caList = dmCaContinuationPageListMapper.getCAListByCano(insertCano);
            //CA号在续页清单存在，主要是负责将未确认的状态数据从dm_ca_continuation_page_list表中删除
            if (!ObjectUtils.isEmpty(caList)){
                List<DmCaContinuationPageListDO> pageListDOList = caList.stream().filter(x -> x.getSupplierhandlerstatus() == 0).collect(Collectors.toList());
                if (!ObjectUtils.isEmpty(pageListDOList)) {
                    pageListDOList.stream().forEach(x->{
                        //将未确认数据从dm_ca_continuation_page_list表中删除
                        dmCaContinuationPageListMapper.deleteById(x.getId());
                    });
                }
            } else {
                DmCaDO dmCaDOByCaNo = dmCaMapper.getDmCaDOByCaNo(insertCano);
                if (null == dmCaDOByCaNo) {
                    //保存CA信息.一张表只需要保存一次
                    Date currentDate = new Date();
                    DmCaDO dmCaDO = new DmCaDO();
                    dmCaDO.setId(String.valueOf(System.currentTimeMillis()));
                    //得帆Apaas平台初始化字段
                    dmCaDO.setDocumentId(String.valueOf(System.currentTimeMillis()));
                    dmCaDO.setStatus("COMPLETED");
                    dmCaDO.setTenantId("195487656275083265");
                    dmCaDO.setFormId("60faa848c169356f03b13fe8");
                    dmCaDO.setOwner("100195912878337818624");
                    dmCaDO.setCreatedBy("100195912878337818624");
                    dmCaDO.setCreationDate(currentDate);
                    dmCaDO.setLastUpdatedBy("100195912878337818624");
                    dmCaDO.setLastUpdateDate(currentDate);
                    dmCaDO.setPreparedUser(prepareduser);
                    dmCaDO.setCaNo(insertCano);
                    dmCaMapper.insert(dmCaDO);
                }
            }

            //插入新数据，插入数据的时候要校验重复
            //成功插入的excel数据，maintaninstatus更新为：1,iscacheck 更新为：1
            //遍历当前sheet中的所有行(调用接口是从第六行开始测试数据，故我从第六行开始测试，回来单独抽取)
            for (int j = 5; j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }

                //图样/文件编号
                String drawingFileNo = changeStringType(getValue(row.getCell(1)));
                //图号为空的数据不进行导入，对于drawingFileNo是空的数据要进行处理,拼接空的数据返回给前端
                if (StringUtils.equals("", drawingFileNo)) {
                    nullDrawingNoLine = StringUtils.join(nullDrawingNoLine,",第",String.valueOf(j+1),"行");
                    continue;
                }

                //图样/文件名称
                String drawingFileName = changeStringType(getValue(row.getCell(2)));
                //配置(梯型)
                String configTx = changeStringType(getValue(row.getCell(3)));
                //是否ODS层级
                String isOds = changeStringType(getValue(row.getCell(4)));
                //所属ODS
                String belongOds = changeStringType(getValue(row.getCell(5)));
                //更改页码
                String changePageNo = changeStringType(getValue(row.getCell(6)));
                //总页码
                Integer totalPageNo = changeIntType(getValue(row.getCell(7)));
                //类别
                String categoryType = changeStringType(getValue(row.getCell(8)));
                //更改类型
                String changeType = changeStringType(getValue(row.getCell(9)));
                //件号变更
                String partNoChange = changeStringType(getValue(row.getCell(10)));
                //更改内容描述
                String changeContent = changeStringType(getValue(row.getCell(11)));
                //自制
                String selfmade = changeStringType(getValue(row.getCell(12)));
                //外协外购
                String outsourcing = changeStringType(getValue(row.getCell(13)));
                //采购状态
                String purchaseStatus = changeStringType(getValue(row.getCell(14)));
                //设备线体
                String equipmentLine = changeStringType(getValue(row.getCell(15)));
                //库存量
                Integer inventory = changeIntType(getValue(row.getCell(16)));

                //dm_ca表和dm_ca_continuation_page_list表的id的值按照这种方式传
                String id = System.currentTimeMillis() + "";

                //保存信息
                DmCaContinuationPageListDO dmCaContinuationPageListDO = new DmCaContinuationPageListDO();
                dmCaContinuationPageListDO.setId(id);
                dmCaContinuationPageListDO.setDrawingfileno(drawingFileNo);
                dmCaContinuationPageListDO.setDrawingfilename(drawingFileName);
                dmCaContinuationPageListDO.setConfigtx(configTx);
                dmCaContinuationPageListDO.setIsods(isOds);
                dmCaContinuationPageListDO.setBelongods(belongOds);
                dmCaContinuationPageListDO.setChangepageno(changePageNo);
                dmCaContinuationPageListDO.setTotalpageno(totalPageNo);
                dmCaContinuationPageListDO.setCategorytype(categoryType);
                dmCaContinuationPageListDO.setChangetype(changeType);
                dmCaContinuationPageListDO.setPartnochange(partNoChange);
                dmCaContinuationPageListDO.setChangecontent(changeContent);
                dmCaContinuationPageListDO.setSelfmade(selfmade);
                dmCaContinuationPageListDO.setOutsourcing(outsourcing);
                dmCaContinuationPageListDO.setPurchasestatus(purchaseStatus);
                dmCaContinuationPageListDO.setEquipmentline(equipmentLine);
                dmCaContinuationPageListDO.setInventory(inventory);
                dmCaContinuationPageListDO.setCano(insertCano);
                dmCaContinuationPageListDO.setCreatetime(DateUtils.format(new Date(), DATE_FORMAT_FULL));
                dmCaContinuationPageListDO.setSupplierhandlerstatus(0);
                dmCaContinuationPageListDO.setSyncstatus(1);

                //在插入数据库的时候，maintaninstatus更新为：1,iscacheck 更新为：1
                dmCaContinuationPageListDO.setMaintainstatus(1);
                dmCaContinuationPageListDO.setIscacheck(1);

                //校验是否有重复的数据：有重复的数据不进行插入，否则插入新数据
                DmCaContinuationPageListDO queryList = dmCaContinuationPageListMapper.getDmCaContinuationPageListDO(cano, drawingFileNo);
                if (ObjectUtils.isEmpty(queryList)) {
                    Integer insert = dmCaContinuationPageListMapper.insert(dmCaContinuationPageListDO);
                    insertNum += insert;
                }
            }
        }
        return RtMsg.ok().put("nullDrawingNoLine",nullDrawingNoLine).put("insertNum",insertNum);
    }


/**
     * @param inStr,fileName
     * @return
     * @throws Exception
     * @Description：根据文件后缀，自适应上传文件的版本
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            wb = new HSSFWorkbook(inStr);  //2003-
        } else if (excel2007U.equals(fileType)) {
            wb = new XSSFWorkbook(inStr);  //2007+
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }


 private static Object getValue(Cell cell) {
        Object obj = null;
        if (null == cell) {
            return null;
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_ERROR:
                obj = cell.getErrorCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case Cell.CELL_TYPE_STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }

    /**
     * @param
     * @Description：对于结果集进行转换
     */
    public String changeStringType(Object obj) {
        if (null == obj) {
            return "";
        } else {
            return obj.toString();
        }
    }

    /**
     * @param
     * @Description：对于结果集进行转换
     */
    public Integer changeIntType(Object obj) {
        if (null == obj) {
            return 0;
        } else {
            Double aDouble = Double.parseDouble(obj.toString());
            int round = (int) Math.round(aDouble);
            return round;
        }
    }