实践环境
Win10
Java JDK1.8
代码实现
pom.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.shouke</groupId>
<artifactId>example</artifactId>
<version>1.0</version>
<properties>
<java.version>1.8</java.version>
<maven.compiler.source>${java.version}</maven.compiler.source>
<maven.compiler.target>${java.version}</maven.compiler.target>
<poi.ooxml.version>4.1.2</poi.ooxml.version>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.ooxml.version}</version>
</dependency>
<dependencies>
</project>
读取Excel
代码实现
exmple.xml
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.util.Iterator;
public class JavaStudy {
public static void readExcel(String filePath) throws Exception {
//获取文件流
FileInputStream inputStream = new FileInputStream(filePath);
//1.创建工作簿
Workbook workbook = new XSSFWorkbook(inputStream);
//2.得到Sheet表
// Sheet sheet = workbook.getSheet("Sheet1"); // 通过Sheet名称获取
Sheet sheet = workbook.getSheetAt(0); // 通过索引获取 //获取第1个Sheet表
//3.获取行
Row row = sheet.getRow(0); // 获取第1行 // 注意:行索引从0开始
System.out.println(sheet.getFirstRowNum()); // 获取首行(内容行)索引 // 输出:0
System.out.println(sheet.getLastRowNum()); // 获取最后行(内容行)索引 // 输出:5
//4.获取单元格
Cell cell = row.getCell(0); // 获取行的第0个元
//5.获取单元格的值
System.out.println(getValue(cell)); // 输出:姓名
System.out.println(row.getFirstCellNum()); // 获取当前行第一个内容单元格索引 // 输出:0
System.out.println(row.getLastCellNum()); // 获取当前行最后内容单元格往后下一个单元格的索引 // 输出:7 // 输出值为:最后内容单元格索引+1
// 遍历当前行内容化单元格
// 方法1:
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
cell = cellIterator.next();
System.out.println(cell);
}
// 方法2:
row.forEach(currCell -> {
System.out.print(currCell+", ");
System.out.println(currCell.getCellType());
});
// 遍历获取所有内容行单元格的值
for (int rowIndex=0; rowIndex<=sheet.getLastRowNum(); rowIndex++) {
row = sheet.getRow(rowIndex);
for(int colIndex=0; colIndex<row.getLastCellNum(); colIndex++) {
System.out.println(getValue(row.getCell(colIndex)));
}
}
inputStream.close();
}
public static Object getValue(Cell cell) {
//匹配类型数据
Object cellValue = null;
if (cell != null) { // 单元格未经过编辑的情况下,一定为null //cell为null的情况下,对空单元格调用API会导致上述for循环提前结束
CellType cellType = cell.getCellType(); // 获取单元格数值类型
switch (cellType) {
case STRING: //字符串
System.out.print("String类型:");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: //布尔类型
System.out.print("Boolean类型:");
cellValue = cell.getBooleanCellValue();
break;
case BLANK: //空
System.out.print("BLA