注:对于一些数据量小的excel使用什么插件都可以,但是大的数据量都建议使用poi,因为内存消耗会非常高。一般推荐使用EasyExcel 。
导入pom
<properties>
<java.version>1.8</java.version>
<poi.version>4.1.2</poi.version>
</properties>
<dependencies>
<!--excel操作框架-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>${poi.version}</version>
</dependency>
<!-- 读取大量excel数据时使用 -->
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>
</dependencies>
方法一
package com.fmz.demofmztest.testUtil;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Objects;
/**
* @Description: 常规POI读取
* 只能读取 xlsx 格式文件
* @Author yizhixiansheng
* @Date 2022/6/20
*/
public class ReadExcel {
public static void main(String[] args) throws Exception {
long t1 = System.currentTimeMillis();
excel("C:\\Users\\88999\\Desktop\\临时文件夹\\活动数据提取表.xlsx",1,0);
long t2 = System.currentTimeMillis();
System.out.println((t2-t1) + "毫秒");
}
/**
* 读取excel,含列头名称
* @param filePath xlsx 格式文件 路径
* @param sheetIndex sheet标签页下标 从0开始
* @param titleRowIndex 默认第一行为列头名称
* @return void
* @author yizhixiansheng
* @create 2022/6/20
**/
public static void excel(String filePath,int sheetIndex,int titleRowIndex) throws Exception {
File file = new File(filePath);
if (!file.exists()){
throw new Exception("文件不存在!");
}
InputStream in = new FileInputStream(file);
// 读取整个Excel
XSSFWorkbook sheets = new XSSFWorkbook(in);
if(Objects.isNull(sheets)){
throw new Exception("读取Excel异常!");
}
// 获取指定表单Sheet
XSSFSheet sheetAt = sheets.getSheetAt(sheetIndex);
// 获取指定默认标题行
XSSFRow titleRow = sheetAt.getRow(titleRowIndex);
// 循环获取每一行数据
for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
XSSFRow row = sheetAt.getRow(i);
// 读取每一格内容
StringBuilder sb = new StringBuilder();
for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
XSSFCell titleCell = titleRow.getCell(index);
XSSFCell cell = row.getCell(index);
cell.setCellType(CellType.STRING);
if (null == cell.getStringCellValue() || "".equals(cell.getStringCellValue())) {
continue;
}
sb.append(cell);
}
System.out.println(i + "\t" + sb);
}
}
/**
* 读取excel
* @param filePath xlsx 格式文件 路径
* @param sheetIndex sheet标签页下标 从0开始
* @return void
* @author yizhixiansheng
* @create 2022/6/20
**/
public static void excel(String filePath,int sheetIndex) throws Exception {
File file = new File(filePath);
if (!file.exists()){
throw new Exception("文件不存在!");
}
InputStream in = new FileInputStream(file);
// 读取整个Excel
XSSFWorkbook sheets = new XSSFWorkbook(in);
if(Objects.isNull(sheets)){
throw new Exception("读取Excel异常!");
}
// 获取指定表单Sheet
XSSFSheet sheetAt = sheets.getSheetAt(sheetIndex);
// 循环获取每一行数据
for (int i = 0; i < sheetAt.getPhysicalNumberOfRows(); i++) {
XSSFRow row = sheetAt.getRow(i);
// 读取每一格内容
StringBuilder sb = new StringBuilder();
for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
XSSFCell cell = row.getCell(index);
cell.setCellType(CellType.STRING);
if (null == cell.getStringCellValue() || "".equals(cell.getStringCellValue())) {
continue;
}
sb.append(cell);
}
System.out.println(i + "\t" + sb);
}
}
}
方法二
package com.fmz.demofmztest.testUtil;
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.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description: 大文件excel读取
* 只能读取 xlsx 格式文件
* @Author yizhixiansheng
* @Date 2022/6/20
*/
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) //缓存到内存中的行数,默认是10
.bufferSize(4096) //读取资源时,缓存到内存的字节大小,默认是1024
.open(in); //打开资源,必须,可以是InputStream或者是File,注意:只能打开XLSX格式的文件
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\\88999\\Desktop\\临时文件夹\\活动数据提取表.xlsx");
long t1 = System.currentTimeMillis();
test(file);
long t2 = System.currentTimeMillis();
System.out.println((t2-t1)/1000 + "秒");
}
}