1、背景描述

2、Apache POI簡介

官網：http://poi.apache.org/index.html

官方文檔：https://poi.apache.org/apidocs/index.html

3、Java讀取Excel

3.1、導入依賴

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version>
</dependency>

3.2、工具類POIUtils.java

為方便使用，直接封裝成工具類

package com.cc;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;import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;public class POIUtils {private static Workbook workbook = null;private static InputStream excel = null;// 獲取列數public static Integer getColsNum(Sheet sheet) {return sheet.getLastRowNum() == -1 ? -1 : sheet.getRow(0).getPhysicalNumberOfCells();}// 獲取行數public static Integer getRowsNum(Sheet sheet) {return sheet.getLastRowNum() == -1 ? -1 : sheet.getPhysicalNumberOfRows();}// 初始化Excelpublic static Sheet excelInit(String path, String name, Boolean ooxml) throws IOException {excel = Files.newInputStream(Paths.get(path));// Excel加載if (ooxml){// Microsoft Office 2007起（xlsx）workbook = new XSSFWorkbook(excel);} else {// Microsoft Office 2007之前（xls）workbook = new HSSFWorkbook(excel);}return workbook.getSheet(name);}// 讀取Excel，返回字符串類型public static String excelReader(Sheet sheet) {// 行數判斷if (sheet.getLastRowNum() == -1){return "";}// 遍歷行和單元格（除了迭代器和如下遍歷，其他遍歷可能解析報錯）StringBuilder builder = new StringBuilder();for (Row row : sheet) {StringBuilder line = new StringBuilder();for (Cell cell : row) {line.append(cell.toString()).append("\001");}builder.append(line.toString().trim()).append("\n");}return builder.toString().trim();}// 讀取Excel，返回列表類型public static List<List<String>> excelParser(Sheet sheet) {// 行數判斷if (sheet.getLastRowNum() == -1){return null;}// 遍歷行和單元格（除了迭代器和如下遍歷，其他遍歷可能解析報錯）ArrayList<List<String>> rows = new ArrayList<>();for (Row row : sheet) {ArrayList<String> cells = new ArrayList<>();for (Cell cell : row) {cells.add(cell.toString().trim());}rows.add(cells);}return rows;}// 釋放資源public static void excelDestroy() throws IOException {workbook.close();excel.close();}}

3.3、測試類POIUtilsTest.java

package com.cc;import org.apache.poi.ss.usermodel.Sheet;
import org.junit.Test;import java.io.IOException;
import java.util.List;public class POIUtilsTest {@Testpublic void test() throws IOException {// 文件String path = "F:\\...\\file.xlsx";// 加載ExcelSheet sheet = POIUtils.excelInit(path, "Sheet1", true);// 獲取行列數System.out.println(POIUtils.getRowsNum(sheet));System.out.println(POIUtils.getColsNum(sheet));// 讀取ExcelSystem.out.println(POIUtils.excelReader(sheet));// List<List<String>> lines = POIUtils.excelParser(sheet);// lines.forEach(System.out::println);// 釋放資源POIUtils.excelDestroy();}}

參考文章：https://blog.csdn.net/qq_47387991/article/details/136207565
https://blog.csdn.net/hadues/article/details/113859228
https://blog.csdn.net/xueguchen/article/details/108741429