前言:
(当前是横竖单元格没有出现合并的情况的简单实现)
一般需要用模板,每一个表都是固定做具体的功能,每一个表的每一列都是有具体格式要求。否则数据处理的时候格式不规范,数据无法使用。
1. 导入依赖
<!--POI 读取文件-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.8</version>
</dependency>
2. 工具类
package com.xyz.utils;
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.util.ArrayList;
import java.util.List;
public class ExcelReadUtils {
/**
* 读取excel文件:列值可为空
*/
public static List<List<List<String>>> getData(XSSFWorkbook wb, Integer ignoreRows, Integer sheetNum) {
if (sheetNum == null) {
sheetNum = wb.getNumberOfSheets();
}
List<List<List<String>>> lists = new ArrayList<>();
//for循环:取前N个表,下标从0开始
for (int i = 0; i < sheetNum; i++) {
XSSFSheet sheetI = wb.getSheetAt(i);
List<List<String>> list = new ArrayList<>();
int cellSize = sheetI.getRow(0).getLastCellNum();//列数
//第N+1行开始,可以通过传参,从第N+1行开始取
for (int rowIndex = ignoreRows; rowIndex <= sheetI.getLastRowNum(); rowIndex++) {
XSSFRow row = sheetI.getRow(rowIndex);
if (row == null) {
continue;
}
List<String> rowList = new ArrayList<>();
//在每行中的每一列,从下标0开始,一直取到所有
for (int a = 0; a < cellSize; a++) {
String cellValue = getCellValue(row.getCell(a));
rowList.add(cellValue);
}
list.add(rowList);
}
lists.add(list);
}
return lists;
}
private static String getCellValue(XSSFCell cell) {
String value = "";
if (cell != null) {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_NUMERIC:
double d = cell.getNumericCellValue();
value = String.valueOf(d).substring(0, String.valueOf(d).indexOf("."));
break;
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_FORMULA:
//导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case XSSFCell.CELL_TYPE_BLANK:
break;
case XSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y" : "N");
break;
}
}
return value;
}
}
3. 具体使用
直接在controller成使用。Result是我个人的封装。
public Result uploadExcel(MultipartFile myFile) {
//两种方式,一种直接上传的文件MultipartFile,一种用本地的文件:File
// File file2 = new File("这里是filePath");
// InputStream inputStream = new FileInputStream(file2);
String fileName = myFile.getOriginalFilename();
assert fileName != null;
if (!fileName.endsWith(".xlsx")) {
return new Result(ResultCode.FILE_WRONG);
}
List<List<List<String>>> list = null;
InputStream inputStream = null;
try {
inputStream = myFile.getInputStream();
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
list = ExcelReadUtils.getData(wb, 0, null);//3个参数。
} catch (Exception e) {
log.info("文件读取失败");
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (Exception e) {
log.info("文件流关闭失败");
}
inputStream = null;
}
}
return new Result(list);//读取到的list(表(行(列))),直接返回,也可以按需求做别的操作。
}