【Java】读取本地Excel文件的数据导入mongo数据库

 pom:

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-mongodb</artifactId>
            <version>1.5.10.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-mongodb</artifactId>
            <version>1.10.10.RELEASE</version>
        </dependency>

 config:

spring:    
  data:
    mongodb:
      uri: mongodb://xxxxxx

    //获取指定目录下的所有文件路径
    public static void getAllFileName(String path, List<String> list) {
        File file = new File(path);
        //获取全部File
        //返回目录名加文件名
        //添加过滤器
        //这些路径名表示此抽象路径名所表示目录中的文件。
        File[] files = file.listFiles(pathname -> true);
        if (files != null) {
            for (File file1 : files) {
                //判断是否是目录，是的话继续递归
                if (file1.isDirectory()) {
                    getAllFileName(file1.getAbsolutePath(), list);
                } else {
                    //获取全部包+文件名
                    list.add(file1.getAbsolutePath());
                }
            }
        }
    }

//过滤Excel路径文件
pathList = pathList.stream().filter(i -> i.endsWith(".xls")||i.endsWith(".xlsx"))
                   .sorted().collect(Collectors.toList());

          //读取文件转换为in流
          File file = new File(path);
          InputStream in = null;
          try {
              in = new FileInputStream(file);
          } catch (FileNotFoundException e) {
              e.printStackTrace();
          }

    /**
     * 根据Url读取Excel信息 转化为DTO
     */
    public static List<CompanyExcelDTO> readExcel(final InputStream in, final boolean isXls) throws IOException {
        //判断格式是否为excel文件
        //url.getPath得到文件的路径
        Sheet sheet;
        HSSFWorkbook wb = null;
        XSSFWorkbook xwb = null;
        if (isXls) {
            wb = new HSSFWorkbook(in);
            sheet = Objects.requireNonNull(wb).getSheetAt(0);
        } else {
            xwb = new XSSFWorkbook(in);
            sheet = Objects.requireNonNull(xwb).getSheetAt(0);
        }
        if (sheet == null) {
            log.error("sheet is null");
            return new ArrayList<>();
        }
        //第一页
        List<CompanyExcelDTO> contractExcelList = new ArrayList<>();
        //总共有多少行
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        int rowIndex = 0;
        for (int q = 1; q < physicalNumberOfRows; ++q) {
            //第一行
            Row row = sheet.getRow(q);
            //第一行最后一个
            int end = row.getLastCellNum();
            CompanyExcelDTO excelDTO = new CompanyExcelDTO();
            for (int i = 0; i < end; ++i) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    String stringCellValue = cell.getStringCellValue();
                    String addressUrl = null;
                    Hyperlink hyperlink = cell.getHyperlink();
                    if (hyperlink != null) {
                        addressUrl = hyperlink.getAddress();
                    }
                    //第一行
                    if (rowIndex != 0) {
                        buildRowInfo(excelDTO, i, stringCellValue, addressUrl);
                    }
                }
            }
            rowIndex++;
            contractExcelList.add(excelDTO);
        }
        if (wb != null) {
            wb.close();
        }
        if (xwb != null) {
            xwb.close();
        }
        in.close();
        return contractExcelList;
    }

    //批量插入mongo数据库
    public void batchInsertCompanyInfo(List<CompanyInfo> companyInfos) {
        if (companyInfos.size() > 0) {
            mongoTemplate.insert(companyInfos, collectionName);
        }
    }