Excel到各种JSON对象 [英] Excel to various JSON objects

问题描述

我有这个Excel表：

I have this Excel table:

country     year    1       2       3       4

Netherlands 1970    3603    4330    5080    5820
Netherlands 1971    3436    4165    4929    5693
Netherlands 1972    3384    4122    4899    5683
Sweden       1970    1479    1963    2520    3132
Sweden       1971    1497    1985    2547    3163
Sweden       1972    1419    1894    2445    3055

我想尝试两种JSON格式。或者：

I would like to experiment with two JSON formats. Either:

data = [
   Netherlands : {
      1970 : [3603, 4330, 5080, 5820],
      1971 : [...],
   },
   Sweden : {
      1970 : [...]
   },
]

或使用标题1,2,3,4作为x值：

Or using the headers 1,2,3,4 as x-values:

data = [
   Netherlands : {
      1970 : [{x: 1, y: 3603}, {x: 2, y: 4330}, {x: 3, y: 5080}, {x: 4, y: 5820}],
      1971 : [...],
   },
   Sweden : {
      1970 : [...]
   },
]

如何轻松地从Excel获取我的首选JSON格式？

How do I easily get from Excel to my preferred JSON formats?

请为此转换提供具体的方法以及通用的数据转换工具，如优秀的数据转换器和 Google Refine 。

Please suggest specific methods for this transformation as well as generic data conversion tools like the excellent Mr Data Converter and Google Refine.

谢谢！

推荐答案

您可以从这里下载一组将excel数据转换为JSON的类。 http://ramblings.mcpher.com/Home/excelquirks/downloadlist 。您想要的项目是数据操作类。

You can download a set of classes that convert excel data to JSON from here. http://ramblings.mcpher.com/Home/excelquirks/downloadlist. The project you want is 'Data manipulation classes'.

使用这些类，此代码

Option Explicit
Public Sub mainExample()
    Dim dSet As cDataSet

    Set dSet = New cDataSet
    With dSet
        .populateData Range("data!$a$1"), , , , , , True

        If .Where Is Nothing Then
            MsgBox ("No data to process")
        Else
            MsgBox .jSonObject
        End If
    End With

End Sub

是从您的数据生成这些所需的。

is all thats needed to produce this from your data.

{  "data": {
        "country": "Sweden",
        "year": "1972",
        "1": "1419",
        "2": "1894",
        "3": "2445",
        "4": "3055"
  }
}

在阅读本文后，您可以做更复杂的事情，或调整输出。 http://ramblings.mcpher.com/Home/excelquirks / recursionlink / hiding-data-in-excel-objects

You can do more complex things, or tailor the output, after reading this article on how it works. http://ramblings.mcpher.com/Home/excelquirks/recursionlink/hiding-data-in-excel-objects

bruce

这篇关于Excel到各种JSON对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！