大体思路:excel文件转list,先确认总行数,列数,然后从行开始解析每个单元格,解析成:key(表头):value(单元格内容),最后add到一个list中;所以最好有一行是英文的,指定表头作为key(titleNum)
1.需导入poi依赖
<!-- excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
工具类:ExcelUtils
package org.meichao.utils;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
/**
* excel转List
* @param inputStream excel输入流
* @param sheetName sheet名字
* @param titleNum 表头的行数 从0开始
* @param ignoreRow 忽略几行 从0开始
* @return
*/
public static List<JSONObject> excelToList(InputStream inputStream, String sheetName, int titleNum, int ignoreRow) {
List<JSONObject> resultList = new ArrayList<>();
try {
//根据Excel数据源创建WorkBook
POIFSFileSystem fs = new POIFSFileSystem(inputStream);
//文档对象
HSSFWorkbook wb = new HSSFWorkbook(fs);
//哪个sheet
HSSFSheet sheet = wb.getSheet(sheetName);
//总行数
int rows = sheet.getLastRowNum();
//表头
Row title = sheet.getRow(titleNum);
//列数
int columns = title.getPhysicalNumberOfCells();
LOGGER.info("------excel转list开始,总行数:{},表头行数:{},列数:{}", rows, title, columns);
//sheet转list
for (int i = ignoreRow; i < rows; i++) {
JSONObject temp = new JSONObject();
//获取行
HSSFRow row = sheet.getRow(i);
//循环每一列
for (int j = 0; j < columns; j++) {
HSSFCell cell = row.getCell(j);
String content = "";
if (cell != null) {
//设置单元格属性
cell.setCellType(CellType.STRING);
content = cell.getStringCellValue().trim();
}
//格式:列表头:单元格数据
temp.put(title.getCell(j).getStringCellValue(), content);
}
resultList.add(temp);
}
} catch (IOException e) {
e.printStackTrace();
}
LOGGER.info("------excel转list结束,转换行数:{}", resultList.size());
return resultList;
}
}