JSON to Excel Cells Serialization

So JSON is becoming a popular serialization format to rival (if not overtake) XML for web services. If you're an Excel VBA programmer and you want to dump some JSON into cells then you could use the ScriptControl to parse this, see my blog entry here. However, it is worth pointing out that Excel has a cell value serialization format which call be demonstrated thus ...

Sub XlSerialization1()
    Dim v
    v = [{1,2;"foo",4.5}]

    Debug.Assert v(1, 1) = 1
    Debug.Assert v(1, 2) = 2
    Debug.Assert v(2, 1) = "foo"
    Debug.Assert v(2, 2) = 4.5

    '* write all cells in one line
    Sheet1.Cells(1, 1).Resize(2, 2).Value2 = v
End Sub

So the square brackets surround the curly bracketed expression, square brackets is a VBA shorthand for evaluate, so the core syntax is the curly-bracketed expression. The given form is for literals where the cell values are constant. What happens when you want to use a variable, this is when you need Application.Evaluate thus ...

Sub XlSerialization2()

    Dim s As String
    s = "{1,2;""foo"",4.5}"
    
    Dim appEval
    appEval = Application.Evaluate(s)

    Debug.Assert appEval(1, 1) = 1
    Debug.Assert appEval(1, 2) = 2
    Debug.Assert appEval(2, 1) = "foo"
    Debug.Assert appEval(2, 2) = 4.5

    '* write all cells in one line
    Sheet1.Cells(4, 1).Resize(2, 2).Value2 = appEval
End Sub

If we want to write a 2D JSON array to cells we can convert the JSON string to this Excel serialization string and then we can get a Variant in one step and write the Variant in one step. For large arrays I should think this would be faster than looping through each element in VBA.

It might be possible to use regular expressions to extract and convert the cells values but instead I chose let a JavaScript engine/parser parse a JSON string and leveraged this thus...

Sub TestJSONArrayToXlArray()
    
    '* [[1,2],[3,4]] is a JSON 2D array, ideal for writing to cells
    Dim vXlArray As Variant
    vXlArray = JsonArrayToXlArray("[[1,2],[3,4]]")

    Debug.Assert vXlArray(1, 1) = 1
    Debug.Assert vXlArray(1, 2) = 2
    Debug.Assert vXlArray(2, 1) = 3
    Debug.Assert vXlArray(2, 2) = 4

    
    '* write all cells in one line
    Sheet1.Cells(1, 4).Resize(2, 2).Value2 = vXlArray


    '* [['foo','bar'],['baz','barry']] is a JSON 2D array, ideal for writing to cells
    Dim vXlArray2 As Variant
    vXlArray2 = JsonArrayToXlArray(Replace("[['foo','bar'],['baz','barry']]", "'", """"))

    Debug.Assert vXlArray2(1, 1) = "foo"
    Debug.Assert vXlArray2(1, 2) = "bar"
    Debug.Assert vXlArray2(2, 1) = "baz"
    Debug.Assert vXlArray2(2, 2) = "barry"

    '* write all cells in one line
    Sheet1.Cells(4, 4).Resize(2, 2).Value2 = vXlArray2


End Sub

Private Function JsonArrayToXlArray(sJson2dArray As String) As Variant
    
    Static oScriptEngine As ScriptControl
    If oScriptEngine Is Nothing Then
        Set oScriptEngine = New ScriptControl
        oScriptEngine.Language = "JScript"
        oScriptEngine.AddCode "function quoteString(cell) {    " & _
                              "  if(typeof cell === 'string' || cell instanceof String)    {" & _
                              "    return '""' + cell +'""';;} else {return cell.toString();} }"
        
        Debug.Assert oScriptEngine.Run("quoteString", 8) = "8"
        Debug.Assert oScriptEngine.Run("quoteString", "bar") = """bar"""
        
        oScriptEngine.AddCode _
            "function JsonArrayToXlArray(array) { " & _
            "  var xlArray=''; " & _
            "  for (var i=0; i0) {xlArray=xlArray+';';} " & _
            "    for (var j=0;j0) {row=row+',';} " & _
            "      row=row + quoteString(array[i][j]); " & _
            "    } " & _
            "    xlArray=xlArray+row; " & _
            "  } " & _
            "  xlArray='{' + xlArray + '}'; " & _
            "return xlArray; }"
    End If
    
    Dim objJson2dArray As Object
    Set objJson2dArray = oScriptEngine.Eval("(" + sJson2dArray + ")")
    
    Dim sXlArray As String
    sXlArray = oScriptEngine.Run("JsonArrayToXlArray", objJson2dArray)
    
    JsonArrayToXlArray = Application.Evaluate(sXlArray)
End Function

If you want to see the Javascript better ...

var array=[['foo',2],[3,4]];
var xlArray="";

xlArray=JsonArrayToXlArray(array);
alert(xlArray);

function JsonArrayToXlArray(array) {
var xlArray=''; 
for (var i=0; i0) {xlArray=xlArray+';';}
    for (var j=0;j0) {row=row+',';}   
        row=row + quoteString(array[i][j]);    
    }
    xlArray=xlArray+row;
} 
xlArray='{' + xlArray + '}';
return xlArray;
}

function quoteString(cell) {
   if(typeof cell === 'string' || cell instanceof String)
   {return '"' + cell +'"';;}
   else
   {return cell.toString();}
}