json - excel转json表格形状
问题描述
我在需要更新的 json 文件中有一个数据集。数据是从 excel 加载项中收集的,因此我需要为我当前的 json 文件创建一个特定形状的 excel 表。将我的 excel 文件转换为 json 后,我应该如何让我的表格获取下面的 json 文件?
json文件:
{"AAL": {"year": [2012, 2013, 2014, 2015],
"eps": [-5.6, -11.25, 4.02, 11.39],
"revenue": [24855.0, 26743.0, 42650.0, 40990.0],
"op_revenue": [148.0, 1399.0, 4249.0, 6204.0]},
"AAP": {"year": [2012, 2013, 2014, 2015],
"eps": [5.29, 5.36, 6.75, 6.45],
"revenue": [6205.003000000001, 6493.814, 9843.860999999999, 9737.018],
"op_revenue": [657.315, 660.318, 851.71, 825.78]},
"AAPL": {"year": [2013, 2014, 2015, 2016],
"eps": [40.03, 6.49, 9.28, 8.35],
"revenue": [171000.0, 183000.0, 234000.0, 216000.0],
"op_revenue": [48999.0, 52503.0, 71230.0, 60024.0]},
"ABBV": {"year": [2013, 2014, 2015], "eps": [2.58, 1.11, 3.15], "revenue": [18790.0, 19960.0, 22859.0], "op_revenue": [5664.0, 3411.0, 7537.0]}, "ABC": {"year": [2013, 2014, 2015, 2016], "eps": [1.88, 1.22, -0.62, 6.73], "revenue": [87959.16699999999, 120000.0, 136000.0, 147000.0], "op_revenue": [898.3989999999999, 782.091, 422.22, 1525.774]}}
解决方案
为什么不使用这样的页面编辑 JSON 呢?顺便说一句,如果您喜欢它,请在投票下勾选接受按钮。使用很好的答案如何迭代表制作了这个片段:
var jsonEl = document.getElementsByName("data")[0];
function saveJSON() {
var table = document.getElementsByName("editor")[0].firstElementChild;
var header = ["Ticker"], comp = {};
for (var i = 0, row; row = table.rows[i]; i++) {
var companyTicker = row.cells[0].innerText;
for (var j = 1, col; col = row.cells[j]; j++) {
if (i) {
comp[companyTicker] = comp[companyTicker] || 0;
json[companyTicker][header[j]][comp[companyTicker]] = getNumber(col.firstElementChild.value);
} else {
header.push(col.innerText);
}
}
if(i) comp[companyTicker]++;
}
jsonEl.value = JSON.stringify(json, null, 2);
var el = document.getElementsByName("editor")[0];
el.removeChild(el.firstElementChild);
}
function buildTable(data) {
var table = [], header = ["Ticker"];
table.push(header);
for (var comp in data) {
var company = [];
company.push(comp);
for (var figures in data[comp]) {
var column = header.indexOf(figures);
if (column < 0) {
header.push(figures);
column = 0;
}
company.push(data[comp][figures]);
}
table.push(company);
}
var html = "<table border=1><tr><th>" + table[0].join("</th><th>") + "</th></tr>\r\n";
for (var row = 1; row < table.length; row++) {
var mainRow = table[row];
for (var subRow = 0; subRow < mainRow[1].length; subRow++) {
html += "<tr><th>" + mainRow[0] + "</th>";
for (var col = 1; col < mainRow.length; col++) {
html += "<td><input type=number value='";
html += mainRow[col][subRow];
html += "'></td>";
}
html += "</tr>\r\n";
mainRow = table[row];
}
}
html += "</table>\r\n"
return html;
}
function getNumber(str) {
return parseFloat(str);
}
function readData() {
json = JSON.parse(jsonEl.value);
var table = buildTable(json);
document.getElementsByName("editor")[0].innerHTML = table;
}
table {
border-spacing: 0px; /* small tricks 2 make rounded table simply or */
}
th {
text-align: left; /* centered looks ugly */
padding: 2px;
background-color: #F0F0F0;
}
td {
padding: 0px;
background-color: #F0FFF0;
}
input[type=number] {
margin: 0px;
background-color: transparent;
border-width:0px;
border:none;
}
<input type="button" value="BuildTable" onclick="readData()">
<input type="button" value="SaveJSON" onclick="saveJSON()"><br>
<span name=editor></span>
<textarea name="data" style="width: 100%; height: 200px;">{
"AAL": {
"year": [2012, 2013, 2014, 2015],
"eps": [-5.6, -11.25, 4.02, 11.39],
"revenue": [24855.0, 26743.0, 42650.0, 40990.0],
"op_revenue": [148.0, 1399.0, 4249.0, 6204.0]
},
"AAP": {
"year": [2012, 2013, 2014, 2015],
"eps": [5.29, 5.36, 6.75, 6.45],
"revenue": [6205.003000000001, 6493.814, 9843.860999999999, 9737.018],
"op_revenue": [657.315, 660.318, 851.71, 825.78]
},
"AAPL": {
"year": [2013, 2014, 2015, 2016],
"eps": [40.03, 6.49, 9.28, 8.35],
"revenue": [171000.0, 183000.0, 234000.0, 216000.0],
"op_revenue": [48999.0, 52503.0, 71230.0, 60024.0]
},
"ABBV": { "year": [2013, 2014, 2015], "eps": [2.58, 1.11, 3.15], "revenue": [18790.0, 19960.0, 22859.0], "op_revenue": [5664.0, 3411.0, 7537.0] }, "ABC": { "year": [2013, 2014, 2015, 2016], "eps": [1.88, 1.22, -0.62, 6.73], "revenue": [87959.16699999999, 120000.0, 136000.0, 147000.0], "op_revenue": [898.3989999999999, 782.091, 422.22, 1525.774] }
}</textarea>
在 XL(2010 年测试)中使用VBA-JSON
Sub Fill()
Dim JsonText As String
Dim Parsed As Dictionary
file_name = "YourFile.json"
my_file = FreeFile()
Open file_name For Input As my_file
i = 1
While Not EOF(my_file)
Line Input #my_file, text_line
JsonText = JsonText + text_line
i = i + 1
Wend
Set Parsed = JsonConverter.ParseJson(JsonText)
Dim ticker, third, v As Variant
Dim dict2, dict3 As Variant
Dim r, c, r2 As Integer
r = 2
For Each ticker In Parsed.Keys() ' AAL
Set dict2 = Parsed.Item(ticker)
ActiveSheet.Cells(r, 1).Value = ticker ' set ticker on first line
c = 2
For Each third In dict2.Keys() ' year
ActiveSheet.Cells(1, c).Value = third
Set dict3 = dict2.Item(third)
r2 = r
For Each v In dict3
ActiveSheet.Cells(r2, 1).Value = ticker ' repeat ticker on next lines
ActiveSheet.Cells(r2, c).Value = v
r2 = r2 + 1
Next v
c = c + 1
Next third
r = r2
Next ticker
'Sheets("example").Range(Cells(1, 1), Cells(Parsed("values").Count, 3)) = Values
End Sub
从specs\VBA-JSON - Specs.xlsm 开始
添加新工作表,使其保持活动状态并运行此宏。
这一行ActiveSheet.Cells(r2, 1).Value = ticker为下一行重复 ticker,为了简单起见,假设 3 级记录的顺序相同。
推荐阅读
- android - Android SDK:repositories.cfg 丢失
- javascript - 按下输入键时如何显示警报
- c# - 尽管已定义 WPF KeyDown 方法,但在类中找不到
- twilio - 我可以将自己的号码转发到 twilio 号码吗?
- java - 让 JavaFX 事件侦听器与我的方法对话
- nginx - nginx.conf 中的两个位置
- html - 如何在按钮单击时导航到下一页网站
- directx - DirectX 11 正面剔除不会剔除阴影贴图中的正面
- apache-spark - spark 2.2 无法处理聚合表达式中的映射列
- django - Django-rest-framework 使用 3 种内容类型如何管理渲染器和序列化器