菜鸡一个，网上没搜到，只能自己写，自己测了几个excel，只要不是特别反常的，应该都能支(bu)持(hui)转(bao)换(cuo)，肯定有bug，希望各位大佬帮忙指正：
excel模板：
解析结果：
成功解析为json树：{"一级7":[{"二级12":[{}]}],"一级5":[{"一级5":[{}]}],"一级4":[{"一级4":[{}]}],"一级1":[{"二级1":[{},{}]}],"一级2":[{"二级2":[{},{}]},{"二级3":[{},{}]}],"一级3":[{"二级4":[{},{}]},{"二级5":[{}]}],"一级6":[{"三级11":[{}]}],"一级a":[{"一级a":[{}]}],"一级b":[{"一级b":[{},{}]}]}
json格式化结果：[[{"children":[{"key":"3fbfdd15f8b54ef6acbedcea4a80535f","title":"二级12","align":"center","customize":[],"validate":[]}],"title":"一级7","align":"center","customize":[],"validate":[]},{"key":"6f8f97a68deb4154a373ab2051408ae5","title":"一级5","align":"center","customize":[],"validate":[]},{"children":[{"key":"1f1b846101854102b3d6c86eee227163","title":"三级10","align":"center","customize":[],"validate":[]}],"title":"一级4","align":"center","customize":[],"validate":[]},{"children":[{"children":[{"key":"9fa97a65c33d45b78e4d255d5fd4f351","title":"三级1","align":"center","customize":[],"validate":[]},{"key":"0e7b471b1053425faa74d16e6a503cdc","title":"三级2","align":"center","customize":[],"validate":[]}],"title":"二级1","align":"center","customize":[],"validate":[]}],"title":"一级1","align":"center","customize":[],"validate":[]},{"children":[{"children":[{"key":"852b43dc34b14fb88fdee1d8c446c448","title":"三级3","align":"center","customize":[],"validate":[]},{"key":"dda5087a04a245df8546fda7482be0e4","title":"三级4","align":"center","customize":[],"validate":[]}],"title":"二级2","align":"center","customize":[],"validate":[]},{"children":[{"key":"66e69b687c2246c49f37f59787e40e6c","title":"三级5","align":"center","customize":[],"validate":[]},{"key":"d00b5dff5aed42f4a5c4df13ce0afcbd","title":"三级6","align":"center","customize":[],"validate":[]}],"title":"二级3","align":"center","customize":[],"validate":[]}],"title":"一级2","align":"center","customize":[],"validate":[]},{"children":[{"children":[{"key":"bc7222fe9c9443e6aa503884671be30b","title":"三级7","align":"center","customize":[],"validate":[]},{"key":"083323dedbd249e0a767ade36b7e586a","title":"三级8","align":"center","customize":[],"validate":[]}],"title":"二级4","align":"center","customize":[],"validate":[]},{"children":[{"key":"a9cd73ce0f01409cb3c42257ed381b3a","title":"三级9","align":"center","customize":[],"validate":[]}],"title":"二级5","align":"center","customize":[],"validate":[]}],"title":"一级3","align":"center","customize":[],"validate":[]},{"children":[{"key":"844796dd7df843659545705cd4c24f55","title":"三级11","align":"center","customize":[],"validate":[]}],"title":"一级6","align":"center","customize":[],"validate":[]},{"children":[{"key":"2e1be533311b4ef6ac945c6c52430d73","title":"二级a","align":"center","customize":[],"validate":[]}],"title":"一级a","align":"center","customize":[],"validate":[]},{"children":[{"key":"cad7f50f34e849f0847bee9042d991c1","title":"二级b","align":"center","customize":[],"validate":[]},{"key":"29cf952b763d4f7795759a78c310273a","title":"二级c","align":"center","customize":[],"validate":[]}],"title":"一级b","align":"center","customize":[],"validate":[]}]]
代码如下：
package gs.thl.web.poi;
import com.alibaba.fastjson.JSONObject;
import groovy.util.logging.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.util.*;
/**
* @author thl
* @version 1.0
* @date 2022/1/20 17:00
*/
@Slf4j
public class Excel2Json {
public static void main(String[] args) {
excelFormatJson("D:\\data\\work\\自然资源\\excel转json\\2.xlsx", 6, null);
}
private static List<ErrorMessage> msgList = new ArrayList<>();
private Set<String> hasKeyList = new HashSet<>();
public List<ErrorMessage> getMsgList() {
return msgList;
}
public void setMsgList(List<ErrorMessage> msgList) {
msgList = msgList;
}
private static class BaseEntity{
private String name;
//包含的下级的行列坐标
private Map<String, List<Integer>> childIndexList = new LinkedHashMap<>();
private List<Map> child;
public BaseEntity(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Map<String, List<Integer>> getChildIndexList() {
return childIndexList;
}
public void setChildIndexList(Map<String, List<Integer>> childIndexList) {
this.childIndexList = childIndexList;
}
public List<Map> getChild() {
return child;
}
public void setChild(List<Map> child) {
this.child = child;
}
}
/**
* 怎么支持重名？？？以行名称做的数组，让一行字符串数组存两个相同名称的字符串，这现实吗？后来还是实现了，开始没想到直接存的时候就把标题重命名，最后再改回去就行了
* 有两个天生bug，能力有限没想到解决版本：
* 1、两行数据，上面的两列不合并，下面的两列合并为一列，下面的父级算是谁
* 2、四行数据，第一行不合并，中间行合并，最后一行不合并，最后始终只有3级，跟旁边的4级比少了一级，那一级只能体现在最后行(这应该是前端显示实现，后端把合并行列都返回给前端就行了)
* @param path 本地文件
* @param lastRowNum 标题起始行，必传
* @param lastCellNum
* @return
*/
public static Map<String, Object> excelFormatJson(String path, Integer lastRowNum, Integer lastCellNum) {
if (path==null || (!path.endsWith(".xlsx") && !path.endsWith(".xls"))) {
msgList.add(new ErrorMessage("文件类型不是excel"));
return null;
}
Map<String, Object> rMap =null;
msgList = new ArrayList<>();
FileInputStream is = null;
try {
is = new FileInputStream(path);
Workbook workbook = null;
if (path.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(is);
}else {
workbook = new HSSFWorkbook(is);
}
Sheet sheet0 = workbook.getSheetAt(0);
if (lastRowNum == null) {
lastRowNum = sheet0.getLastRowNum() + 1;
}
//遍历将所有行 存为数组
List<List<String>> sheetList = new LinkedList<>();
for (int i = 0; i < lastRowNum; i++) {
Row row = sheet0.getRow(i);
if (row == null) {
msgList.add(new ErrorMessage((i + 1) + "", null, ErrorMessage.MSG_DATA_NULL));
continue;
}
if (lastCellNum == null) {
lastCellNum = (int) row.getLastCellNum();
}
List<String> rowList = new LinkedList<>();
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
msgList.add(new ErrorMessage((i + 1) + "", (j + 1) + "", ErrorMessage.MSG_DATA_NULL));
continue;
}
//tm的非得支持同名，没办法，先把名称加索引上去试试，最后遍历去除
String strCellValue = getStrCellValue(cell);
rowList.add(j, strCellValue == null ? null : strCellValue + "#" + i + "_" + j);
}
sheetList.add(i, rowList);
}
if (sheetList.isEmpty()) {
//获取到的行列数据为空
msgList.add(new ErrorMessage(null, null, "解析到的行列数据为空"));
return null;
}
if (!msgList.isEmpty()) {
return null;
}
//总的list包含按索引每行一个map,每行的map再包含多个标题单元格
List<Map<String, BaseEntity>> relationList = new LinkedList<>();
for (int i = 0; i < sheetList.size(); i++) {
findIndexRelation(i, sheetList, sheet0, relationList);
}
System.out.println("上下级索引关系构造结果：\n" + JSONObject.toJSONString(relationList));
List<Map<String, Object>> treeList = null;
List<String> keyList = new LinkedList<>();
for (int i = 0; i < sheetList.size(); i++) {
treeList = indexRelation2Tree(i, sheetList, relationList, keyList, treeList);
}
System.out.println("树形结构构造结果：\n" + JSONObject.toJSONString(treeList));
formateResult(treeList);
System.out.println("树形结构格式化结果：\n" + JSONObject.toJSONString(treeList));
rMap = new LinkedHashMap<>();
rMap.put("keyList", keyList);
rMap.put("columns", relationList);
} catch (Exception e) {
e.printStackTrace();
}
return rMap;
}
/**
* 格式化json，去除最后层children，标题名称正常化
* @param treeList
*/
private static void formateResult(List<Map<String, Object>> treeList) {
if (treeList == null || treeList.isEmpty()) {
return;
}
for (Map<String, Object> map : treeList) {
String title = (String) map.get("title");
String[] split = title.split("#\\d*_");
map.replace("title", split[0]);
List<Map<String, Object>> children = (List<Map<String, Object>>) map.get("children");
if (children.isEmpty()) {
map.remove("children");
}
formateResult(children);
}
}
/**
* 通过上下级关系构造树形结构
* @param currRowNum
* @param sheetList
* @param relationList
* @param treeList
* @return
*/
private static List<Map<String, Object>> indexRelation2Tree(Integer currRowNum, List<List<String>> sheetList,
List<Map<String, BaseEntity>> relationList, List<String> keyList ,
List<Map<String, Object>> treeList) {
Map<String, BaseEntity> currRowMap = relationList.get(currRowNum);
if (currRowNum == 0) {
treeList = new LinkedList<Map<String, Object>>();
}
//第一行
for (String name : currRowMap.keySet()) {
Map<String, Object> map = new LinkedHashMap<>();
String uuidKey = UUID.randomUUID().toString().replace("-","");
map.put("key", uuidKey);
keyList.add(uuidKey);
map.put("title", name);
map.put("align", "center");
map.put("validate", Collections.EMPTY_LIST);
map.put("children", new LinkedList<Map<String, Object>>());
map.put("row", currRowNum);
int childCol = sheetList.get(currRowNum).indexOf(name);
map.put("col", childCol);
BaseEntity bs = currRowMap.get(name);
map.put("childIndexList", bs);
//第一行
if (currRowNum == 0) {
treeList.add(map);
}else {
Map<String, Object> parentMap = findParentTree(treeList, currRowNum, childCol);
List<Map<String, Object>> cList = (List<Map<String, Object>>) parentMap.get("children");
cList.add(map);
}
}
return treeList;
}
/**
* 递归找到上级的tree结构，给上级添加孩子
* @param treeList
* @param childRow
* @param childColl
* @return
*/
private static Map<String, Object> findParentTree(List<Map<String, Object>> treeList, Integer childRow, Integer childColl) {
if (treeList == null || treeList.isEmpty()) {
return null;
}
for (Map<String, Object> tr : treeList) {
BaseEntity bs = (BaseEntity) tr.get("childIndexList");
for (Map.Entry<String, List<Integer>> row : bs.getChildIndexList().entrySet()) {
if (String.valueOf(childRow).equals(row.getKey())) {
for (Integer coll : row.getValue()) {
if (childColl.equals(coll)) {
return tr;
}
}
}
}
Map<String, Object> children = findParentTree((List<Map<String, Object>>) tr.get("children"), childRow, childColl);
if (children != null) {
return children;
}
}
return null;
}
/**
* 找到索引间关系，上级包含下级的哪些索引
* @param currRowIndex
* @param sheetList
* @param sheet0
* @param result
* @return
*/
private static List<Map<String,BaseEntity>> findIndexRelation(Integer currRowIndex, List<List<String>> sheetList,
Sheet sheet0, List<Map<String, BaseEntity>> result) {
//初始化
List<String> currRowList = sheetList.get(currRowIndex);
Map<String, BaseEntity> map = new LinkedHashMap<>();
result.add(map);
for (int i = 0; i < currRowList.size(); i++) {
String name = currRowList.get(i);
if (name != null) {
map.put(name, new BaseEntity(name));
}
}
//currRowIndex第一行不需要设置上一级包含的子索引
if (currRowIndex == 0) {
return result;
}
List<String> topRowList = sheetList.get(currRowIndex - 1);
for (int i = 0; i < currRowList.size(); i++) {
int[] mIndex = getMergedRegionIndex(sheet0, currRowIndex-1, i);
//上一级没有合并，直接加到上一级的childIndexList
if (mIndex == null) {
Map<String, BaseEntity> maps = result.get(currRowIndex - 1);
BaseEntity be = maps.get(topRowList.get(i));//上级发生合并i肯定空的，获取个毛
//第一个元素
if (be.getChildIndexList().get(String.valueOf(currRowIndex)) == null) {
be.getChildIndexList().put(String.valueOf(currRowIndex), new ArrayList<>(Collections.singletonList(i)));
} else {
be.getChildIndexList().get(String.valueOf(currRowIndex)).add(i);
}
}else {//上一级合并了，把这一行的列都放到上一行的第一合并列
int row = mIndex[0], col = mIndex[1];
Map<String, BaseEntity> maps = result.get(row);
BaseEntity be = maps.get(topRowList.get(col));
//下面的递归是获取发生行合并后，父级是上上级的单元格，单独判断下，没必要就不用进行递归了
if (be == null) {
be = findParentBaseEntity(currRowIndex, col, sheetList, result, row);
}
//第一个元素
if (be.getChildIndexList().get(String.valueOf(currRowIndex)) == null) {
be.getChildIndexList().put(String.valueOf(currRowIndex), new ArrayList<>(Collections.singletonList(i)));
} else {
be.getChildIndexList().get(String.valueOf(currRowIndex)).add(i);
}
}
}
return result;
}
/**
* 获取行合并后的上上级单元格
* @param currRowIndex
* @param currCollIndex
* @param sheetList
* @param result
* @return
*/
private static BaseEntity findParentBaseEntity(Integer currRowIndex,Integer currCollIndex,
List<List<String>> sheetList,List<Map<String, BaseEntity>> result,int topRow) {
//fixme 其实递归也不是太难，只要能找到三个点就能保证递归在可控范围
//1.递归底层返回点
if (currRowIndex < 0) {
return null;
}
List<String> topRowList = sheetList.get(currRowIndex);
try {
Map<String, BaseEntity> maps = result.get(currCollIndex);
BaseEntity bs = maps.get(topRowList.get(currCollIndex));
//2.递归每层返回点
if (bs != null) {
return bs;
}else {
//3.递归进入点
return findParentBaseEntity(currRowIndex - 1, currCollIndex, sheetList, result, topRow);
}
} catch (Exception e) {
return null;
}
}
/**
* 获取合并单元格的第一格的坐标
* @param sheet
* @param row 行下标
* @param column 列下标
* @return [行坐标，列坐标]，为空表示没有合并
*/
private static int[] getMergedRegionIndex(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return new int[]{firstRow,firstColumn};
}
}
}
return null;
}
/**
* poi 获取单元格的值
* @param cell
* @return string类型值
*/
public static String getStrCellValue(Cell cell) {
String cv = null;
if (cell == null) {
return null;
}
if (cell.getCellTypeEnum() == CellType.STRING) {
cv = cell.getStringCellValue();
} else if (cell.getCellTypeEnum() == CellType.STRING) {
cv = String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellTypeEnum() == CellType.FORMULA) {
cv = cell.getCellFormula();
} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
cv = String.valueOf(cell.getNumericCellValue());
} else {
cell.setCellType(CellType.STRING);
cv = cell.getStringCellValue();
}
return (cv == null || "".equals(cv.trim())) ? null : cv;
}
}
//结束不了，消息封装，如果直接用最好把方法的static都去掉,上面是方便测试，消息这我还没怎么做好
package gs.thl.web.poi;
/**
* @author thl
* @version 1.0
* @date 2021/12/20 20:19
*/
public class ErrorMessage {
public static String MSG_DATA_NULL = "数据为空";
private String row;
private String cell;
private String msg;
private String fullMsg;
public ErrorMessage() {
}
public ErrorMessage(String msg) {
this.msg = msg;
this.fullMsg = msg;
}
public ErrorMessage(String row, String cell, String msg) {
this.row = row;
this.cell = cell;
this.msg = msg;
if (cell == null) {
this.fullMsg = "第" + row + "行" + msg;
}else {
this.fullMsg = "第" + row + "行" + "第" + cell + "列" + msg;
}
}
public String getFullMsg() {
return fullMsg;
}
public void setFullMsg(String fullMsg) {
this.fullMsg = fullMsg;
}
public String getRow() {
return row;
}
public void setRow(String row) {
this.row = row;
}
public String getCell() {
return cell;
}
public void setCell(String cell) {
this.cell = cell;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
