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.