读取指定行、列(list为空代表excel首为字段名)数据
public class ReadExcelUtil {
/**
* 读excel
* @param pathName: 文件路径;sheetNum:表索引;rowNum:行数;colNum列数;list:传递当前的字段名,不传代表从excel读取
* @return 数组对象
*/
public static JSONArray readExcel(String pathName, Integer sheetNum, Integer rowNum, Integer colNum, List<String> list){
rowNum--;//获得当前的 行和列索引
colNum--;
JSONArray json = new JSONArray();
//jdk1.8的新特性
try (
FileInputStream fileInputStream = new FileInputStream(pathName);
Workbook workbook = WorkbookFactory.create(fileInputStream);
) {
Sheet sheet = workbook.getSheetAt(sheetNum);
//读取总共有多少行数据
int rowcount = sheet.getPhysicalNumberOfRows() + rowNum;
//判断字段名是否存在,为空表示excel中 头行数据为字段名;有值表示 excel为纯数据,list中为当前字段名
if (CollectionUtil.isEmpty(list)) {
//获取字段名
Row row = sheet.getRow(rowNum);
if (row != null) {
//获取总共有多少列
int cellncount = row.getPhysicalNumberOfCells() + colNum;
list = new ArrayList<String>();
//从索引列开始读 读取数据 赋值给集合字段
for (int cellnum = colNum; cellnum < cellncount; cellnum++) {
Cell cell = row.getCell(cellnum);
if (cell != null) {
String stringCellValue = cell.getStringCellValue();
list.add(cellnum - colNum,stringCellValue);
}
}
}
rowNum++;
}
//从当前行索引开始读
for (int rownum = rowNum; rownum < rowcount; rownum++) {
JSONObject map = new JSONObject();
Row rowData = sheet.getRow(rownum);
if (rowData != null) {
//读取总列数 ,并从列索引开始 读取单元格数据 并使用 hutool工具包 CellUtil 进行类型匹配
int cellcount = rowData.getPhysicalNumberOfCells() + colNum;
for (int celnum = colNum; celnum < cellcount; celnum++) {
Cell cell = rowData.getCell(celnum);
if (cell != null) {
Object cellValue = CellUtil.getCellValue(cell);
//满足日期 进行 日期格式转换
if (cellValue instanceof DateTime){
DateTime d = (DateTime)cellValue;
String s = d.toString();
cellValue = s;
}
map.put(list.get(celnum - colNum), cellValue);
}
}
}
json.add(map);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return json;
}
}