Java读取excel表头转为json(前端展示成动态表单)

菜鸡一个,网上没搜到,只能自己写,自己测了几个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;
    }
}

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值