SpreadJS V14 Update1 新特性 - 公式函数增强 - FILTERJSON 函数
本帖最后由 lynn512 于 2021-5-10 12:13 编辑SpreadJS V14 Update1新增了FILTERJSON函数，可以将有效的json字符串解析为值、对象或对象数组。（允许垂直溢出）
语法：
=FILTERJSON(json_string)
参数：
参数
描述
json_string
Required
The invalid JSON data.
注意：1、用户需要先启动动态数组相关功能，才能使用FILTERJSON函数返回一个对象数组。2、如果json字符串数据格式无效，则返回 #VALUE! 错误。
示例函数计算结果：
JSONString
Formula
Result
123=FILTERJSON(123)
"123"=FILTERJSON("123")
''=FILTERJSON("")
=FILTERJSON()
{a: 123}=FILTERJSON({a: 123})
'{a: 123}'=FILTERJSON("{a: 123}")
'{"a": 123}'=FILTERJSON("{""a"": 123}")
"{'a': 123}"=FILTERJSON(A1)
=FILTERJSON(A1)
''=FILTERJSON("")
'[
{"a": 1},
{"b": "2"},
{"c": [
{"c1": "21"},
{"c2": "22"}
]},
"string",
123
]'
=FILTERJSON("[
{""a"": 1},
{""b"": ""2""},
{""c"": [
{""c1"": ""21""},
{""c2"": 22}
]},
""string"",
123
]")
示例代码：
var json_string = `{
"store": {
"book": [
{
"category": "reference",
"author": "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{
"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 12.99
},
{
"category": "fiction",
"author": "Herman Melville",
"title": "Moby Dick",
"isbn": "0-553-21311-3",
"price": 8.99
},
{
"category": "fiction",
"author": "J. R. R. Tolkien",
"title": "The Lord of the Rings",
"isbn": "0-395-19395-8",
"price": 22.99
}
],
"bicycle": {
"color": "red",
"price": 19.95
}
},
"expensive": 10
}`;
sheet.setValue(0,0,json_string);
sheet.setFormula(1,0,'=FILTERJSON(A1)');
sheet.setFormula(2,0,'=PROPERTY(A2,"store.book.0.title")'); // "Sayings of the Century"计算结果如下图所示：
页:
[1]