import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public List<List> readExcel(File file){
InputStream is = new FileInputStream(file.getAbsolutePath());
Workbook wb = Workbook.getWorkbook(is);
int sheet_size = wb.getNumberOfSheets();
for (int index = 0; index < sheet_size; index++) {
List<List> outerList=new ArrayList<List>();
// 每个页签创建一个Sheet对象
Sheet sheet = wb.getSheet(index);
// sheet.getRows()返回该页的总行数
for (int i = 0; i < sheet.getRows(); i++) {
List innerList=new ArrayList();
// sheet.getColumns()返回该页的总列数
for (int j = 0; j < sheet.getColumns(); j++) {
String cellinfo = sheet.getCell(j, i).getContents();
if(cellinfo.isEmpty()){
continue;
}
innerList.add(cellinfo);
}
outerList.add(i, innerList);
}
return outerList;
}
return null;
}
//2020-12-29
新增xlsx格式excel读取方式
增加两个依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.monitorjbl/xlsx-streamer -->
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>
public static List<List<String>> readXlxsExcel(String filePath) {
FileInputStream in = null;
try {
in = new FileInputStream(filePath);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
//根据路径建一个输入流
Workbook wk = StreamingReader.builder()
.rowCacheSize(100) //缓存到内存中的行数,默认是10
.bufferSize(4096) //读取资源时,缓存到内存的字节大小,默认是1024
.open(in); //打开资源,必须,可以是InputStream或者是File,注意:只能打开XLSX格式的文件
//读取excel第一个工作簿
Sheet sheet = wk.getSheetAt(0);
//存放所有数据的集合
List<List<String>> lists = new ArrayList<>();
int idx = 0;
String value;
for (Row row : sheet) {//遍历所有的行
idx=row.getRowNum();
if(idx==0)//一般第一行是标题,人性化忽略
continue;
List<String> rowList = new ArrayList<>();//存放每一行数据的集合
for (Cell cell : row) {//遍历每一行数据的列
value = cell.getStringCellValue();
rowList.add(value);//把每个单元格的数据一一放进每一行集合中
}
lists.add(rowList);//把每行数据一行一行放进整个集合中
}
System.out.println("读取excel表中的记录数量 "+lists.size());
return lists;
}