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

已于 2023-07-06 14:05:49 修改
阅读量821 收藏 2
点赞数
分类专栏： Java 文章标签： java 前端 json
于 2022-01-23 01:20:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43329639/article/details/122646635
版权

菜鸡一个，网上没搜到，只能自己写，自己测了几个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
    评论
专栏目录
java excel 读取表头_java 读取 excel 表格内容
weixin_29317963的博客
02-13 5408
一、添加依赖org.apache.poipoi3.8org.apache.poipoi-ooxml3.812345678910二、工具类根据文件后缀判断 2003 || 2007 || 2010 格式。import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.a...
json to excel java_Java实现excel表格转成json的方法
weixin_31209173的博客
02-12 287
今天有个朋友问我，有没有excel表格到处json的方法，在网上找到了好几个工具，都不太理想，于是根据自己的需求，自己写了一个工具。功能代码package org.duang.test;import java.io.File;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.u...
JAVA POI 把execl树形结构转成多叉树结构返回JSON字符串给前端
最新发布
tellmewhoisi的博客
05-09 244
好文章原文复制(我只是传播者)，直接去看原文(我至今还不会使用，写死的实现了)
java 读取Excel复杂表头并转换成json
javaXingzhe的博客
03-03 2142
java 使用easyexcel读取excel表头并转成json
java将excel转成json,将excel转换成Json格式
weixin_34532234的博客
03-22 1438
平时使用excel较多，但是编程使用json格式较多，每次转换都很麻烦。所以写了一个小脚本。实现的思路很简单，利用xlrd操作excel，把表头作为字典的字段，逐行读取记录，其中数字python默认是float类型，此处写死强行换成str类型。最后利用Python的json包dumps一下，不过要注意ensure_ascii=False，不然会输出'/u989'之类的编码，indent = 2是为...
java 读取.xlsx_java 读取xlsx(示例代码)
weixin_42458217的博客
02-15 805
(1)导入mavenorg.apache.poipoi3.9(2)代码public static void main(String[] args) {// 解压Book1.xlsxZipFile xlsxFile;try {xlsxFile = new ZipFile(new File("/Users/chengtao/downloads/教练入职时间.xlsx"));DocumentBuilde...
Java实现excel表格转成json的方法
10-19
2. `main` 方法是程序的入口点，它创建了一个 `Excel2JSONHelper` 实例，并调用 `readExcle` 方法来读取Excel文件并将其转换为JSONArray。 3. `getExcel2JSONHelper` 是一个静态方法，用于获取`Excel2JSONHelper` ...
java如何生成可变表头的excel
08-29
Java生成可变表头的Excel方法详解 在本文中，我们将详细介绍如何使用Java生成可变表头的Excel文件。这个方法可以传入一个表头和数据，将数据导入到Excel中，并具有有一定的参考价值。 知识点1: 传入表头和数据 在...
利用java读取web项目中json文件为map集合方法示例
10-19
在Java Web开发中，有时我们需要从项目资源目录中读取JSON文件，并将其内容转换为Map集合以便于操作和处理。本文将详细讲解如何利用Java实现这一功能，同时提供一个具体的示例。 首先，我们需要了解JSON...
java解析读取excel，word,txt,xml,json等jar包
08-20
支持excel2003，2007的解析读取，可指定sheet页读取，指定行开始读取，过滤空行，指定列读取，过滤空单元格，支持json文件的解析读取，支持word文件的解析读取，txt文件等等，需要API文档可私信本人
python3 循环读取excel文件并写入json操作
09-16
首先，我们导入所需的库：`xlrd`用于读取Excel文件，`json`用于处理JSON数据，以及`operator`用于按特定字段对数据进行排序。 ```python import xlrd import json import operator ``` `read_xlsx()`函数用于读取...
java通过apache POI读取Excel文件生成JSON数据
weixin_34221073的博客
02-16 382
2019独角兽企业重金招聘Python工程师标准>>> ...
java将excel格式字符串转为json
weixin_42610010的博客
02-15 706
要将Excel格式的字符串转换为JSON，您可以使用Java中的Apache POI库来读取Excel文件并从中提取数据，然后将数据转换为JSON格式。下面是一个简单的示例代码片段，它演示了如何将Excel文件中的数据转换为JSON格式： import java.io.File; import java.io.FileInputStream; import java.io.IOException;...
java poi将Excel中数据转成json
qq_34797933的博客
04-26 1826
在maven项目中引入相应的jar 包，poi 和json <!-- alibaba fastjson--> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId&...
excel to json java_java 读取 excel 转 json
weixin_28676983的博客
03-04 232
import com.alibaba.fastjson.JSONArray;import com.alibaba.fastjson.JSONObject;import org.apache.commons.lang3.StringUtils;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel....
读取xls文件，并以json的形式返回至前端（postman形式进行导入范文）
qq_34302802的博客
04-12 677
1、xls文件范文 2、postman部分 3、代码部分 import java.io.*; import java.util.*; import com.newcsp.security.annotation.SysLog; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HS.
java表头表体动态返回_JAVA LIST 简单分页 集成EXT4 grid表头动态返回
weixin_39587238的博客
02-28 517
用于小数据量简单分页package com.utill.tools;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** JAVA 分页* @author long*/public class PageTools{private static int p...
JAVA读取xls文件和xlsx文件
mingtianhaiyouwo的博客
06-06 1207
xls和xlsx文件：office excel2007以后版本为xlsx,以前的版本为xls
Vue+elementui 纯前端实现Excel导入导出功能（区分表头标题）
朝花夕拾的博客
11-02 1951
设置中英文映射 就是字段对应。解析获取到表头的数据。
java读取excel表格转为JSON数据
04-11
好的，我可以回答这个问题。您可以使用第三方库，如Apache POI，读取Excel表格并将其转化为JSON数据。以下是Java代码示例： ```java // 导入依赖 import org.apache.poi.ss.usermodel.*; import org.json.*; import java.io.*; import java.util.*; public class ExcelToJsonConverter { public static void main(String[] args) throws Exception { // 读取Excel文件 Workbook workbook = WorkbookFactory.create(new File("data.xlsx")); // 读取第一个工作表 Sheet sheet = workbook.getSheetAt(0); // 获取表头行 Row headerRow = sheet.getRow(0); // 遍历每一行 JSONArray rows = new JSONArray(); for (int i = 1; i < sheet.getLastRowNum() + 1; i++) { Row row = sheet.getRow(i); JSONObject obj = new JSONObject(); // 遍历每一列 for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); String columnName = headerRow.getCell(j).getStringCellValue(); obj.put(columnName, getCellValue(cell)); } rows.put(obj); } // 输出JSON数据 System.out.println(rows.toString()); } // 获取单元格的值 private static Object getCellValue(Cell cell) { switch (cell.getCellType()) { case BLANK: return null; case BOOLEAN: return cell.getBooleanCellValue(); case ERROR: throw new RuntimeException("Cell contains an error"); case NUMERIC: return cell.getNumericCellValue(); case STRING: return cell.getStringCellValue(); case FORMULA: return getCellValue(cell.getCachedFormulaResultType() == CellType.NUMERIC ? cell : cell.getSheet().getRow(cell.getRowIndex()).getCell(cell.getColumnIndex())); default: throw new RuntimeException("Cell type not supported"); } } } ``` 请注意，此代码示例仅适用于Excel表格中的简单数据类型，如字符串、数字和布尔值。对于复杂数据类型，如日期和时间，您需要编写自定义的转换逻辑。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值