java读取excel中的数据（包括.xlsx和.xls）

叁歲伎倆 2024-03-22 22:52 92阅读 0赞

## maven 依赖 ##

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
    
    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.11.0</version>
    </dependency>
    
    
    
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-api</artifactId>
        <version>2.15.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-core</artifactId>
        <version>2.15.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-slf4j-impl</artifactId>
        <version>2.15.0</version>
    </dependency>

## 读取Workbook ##

### 方式1：自定义区分.xlsx和.xls ###

public static Workbook getWorkbook1(String path) throws Exception {
        
        File file = new File(path);
        InputStream inputStream = new FileInputStream(file);
        String fileName = file.getName();
        Workbook workbook;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
        
            workbook = new HSSFWorkbook(inputStream);
        } else if (".xlsx".equals(fileType)) {
        
            workbook = new XSSFWorkbook(inputStream);
        } else {
        
            throw new Exception("请上传Excel");
        }
        inputStream.close();
        return workbook;
    }

### 方式2：WorkbookFactory.create + InputStream ###

public static Workbook getWorkbook2(String path) throws IOException, InvalidFormatException {
        
        InputStream inputStream = new FileInputStream(path);
        return WorkbookFactory.create(inputStream);
    }

### 方式3：WorkbookFactory.create + FileInputStream ###

public static Workbook getWorkbook3(String path) throws IOException, InvalidFormatException {
        
        FileInputStream inputStream = FileUtils.openInputStream(new File(path));
        return WorkbookFactory.create(inputStream);
    }

## 读取Cell的值 ##

public static void getCell(Workbook workbook) throws Exception {
        
        Sheet sheet;
        Row row;
        Cell cell;
        // sheet页3页时则NumberOfSheets=3，下标索引取值0-2
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        
            sheet = workbook.getSheetAt(i);
            // 3行数据时,FirstRowNum=0,LastRowNum=2
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
        
                row = sheet.getRow(j);
                // 2列时,FirstCellNum=0,LastCellNum=2
                for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) {
        
                    cell = row.getCell(k);
                    System.out.print(cell + "  ");
                }
                System.out.println();
            }
        }
        workbook.close();
    }

## UT ##

@Test
    public void getWorkbook2_1() throws Exception {
        
        String xlsx = "D:\\projects\\tutorails_kanomoku\\business-prac\\src\\main\\resources\\input.xlsx";
        Workbook workbook2 = ExcelUtils.getWorkbook2(xlsx);
        ExcelUtils.getCell(workbook2);
    }
    @Test
    public void getWorkbook2_2() throws Exception {
        
        String xls = "D:\\projects\\tutorails_kanomoku\\business-prac\\src\\main\\resources\\input2.xls";
        Workbook workbook2 = ExcelUtils.getWorkbook2(xls);
        ExcelUtils.getCell(workbook2);
    }
    @Test
    public void getWorkbook3_1() throws Exception {
        
        String xlsx = "D:\\projects\\tutorails_kanomoku\\business-prac\\src\\main\\resources\\input.xlsx";
        Workbook workbook2 = ExcelUtils.getWorkbook3(xlsx);
        ExcelUtils.getCell(workbook2);
    }
    @Test
    public void getWorkbook3_2() throws Exception {
        
        String xls = "D:\\projects\\tutorails_kanomoku\\business-prac\\src\\main\\resources\\input2.xls";
        Workbook workbook2 = ExcelUtils.getWorkbook3(xls);
        ExcelUtils.getCell(workbook2);
    }
    @Test
    public void getWorkbook4_1() throws Exception {
        
        String xlsx = "D:\\projects\\tutorails_kanomoku\\business-prac\\src\\main\\resources\\input.xlsx";
        Workbook workbook2 = ExcelUtils.getWorkbook1(xlsx);
        ExcelUtils.getCell(workbook2);
    }
    @Test
    public void getWorkbook4_2() throws Exception {
        
        String xls = "D:\\projects\\tutorails_kanomoku\\business-prac\\src\\main\\resources\\input2.xls";
        Workbook workbook2 = ExcelUtils.getWorkbook1(xls);
        ExcelUtils.getCell(workbook2);
    }