基于node的excel转换json工具（支持嵌套）

1.支持表格转成常规json对象

网页上的工具也可以实现同样功能

image.png
转换结果：

[
    {
        "province": "安徽",
        "city": "蚌埠",
        "county": "区1"
    },
    {
        "province": "安徽",
        "city": "阜阳",
        "county": "区1"
    },
    {
        "province": "安徽",
        "city": "阜阳",
        "county": "区2"
    },
    {
        "province": "安徽",
        "city": "合肥",
        "county": "区1"
    },
    {
        "province": "安徽",
        "city": "合肥",
        "county": "区2"
    }
]

2.支持转成嵌套json对象（数据联动）

image.png

转换结果：

[
    {
        "title": "安徽",
        "children": [
            {
                "title": "蚌埠",
                "s": 10,
                "children": [
                    {
                        "title": "区1"
                    }
                ]
            },
            {
                "title": "阜阳",
                "s": 10,
                "children": [
                    {
                        "title": "区1"
                    },
                    {
                        "title": "区2"
                    }
                ]
            },
            {
                "title": "合肥",
                "s": 103,
                "children": [
                    {
                        "title": "区1"
                    },
                    {
                        "title": "区2"
                    }
                ]
            }
        ]
    }
]

1. 注意表格需要合并的子级前要加一列children
2. 代表当前级别的表头应为title（后期可以优化）

代码为：

const xlsx = require('node-xlsx');
const fs = require('fs');
const allData = xlsx.parse('./a1.xlsx');

allData.forEach(data => {
  const filename = data.name;
  const lists = data.data;
  const arr = lists.shift();
  const newArray = [];
  lists.forEach(list => {
    let objList = [];
    let childIndex = [];
    arr.forEach((item, i) => {
      if (item === 'children') {
        childIndex.push(i + 1);
        objList[i + 1] = {};
      }
    })
    objList.push({});
    let i = 0;
    arr.forEach((item, index) => {
      if (index < childIndex[i]) {
        isChildren(objList[childIndex[i]], item, list, index);
      } else if (index === childIndex[i]) {
        i++;
        if (childIndex[i]) {
          isChildren(objList[childIndex[i]], item, list, index);
        } else {
          objList[objList.length - 1][item] = list[index] ? list[index] : "";
        }
      } else {
        objList[objList.length - 1][item] = list[index] ? list[index] : "";
      }
    })
    objList = objList.filter(item => item)
    let cur=objList.reduceRight((pre, next, index, inp) => {
      if (next && next.children) {
        next.children.push(pre);
      }
      return next;
    })
    newArray.push(cur);
    // console.log(newArray)
  })
  compare(newArray)
  childrenNull(newArray)
  writeFile(`${filename}.json`, JSON.stringify(newArray));
})

//compare
function compare(newArray) {
  if (newArray.length) {
    compareItem(newArray);
    newArray.length && newArray.forEach(item => {
      if (item.children) {
        compare(item.children)
      }
    })
  }
}

function compareItem(item) {
  for (let i = 0, len = item.length - 1; i < len; i++) {
    for (let j = item.length - 1; j > i; j--) {
      if (item[i].title === item[j].title) {
        item[j].children && item[j].children.forEach(n => {
          item[i].children.unshift(n);
          item.splice(j, 1);
        })
      }
    }
  }
  item.map(child => child.children && child.children.unshift(child.children.pop()))
}

function isChildren(obj, item, itm, index) {
  if (item === "children") {
    obj[item] = [];
  } else {
    obj[item] = itm[index] ? itm[index] : "";
  }
  return obj;
}

function childrenNull(newArray) {
  newArray.forEach(item => {
    if (item.children && item.children[0].title === "") {
      item.children = [];
    } else {
      item.children && item.children.forEach(itm => {
        if (itm.children && itm.children[0].title === "") {
          itm.children = [];
        }
      })
    }
  })
}

function writeFile(filename, data) {
  fs.writeFileSync(filename, data, 'utf-8', complete);
  function complete(err) {
    if (!err) {
      console.log('文件生成成功')
    }
  }
}

安装好包之后，根据需求调整一下表格，右键run code即可生成json文件。