时间戳

首页 > 解决方案 > 如何在 Excel 的下一列中填充 json 解析结果？

json - 如何在 Excel 的下一列中填充 json 解析结果？

问题描述

我试图将 json 文件转换为 Excel。

我从以下位置获得模块：https ://github.com/VBA-tools/VBA-JSON/releases

我的代码：

Private Sub CommandButton1_Click()
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Title = "Select a JSON File"
        .AllowMultiSelect = False
        If .Show() Then
            Filename = .SelectedItems(1)
            Dim content As String
            Dim iFile As Integer: iFile = FreeFile
            Open Filename For Input As #iFile
            content = Input(LOF(iFile), iFile)
            ' Parse JSON String
            Dim lastCol As Long
            lastCol = Cells(Columns.Count, 1).End(xlDown).Column
            Dim dummyData As Object
            Set dummyData = JsonConverter.ParseJson(content)
            i = lastCol + 1
            For Each dummyDatas In dummyData
                Cells(i, 1) = dummyDatas("perusahaan")
                Cells(i, 2) = dummyDatas("nama1")
                Cells(i, 3) = dummyDatas("email1")
                Cells(i, 4) = dummyDatas("nama2")
                Cells(i, 5) = dummyDatas("email2")
                Cells(i, 6) = dummyDatas("nama3")
                Cells(i, 7) = dummyDatas("email3")

                i = i + 1
                Next
            Close #iFile
        End If
    End With
End Sub

我的 JSON 文件：

    [
  {
    "perusahaan": "Indofood",
    "nama1": "afif",
    "email1": "afif@gmail.com",
    "nama2": "dino",
    "email2": "dino@gmail.com",
    "nama3": "dito",
    "email3": "dito@gmail.com"
  },
  {
    "perusahaan": "Garuda",
    "nama1": "alif",
    "email1": "alif@gmail.com",
    "nama2": "dini",
    "email2": "dini@gmail.com",
    "nama3": "diti",
    "email3": "diti@gmail.com"
  },
  {
    "perusahaan": "Unilever",
    "nama1": "arif",
    "email1": "arif@gmail.com",
    "nama2": "dina",
    "email2": "dina@gmail.com",
    "nama3": "dita",
    "email3": "dita@gmail.com"
  },
  {
    "perusahaan": "Wisikita",
    "nama1": "ajif",
    "email1": "ajif@gmail.com",
    "nama2": "dana",
    "email2": "dana@gmail.com",
    "nama3": "data",
    "email3": "data@gmail.com"
  }
]

最终结果是这样的 这个结果将始终从 A:1 列填充，而不是自动继续该列。
最终结果

如何输入json结果，结果会延续之前的列？

标签： jsonexcelvba

解决方案

您使用lastCol的好像是lastRow. 如果您在整个方法中重命名变量并将lastCol = ...行更改为此

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

您将从第一个空行开始填充。

推荐阅读