(Excel) Using Pre-defined JSON Templates

Demonstrates how to predefine a JSON template, and then use it to emit JSON with variable substitutions.

Note: This example requires Chilkat v9.5.0.67 or greater.

Download Excel Class Modules Chilkat Excel Class Modules

' One way to create JSON is to do it in a straightforward manner:
Dim json As Chilkat.JsonObject
Set json = Chilkat.NewJsonObject
json.EmitCompact = False
Dim success As Boolean
success = json.UpdateString("id","0001")
success = json.UpdateString("type","donut")
success = json.UpdateString("name","Cake")
success = json.UpdateString("image.url","images/0001.jpg")
success = json.UpdateInt("image.width",200)
success = json.UpdateInt("image.height",200)
success = json.UpdateString("thumbnail.url","images/thumbnails/0001.jpg")
success = json.UpdateInt("thumbnail.width",32)
success = json.UpdateInt("thumbnail.height",32)
Debug.Print json.Emit()

' The JSON created by the above code:

' 	{ 
' 	  "id": "0001",
' 	  "type": "donut",
' 	  "name": "Cake",
' 	  "image": { 
' 	    "url": "images/0001.jpg",
' 	    "width": 200,
' 	    "height": 200
' 	  },
' 	  "thumbnail": { 
' 	    "url": "images/thumbnails/0001.jpg",
' 	    "width": 32,
' 	    "height": 32
' 	  }
' 	}

' An alternative is to predefine a template, and then use it to emit with variable substitutions.
' For example:

Dim jsonTemplate As Chilkat.JsonObject
Set jsonTemplate = Chilkat.NewJsonObject
success = jsonTemplate.UpdateString("id","{$id}")
success = jsonTemplate.UpdateString("type","donut")
success = jsonTemplate.UpdateString("name","{$name}")
success = jsonTemplate.UpdateString("image.url","{$imageUrl}")
' The "i." indicates that it's an integer variable.
success = jsonTemplate.UpdateString("image.width","{$i.imageWidth}")
success = jsonTemplate.UpdateString("image.height","{$i.imageHeight}")
success = jsonTemplate.UpdateString("thumbnail.url","{$thumbUrl}")
success = jsonTemplate.UpdateString("thumbnail.width","{$i.thumbWidth}")
success = jsonTemplate.UpdateString("thumbnail.height","{$i.thumbHeight}")
' Give this template a name.
success = jsonTemplate.Predefine("donut")

' --------------------------------------------------------------------------
' OK, the template is defined.  Defining a template can be done once
' at the start of your program, and you can discard the jsonTemplate object (it
' doesn't need to stick around..)

' Now we can create instances of the JSON object by name:
Dim jsonDonut As Chilkat.JsonObject
Set jsonDonut = Chilkat.NewJsonObject
jsonDonut.EmitCompact = False
success = jsonDonut.LoadPredefined("donut")
Debug.Print jsonDonut.Emit()

' The output is this:

' 	{ 
' 	  "id": "{$id}",
' 	  "type": "donut",
' 	  "name": "{$name}",
' 	  "image": { 
' 	    "url": "{$imageUrl}",
' 	    "width": "{$i.imageWidth}",
' 	    "height": "{$i.imageHeight}"
' 	  },
' 	  "thumbnail": { 
' 	    "url": "{$thumbUrl}",
' 	    "width": "{$i.thumbWidth}",
' 	    "height": "{$i.thumbHeight}"
' 	  }
' 	}

' Finally, we can substitute variables like this:
Dim donutValues As Chilkat.Hashtable
Set donutValues = Chilkat.NewHashtable
success = donutValues.AddStr("id","0001")
success = donutValues.AddStr("name","Cake")
success = donutValues.AddStr("imageUrl","images/0001.jpg")
success = donutValues.AddInt("imageWidth",200)
success = donutValues.AddInt("imageHeight",200)
success = donutValues.AddStr("thumbUrl","images/thumbnails/0001.jpg")
success = donutValues.AddInt("thumbWidth",32)
success = donutValues.AddInt("thumbHeight",32)

' Emit with variable substitutions:

omitEmpty = True
Debug.Print jsonDonut.EmitWithSubs(donutValues,omitEmpty)

' Output:

' 	{ 
' 	  "id": "0001",
' 	  "type": "donut",
' 	  "name": "Cake",
' 	  "image": { 
' 	    "url": "images/0001.jpg",
' 	    "width": 200,
' 	    "height": 200
' 	  },
' 	  "thumbnail": { 
' 	    "url": "images/thumbnails/0001.jpg",
' 	    "width": 32,
' 	    "height": 32
' 	  }
' 	}

' Change some of the values:
success = donutValues.AddStr("id","0002")
success = donutValues.AddStr("imageUrl","images/0002.jpg")
success = donutValues.AddStr("thumbUrl","images/thumbnails/0002.jpg")

Debug.Print jsonDonut.EmitWithSubs(donutValues,omitEmpty)

' Output:

' 	{ 
' 	  "id": "0002",
' 	  "type": "donut",
' 	  "name": "Cake",
' 	  "image": { 
' 	    "url": "images/0002.jpg",
' 	    "width": 200,
' 	    "height": 200
' 	  },
' 	  "thumbnail": { 
' 	    "url": "images/thumbnails/0002.jpg",
' 	    "width": 32,
' 	    "height": 32
' 	  }
' 	}