So it's not just Script Control based JSON parsers that can benefit from the bang ! syntax, we can use C# and COM interop to export Newtonsoft's very popular .NET JSON parser for use in VBA. When defining the interop interface if we add DispId(0) then enable the bang ! operator which calls the default method to get compact syntax. Here is client VBA...
Sub Test()
Dim sJSON As String
sJSON = VBA.Replace("{ 'name':'John', 'age':30, 'cars':{ 'car1':'Ford','car2':'BMW','car3':'Fiat'} }", "'", """")
Dim oCJSONParser As CJSONParser
Set oCJSONParser = New CJSONParser
Dim oRoot As CJSONToken
Set oRoot = oCJSONParser.ParseJSONString(sJSON)
Dim oCars As CJSONToken
Set oCars = oRoot!cars '* equivalent of 'Set oCars = oItem.GetToken("cars")
Dim oCar2
oCar2 = oCars!car2 '* equivalent of 'Set oCar2 = oCars.GetToken("car2")
'* or chain syntax
oCar2 = oRoot!cars!car2
Stop
End Sub
And here is the C# code for a .NET library assembly (i.e. a DLL) with Register for Interop checkbox checked and ComVisible(true) in AssemblyInfo.cs
using Newtonsoft.Json.Linq; //Nuget Newtonsoft.Json.11.0.2
using System.Runtime.InteropServices;
namespace Foo
{
public interface IJSONParser
{
IJSONToken ParseJSONString(string sJSON);
}
[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IJSONParser))]
public class CJSONParser : IJSONParser
{
private JToken m_oJObject;
IJSONToken IJSONParser.ParseJSONString(string sJSON)
{
m_oJObject = JToken.Parse(sJSON);
CJSONToken oToken = new CJSONToken(m_oJObject);
return oToken;
}
}
public interface IJSONToken
{
[DispId(0)]
object GetToken(string sKey);
}
[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IJSONToken))]
public class CJSONToken : IJSONToken
{
private JToken m_oToken;
public CJSONToken(JToken token)
{
m_oToken = token;
}
object IJSONToken.GetToken(string sKey)
{
JToken oToken = m_oToken[sKey];
if (oToken is JValue)
{
JValue jv = (JValue)oToken;
return jv.Value;
}
else
{
CJSONToken token = new CJSONToken(oToken);
return token;
}
}
}
}
No comments:
Post a Comment