The Excel Development Platform: Java

Showing posts with label Java. Show all posts

Tuesday, 22 January 2019

VBA - Sockets - Ruby - Java - Interop Nirvana with Sockets!

I hope you have noticed that inter-operability features large on this blog. Interop is easy with C# because it has an excellent COM interop library. Python and other languages also have COM APIs (not always as comprehensive as C#). But Java and Ruby do not have COM APIs callable from VBA. But this month we have shown that it is possible to use Sockets to connect with other processes (in the demos on the same machine but in real life potentially on remote machines). Thus, using sockets Excel VBA call anything, literally anything (so long the target langauge has a sockets API).

In the previous two articles I wrote sockets servers in Ruby then Java which ran simple calculations. They were just warm-up and test-beds to be honest. What I really wanted to show was VBA calling Ruby and Java.

So in the code below if you run the procedure TestWS2SendAndReceive() at line 159 then you can see the code will attempt to call the Java and Ruby servers, make sure you have these running. If all works then you should get output in the Immediate window

1+3 = 4.0
6*7 = 42.0

modVBASocketsClient Standard Module

Option Explicit
Option Private Module
'reference Windows Sockets 2 - Windows applications _ Microsoft Docs
'http://msdn.microsoft.com/en-us/library/windows/desktop/ms740673(v=vs.85).aspx
Private Const INVALID_SOCKET = -1
Private Const WSADESCRIPTION_LEN = 256
Private Const SOCKET_ERROR As Long = -1 'const #define SOCKET_ERROR (-1)
Private Enum AF
AF_UNSPEC = 0
AF_INET = 2
AF_IPX = 6
AF_APPLETALK = 16
AF_NETBIOS = 17
AF_INET6 = 23
AF_IRDA = 26
AF_BTH = 32
End Enum
Private Enum sock_type
SOCK_STREAM = 1
SOCK_DGRAM = 2
SOCK_RAW = 3
SOCK_RDM = 4
SOCK_SEQPACKET = 5
End Enum
Private Enum Protocol
IPPROTO_ICMP = 1
IPPROTO_IGMP = 2
BTHPROTO_RFCOMM = 3
IPPROTO_TCP = 6
IPPROTO_UDP = 17
IPPROTO_ICMPV6 = 58
IPPROTO_RM = 113
End Enum
'Private Type sockaddr
' sa_family As Integer
' sa_data(0 To 13) As Byte
'End Type
Private Type sockaddr_in
sin_family As Integer
sin_port(0 To 1) As Byte
sin_addr(0 To 3) As Byte
sin_zero(0 To 7) As Byte
End Type
'typedef UINT_PTR SOCKET;
Private Type udtSOCKET
pointer As Long
End Type
' typedef struct WSAData {
' WORD wVersion;
' WORD wHighVersion;
' char szDescription[WSADESCRIPTION_LEN+1];
' char szSystemStatus[WSASYS_STATUS_LEN+1];
' unsigned short iMaxSockets;
' unsigned short iMaxUdpDg;
' char FAR *lpVendorInfo;
'} WSADATA, *LPWSADATA;
Private Type udtWSADATA
wVersion As Integer
wHighVersion As Integer
szDescription(0 To WSADESCRIPTION_LEN) As Byte
szSystemStatus(0 To WSADESCRIPTION_LEN) As Byte
iMaxSockets As Integer
iMaxUdpDg As Integer
lpVendorInfo As Long
End Type
'int errorno = WSAGetLastError()
Private Declare Function WSAGetLastError Lib "Ws2_32" () As Integer
' int WSAStartup(
' __in WORD wVersionRequested,
' __out LPWSADATA lpWSAData
');
Private Declare Function WSAStartup Lib "Ws2_32" _
(ByVal wVersionRequested As Integer, ByRef lpWSAData As udtWSADATA) As Long 'winsockErrorCodes2
' SOCKET WSAAPI socket(
' __in int af,
' __in int type,
' __in int protocol
');
Private Declare Function ws2_socket Lib "Ws2_32" Alias "socket" _
(ByVal AF As Long, ByVal stype As Long, ByVal Protocol As Long) As LongPtr
Private Declare Function ws2_closesocket Lib "Ws2_32" Alias "closesocket" _
(ByVal socket As Long) As Long
'int recv(
' SOCKET s,
' char *buf,
' int len,
' int flags
');
Private Declare Function ws2_recv Lib "Ws2_32" Alias "recv" _
(ByVal socket As Long, ByVal buf As LongPtr,
ByVal length As Long, ByVal flags As Long) As Long
'int WSAAPI connect(
' SOCKET s,
' const sockaddr *name,
' int namelen
');
Private Declare Function ws2_connect Lib "Ws2_32" Alias "connect" _
(ByVal S As LongPtr, ByRef name As sockaddr_in, ByVal namelen As Long) As Long
'int WSAAPI send(
' SOCKET s,
' const char *buf,
' int len,
' int flags
');
Private Declare Function ws2_send Lib "Ws2_32" Alias "send" _
(ByVal S As LongPtr, ByVal buf As LongPtr, ByVal buflen As Long, ByVal flags As Long) As Long
Private Declare Function ws2_shutdown Lib "Ws2_32" Alias "shutdown" _
(ByVal S As Long, ByVal how As Long) As Long
Private Declare Sub WSACleanup Lib "Ws2_32" ()
Private Enum eShutdownConstants
SD_RECEIVE = 0 '#define SD_RECEIVE 0x00
SD_SEND = 1 '#define SD_SEND 0x01
SD_BOTH = 2 '#define SD_BOTH 0x02
End Enum
Sub TestPortLongToBytes()
'redis is on port number 6379
Dim abytPortAsBytes() As Byte
abytPortAsBytes() = PortLongToBytes(6379)
Debug.Assert abytPortAsBytes(0) = 24
Debug.Assert abytPortAsBytes(1) = 235
End Sub
Private Function PortLongToBytes(ByVal lPort As Integer) As Byte()
ReDim abytReturn(0 To 1) As Byte
abytReturn(0) = lPort \ 256
abytReturn(1) = lPort Mod 256
PortLongToBytes = abytReturn()
End Function
Private Sub TestWS2SendAndReceive()
Dim sResponse As String
Const clJavaPort As Long = 6666
Const clRubyPort As Long = 3000
If WS2SendAndReceive(clJavaPort, "1+3" & vbCrLf, sResponse) Then
Debug.Print sResponse
End If
If WS2SendAndReceive(clRubyPort, "6*7" & vbCrLf, sResponse) Then
Debug.Print sResponse
End If
End Sub
Public Function WS2SendAndReceive(ByVal lPort As Long,
ByVal sText As String, ByRef psResponse As String) As Boolean
'https://docs.microsoft.com/en-gb/windows/desktop/api/winsock/nf-winsock-recv
If Right$(sText, 2) <> vbCrLf Then Err.Raise vbObjectError, , "Best suffix your sends with a new line (vbCrLf)"
psResponse = ""
'//----------------------
'// Declare and initialize variables.
Dim iResult As Integer : iResult = 0
Dim wsaData As udtWSADATA
Dim ConnectSocket As LongPtr
Dim clientService As sockaddr_in
Dim sendBuf() As Byte
sendBuf = StrConv(sText, vbFromUnicode)
Const recvbuflen As Long = 512
Dim recvbuf(0 To recvbuflen - 1) As Byte
'//----------------------
'// Initialize Winsock
Dim eResult As Long 'winsockErrorCodes2
eResult = WSAStartup(&H202, wsaData)
If eResult <> 0 Then
Debug.Print "WSAStartup failed with error: " & eResult
WS2SendAndReceive = False
GoTo SingleExit
End If
'//----------------------
'// Create a SOCKET for connecting to server
ConnectSocket = ws2_socket(AF_INET, SOCK_STREAM, IPPROTO_TCP)
If ConnectSocket = INVALID_SOCKET Then
Dim eLastError As Long 'winsockErrorCodes2
eLastError = WSAGetLastError()
Debug.Print "socket failed with error: " & eLastError
Call WSACleanup
WS2SendAndReceive = False
GoTo SingleExit
End If
'//----------------------
'// The sockaddr_in structure specifies the address family,
'// IP address, and port of the server to be connected to.
clientService.sin_family = AF_INET
clientService.sin_addr(0) = 127
clientService.sin_addr(1) = 0
clientService.sin_addr(2) = 0
clientService.sin_addr(3) = 1
Dim abytPortAsBytes() As Byte
abytPortAsBytes() = PortLongToBytes(lPort)
clientService.sin_port(1) = 235 '* 6379
clientService.sin_port(0) = 24
clientService.sin_port(1) = abytPortAsBytes(1)
clientService.sin_port(0) = abytPortAsBytes(0)
'//----------------------
'// Connect to server.
iResult = ws2_connect(ConnectSocket, clientService, LenB(clientService))
If (iResult = SOCKET_ERROR) Then
eLastError = WSAGetLastError()
Debug.Print "connect failed with error: " & eLastError
Call ws2_closesocket(ConnectSocket)
Call WSACleanup
WS2SendAndReceive = False
GoTo SingleExit
End If
'//----------------------
'// Send an initial buffer
Dim sendbuflen As Long
sendbuflen = UBound(sendBuf) - LBound(sendBuf) + 1
iResult = ws2_send(ConnectSocket, VarPtr(sendBuf(0)), sendbuflen, 0)
If (iResult = SOCKET_ERROR) Then
eLastError = WSAGetLastError()
Debug.Print "send failed with error: " & eLastError
Call ws2_closesocket(ConnectSocket)
Call WSACleanup
WS2SendAndReceive = False
GoTo SingleExit
End If
'Debug.Print "Bytes Sent: ", iResult
'// shutdown the connection since no more data will be sent
iResult = ws2_shutdown(ConnectSocket, SD_SEND)
If (iResult = SOCKET_ERROR) Then
eLastError = WSAGetLastError()
Debug.Print "shutdown failed with error: " & eLastError
Call ws2_closesocket(ConnectSocket)
Call WSACleanup
WS2SendAndReceive = False
GoTo SingleExit
End If
' receive only one message (TODO handle when buffer is not large enough)
iResult = ws2_recv(ConnectSocket, VarPtr(recvbuf(0)), recvbuflen, 0)
If (iResult > 0) Then
'Debug.Print "Bytes received: ", iResult
ElseIf (iResult = 0) Then
Debug.Print "Connection closed"
WS2SendAndReceive = False
Call ws2_closesocket(ConnectSocket)
Call WSACleanup
GoTo SingleExit
Else
eLastError = WSAGetLastError()
Debug.Print "recv failed with error: " & eLastError
End If
psResponse = Left$(StrConv(recvbuf, vbUnicode), iResult)
'Debug.Print psResponse
'// close the socket
iResult = ws2_closesocket(ConnectSocket)
If (iResult = SOCKET_ERROR) Then
eLastError = WSAGetLastError()
Debug.Print "close failed with error: " & eLastError
Call WSACleanup
WS2SendAndReceive = False
GoTo SingleExit
End If
Call WSACleanup
WS2SendAndReceive = True
SingleExit:
Exit Function
ErrHand:
End Function

Sockets - Java to Java - Sockets programming

Like most great languages Java has the ability open and read/write from/to sockets, a TCP/IP programming protocol lower than HTTP. This month I have been demonstrating Excel VBA acting as one endpoint for a Sockets connection (specifically communicating to Redis). That Excel VBA can talk to sockets means we can get VBA to talk to Java. I will write an VBA to Java article shortly but first it is better to write a Java to Java sockets application to see what is involved.

(And the more attentive amongst you will spot that I have just written a Ruby to Ruby sockets application so let's see if its true than Java is more verbose.)

The following code is a simple single-thread app (if you want multi-threaded, see the original article as we are here to demonstrate the sockets only.

JavaSocketsCalcClient.java

So this is the client code which simply reads a line of input in the form of a sum like "1+3" (actually you can do subtraction, divide and multiply), calls the server via sockets and then prints the return.

Here is the code for the file JavaSocketsCalcClient.java

//With thanks to https://www.baeldung.com/a-guide-to-java-sockets
import java.net.*;
import java.io.*;

public class JavaSocketsCalcClient {
    private Socket clientSocket;
    private PrintWriter out;
    private BufferedReader in;

    public void startConnection(String ip, int port) {
        try {
            clientSocket = new Socket(ip, port);
            out = new PrintWriter(clientSocket.getOutputStream(), true);
            in = new BufferedReader(new InputStreamReader(clientSocket.getInputStream()));
        } catch (Exception exc) {
            System.out.println(exc.getMessage());
        }
    }

    public String sendMessage(String msg) {
        try {
            out.println(msg);
            String resp = in.readLine();
            return resp;
        } catch (Exception exc) {
            System.out.println(exc.getMessage());
            return "";
        }
    }

    public void stopConnection() {
        try {
            in.close();
            out.close();
            clientSocket.close();
        } catch (Exception exc) {
            System.out.println(exc.getMessage());
        }
    }

    public static void main(String[] args) {
        try {
            GreetClient client = new GreetClient();
            client.startConnection("127.0.0.1", 6666);
            InputStreamReader reader = new InputStreamReader(System.in);
            BufferedReader in = new BufferedReader(reader);
            while (true) {
                System.out.println(client.sendMessage(in.readLine()));
            }
        } catch (Exception exc) {
            System.out.println(exc.getMessage());
        }
    }
}

So compile and run you need the following commands (and I'm surprised we do not use the .class suffix in the second to be honest).

N:\java\javaSockets>javac JavaSocketsCalcClient.java
N:\java\javaSockets>java JavaSocketsCalcClient

JavaSocketsCalcServer.java

This is the server which takes the sum string e.g. "1+3" and then parses it with a regular expression before calculating and returning the answer to the client.

//With thanks to https://www.baeldung.com/a-guide-to-java-sockets
import java.net.*;
import java.io.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class JavaSocketsCalcServer {
    private ServerSocket serverSocket;
    private Socket clientSocket;
    private PrintWriter out;
    private BufferedReader in;

    public void start(int port) {
        try {
            serverSocket = new ServerSocket(port);
            System.out.println("Running on port " + port);

            String pattern = "(\\d+)\\s*(\\+|\\*|-|\\/)\\s*(\\d+)";

            // Create a Pattern object
            Pattern r = Pattern.compile(pattern);

            while (true) {

                clientSocket = serverSocket.accept();
                out = new PrintWriter(clientSocket.getOutputStream(), true);
                in = new BufferedReader(new InputStreamReader(clientSocket.getInputStream()));

                try {
                    System.out.println("waiting to read line ...");
                    String sum = in.readLine();
                    System.out.println("Received text:" + sum);
                    // Now create matcher object.
                    Matcher m = r.matcher(sum);
                    if (m.find()) {
                        Float arg0 = Float.parseFloat(m.group(1));
                        String op = m.group(2);
                        Float arg1 = Float.parseFloat(m.group(3));
                        String resp = "";
                        if (op.equals("+")) {
                            resp = String.valueOf(arg0 + arg1);
                        } else if (op.equals("-")) {
                            resp = String.valueOf(arg0 - arg1);
                        } else if (op.equals("*")) {
                            resp = String.valueOf(arg0 * arg1);
                        } else if (op.equals("/")) {
                            resp = String.valueOf(arg0 / arg1);
                        } else {
                            out.println("could not match operand");
                        }

                        out.println(sum + " = " + resp);
                    } else {
                        out.println("does not look calculable");
                    }
                } catch (Exception exc) {
                    System.out.println(exc.getMessage());
                }
            }
        } catch (Exception exc) {
            System.out.println(exc.getMessage());
        }
    }

    public void stop() {
        try {
            in.close();
            out.close();
            clientSocket.close();
            serverSocket.close();
        } catch (Exception exc) {
            System.out.println(exc.getMessage());
        }
    }

    public static void main(String[] args) {
        GreetServer server = new GreetServer();
        server.start(6666);
    }
}

So in a separate console window, do the following

N:\java\javaSockets>javac JavaSocketsCalcServer.java
N:\java\javaSockets>java JavaSocketsCalcServer

Here is a screenshot of the two consoles running and communicating

Keen observers of the code will realise they both run in endless loops. Never mind about that, I simply wanted to get the servers running and demonstrable.

What's Next?

So what's next is some code which shows VBA calling this Java server and also the Ruby server from the previous article to illustrate VBA can call Java and Ruby despite them not having a COM APIs.

Thursday, 2 August 2018

Python - Java - Nu Html Checker - Running an HTML validator on old help pages

So in previous post I showed how to use HTMLTidy to restructure old HTML help pages in that case decompiled from a help file (*.chm) but could apply to any old HTML files. To raise compliance to HTML5, it is still necessary to further triage them. In its output messages, HTMLTidy recommends validating at http://validator.w3.org/nu/ but in this post we show how one can run this logic locally by downloading the java jar that drives that web site.

The Nu Html Checker

So HTML Tidy recommends the useful web site Nu Html Checker, https://validator.w3.org/nu/#textarea but before you feel tempted to write code to script against this page be advised you can run your own copy of the Nu Html Checker from a command line so long as you have Java installed.

Install Java

Do please install Java before attempting the code below

Install Nu Html Checker

Instructions as to how to get your own copy of the tool are here. So I navigated to Nu Html Checker version 18.7.23 and downloaded vnu.jar_18.7.23.zip . When the download completed, I unzipped it and extracted contained files to a subdirectory in my Downloads folder. For later use, I defined an environment variable %vnu% to point to vnu.jar's parent folder, %userprofile%\Downloads\vnu.jar_18.7.23\dist . A better long run place to install would be somewhere in Program Files.

Running Nu Html Checker from command line

With the environment variable %vnu% defined I can test the install is working (both java and the downloaded jar file) with ...

C:\>java -jar %vnu%\vnu.jar --version
18.7.23

You can see the Nu Html Checker version number is returned, so all is installed correctly.

Running Nu Html Checker from command line on a single file

Installation confirmed, we can confidently advance to running the tool on an HTML file, I have some files resulting from a previous post. So I will try this file

C:\>java -jar %vnu%\vnu.jar --no-langdetect --format xml %Temp%\HelpFileDecompiler\VBLR6\vblr6.hhc.tidied.html
<?xml version='1.0' encoding='utf-8'?>
<messages xmlns="http://n.validator.nu/messages/">
<error url="file:/C:/Users/Simon/AppData/Local/Temp/HelpFileDecompiler/VBLR6/vblr6.hhc.tidied.html" last-line="8" last-column="15" first-column="8">
<message>Element <code xmlns="http://www.w3.org/1999/xhtml">title</code> must not be empty.</message>
<extract>-&gt;
&lt;title&gt;<m>&lt;/title&gt;</m>
&lt;/hea</extract>
</error>

</messages>

C:\>

So we get a report. In this case one message only complaining about an empty title element; the message carries text file co-ordinates (line, column) so we can locate easily. Some of the message is itself entitized HTML and so reads a little cryptically but the other output formats are not much better.

Running Nu Html Checker from command line on a directory

Running on a whole directory created a huge massive file. I'd prefer a report file per HTML file. Fortunately we can write some Python code to do this.

Python Script to walk a folder and run Nu Html Checker on each file

If you have been reading my Python posts then the next script follows a familiar pattern. The script has a COM callable class so Excel VBA can call into it but it also stands alone and is callable by running Python from the command line. This is an Excel blog and I feel obliged to tie non VBA code back to VBA. In fact, there are two classes, I am working on a series of posts and would like to reuse the naming logic so that explains the HTMLTidiedChmFileNamer class.

The ValidatorReporter class runs the Nu Html Checker validation checker. It walks a folder as found in previous scripts. It shells a process using subprocess as in previous scripts. One thing that is new here is that we are shelling to java. Another things that is new here is that we are capturing the stderr by specifying PIPE in subprocess.run() arguments; this allows us to read the stderr stream and then we write it to a file.

import os
import subprocess
from subprocess import PIPE
import codecs


class HTMLTidiedChmFileNamer(object):
    _reg_clsid_ = "{8807D2B9-C83F-4AEB-A71D-15DBE8EFED9A}"
    _reg_progid_ = 'PythonInVBA.HTMLTidiedChmFileNamer'
    _public_methods_ = ['TidiedFilenameWin32Dict']

    def TidiedFilename(self, subdir, file):
        file2 = file.lower()
        tidiedFile = ""
        errorfile = ""
        validationErrorsFile = ""

        if ".tidied." not in file:
            if file2.endswith((".hhc", ".hhk")):
                tidiedFile = subdir + os.sep + file + ".tidied.html"
                errorfile = subdir + os.sep + file + ".tidied.errors.txt"
                validationErrorsFile = (subdir + os.sep + file +
                                        ".tidied.validationErrors.txt")

            if file2.endswith((".htm", ".html")):
                tidiedFile = (subdir + os.sep +
                              file.split('.')[0] + ".tidied.html")
                errorfile = (subdir + os.sep +
                             file.split('.')[0] + ".tidied.errors.txt")
                validationErrorsFile = (subdir + os.sep +
                                        file.split('.')[0] +
                                        ".tidied.validationErrors.txt")
        return (tidiedFile, errorfile, validationErrorsFile)


class ValidatorReporter(object):
    _reg_clsid_ = "{321F338F-75AE-460B-85A2-5C553A39CDE1}"
    _reg_progid_ = 'PythonInVBA.ValidatorReporter'
    _public_methods_ = ['ValidateBatch']

    def ValidateBatch(self, rootDir):

        if "vnu" not in os.environ:
            raise Exception(
                "vnu environment variable not defined, "
                "please define as vnu jar's parent folder")

        sVNUExe = os.path.join(os.environ["vnu"], "vnu.jar")
        FileNamer = HTMLTidiedChmFileNamer()

        for subdir, dirs, files in os.walk(rootDir):
            for file in files:
                tidiedFile, errorfile, validationErrorsFile = 
                        FileNamer.TidiedFilename(subdir, file)
                if not tidiedFile == "":
                    # https://github.com/validator/validator#user-content-usage
                    args = ['java', '-jar', sVNUExe, '--no-langdetect',
                            '--format', 'xml', tidiedFile]
                    proc = subprocess.run(args, stderr=PIPE)

                    file = codecs.open(validationErrorsFile, "w", "utf-8")
                    file.write(proc.stderr.decode("utf-8"))
                    file.close()

if __name__ == '__main__':
    print ("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(ValidatorReporter)
    win32com.server.register.UseCommandLine(HTMLTidiedChmFileNamer)
    
    rootdir = os.path.join(os.environ["tmp"], 'HelpFileDecompiler', "vblr6")
    test = ValidatorReporter()
    test.ValidateBatch(rootdir)

The portion of code that registers the COM classes require administrator rights. You can comment them out and run the script from command line instead in a purely Pythonic way.

The code assumes you have a folder with HTML files in it. For me I have given the code a folder of HTML files extracted from a decompiled *.chm file and the code takes a good while.

Client VBA Code

To prove we can call this Python script from VBA here is the client code

Sub TestValidatorReporter()
    
    Dim objValidatorReporter As Object
    Set objValidatorReporter = VBA.CreateObject("PythonInVBA.ValidatorReporter")
    
    objValidatorReporter.ValidateBatch Environ$("tmp") & "\HelpFileDecompiler\VBLR6\"

End Sub

Final Thoughts

For me, the resultant output is huge and will take time to comb through but it looks like I'll need to load HTML files into Xml parsers and rearrange attributes etc. More Python code to come in this series. So look out for that.

Tuesday, 26 September 2017

Excel on the Server? No thanks, Xml ADO recordsets please

I have encountered a variety of what I would call "Excel on the server" technologies and these include Microsoft SharePoint Server but also there is an Apache (and thus open source) Java Apache-POI, I chanced upon the latter whilst looking at StackOverflow bounties. Mulling the use case of generating excel workbooks on a server I think that the majority use case is the creation of reports, and the best way to do this is pivot tables and charts based on those pivot table. But is the creation of pivot tables in an Excel workbook on a server a smart thing to do? If you look at some sample Apachi-POI code it would appear a bit clunky.

In this older post I show worksheet cell contents converted to Xml and then to an ActiveX Data Objects (hereafter ADO) recordset. Use of ADO recordsets as a means to marshalling data between a client desktop and a computer room server should not be underestimated. Indeed, in the era of Visual Basic 6 the N-tier architecture was Windows DNA and all these distributed architectures require some state container/vessel to marshal data from one tier to another. For Windows DNA an ADO recordset that the state marshalling container/vessel.

So I would recommend web services emitting a Xml version of an ADO recordset to an Excel workbook. The magic line of code that eliminates a ton of scripting is the CopyFromRecordset method, it is the penultimate line in the following VBA example. You'll need the Xml to be saved into a file (I have chosen c:\temp\xl_persists_2.xml)


<xml xmlns:x="urn:schemas-microsoft-com:office:excel" 
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
    xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
    xmlns:rs="urn:schemas-microsoft-com:rowset" 
    xmlns:z="#RowsetSchema">
<x:PivotCache>
<x:CacheIndex>1</x:CacheIndex>
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly">
<s:attribute type="Col1"/>
<s:attribute type="Col2"/>
<s:attribute type="Col3"/>
<s:extends type="rs:rowbase"/>
</s:ElementType>
<s:AttributeType name="Col1" rs:name="FirstName">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col2" rs:name="FamilyName">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col3" rs:name="Role">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
</s:Schema>
<rs:data>
<z:row Col1="John" Col2="Snow" Col3="President"/>
<z:row Col1="Ygritte" Col2="Wild" Col3="Vice-President"/>
</rs:data>
</x:PivotCache>
</xml>

For the VBA you'll need Tools->References to Microsoft ActiveX Data Object 6.1 Library (or similar) and Microsoft Xml, v6.0 (or similar)


Function RecordsetAsXml() As String
    '* in this example I'm loading from a file but it can be a webservice.
    
    RecordsetAsXml = VBA.CreateObject("Scripting.FileSystemObject").OpenTextFile("c:\temp\xl_persist_2.xml").ReadAll
End Function

Sub LoadXmlRecordset()

    'Tools->References:Microsoft ActiveX Data Object 6.1 Library
    Dim rs As ADODB.Recordset
    
    'Tools->References:Microsoft Xml, v6.0
    Dim domRecordsetAsXml As MSXML2.DOMDocument60
    Set domRecordsetAsXml = New MSXML2.DOMDocument60
    domRecordsetAsXml.LoadXML RecordsetAsXml
    Debug.Assert domRecordsetAsXml.parseError.ErrorCode = 0

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open domRecordsetAsXml
    
    '* placed a little under the original data for comparison
    Dim rngOrigin As Excel.Range
    Set rngOrigin = ThisWorkbook.Worksheets.Item(1).Cells(6, 1)
    
    Dim lFieldLoop As Long
    For lFieldLoop = 0 To rs.Fields.Count - 1
        rngOrigin.Offset(0, lFieldLoop).Value = rs.Fields(lFieldLoop).Name
    Next lFieldLoop
    
    rngOrigin.Offset(1).CopyFromRecordset rs

End Sub

From this point it is very easy to generate a pivot table and charts from the table of data zapped into the worksheet by CopyFromRecordSet. So, I prefer Xml ADO recordsets to Sharepoint or Apache POI generated workbooks.