今天项目需求需要将一个excel表中的数据读取到程序中,就查找资料学习了一下,顺便记录一下,以便后续使用。
准备工作,需要导入坐标依赖:
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
直接上代码:
package com.sxdx.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.FileInputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @author Attention
* @version1.0 2019/12/3
*/
public class ParseExcel {
public static List<List<Object>> parseExcel() throws Exception {
//创建excel工作对象
Workbook workbook = null;
//要读取的文件名
String fileName = "D:\\IdeaProjects\\awards_system\\src\\main\\resources\\第三学期.xls";
FileInputStream fis = new FileInputStream(fileName);
//判断当前excel是哪一个版本的,如果不是excel,返回null
String substring = fileName.substring(fileName.indexOf(".") + 1);
if ("xls".equals(substring)) {
//2003版
workbook = new HSSFWorkbook(fis);
} else if ("xlsx".equals(substring)) {
//2007版
workbook = new XSSFWorkbook(fis);
} else {
//未知内容,返回null
return null;
}
Sheet sheetAt = workbook.getSheetAt(0);
if (sheetAt == null) {
return null;
}
//创建一个集合用来返回所有的读取到excel中所有的结果
List<List<Object>> lists = new ArrayList<>();
//获取到excel最后一行的下标
int lastRowNum = sheetAt.getLastRowNum();
Row row = sheetAt.getRow(0);
//获取第一列的下标
System.out.println(row.getFirstCellNum());
//获取第二列的下标
System.out.println(row.getLastCellNum());
//循环遍历读取excel中的内容
for (int i = 0; i <= lastRowNum; i++) {
//获取到每一行
row = sheetAt.getRow(i);
List<Object> list = new ArrayList<>();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum()+1; j++) {
//判断每一个单元格的内容是否为空,如果为空,用null值替换
list.add(row.getCell(j)!=null?row.getCell(j):null);
}
lists.add(list);
}
lists.forEach(System.out::println);
return lists;
}
}