Wednesday, 2 May 2018

VBA - C# - Bang Syntax Part 3 - C# Interop JSON with Bang Syntax

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