Java处理嵌套表格，递归方法先自动识别表头，接着读取表格数据，由表头和收集的表格数据根据colIndex匹配，组装成"name":“value”–>表头名：数据值，所有数据匹配处理过后收集起来，组成List<Map<String, Object>>形式数据，进行使用。
解析处理嵌套表格表头以及展示数据。
/**
* 解析处理嵌套表格表头以及展示数据
* @param workbook
* @return
*/
public static Map<String, Object> readExcelHead(Workbook workbook) {
Map<String, Object> resultAllMap = new HashMap<>();
JSONArray schema = new JSONArray();
List<Map<String, Object>> collectList = new ArrayList<>();
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
// 得到第一个sheet
Sheet sheetAt = workbook.getSheetAt(0);
//表头第一行
int head = 0;
Row eachRowHead = sheetAt.getRow(head);
//每一行总列数(第一行总列数)
int colSize = eachRowHead.getLastCellNum();
//寻找最大嵌套行数
List<Integer> collectDepthIndex = new ArrayList<>();
//int lastRowNum = sheetAt.getLastRowNum();
//表头树状结构
List<Map<String, Object>> resultMapList = new ArrayList<>();
for (int columnIndex = 0; columnIndex < colSize; columnIndex++) {
Cell cell = eachRowHead.getCell(columnIndex);
if (StringUtils.isNotBlank(cell.toString())) {
Map<String, Object> resultMap = new HashMap<>();
//获取数据类型以及数据
CellType cellType = cell.getCellType();
String cellValue = null;
switch (cellType) {
case STRING: //文本
cellValue = cell.getStringCellValue();
break;
case NUMERIC: //数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); //日期型
} else {
cellValue = String.valueOf(cell.getNumericCellValue()); //数字
}
break;
case BOOLEAN: //布尔型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: //空白
cellValue = cell.getStringCellValue();
break;
case FORMULA: //公式
cellValue = "公式";
break;
case ERROR: //错误
cellValue = "错误";
break;
default:
cellValue = "错误";
}
cell.setCellType(cellType);
//判断该字段值转为数字或字符类型
Boolean flag = checkNumber(cellValue);
String value = "";
if (flag) {
String cellValueProcess = cellValue.split("\\.")[0];
value = cellValueProcess;
} else {
value = cellValue.replaceAll("\\.", "");
}
// // 读取单元格数据格式（标记为字符串）
// cell.setCellType(STRING);
// String value = cell.getStringCellValue().replaceAll("\\.","");
Map<String, Integer> map = getMergerCellRegionRow(sheetAt, head, columnIndex);
Integer isMergedRegion = map.get("isMergedRegion");
//寻找表头递归方法
commonMethod(sheetAt, isMergedRegion, map, resultMap, value, cellType, columnIndex, resultMapList, head, collectDepthIndex);
}
}
Integer rowMax = Collections.max(collectDepthIndex) + 1;
List<JSONObject> dataList = new ArrayList<>();
//只取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
//遍历每一行 (从第三行开始，index=2)
int headerNum = rowMax;
//只收集一行数据即可
rowCount = headerNum + 1;
for (int r = headerNum; r < rowCount; r++) {
Row row = sheet.getRow(r);
int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
//收集每一列
//LinkedHashMap<Integer,Object> linkedHashMapData = new LinkedHashMap();
JSONObject rowJson = new JSONObject();
//遍历每一列
for (int c = 0; c <= cellCount; c++) {
Cell cell = row.getCell(c);
if (ObjectUtils.isNotEmpty(cell)) {
CellType cellType = cell.getCellType();
String cellValue = null;
switch (cellType) {
case STRING: //文本
cellValue = cell.getStringCellValue();
break;
case NUMERIC: //数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); //日期型
} else {
cellValue = String.valueOf(cell.getNumericCellValue()); //数字
}
break;
case BOOLEAN: //布尔型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: //空白
cellValue = cell.getStringCellValue();
break;
case FORMULA: //公式
cellValue = "公式";
break;
case ERROR: //错误
cellValue = "错误";
break;
default:
cellValue = "错误";
}
//System.out.println(cellValue + " ");
rowJson.put(String.valueOf(cell.getColumnIndex()), cellValue);
}
}
dataList.add(rowJson);
}
//处理Excel内部数据(n层表头)//只收集一行数据即可
for (JSONObject object : dataList) {
JSONObject jsonObjectEach = new JSONObject();
//由表头匹配数据递归方法
getChiCommonMethod(object, resultMapList, jsonObjectEach);
collectList.add(jsonObjectEach);
}
//inputStream.close();
if (dataList.size() > 0) {
JSONObject object = dataList.get(0);
//由表头匹配数据递归方法
processHeaderStructure(object, resultMapList);
//表头树型结构数据处理成特征字段要求形式
schema = JSONObject.parseArray(JSONObject.toJSONString(resultMapList));
//System.out.println("schema====>\n"+schema);
}
//表头树型结构数据处理成特征字段要求形式
//processHeaderStructureTest(resultMapList);
//schema = JSONObject.parseArray(JSONObject.toJSONString(resultMapList));
} catch (Exception e) {
e.printStackTrace();
}
resultAllMap.put("schema",schema);
resultAllMap.put("collectList",collectList);
return resultAllMap;
}
解析处理嵌套表格数据。
/**
* 解析处理嵌套表格数据
* @param excelFile
* @return
*/
public static Map<String, Object> readExcel(File excelFile) {
Map<String, Object> result = new HashMap<>();
//收集嵌套表格内容数据
List<Map<String, Object>> collectList = new ArrayList<>();
//存储第一行表头列表
List<String> columnList = new LinkedList<>();
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
try {
// //同时支持Excel 2003、2007
// File excelFile = new File(path); //创建文件对象
FileInputStream is = new FileInputStream(excelFile); //文件流
Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
// 得到第一个sheet
Sheet sheetAt = workbook.getSheetAt(0);
//表头第一行
int head = 0;
Row eachRowHead = sheetAt.getRow(head);
//每一行总列数(第一行总列数)
int colSize = eachRowHead.getLastCellNum();
List<Integer> collectDepthIndex = new ArrayList<>();
//int lastRowNum = sheetAt.getLastRowNum();
//表头树状结构数据
List<Map<String, Object>> resultMapList = new ArrayList<>();
for (int columnIndex = 0; columnIndex < colSize; columnIndex++) {
Cell cell = eachRowHead.getCell(columnIndex);
if (StringUtils.isNotBlank(cell.toString())) {
Map<String, Object> resultMap = new HashMap<>();
//获取数据类型以及数据
CellType cellType = cell.getCellType();
String cellValue = null;
switch (cellType) {
case STRING: //文本
cellValue = cell.getStringCellValue();
break;
case NUMERIC: //数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); //日期型
} else {
cellValue = String.valueOf(cell.getNumericCellValue()); //数字
}
break;
case BOOLEAN: //布尔型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: //空白
cellValue = cell.getStringCellValue();
break;
case FORMULA: //公式
cellValue = "公式";
break;
case ERROR: //错误
cellValue = "错误";
break;
default:
cellValue = "错误";
}
cell.setCellType(cellType);
String value = cellValue.replaceAll("\\.","");
// // 读取单元格数据格式（标记为字符串）
// cell.setCellType(STRING);
// String value = cell.getStringCellValue().replaceAll("\\.","");
Map<String, Integer> map = getMergerCellRegionRow(sheetAt, head, columnIndex);
Integer isMergedRegion = map.get("isMergedRegion");
//寻找表头递归方法
commonMethod(sheetAt, isMergedRegion,map, resultMap, value, cellType, columnIndex, resultMapList, head,collectDepthIndex);
}
}
Integer rowMax = Collections.max(collectDepthIndex) + 1;
List<JSONObject> dataList = new ArrayList<>();
//只取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
//遍历每一行 (从第三行开始，index=2)
int headerNum = rowMax;
for (int r = headerNum; r < rowCount; r++) {
Row row = sheet.getRow(r);
int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
//收集每一列
//LinkedHashMap<Integer,Object> linkedHashMapData = new LinkedHashMap();
JSONObject rowJson = new JSONObject();
//遍历每一列
for (int c = 0; c <= cellCount; c++) {
Cell cell = row.getCell(c);
if (ObjectUtils.isNotEmpty(cell)) {
CellType cellType = cell.getCellType();
String cellValue = null;
switch (cellType) {
case STRING: //文本
cellValue = cell.getStringCellValue();
break;
case NUMERIC: //数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); //日期型
} else {
cellValue = String.valueOf(cell.getNumericCellValue()); //数字
}
break;
case BOOLEAN: //布尔型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: //空白
cellValue = cell.getStringCellValue();
break;
case FORMULA: //公式
cellValue = "公式";
break;
case ERROR: //错误
cellValue = "错误";
break;
default:
cellValue = "错误";
}
//System.out.println(cellValue + " ");
rowJson.put(String.valueOf(cell.getColumnIndex()), cellValue);
}
}
dataList.add(rowJson);
}
//处理Excel内部数据(n层表头)
for (JSONObject object : dataList) {
//System.out.println(object);
JSONObject jsonObjectEach = new JSONObject();
//由表头匹配数据递归方法
getChiCommonMethod(object, resultMapList, jsonObjectEach);
collectList.add(jsonObjectEach);
}
is.close();
} catch (Exception e) {
e.printStackTrace();
}
result.put("result", collectList);
result.put("title", columnList);
return result;
}
寻找表头递归方法。
/**
* 寻找表头递归方法
* @param sheetAt
* @param isMergedRegion
* @param resultMap
* @param value
* @param columnIndex
* @param resultMapList
* @param head
*/
public static List<Integer> commonMethod(Sheet sheetAt, Integer isMergedRegion,Map<String, Integer> map, Map<String, Object> resultMap,
String value, CellType cellType, int columnIndex, List<Map<String, Object>> resultMapList, int head,List<Integer> collectDepthIndex) {
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
// 对数据对应的数据类型特殊处理。规则：数据类型（其中字符转小写，首字母再变大写）
String cellTypeProcess = getFirstLetterUppercase(cellType.toString().toLowerCase(Locale.ROOT));
if (isMergedRegion == 1) {//判断是否合并格，1为合并格
resultMap.put("colName", value);
resultMap.put("colType", cellTypeProcess);
resultMap.put("colIndex", columnIndex);
resultMapList.add(resultMap);
//使用传进来的map信息
Integer cellCol = map.get("col") == 0 ? 1 : map.get("col"); // 得到合并的列数
Integer cellRow = map.get("row") == 0 ? 1 : map.get("row"); // 得到合并的行数
Integer firstRow = map.get("firstRow");
Integer lastRow = map.get("lastRow");
Integer firstCol = map.get("firstCol");
Integer lastCol = map.get("lastCol");
//收集跨列数据
List<Map<String, Object>> resultMapList2 = new ArrayList<>();
//cellCol>1说明它是跨列合并单元格
if (cellCol > 1) {
for (int j = firstCol; j <= lastCol; j++) {//3,4,5 //联系人姓名,"", 联系人电话
int headTemp = head + 1;
Cell cell2 = sheetAt.getRow(headTemp).getCell(j);
if (StringUtils.isNotBlank(cell2.toString())) {
//获取数据类型以及数据
CellType cellType2 = cell2.getCellType();
String cellValue2 = null;
switch (cellType2) {
case STRING: //文本
cellValue2 = cell2.getStringCellValue();
break;
case NUMERIC: //数字、日期
if (DateUtil.isCellDateFormatted(cell2)) {
cellValue2 = fmt.format(cell2.getDateCellValue()); //日期型
} else {
cellValue2 = String.valueOf(cell2.getNumericCellValue()); //数字
}
break;
case BOOLEAN: //布尔型
cellValue2 = String.valueOf(cell2.getBooleanCellValue());
break;
case BLANK: //空白
cellValue2 = cell2.getStringCellValue();
break;
case FORMULA: //公式
cellValue2 = "公式";
break;
case ERROR: //错误
cellValue2 = "错误";
break;
default:
cellValue2 = "错误";
}
cell2.setCellType(cellType2);
//判断该字段值转为数字或字符类型
Boolean flag2 = checkNumber(cellValue2);
String value2 = "";
if (flag2) {
String cellValueProcess = cellValue2.split("\\.")[0];
value2 = cellValueProcess;
} else {
value2 = cellValue2.replaceAll("\\.", "");
}
String value2 = cellValue2.replaceAll("\\.","");
// // 读取单元格数据格式（标记为字符串）
// cell2.setCellType(STRING);
// String value2 = cell2.getStringCellValue().replaceAll("\\.","");
int columnIndex2 = cell2.getColumnIndex();
Map<String, Object> resultMap2 = new HashMap<>();
//寻找表头递归方法
Map<String, Integer> map2 = getMergerCellRegionRow(sheetAt, headTemp, j);
Integer isMergedRegion2 = map2.get("isMergedRegion");
commonMethod(sheetAt, isMergedRegion2, map2, resultMap2, value2, cellType2, columnIndex2, resultMapList2, headTemp,collectDepthIndex);
}
}
}
resultMap.put("children", resultMapList2);
} else {
resultMap.put("colName", value);
resultMap.put("colType", cellTypeProcess);
resultMap.put("colIndex", columnIndex);
//resultMap.put("children", new ArrayList<>());//这里需要多加一个children，最后以一级也满足相同格式
resultMapList.add(resultMap);
}
collectDepthIndex.add(head);
return collectDepthIndex;
}
判断当前所在单元格是否是合并单元格`。
/**
* 判断当前所在单元格是否是合并单元格
* @param sheet 表单
* @param cellRow 被判断的单元格的行号
* @param cellCol 被判断的单元格的列号
* @return row: 行数；col列数
* @Author kongfangfang
*/
private static Map<String, Integer> getMergerCellRegionRow(Sheet sheet, int cellRow, int cellCol) {
Map<String, Integer> map = new HashMap<>();
int retVal = 0, retCol= 0 ;
Integer flag = 0; //不是合并单元格
int sheetMergerCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergerCount; i++) {
CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
if (cellCol >= firstCol && cellCol <= lastCol) {
//是合并单元格
flag = 1;
retVal = lastRow - firstRow + 1; // 得到合并的行数
retCol = lastCol - firstCol + 1; // 得到合并的列数
map.put("firstRow", firstRow);
map.put("lastRow", lastRow);
map.put("firstCol", firstCol);
map.put("lastCol", lastCol);
break;
}
}
}
map.put("row", retVal);
map.put("col", retCol);
map.put("isMergedRegion", flag);
return map;
}
判断字符串是否可以转化为json对象。
/**
* 判断字符串是否可以转化为json对象
* @param content
* @return
*/
public static boolean checkJsonObject(String content) {
if (StringUtils.isBlank(content))
return false;
try {
JSONObject.parseObject(content);
return true;
} catch (Exception e) {
return false;
}
}
判断字符串是否是数字。
public static Boolean checkNumber(String dataValue) {
try {
Integer.valueOf(dataValue);
} catch (NumberFormatException e) {
try {
Double.valueOf(dataValue);
} catch (NumberFormatException n) {
return false;
}
}
return true;
}
首字母大写。
/**
* 首字母大写
* @param str
* @return
*/
private static String getFirstLetterUppercase(String str) {
char[] cs=str.toCharArray();
cs[0]-=32;
return String.valueOf(cs);
}
由表头匹配数据递归方法。
/**
* 由表头匹配数据递归方法
* @param object
* @param resultMapList
* @param jsonObjectEach
*/
public static void getChiCommonMethod(JSONObject object, List<Map<String, Object>> resultMapList,JSONObject jsonObjectEach) {
for (Map<String, Object> objectMap : resultMapList) {
String colName = objectMap.get("colName").toString();
Integer colIndex = Integer.valueOf(objectMap.get("colIndex").toString());
List<Map<String, Object>> children = (List<Map<String, Object>>) objectMap.get("children");
if (children == null || children.isEmpty()) { //"客户编号
String dataValue = object.get(colIndex).toString();
//children为空表明数据到了最后一级
//将该字段值转为数字或字符类型，并更新json
Object childrenColValue = null;
try {
childrenColValue = Integer.valueOf(dataValue);
} catch (NumberFormatException e) {
try {
childrenColValue = Double.valueOf(dataValue);
} catch (NumberFormatException n) {
try {
childrenColValue = String.valueOf(dataValue);
} catch (Exception s) {
e.printStackTrace();
n.printStackTrace();
s.printStackTrace();
}
}
}
jsonObjectEach.put(colName, childrenColValue);
} else { //人员信息
JSONObject eachChildren = new JSONObject();
getChiCommonMethod(object, children, eachChildren);
jsonObjectEach.put(colName, eachChildren);
}
}
}
由表头匹配数据递归方法。
/**
* 由表头匹配数据递归方法
* @param object
* @param resultMapList
*/
public static void processHeaderStructure(JSONObject object, List<Map<String, Object>> resultMapList) {
for (Map<String, Object> objectMap : resultMapList) {
String colName = objectMap.get("colName").toString();
Integer colIndex = Integer.valueOf(objectMap.get("colIndex").toString());
List<Map<String, Object>> children = (List<Map<String, Object>>) objectMap.get("children");
if (children == null || children.isEmpty()) { //"客户编号
String dataValue = object.get(colIndex).toString();
//children为空表明数据到了最后一级
//判断该字段值转为数字或字符类型
Boolean flag = checkNumber(dataValue);
if (flag){
objectMap.put("colType","Number");
}else{
objectMap.put("colType","String");
}
objectMap.remove("children");
objectMap.remove("colIndex");
} else { //人员信息
processHeaderStructure(object, children);
objectMap.remove("colIndex");
objectMap.put("colType","Json");
}
}
}
1929
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
