Xlsx转Json(JS Object/Array) Javascript/Typescript版本

FrSyrup

已于 2022-05-31 15:53:41 修改

阅读量4.7k

点赞数

分类专栏：算法文章标签： javascript typescript json

于 2021-12-18 18:30:11 首次发布

本文链接：https://blog.csdn.net/Yishionwang/article/details/122015212

版权

算法专栏收录该内容

12 篇文章 0 订阅

订阅专栏

源起

由于游戏策划异常偏爱用excel编写数据。很多数据可以用二维表的形式处理。但是还有一些数据更方便用树形结构存储。如果同时能也写在excel中，就可以方便双方的协作。
之前在网络上找到过一些xlsx还原为json的代码，但是大都为了各自的需求而设计。且很难表示出复杂的属性结构。比如无穷深度的object和array层叠互套。为此，我制定了一个规则，可以实现上面的需求。
这里描述流程使用CocosCreator 2.6.6。但实际转换核心与引擎无关。

基础

xlsx首先经过预处理，导出为原始json。这个过程使用npm上面的 node-xlsx简单完成。我只处理之后的解析操作。游戏中使用自己的方式读这个json即可。具体我写成了一个packages，在creator工具栏里进行这一步的导原始json。
数据表如下：（测试各种转换情况，没有实际意义，因此看起来有点夸张）
在这里插入图片描述

规则说明

左格是右格的键
右格是左格的值
键格以点结尾，值为对象（非数组），键自动去除点
键以减号结尾，值为数组，键自动去除减号
左格为空，相当于沿用上面的键
数组对应的右格只有值
数组中嵌套子对象或者数组同样使用点或减号并忽略到具体键值【如27,29行】
相同的键会被合并【如2,18行】【7,13,16,17行】
空的一整行会被忽略

node-xlsx的使用

let xlsx = require('node-xlsx');

//... key是文件名
let srcFile = path.normalize(`${__dirname}/../../GameDataDesign/${key}.xlsx`);
let tarFile = path.normalize(`${__dirname}/../../assets/${key}.json`);
Editor.log('开始导入数据', srcFile, '->', tarFile);

const workSheetsFromFile = xlsx.parse(srcFile);
let book = workSheetsFromFile.filter(_ => !_.name.startsWith('__'));
let jsonText = JSON.stringify(book);

 阻止导出注释：首行双下划线“__”所在的列，和行中格以双斜线“//”开头的后面的格（包含）
if (['Sheet1'].includes(key)) { // 只针对特定的表应用此特性，可以按需要进行修改
	for (let i = 0; i < book.length; i++) {
	    let sheet = book[i];
	    let firstRow = sheet.data[0];
	    let keepFlag = firstRow.map(_ => {
	        if (typeof (_) == 'string') {
	            return !_.startsWith('__');
	        }
	        return true;
	    });
	    let data = [];
	    for (let j = 0; j < sheet.data.length; j++) {
	        let row = sheet.data[j];
	        if (row != null && row.length > 0 && typeof (row[0]) == 'string' && row[0].startsWith(`//`)) continue;
	        let leanRow = [];
	        for (let k = 0; k < row.length; k++) {
	            let value = row[k];
	            if (typeof (value) == 'string' && value.startsWith(`//`)) break;
	            if (keepFlag[k] != null && !keepFlag[k]) {
	            } else {
	                leanRow.push(value);
	            }
	        }
	        // sheet.data[j] = leanRow;
	        data.push(leanRow);
	    }
	    sheet.data = data;
	}
}


Editor.log('jsonText', jsonText);

let str = Editor.assetdb.exists(`db://assets/${key}.json`);
if (str) {
    Editor.assetdb.saveExists(`db://assets/${key}.json`, jsonText, function (err, results) { });
} else {
    Editor.assetdb.create(`db://assets/${key}.json`, jsonText, function (err, results) { });
}

这里只用这其中的一个vars表做说明，node-xlsx处理之后的结果JSON形式：

[
  {
    "name": "vars",
    "data": [
      ["version", 1],
      ["daily.", "refreshAt", 0],
      ["season.", "refreshAt", 0.08333333333333333],
      ["achievement.", "key1", "a"],
      [null, "key1b", "b"],
      [null, "key2", 100],
      [null, "key3-", 1],
      [null, null, 2],
      [null, null, 3],
      [],
      [null, null, 4],
      [null, null, 5],
      [null, "key3-", 6],
      [null, "key.", "c", 5],
      [null, null, "d", 6],
      [null, "key3-", 7],
      [null, "key3-", 8],
      ["daily.", 10, "a"],
      [null, 20, "b"],
      [null, "key2", true],
      [null, "key3", 44548.73535729167],
      [null, "key4.", "a", 1],
      [null, null, "b", 2],
      [null, null, "c-", 10],
      [null, null, null, 20],
      [null, null, null, 30],
      [null, null, null, "a.", "key", "value"],
      [null, null, null, null, "key2", "value2"],
      [null, null, null, "b-", 1],
      [null, null, null, null, 2],
      [null, null, null, null, 3]
    ]
  },
//... 其它的数据表
]

转换函数，单文件

下面这个ts文件就可以把代码拷走使用。

// JsonFromNodeXlsxTools.ts
// write by Ethan @2021.12.18

export function jsonObjectTree(data: any[][], r = 0, l = 0) {
    let output = {};
    makeObj(0, 0, output, data);
    return output;
}

export function jsonArrayTree(data: any[][], r = 0, l = 0) {
    let output = [];
    makeArray(0, 0, output, data);
    return output;
}

function isDirectArrayKey(value) {
    if (typeof (value) == 'string') {
        let v = value.trim();
        if (v.startsWith('[') && v.endsWith(']')) {
            return true;
        }
    }
    return false;
}

function isArrayKey(value) {
    if (typeof (value) == 'string') {
        if (value.endsWith('-')) return true;
    }
    return false;
}

function isObjectKey(value) {
    if (typeof (value) == 'string') {
        if (value.endsWith('.')) return true;
    }
    return false;
}

function isDate(key) {
    if (typeof (key) == 'string') {
        if (key.endsWith('<Date>')) return true;
    }
    return false;
}

// 要确保输入的key是需要leankey操作的再使用该函数
function leanKey(key: string) {
    if (key.endsWith('<Date>')) return key.substring(0, key.length - 6);
    return key.substring(0, key.length - 1);
}

function parseDate(raw: number): Date {
    let oneDay = 86400000;
    let msOfTheDay = raw * oneDay;
    let date = new Date(msOfTheDay);
    date.setUTCFullYear(date.getUTCFullYear() - 70);
    date.setUTCDate(date.getUTCDate() - 2);
    return date;
}

function parseDirectArray(raw: string): any[] {
    if (isDirectArrayKey(raw)) {
        let trimed = raw.trim();
        let content = trimed.substring(1, trimed.length - 1);
        let item = content.split(',');
        let result: any[] = item.map(_ => {
            if (isNaN(Number(_))) {
                return _;
            }
            return Number(_);
        });
        return result;
    }
}

function makeObj(r: number, l: number, obj: {}, data: any[][]) {
    for (let pr = r; pr < data.length; pr++) {
        let row = data[pr];
        // console.log('jfn', r, pr, row);

        // 判断已经结束了，递归末端
        let end = false;
        if (pr > r) {
            for (let _l = l - 1; _l >= 0; _l--) {
                let _v = row[_l];
                if (pr == r && _l == l - 1) continue;
                if (_v != null) end = true;
            }
        }
        if (end) break;

        // 内层递归用到的行，在本层是空行，略过
        let value = row[l];
        if (value == null) continue;

        if (isObjectKey(value)) {
            value = leanKey(value);
            if (value == 'w') {
                this.log('value', value);
            }
            obj[value] ?? (obj[value] = {});
            makeObj(pr, l + 1, obj[value], data);
        } else if (isArrayKey(value)) {
            value = leanKey(value);
            obj[value] ?? (obj[value] = []);
            makeArray(pr, l + 1, obj[value], data);
        } else if (isDirectArrayKey(value2)) {
            obj[value] = parseDirectArray(value2);
        } else if (isDate(value)) {
            value = leanKey(value);
            let vov = row[l + 1];
            let date = parseDate(vov);
            obj[value] = date;
        } else {
            let vov = row[l + 1];
            obj[value] = vov;
        }
    }
};

function makeArray(r: number, l: number, obj: any[], data: any[][]) {
    for (let pr = r; pr < data.length; pr++) {
        let row = data[pr];

        // 判断已经结束了，递归末端
        let end = false;
        if (pr > r) {
            for (let _l = l - 1; _l >= 0; _l--) {
                let _v = row[_l];
                if (pr == r && _l == l - 1) continue;
                if (_v != null) end = true;
            }
        }
        if (end) break;

        // 内层递归用到的行，在本层是空行，略过
        let value = row[l];
        if (value == null) continue;

        if (isObjectKey(value)) {
            let _obj = {};
            obj.push(_obj);
            makeObj(pr, l + 1, _obj, data);
        } else if (isArrayKey(value)) {
            let array = [];
            obj.push(array);
            makeArray(pr, l + 1, array, data);
        } else if (isDirectArrayKey(value2)) {
            obj[value] = parseDirectArray(value2);
        } else {
            obj.push(value)
        }
    }
}

调用方法和结果

import { jsonArrayTree, jsonObjectTree } from "../Basic/JsonFromNodeXlsxTools";
// ... sheets对应整个工作簿，此时只处理vars表
sheets.forEach(sheet => {
    if (sheet.name == 'vars') {
        output[sheet.name] = jsonObjectTree(sheet.data);
    }
});

这个是结果：

{
  "version": 1,
  "daily": {
    "10": "a",
    "20": "b",
    "refreshAt": 0,
    "key2": true,
    "key3": 44548.73535729167,
    "key4": {
      "a": 1,
      "b": 2,
      "c": [10, 20, 30, { "key": "value", "key2": "value2" }, [1, 2, 3]]
    }
  },
  "season": { "refreshAt": 0.08333333333333333 },
  "achievement": {
    "key1": "a",
    "key1b": "b",
    "key2": 100,
    "key3": [1, 2, 3, 4, 5, 6, 7, 8],
    "key": { "c": 5, "d": 6 }
  }
}

问题修正

fixed: 对象第一个键也对象的则内部对象丢失在这里插入图片描述

简易数组行

在之前，数组在xlsx文件中需要纵向扩展为多行。但在很多情况下，数组都是末端节点，构成很简单，比如并列的一排数字，字符串等等，如果还需要写多行，那么可读性较差。为了克服这个问题，增加了支持简易数组行的特性。
使用简易数组行示例
之前，想要一个数组，那么一定在key单元格后面写个’-'减号。而现在，如果后面接简易数组行，就直接在key后面写这个中括号的数组即可。另外，如果是字符串数组，则省略引号。如上图的[ta2,ta3]
特性已经改入上面贴出的代码，关键词：DirectArray。