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.

Sharepoint Excel Services for server-side Excel calculations

For many years Excel was limited to client-side, i.e. desktop installation, operation. However, I did work on many projects where we launched an instance of Excel on a server as a calculation agent despite Microsoft recommending against because this option is fraught with unforeseen consequences. For example, message boxes get thrown but on a server there is no user to see and dismiss them. Microsoft invented Sharepoint for clients who really want to run Excel on the server.

The downsides of Sharepoint are cost, it is not cheap in itself and also it requires a Windows Server licence. If your employer is pro-Unix then this will be a deal-breaker. The other downside of Sharepoint is that your VBA code will not run and will have to be converted to C# or some other managed .NET language.

Nevertheless, the Sharepoint 'market share' of the Excel solution space will grow and a blog on Excel Development should address it.

I've yet to reach recommendations for this technology so this first blog post will solely be a collection of links for more reading.

Wikipedia is always good place to start, here is article on Excel Services

Office Support: Getting Started with Excel Services and Excel Web Access

Book: Wrox - Professional Excel Services

Technet: Overview of Excel Services in SharePoint Server 2013

Technet: Administer Excel Services in SharePoint Server 2013

safaribooksonline: Chapter 1. An Introduction to Excel Services

Technet Forums: VBA won't work for workbooks rendered in a browser from a Sharepoint server (Excel Services)

Office Dev Center: Understanding Excel Services UDFs

Office Dev Center: Walkthrough: Developing a Managed-Code UDF

MSDN: Creating Custom Solutions with Excel Services

Some sample C# code for a server-side Excel Services UDF.

// From https://dev.office.com/sharepoint/docs/general-development/step-2-creating-a-managed-code-udf
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;

namespace SampleUdf
{
    [UdfClass]
    public class Class1
    {
        [UdfMethod]
        public double MyDouble(double d)
        {
            return d * 9;
        }  

        [UdfMethod(IsVolatile = true)]
        public DateTime ReturnDateTimeToday()
        {
            return (DateTime.Today);
        }
    }
}

However, if you thought getting a UDF to run on both the client and the server in a unified code base would be a breeze then check out this web page which shows how to do it. It seems a major undertaking requiring C#, C++/CLI (formerly Managed C++), creating code to handle a managed Add-in and a custom shim. I expect many to be dissuaded from using SharePoint because of the complexity there but I also expect Microsoft to unify the client side and server interfaces in the future.

Whilst I have yet to reach recommendations on Sharepoint and Excel Services one has to wonder why rendering a workbook in a browser which requires a Windows Server, Sharepoint Server and Internet Information Server is better than keeping a workbook of a network file server (running on Linux) and opening in Excel. I guess the question boils down to client licences versus server licences.