java 读取excel（包括.xlsx和.xls）

maven依赖

<!--读取excel文件-->
<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>

读取Workbook

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

public static Workbook getWorkbook1(String filePath) throws Exception {
    File file = new File(filePath);
    String fileName = file.getName();
    Workbook workbook = null;
    try (FileInputStream inputStream = new FileInputStream(file)) {
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            System.out.println("非Excel文件");;
        }
    }
    return workbook;
}

方式２：WorkbookFactory.create + InputStream

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

方式３：WorkbookFactory.create + FileInputStream

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

读取Cell的值

方式１：for i 遍历方式

public static void getCellForI(Workbook workbook) throws Exception {
    Sheet sheet;
    Row row;
    Cell cell;
    # 2sheet页时,NumberOfSheets=2,下标索引 0~1
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        sheet = workbook.getSheetAt(i);
        # 6行数据时,FirstRowNum=0,LastRowNum=5
        for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            # 4列时,FirstCellNum=0,LastCellNum=4
            for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) {
                cell = row.getCell(k);
                // String stringCellValue = convertCellValueToString(cell);
                System.out.print(workbook.getNumberOfSheets() + "-" + sheet.getFirstRowNum() + "-" + sheet.getLastRowNum() + "-" + row.getFirstCellNum() + "-" + row.getLastCellNum() + ":" + cell + "  ");
            }
            System.out.println();
        }
        System.out.println();
    }
    workbook.close();
}

方式２：for each 遍历方式

public static void getCell2ForEach(Workbook workbook) throws Exception {
    Sheet sheet;
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        sheet = workbook.getSheetAt(i);
        for (Row row : sheet) {
            for (Cell cell : row) {
                // String stringCellValue = convertCellValueToString(cell);
                System.out.print(cell + "  ");
            }
            System.out.println();
        }
        System.out.println();
    }
    workbook.close();
}

拓展

Java读取excel中的数据
Java导出数据到Excel
Java给Excel设置单元格格式