java基于POI将Excel表格数据导入到数据库

java基于POI将Excel表格数据导入到数据库

导入依赖

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.16</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.14</version>
    </dependency>
    <!-- 处理excel和上面功能是一样的-->
    <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.10</version>
    </dependency>

下面是业务层的代码，我这边是直接取指定列的表格内容

public EceclEntity getEcecl() throws IOException, InvalidFormatException {
        File xlsFile = new File("路径\\原始表单.xlsx");
        // 获得工作簿
        Workbook workbook = WorkbookFactory.create(xlsFile);
        //创建list 
         //EceclEntity是数据库的实体类
        List<EceclEntity> list = new ArrayList<>();
        // 获得工作表个数
        // int sheetCount = workbook.getNumberOfSheets();
        //获取表
        Sheet sheet = workbook.getSheetAt(0);
        for (int col = 1; col < sheet.getLastRowNum(); col++) {
            Row row = sheet.getRow(col);//取得第i行数据
            String[] str = new String[row.getLastCellNum()];
            EceclEntity ececlEntity = new EceclEntity();
            //取得第j列数据
            Cell cell = row.getCell(18, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            //获取表格的第18列数据
            str[18] = cell.getStringCellValue().trim();
            //存入数据到集合
            ececlEntity.setName(str[18]);
            ececlEntity.setStatus(row.getCell(16, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim());
            list.add(ececlEntity);
        }
        //遍历将list里的数据存到数据库
        for (EceclEntity eclEntitys:list) {
            System.out.println("eclEntitys "+eclEntitys);
            this.execlRepo.save(eclEntitys);
        }
        return null;
    }

大概就是这样的