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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;

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

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import org.apache.commons.io.FileUtils;

import java.io.FileInputStream;
import java.io.InputStream;

public static Workbook getWorkbook2(String filePath) throws IOException {
	# 方案1：
	FileInputStream inputStream = new FileInputStream(filePath);
	# 方案2：
    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设置单元格格式