Xbasic

a5_json_to_excel Function

Syntax

dim result as L = a5_json_to_excel(JSON as C, filename as C)

Arguments

JSONCharacter

The JSON data to export to Excel.

filenameCharacter

The filename of the Excel file (must have a .xlsx extension.)

Returns

resultLogical

Returns .t. if the excel file was created, otherwise .f.

Description

Exports data in a JSON document to an Excel file.

Discussion

The a5_json_to_excel function creates an Excel file from a JSON object. If the JSON document is an object with multiple arrays, each array is exported as a separate sheet in the Excel file.

If the JSON document is an array, the Excel file will have a single sheet called 'Table1'.

For example, if the JSON document shown below was exported to Excel, the Excel file would have two sheets ('customers' and 'phoneNumbers'). Each sheet would have the data in the corresponding array in the JSON data.

Example

{
    "customers" : [
        {"id": "1", "firstname" : "Fred", "lastname": "Jones"},
        {"id": "2", "firstname" : "Mary", "lastname": "Nickerson"}
    ],
    
    "phoneNumbers" : [
        {"id": "1", "type" : "Home", "number" : "555-555-1234"},
        {"id": "1", "type" : "Office", "number" : "555-555-1235"},
        {"id": "2", "type" : "Home", "number" : "555-555-1236"},
        {"id": "2", "type" : "Office", "number" : "555-555-1237"},
        {"id": "2", "type" : "Vacation", "number" : "555-555-1238"}
    ]
}