引入依赖
<!-- excel工具 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
<!-- 读取大量excel数据时使用 -->
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>
流方式读取数据
import com.monitorjbl.xlsx.StreamingReader;
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 java.io.File;
import java.io.FileInputStream;
import java.util.*;
public class ReadBigExcel {
public static Map<String, List<String>> test(File file) throws Exception{
FileInputStream in = new FileInputStream(file);
Map<String,List<String>> mapData = new HashMap<String, List<String>>();
Workbook wk = StreamingReader.builder()
.rowCacheSize(100)
.bufferSize(4096)
.open(in);
int sheetNums = wk.getNumberOfSheets();
for(int i = 0 ; i < sheetNums;i ++){
List<String> sheetData = new ArrayList<String>();
Sheet sheet = wk.getSheetAt(i);
String sheetName = wk.getSheetName(i);
int k = 0;
for (Row row : sheet) {
StringBuilder sb = new StringBuilder();
for (Cell cell : row) {
sb.append(cell.getStringCellValue());
}
System.out.println(k++ + "\t" + sb.toString());
}
}
return mapData;
}
public static void main(String[] args) throws Exception {
File file = new File("C:\\Users\\Lenovo\\Desktop\\数据导入模板及填写格式 (2).xlsx");
long t1 = new Date().getTime();
test(file);
long t2 = new Date().getTime();
System.out.println((t2-t1)/1000 + "秒");
}
}