Exporter des données depuis Excel vers un fichier JSON, Excel VBA

Exporter des données depuis Excel vers un fichier JSON, Excel VBA

Voici une explication détaillée et un code VBA pour exporter des données depuis Excel vers un fichier JSON.
Objectif
L’objectif est de créer une macro VBA qui lit les données d’une feuille de calcul Excel et les convertit en format JSON. Le format JSON (JavaScript Object Notation) est couramment utilisé pour stocker et transmettre des données de manière légère et lisible, ce qui le rend facile à utiliser pour les développeurs et les applications.
Étapes
1. Lire les données depuis Excel : Nous allons parcourir les lignes et les colonnes de la feuille Excel pour collecter les données.
2. Formater les données en JSON : Ensuite, nous allons formater ces données au format JSON.
3. Écrire le JSON dans un fichier : Enfin, nous allons exporter ces données JSON dans un fichier .json.
Code VBA
1. Configuration de base
Nous allons d’abord définir une sous-routine pour exporter les données.

Sub ExportDataToJSON()
    Dim ws As Worksheet
    Dim rng As Range
    Dim json As String
    Dim row As Range
    Dim cell As Range
    Dim colHeaders() As String
    Dim dataArray() As Variant
    Dim i As Integer
    Dim j As Integer
    ' Définir la feuille de travail à partir de laquelle les données seront exportées
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Ajuster le nom de la feuille si nécessaire
    ' Définir la plage de données à exporter
    Set rng = ws.UsedRange
    ' Initialiser la chaîne JSON
    json = "["
    ' Obtenir les en-têtes de colonnes à partir de la première ligne
    colHeaders = Application.Transpose(rng.Rows(1).Value)
    ' Commencer à parcourir les lignes (à partir de la deuxième ligne)
    For Each row In rng.Rows
        ' Ignorer la ligne d'en-têtes (première ligne)
        If row.Row > 1 Then
            ' Ouvrir un objet JSON pour la ligne actuelle
            json = json & "{"
            ' Parcourir les colonnes de la ligne actuelle
            For j = 1 To rng.Columns.Count
                ' Ajouter chaque donnée de cellule en tant que paire clé-valeur
                json = json & """" & colHeaders(j) & """: """ & row.Cells(1, j).Value & """"
                ' Ajouter une virgule de séparation si ce n'est pas la dernière colonne
                If j < rng.Columns.Count Then
                    json = json & ","
                End If
            Next j
            ' Fermer l'objet JSON pour la ligne actuelle
            json = json & "}"
            ' Ajouter une virgule de séparation si ce n'est pas la dernière ligne
            If row.Row < rng.Rows.Count Then
                json = json & ","
            End If
        End If
    Next row
    ' Fermer le tableau JSON
    json = json & "]"
    ' Écrire le JSON dans un fichier
    Dim filePath As String
    filePath = Application.GetSaveAsFilename(FileFilter:="Fichiers JSON (*.json), *.json")
    If filePath <> "False" Then
        ' Créer et ouvrir le fichier
        Dim jsonFile As Integer
        jsonFile = FreeFile
        Open filePath For Output As jsonFile
        Print #jsonFile, json
        Close jsonFile
        MsgBox "Les données ont été exportées avec succès en JSON !", vbInformation
    End If
End Sub

Explication du code
1. Configuration de la feuille et de la plage :

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.UsedRange

• ThisWorkbook fait référence au classeur dans lequel se trouve le code VBA.
• ws est la feuille de travail à partir de laquelle vous souhaitez exporter les données.
• UsedRange fait référence à toute la plage utilisée dans la feuille, c’est-à-dire toutes les cellules contenant des données.

2. Récupération des en-têtes :
colHeaders = Application.Transpose(rng.Rows(1).Value)

• La première ligne (rng.Rows(1)) contient les en-têtes de colonnes.
• Application.Transpose convertit la ligne d’en-têtes en un tableau vertical pour que nous puissions utiliser ces en-têtes comme clés dans notre objet JSON.

3. Parcours des lignes :

For Each row In rng.Rows
    If row.Row > 1 Then
        json = json & "{"
        For j = 1 To rng.Columns.Count
            json = json & """" & colHeaders(j) & """: """ & row.Cells(1, j).Value & """"
            If j < rng.Columns.Count Then
                json = json & ","
            End If
        Next j
        json = json & "}"
        If row.Row < rng.Rows.Count Then
            json = json & ","
        End If
    End If
Next row

• La boucle externe parcourt chaque ligne de la UsedRange.
• La boucle interne parcourt chaque colonne de la ligne. Pour chaque cellule, elle crée une paire clé-valeur où la clé est l’en-tête de la colonne et la valeur est la donnée de la cellule.
• Les lignes sont enveloppées dans des accolades {} pour représenter un objet JSON, et des virgules sont ajoutées pour séparer chaque paire clé-valeur.

4. Exportation des données JSON :

Dim filePath As String
filePath = Application.GetSaveAsFilename(FileFilter:="Fichiers JSON (*.json), *.json")
If filePath <> "False" Then
    Dim jsonFile As Integer
    jsonFile = FreeFile
    Open filePath For Output As jsonFile
    Print #jsonFile, json
    Close jsonFile
    MsgBox "Les données ont été exportées avec succès en JSON !", vbInformation
End If

• Ce code ouvre une boîte de dialogue de sauvegarde et demande à l’utilisateur où enregistrer le fichier .json.
• La fonction FreeFile retourne un numéro de fichier pour créer et écrire dans le fichier.
• On utilise Open pour créer ou ouvrir le fichier, Print pour écrire la chaîne JSON dans le fichier, puis Close pour fermer le fichier.

Considérations supplémentaires
1. Gestion des différents types de données : Ce code suppose que toutes les données dans la feuille sont du texte. Si vous avez des nombres ou des dates, vous devrez peut-être ajuster le formatage avant de les ajouter à la chaîne JSON.
2. Grandes quantités de données : Si votre ensemble de données est très volumineux, vous pourriez envisager d’optimiser le code ou de traiter les données par morceaux pour éviter les problèmes de mémoire.
3. Gestion des erreurs : Pour un code de production, il est conseillé d’ajouter une gestion des erreurs afin de garantir que les opérations sur les fichiers et le traitement des données sont robustes.
4. Personnalisation : Si vous souhaitez structurer le JSON de manière hiérarchique ou imbriquée, vous devrez modifier la logique pour regrouper les données différemment (par exemple, par catégories ou par lignes liées).
Conclusion
Ce code offre une méthode détaillée pour exporter des données depuis Excel vers un fichier JSON en utilisant VBA. En lisant les données d’une feuille de calcul, en les convertissant au format JSON et en les enregistrant dans un fichier, cette solution peut être adaptée à différents cas d’utilisation où les données doivent être exportées depuis Excel dans un format structuré et largement utilisé comme le JSON.