excel通过宏来导出json数据

最新推荐文章于 2025-02-21 16:36:06 发布

lg_lin

最新推荐文章于 2025-02-21 16:36:06 发布

阅读量3.3k

点赞数

分类专栏： Excel - 宏

本文链接：https://blog.youkuaiyun.com/lg_lin/article/details/17438779

版权

Excel - 宏专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用VBA宏将Excel数据转换并导出为JSON格式的方法。通过简单的宏按钮实现，适用于批量导出数据的需求。文章提供了完整的VBA代码示例，包括设置文件路径、定义数据范围及格式化输出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Sub 导出JSON()

    Dim s As String
    Dim FullName As String, rng As Range
    Dim row As Integer, column As Integer, maxrow As Integer
    Dim strContent As String
    Application.ScreenUpdating = False
    
    FullName = (ThisWorkbook.Path & "/" & Cells(1, 2) & ".txt") ' Cells(1, 2) 表示第1行，第二列的内容

    '配置区域
    proline = 5 '程序名定义行
    begindatalen = 6 '数据起始行
    row = ActiveSheet.UsedRange.Rows.Count '最大行
    column = ActiveSheet.UsedRange.Columns.Count '最大列

    strContent = "{" & """" & Cells(1, 2) & """" & ":["  'json组成部分
    For m = begindatalen To row
        If Cells(m, 1) <> "" Then
            
            strContent = strContent & "{"
            
            For n = 1 To column
                If n = column Then
                    strContent = strContent & """" & Cells(proline, n) & """" & ":" & """" & Cells(m, n) & """"
                Else
                    strContent = strContent & """" & Cells(proline, n) & """" & ":" & """" & Cells(m, n) & """" & ","
                End If
            Next n
            
            If m = row Then
                
                strContent = strContent & "}"
                
            Else
                
                strContent = strContent & "},"
                
            End If
            

        End If
    Next m
    
    strContent = strContent & "]}"


    WriteUTF8File strContent, FullName, False
    Application.ScreenUpdating = True
    MsgBox "数据已导出"

End Sub

PS:在excel中插入一个宏安钮，然后编辑该宏代码即可。