javascript - 通过 Javascript 迭代 XERO API JSON 响应的行和单元格
问题描述
我正在使用应用程序脚本通过 API 获取 XERO 帐户系统的数据
但是我可以检索数据,结果是一个复杂的嵌套表结构。我需要以二维表(行和列)的形式编写这种复杂的结构
我无法迭代每个嵌套值。有没有办法获取行和列的每个可能的嵌套单元格值,并以二维数组的形式组织到解析的数据?
我试过了
<!DOCTYPE html>
<html>
<body>
<script>
var x = {
"Reports": [{
"ReportID": "TrialBalance",
"ReportName": "Trial Balance",
"ReportType": "TrialBalance",
"ReportTitles": [
"Trial Balance",
"Demo Company (NZ)",
"As at 30 August 2010"
],
"ReportDate": "21 February 2011",
"UpdatedDateUTC": "\/Date(1519357171249)\/",
"Rows": [{
"RowType": "Header",
"Cells": [{
"Value": "Account"
},
{
"Value": "Debit"
},
{
"Value": "Credit"
},
{
"Value": "YTD Debit"
},
{
"Value": "YTD Credit"
}
]
}, {
"RowType": "Section",
"Title": "Revenue",
"Rows": [{
"RowType": "Row",
"Cells": [{
"Value": "Interest Income (270)",
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
}, {
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
}, {
"Value": "0.00",
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
},
{
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
},
{
"Value": "500.00",
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
}
]
}, {
"RowType": "Row",
"Cells": [{
"Value": "Sales (200)",
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}, {
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}, {
"Value": "12180.25",
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}, {
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}, {
"Value": "20775.53",
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}]
}]
}, {
"RowType": "Section",
"Rows": [{
"RowType": "SummaryRow",
"Cells": [{
"Value": "Total"
},
{
"Value": "17447.02"
},
{
"Value": "17447.02"
},
{
"Value": "33459.76"
},
{
"Value": "33459.76"
}
]
}]
}]
}]
}
var r = x.Reports[0].Rows;
var n = r.length;
for(var i=0;i<n;i++)
{
if(r[i].hasOwnProperty("Cells"))
{
var c = r[i].Cells;
var row = []
for(var j=0;j<c.length;j++)
{
row.push([c[j].Value]);
}
console.log(row)
}
else
{
var rr = r[i].Rows
var nn = r[i].Rows.length
for(var k=0;k<nn;k++)
{
if(rr[k].hasOwnProperty("Cells"))
{
var cc = rr[k].Cells;
var rowrow = [];
for(var l=0;l<cc.length;l++)
{
rowrow.push([cc[l].Value]);
}
}
}
console.log(rowrow)
}
}
</script>
</body>
</html>
更新
根据 Tom 的建议,如何从下面的代码片段中获取 2d 数组?
function test(json) {
var it = new JIterator(json);
var i = 0;
console.log("DepthFirst traverse:");
do {
console.log(i++ + '\t' + it.Level + '\t' + it.Path().join('.') + '\t' + it.KeyDots(), (it.Value() instanceof Object) ? (it.Value() instanceof Array ? "[]" : "{}") : it.Value());
} while (it.DepthFirst());
}
解决方案
相当棘手的 JSON 和遍历,但是这个呢?
function demoPrint() {
var it = new JIterator(x());
var rows = [];
do {
if(it.Key() == "Cells") {
var col = [];
var returnLevel = it.Level;
it.DepthFirst();
it.DepthFirst();
var rowLevel = it.Level;
do {
if (it.Level == rowLevel) {
if (it.Key() == "Value") col.push(it.Value());
//else col.push("?"); // missing Value(s)
}
} while (it.DepthFirst() && it.Level > returnLevel)
rows.push(col);
while (it.Level != returnLevel && it.DepthFirst());
}
} while (it.DepthFirst());
var res = "<TABLE border=1>";
for(var i in rows) {
res += "<TR><TD>"
res += rows[i].join("</TD><TD>");
res += "</TD><TR>"
}
res += "</TABLE>";
document.body.innerHTML = res;
}
function x() {
return {
"Reports": [{
"ReportID": "TrialBalance",
"ReportName": "Trial Balance",
"ReportType": "TrialBalance",
"ReportTitles": [
"Trial Balance",
"Demo Company (NZ)",
"As at 30 August 2010"
],
"ReportDate": "21 February 2011",
"UpdatedDateUTC": "\/Date(1519357171249)\/",
"Rows": [{
"RowType": "Header",
"Cells": [{
"Value": "Account"
},
{
"Value": "Debit"
},
{
"Value": "Credit"
},
{
"Value": "YTD Debit"
},
{
"Value": "YTD Credit"
}
]
}, {
"RowType": "Section",
"Title": "Revenue",
"Rows": [{
"RowType": "Row",
"Cells": [{
"Value": "Interest Income (270)",
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
}, {
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
}, {
"Value": "0.00",
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
},
{
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
},
{
"Value": "500.00",
"Attributes": [{
"Value": "e9482110-7245-4a76-bfe2-14500495a076",
"Id": "account"
}]
}
]
}, {
"RowType": "Row",
"Cells": [{
"Value": "Sales (200)",
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}, {
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}, {
"Value": "12180.25",
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}, {
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}, {
"Value": "20775.53",
"Attributes": [{
"Value": "5040915e-8ce7-4177-8d08-fde416232f18",
"Id": "account"
}]
}]
}]
}, {
"RowType": "Section",
"Rows": [{
"RowType": "SummaryRow",
"Cells": [{
"Value": "Total"
},
{
"Value": "17447.02"
},
{
"Value": "17447.02"
},
{
"Value": "33459.76"
},
{
"Value": "33459.76"
}
]
}]
}]
}]
};
}
// https://github.com/eltomjan/ETEhomeTools/blob/master/HTM_HTA/JSON_Iterator_IIFE.js
'use strict';
var JNode = (function (jsNode) {
function JNode(_parent, _pred, _key, _value) {
this.parent = _parent;
this.pred = _pred;
this.node = null;
this.next = null;
this.key = _key;
this.value = _value;
}
JNode.prototype.HasOwnKey = function () { return this.key && (typeof this.key != "number"); }
JNode.prototype.HasStringValue = function () { return !(this.value instanceof Object); }
return JNode;
})();
var JIterator = (function (json) {
var root, current, maxLevel = -1;
function JIterator(json, parent) {
if (parent === undefined) parent = null;
var pred = null, localCurrent;
for (var child in json) {
var obj = json[child] instanceof Object;
if (json instanceof Array) child = parseInt(child); // non-associative array
if (!root) root = localCurrent = new JNode(parent, null, child, json[child]);
else {
localCurrent = new JNode(parent, pred, child, obj ? ((json[child] instanceof Array) ? [] : {}) : json[child]);
}
if (pred) pred.next = localCurrent;
if (parent && parent.node == null) parent.node = localCurrent;
pred = localCurrent;
if (obj) {
var memPred = pred;
JIterator(json[child], pred);
pred = memPred;
}
}
if (this) {
current = root;
this.Level = 0;
}
}
JIterator.prototype.Current = function () { return current; }
JIterator.prototype.SetCurrent = function (newCurrent) { current = newCurrent; }
JIterator.prototype.Parent = function () {
var retVal = current.parent;
if (retVal == null) return false;
this.Level--;
return current = retVal;
}
JIterator.prototype.Pred = function () {
var retVal = current.pred;
if (retVal == null) return false;
return current = retVal;
}
JIterator.prototype.Node = function () {
var retVal = current.node;
if (retVal == null) return false;
this.Level++;
return current = retVal;
}
JIterator.prototype.Next = function () {
var retVal = current.next;
if (retVal == null) return false;
return current = retVal;
}
JIterator.prototype.Key = function () { return current.key; }
JIterator.prototype.KeyDots = function () { return (typeof (current.key) == "number") ? "" : (current.key + ':'); }
JIterator.prototype.Value = function () { return current.value; }
JIterator.prototype.Reset = function () {
current = root;
this.Level = 0;
}
JIterator.prototype.RawPath = function () {
var steps = [], level = current;
do {
if (level != null && level.value instanceof Object) {
steps.push(level.key + (level.value instanceof Array ? "[]" : "{}"));
} else {
if (level != null) steps.push(level.key);
else break;
}
level = level.parent;
} while (level != null);
var retVal = "";
retVal = steps.reverse();
return retVal;
}
JIterator.prototype.Path = function () {
var steps = [], level = current;
do {
if (level != null && level.value instanceof Object) {
var size = 0;
var items = level.node;
if (typeof (level.key) == "number") steps.push('[' + level.key + ']');
else {
while (items) {
size++;
items = items.next;
}
var type = (level.value instanceof Array ? "[]" : "{}");
var prev = steps[steps.length - 1];
if (prev && prev[0] == '[') {
var last = prev.length - 1;
if (prev[last] == ']') {
last--;
if (!isNaN(prev.substr(1, last))) {
steps.pop();
size += '.' + prev.substr(1, last);
}
}
}
steps.push(level.key + type[0] + size + type[1]);
}
} else {
if (level != null) {
if (typeof (level.key) == "number") steps.push('[' + level.key + ']');
else steps.push(level.key);
}
else break;
}
level = level.parent;
} while (level != null);
var retVal = "";
retVal = steps.reverse();
return retVal;
}
JIterator.prototype.DepthFirst = function () {
if (current == null) return 0; // exit sign
if (current.node != null) {
current = current.node;
this.Level++;
if (maxLevel < this.Level) maxLevel = this.Level;
return 1; // moved down
} else if (current.next != null) {
current = current.next;
return 2; // moved right
} else {
while (current != null) {
if (current.next != null) {
current = current.next;
return 3; // returned up & moved next
}
this.Level--;
current = current.parent;
}
}
return 0; // exit sign
}
JIterator.prototype.BreadthFirst = function () {
if (current == null) return 0; // exit sign
if (current.next) {
current = current.next;
return 1; // moved right
} else if (current.parent) {
var level = this.Level, point = current;
while (this.DepthFirst() && level != this.Level);
if (current) return 2; // returned up & moved next
do {
this.Reset();
level++;
while (this.DepthFirst() && level != this.Level);
if (current) return 3; // returned up & moved next
} while (maxLevel >= level);
return current != null ? 3 : 0;
} else if (current.node) {
current = current.node;
return 3;
} else if (current.pred) {
while (current.pred) current = current.pred;
while (current && !current.node) current = current.next;
if (!current) return null;
else return this.DepthFirst();
}
}
JIterator.prototype.ReadArray = function () {
var retVal = {};
var item = current;
do {
if (item.value instanceof Object) {
if (item.value.length == 0) retVal[item.key] = item.node;
else retVal[item.key] = item;
} else retVal[item.key] = item.value;
item = item.next;
} while (item != null);
return retVal;
}
JIterator.prototype.FindKey = function (key) {
var pos = current;
while (current && current.key != key) this.DepthFirst();
if (current.key == key) {
var retVal = current;
current = pos;
return retVal;
} else {
current = pos;
return null;
}
}
return JIterator;
})();
demoPrint();
table {
border-spacing: 0px; /* small tricks 2 make rounded table simply or */
}
th {
text-align:left; /* centered looks ugly */
}
推荐阅读
- node.js - 如何使用颤振连接到nodejs服务器上的socket.io?
- microsoft-graph-api - 缺少频道电子邮件地址 Microsoft Teams API
- angular - 根据值更改范围滑块的颜色
- html - 如何使网格行占据全高直到达到最小尺寸?
- python - 如何检查是否在列表中输入
- amazon-web-services - AWS 读取 Greengrass 设备的 IoT Thing 连接状态
- nginx - 如何使用 nginx 反向代理托管 flask-restx swagger UI
- flutter - 使用 BottomNavigation Flutter 从嵌套路由内部修改 FAB
- ios - 为什么 UITextField 在输入字符串的末尾添加空格?
- google-cloud-platform - 如果 HTTP 请求被取消,云函数会完成吗?