Apache POI的使用：读取excel文件

一、导包

        <!-- 操作2003版本的excel，以.xls结尾的 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- 操作2007版本以上的excel，以.xlsx结尾的 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

二、读取Excel

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * poi操作工具类
 *
 * @author yangzihe
 * @date 2021/9/28
 */
@Slf4j
public class PoiUtils {

    private static final String XLS = ".xls";
    private static final String XLSX = ".xlsx";

    /**
     * 读取excel文件
     * 流程：Workbook(工作簿)——>Sheet(工作表)——>Row(行)——>Cell(单元格)
     *
     * @param filePath excel文件地址
     */
    public static void readExcel(String filePath) {
        if (StringUtils.isBlank(filePath)) {
            throw new IllegalArgumentException("excel文件地址不可为空");
        }
        boolean validEnds = filePath.endsWith(XLS) || filePath.endsWith(XLSX);
        if (!validEnds) {
            throw new IllegalArgumentException("excel文件地址无效，filePath=" + filePath);
        }

        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            inputStream = new FileInputStream(filePath);
            //创建工作簿对象
            if (filePath.endsWith(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (filePath.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                throw new IllegalArgumentException("excel文件地址无效，filePath=" + filePath);
            }

            //获取工作簿下sheet的个数
            int sheetNum = workbook.getNumberOfSheets();

            //遍历工作簿中的所有数据
            for (int i = 0; i < sheetNum; i++) {
                // sheet
                Sheet sheet = workbook.getSheetAt(i);
                // 循环行Row
                for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    // System.out.println("第" + (rowIndex + 1) + "行的数据如下：");
                    Row row = sheet.getRow(rowIndex);
                    // 迭代单元格cell
                    for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                        Cell cell = row.getCell(cellIndex);
                        String cellString = cell.toString();
                        // todo 业务处理 可在这里将每行数据封装成自己的业务对象 作为函数返参
                        System.out.print(cellString + " ");

                    }
                    System.out.println();
                }

                System.out.println();
            }
        } catch (FileNotFoundException e) {
            log.error("文件不存在，filePath={}", filePath);
            throw new RuntimeException("文件不存在", e);
        } catch (IOException e) {
            log.error("文件读取异常，filePath={}", filePath);
            throw new RuntimeException("文件读取异常", e);
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                log.error("数据流关闭异常！", e);
            }
        }
    }

}

三、代码测试

public class PoiTest {
    public static void main(String[] args) {
        PoiUtils.readExcel("/Users/yangzihe1/Downloads/日志.xlsx");
    }
}