如何用JavaScript将JSON二维数组及嵌套属性转为Excel新列

DevOps 小满

2026-7-5

问题描述

我需要开发一个基于JavaScript的Web应用，用于将JSON文件转换为Excel文件，要求如下：

JSON中的普通属性值各自对应Excel的一列；
对于二维数组或嵌套JSON类型的属性，其子属性需在Excel中生成新列或行。

以下是JSON文件片段：

{
  "name": "processingPriority",
  "type": [
    "null",
    "string"
  ],
  "doc": "",
  "default": null,
  "since": "1.0"
},
{
  "name": "flags",
  "type": [
    "null",
    {
      "type": "array",
      "items": "string",
      "java-class": "java.util.List"
    }
  ],
  "doc": "Mandatory. Empty list allowed, see below for allowed values, List of <<TransactionFlags, allowed ENUM values>>",
  "default": null,
  "since": "1.0"
},
{
  "name": "customerData",
  "type": [
    "null",
    "string"
  ],
  "doc": "",
  "default": null,
  "since": "1.0"
},
{
  "name": "account",
  "type": [
    "null",
    {
      "type": "record",
      "name": "TransactionAccount",
      "fields": [
        {
          "name": "id",
          "type": [
            "null",
            "string"
          ],
          "doc": "Mandatory. Unique ID (needs to match the one used by WebAPI)",
          "default": null,
          "since": "1.0"
        }
      ]
    }
  ]
}

我已通过以下代码生成Excel文件，但希望为嵌套属性的值在Excel的type列旁新增一列，展示嵌套类型的具体信息，请问如何修改代码？

现有代码：

// ... my existing code

function downloadAsExcel() {
    // ... my existing code

    function getTypeAsString(type, parentName = '') {
        if (Array.isArray(type)) {
            return type.map(t => getTypeAsString(t)).join(', ');
        } else if (typeof type === 'object' && type !== null) {
            if (type.type === 'record' && Array.isArray(type.fields)) {
                const fields = type.fields.map(field => {
                    const fieldName = field.name;
                    const fieldComments = field.doc ? ` (${field.doc})` : '';
                    return `${parentName}.${fieldName}${fieldComments}:${getTypeAsString(field.type, `${parentName}.${fieldName}`)}`;
                }).join(', ');
                return `{${fields}}`;
            } else {
                const objKeys = Object.keys(type).map(key => {
                    if (typeof type[key] === 'object' && type[key] !== null) {
                        return `${key}:${getTypeAsString(type[key], `${parentName}.${key}`)}`;
                    } else {
                        return `${key}:${type[key]}`;
                    }
                }).join(', ');
                return `{${objKeys}}`;
            }
        } else {
            return typeof type === 'undefined' ? 'null' : type.toString();
        }
    }

    function processNestedAttributes(fields, nestedLevel = 0, parentName = '') {
        let nestedRows = [];

        fields.forEach(field => {
            const typeValue = getTypeAsString(field.type, `${parentName}.${field.name}`);

            if (typeof field.type === 'object' && field.type !== null && field.type.type === 'record' && Array.isArray(field.type.fields)) {
                field.type = `{Nested Table}`;
                nestedRows.push([field.name, 'null', typeValue, field.doc, field.default, field.since, nestedLevel]);
                const nestedFieldRows = processNestedAttributes(field.type.fields, nestedLevel + 1, `${parentName}.${field.name}`);
                nestedRows = nestedRows.concat(nestedFieldRows);
            } else {
                if (typeValue === 'null') {
                    nestedRows.push([field.name, typeValue, '', field.doc, field.default, field.since, nestedLevel]);
                } else {
                    nestedRows.push([field.name, 'other', typeValue, field.doc, field.default, field.since, nestedLevel]);
                }
            }
        });

        return nestedRows;
    }

    for (var i = 0; i < jsonContent.fields.length; i++) {
        const field = jsonContent.fields[i];

        if (typeof field.type === 'object' && field.type !== null && field.type.type === 'record' && Array.isArray(field.type.fields)) {
            field.type = `{Nested Table}`;
            dataRows.push([field.name, 'null', getTypeAsString(field.type), field.doc, field.default, field.since, 0]);
            const nestedRows = processNestedAttributes(field.type.fields, 1, field.name);
            dataRows = dataRows.concat(nestedRows);
        } else {
            const typeValue = getTypeAsString(field.type);
            if (typeValue === 'null') {
                dataRows.push([field.name, typeValue, '', field.doc, field.default, field.since, 0]);
            } else {
                dataRows.push([field.name, 'other', typeValue, field.doc, field.default, field.since, 0]);
            }
        }
    }

    // ... my existing code
}
// ... my existing code

修改方案

核心思路是拆分类型信息为基础类型和嵌套详情，新增列存放嵌套内容，同时更新所有行生成逻辑：

以下是修改后的完整代码：

// ... my existing code

function downloadAsExcel() {
    // ... my existing code

    // 新增：解析类型，同时返回基础类型和嵌套详情
    function parseType(type, parentName = '') {
        let baseType = '';
        let nestedDetails = '';

        if (Array.isArray(type)) {
            baseType = type.join(', ');
            const nonNullTypes = type.filter(t => t !== 'null');
            if (nonNullTypes.length > 0) {
                nestedDetails = nonNullTypes.map(t => {
                    if (typeof t === 'object' && t !== null) {
                        if (t.type === 'record' && Array.isArray(t.fields)) {
                            const fields = t.fields.map(field => {
                                const fieldComments = field.doc ? ` (${field.doc})` : '';
                                return `${parentName}.${field.name}${fieldComments}:${parseType(field.type, `${parentName}.${field.name}`).baseType}`;
                            }).join(', ');
                            return `{${fields}}`;
                        } else if (t.type === 'array') {
                            return `array<${parseType(t.items, parentName).baseType}>`;
                        } else {
                            const objKeys = Object.keys(t).map(key => `${key}:${t[key]}`).join(', ');
                            return `{${objKeys}}`;
                        }
                    }
                    return t;
                }).join(', ');
            }
        } else if (typeof type === 'object' && type !== null) {
            if (type.type === 'record' && Array.isArray(type.fields)) {
                baseType = 'record';
                const fields = type.fields.map(field => {
                    const fieldComments = field.doc ? ` (${field.doc})` : '';
                    return `${parentName}.${field.name}${fieldComments}:${parseType(field.type, `${parentName}.${field.name}`).baseType}`;
                }).join(', ');
                nestedDetails = `{${fields}}`;
            } else if (type.type === 'array') {
                baseType = 'array';
                nestedDetails = parseType(type.items, parentName).baseType;
            } else {
                baseType = 'object';
                nestedDetails = Object.keys(type).map(key => `${key}:${type[key]}`).join(', ');
            }
        } else {
            baseType = typeof type === 'undefined' ? 'null' : type.toString();
        }

        return { baseType, nestedDetails };
    }

    function processNestedAttributes(fields, nestedLevel = 0, parentName = '') {
        let nestedRows = [];

        fields.forEach(field => {
            const { baseType, nestedDetails } = parseType(field.type, `${parentName}.${field.name}`);

            if (typeof field.type === 'object' && field.type !== null && field.type.type === 'record' && Array.isArray(field.type.fields)) {
                nestedRows.push([
                    field.name, 
                    baseType, 
                    nestedDetails,
                    field.doc, 
                    field.default, 
                    field.since, 
                    nestedLevel
                ]);
                const nestedFieldRows = processNestedAttributes(field.type.fields, nestedLevel + 1, `${parentName}.${field.name}`);
                nestedRows = nestedRows.concat(nestedFieldRows);
            } else {
                nestedRows.push([
                    field.name, 
                    baseType, 
                    nestedDetails || '',
                    field.doc, 
                    field.default, 
                    field.since, 
                    nestedLevel
                ]);
            }
        });

        return nestedRows;
    }

    for (var i = 0; i < jsonContent.fields.length; i++) {
        const field = jsonContent.fields[i];
        const { baseType, nestedDetails } = parseType(field.type, field.name);

        if (typeof field.type === 'object' && field.type !== null && field.type.type === 'record' && Array.isArray(field.type.fields)) {
            dataRows.push([
                field.name, 
                baseType, 
                nestedDetails,
                field.doc, 
                field.default, 
                field.since, 
                0
            ]);
            const nestedRows = processNestedAttributes(field.type.fields, 1, field.name);
            dataRows = dataRows.concat(nestedRows);
        } else {
            dataRows.push([
                field.name, 
                baseType, 
                nestedDetails || '',
                field.doc, 
                field.default, 
                field.since, 
                0
            ]);
        }
    }

    // 同步更新Excel表头，新增一列（示例命名为"Nested Type Details"）
    // 示例：headers = ["Name", "Type", "Nested Type Details", "Doc", "Default", "Since", "Level"];

    // ... my existing code
}
// ... my existing code

修改说明

替换原getTypeAsString为parseType，同时返回基础类型和嵌套详情，实现类型信息拆分；
所有生成数据行的逻辑中新增一列，专门存放嵌套类型的具体结构；
需同步修改Excel表头，新增对应列名（可根据需求调整名称）；
针对数组、record等嵌套类型，单独提取结构化信息放入新增列，让表格层级更清晰。

内容的提问来源于stack exchange，提问作者Samira

如何用JavaScript将JSON二维数组及嵌套属性转为Excel新列

修改说明 #

修改说明