Excel 到各种 JSON 对象

发布于 2024-10-14 22:19:29 字数 1055 浏览 8 评论 0原文

我有这个 Excel 表格：

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 格式。或者：

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

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

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

如何轻松地从 Excel 转换为我喜欢的 JSON 格式？

请建议这种转换的具体方法以及通用数据转换工具，例如优秀的 Mr Data Converter 和 Google 优化。

谢谢你！

原文

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

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

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

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 : [...]
   },
]

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

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

Thank you!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

七七 2024-10-21 22:19:29

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

使用这些类，此代码

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

就可以从您的数据生成此代码。

{  "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 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'.

Using these classes, this code

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"
  }
}

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

回复收藏 0 原文