Exsead - Empower Users With an XML Configured Excel Database Query Tool

Introduction

In my world of helping real businesses do real IT, there is rarely time to write full application extensions to achieve reports. Managers are under massive pressure to get projects back on track and prevent cash hemorrhaging away. They want a spreadsheet of figures today - this post shows how to give it to them. You'll be loved!

The script here is just the beginning. It has embedded in it an XML configuration section which defines a set of SQL queries to run to create reports in Excel. I am already working on much more complex versions for real projects, where data is merged from different sources. In a few hours, this technique can give the sort of report that traditional application programming would take days or weeks to produce. What is more, this flexibility and direct access to information is what managers want.

So, enough of the hype, let us look at what the script actually does:

At the top of the script is embedded the XML configuration. This is read by the IncludeGrabber function and then parsed using Microsoft's DOM parser. In my test script, the XML looks like this:

// Inside the comment block below must be an XML document which 
// defines the properties for the reports created from this
// script
/*
//---START-config---
<config>
  <!-- There is one report section per SQL query and the results 
       from that query will go into a spreadsheet. There can be
       as many reports as you want.
    -->
  <report>
     <!-- Put the ado connect string in here. It must be the dbole
          string appropreate for the db in question.
       -->
     <connect><![CDATA[
       Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes; 
     ]]></connect>
     
     <!-- Put in here some sql which will produce a single result set.
       -->
     <sql><![CDATA[
     select * from T_User
     ]]></sql>
     
     <!-- This section holds the config for the spread sheet which will
          be created for and populated by the query defined for this
          report.
       -->
     <workSheet>
       <name>Users</name>
       <useAutoFilter>true</useAutoFilter>
       <verticleSplitPoint>1</verticleSplitPoint>
       <horrizontalSplitPoint>1</horrizontalSplitPoint>
     </workSheet>
  </report>
  <report>
     <connect><![CDATA[
        Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes; 
     ]]></connect>
     <sql><![CDATA[
     select * from T_BusnUnit
     ]]></sql>
     <workSheet>
       <name>UserGroups</name>
       <useAutoFilter>true</useAutoFilter>
       <verticleSplitPoint>1</verticleSplitPoint>
       <horrizontalSplitPoint>1</horrizontalSplitPoint>
     </workSheet>
  </report>
  <report>
     <connect><![CDATA[
Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes; 
     ]]></connect>
     <sql><![CDATA[
     select * from T_Mchn
     ]]></sql>
     <workSheet>
       <name>Machines</name>
       <useAutoFilter>true</useAutoFilter>
       <verticleSplitPoint>1</verticleSplitPoint>
       <horrizontalSplitPoint>1</horrizontalSplitPoint>
     </workSheet>
  </report>
</config>
//---END-config---
*/

Each report section causes the generation of a separate worksheet in the created Excel document. Within the report are sections which then define the connection to the database, the SQL to run, if to and where to split the report spread sheet, and its name.

Parsing the XML is fairly straightforward. I have taken a bit of care to allow for the presence of comments, but in general, it uses a normal 'walk the DOM' approach. Handling comments is done by looking for appropriate node names:

// Simply loading the node assuming there are no comments
var reportNode=entryNode.firstChild;

// Becomes - allowing for comments
var reportNode=entryNode.firstChild;
while(reportNode.nodeName!='report')
    reportNode=reportNode.nextSibling;

The block of code below does the actual loading of configuration from the parsed DOM. Then, you can see where the IncludeGrabber gets the XML from the script. There are also a couple of Regular Expression replace functions in there to remove any white space from the enclosing root node of the XML before parsing it with the DOM parser.

// Get the config XML and parse it into a DOM. 
var xml=IncludeGrabber('config');
var dom = WScript.CreateObject("Microsoft.XMLDOM");
xml=xml.replace(/^\s*/,'');
xml=xml.replace(/\s*$/,'');
dom.loadXML(xml); 
var entryNode=dom.firstChild;
var reportNode=entryNode.firstChild;
// Hunt through and comments
while(reportNode.nodeName!='report')
    reportNode=reportNode.nextSibling;

// Loop over all the report
while(reportNode!=null)
{
    // 'Walk' the dom to get the data for the report
    var connect=reportNode.firstChild
    while(connect.nodeName!='connect')
        connect=connect.nextSibling;

    var sql=connect.nextSibling;
    while(sql.nodeName!='sql')
        sql=sql.nextSibling;

    var workSheet=sql.nextSibling;        
    while(workSheet.nodeName!='workSheet')
        workSheet=workSheet.nextSibling;

    var name=workSheet.firstChild;
    while(name.nodeName!='name')
        name=name.nextSibling;

    var useAutoFilter=name.nextSibling;
    while(useAutoFilter.nodeName!='useAutoFilter') 
        useAutoFilter=useAutoFilter.nextSibling;

    var verticleSplitPoint=useAutoFilter.nextSibling;
    while(verticleSplitPoint.nodeName!='verticleSplitPoint')
        verticleSplitPoint=verticleSplitPoint.nextSibling;

    var horrizontalSplitPoint=verticleSplitPoint.nextSibling;
    while(verticleSplitPoint.nodeName!='verticleSplitPoint') 
        verticleSplitPoint=verticleSplitPoint.nextSibling;
 
    // Extract the text data from the text child nodes
    connect=connect.firstChild.nodeValue;
    sql=sql.firstChild.nodeValue;
    name=name.firstChild.nodeValue;
    useAutoFilter=useAutoFilter.firstChild.nodeValue;
    verticleSplitPoint=verticleSplitPoint.firstChild.nodeValue;
    horrizontalSplitPoint=horrizontalSplitPoint.firstChild.nodeValue;

Now that we have the configuration, we need to run the SQL and put the results into the spreadsheet. There are ways of attaching a spreadsheet to a data-source. However, what we want is a report, not an attached spreadsheet. So, the approach I have taken is to write to a result set and copy the result data into the spreadsheet. This is done inside RecSet2WorkSheet. This trick to getting good performance is to store each row worth of data into a Scripting.Dictionary object and then set the Row of the spreadsheet from the items property of the Dictionary. This works because the items property returns an OLE SafeArray; when an Excel range has its value set to a SafeArray, the whole value is set as one go, which is much more efficient than setting it one value at a time.

Once the worksheet has had all its values set, it remains to add a little formatting and some auto-filters. The choice as to if this is done is actually set in the XML. Here is the formatting code:

ws.Activate();

var rng=ws.range(ws.cells(1,1),ws.cells(1,nColumns));

// Apply Filters
if(UseAutoFilter)
{
    rng.AutoFilter();
}

// Apply Colour etc
with(rng.Font)
{
    Name = "Arial";
    FontStyle = "Bold";
    Size = 12;
}

with(rng.Interior)
{
    ColorIndex = 42;
    Pattern = 1;
    PatternColorIndex = -4105;
}

// Autofit to make look nice
for(var i=1;i<=nColumns;++i)
{
    ws.columns(i).AutoFit();
}
    
// Apply splits
if(VerticleSplitPoint!=0)
{
    excel.ActiveWindow.SplitColumn=VerticleSplitPoint;
}
if(HorrizontalSplitPoint!=0)
{
    excel.ActiveWindow.SplitRow=HorrizontalSplitPoint;
}

Note that you have to AutoFit the columns before you set the screen splitting, else the splitting does not line up with the cell boundaries. Also, some formatting methods/properties in Excel apply to a worksheet but then others apply to a Window. The way to make this work nicely is to use the Activate method of the Worksheet. This makes the window containing the worksheet the active window; you can then use the ActiveWindow of the Application object to get to those method/properties which you cannot get to through the worksheet directly.

The complete script!

Do not forget that this example is set up for my test machine, so you will have to alter the connect strings and the queries if you want to use it.

// Inside the comment block below must be an XML document which 
// defines the properties for the reports created from this
// script
/*
//---START-config---
<config>
  <!-- There is one report section per SQL query and the results 
       from that query will go into a spreadsheet. There can be
       as many reports as you want.
    -->
  <report>
     <!-- Put the ado connect string in here. It must be the dbole
          string appropreate for the db in question.
       -->
     <connect><![CDATA[
       Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes; 
     ]]></connect>
     
     <!-- Put in here some sql which will produce a single result set.
       -->
     <sql><![CDATA[
     select * from T_User
     ]]></sql>
     
     <!-- This section holds the config for the spread sheet which will
          be created for and populated by the query defined for this
          report.
       -->
     <workSheet>
       <name>Users</name>
       <useAutoFilter>true</useAutoFilter>
       <verticleSplitPoint>1</verticleSplitPoint>
       <horrizontalSplitPoint>1</horrizontalSplitPoint>
     </workSheet>
  </report>
  <report>
     <connect><![CDATA[
        Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes; 
     ]]></connect>
     <sql><![CDATA[
     select * from T_BusnUnit
     ]]></sql>
     <workSheet>
       <name>UserGroups</name>
       <useAutoFilter>true</useAutoFilter>
       <verticleSplitPoint>1</verticleSplitPoint>
       <horrizontalSplitPoint>1</horrizontalSplitPoint>
     </workSheet>
  </report>
  <report>
     <connect><![CDATA[
        Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes; 
     ]]></connect>
     <sql><![CDATA[
     select * from T_Mchn
     ]]></sql>
     <workSheet>
       <name>Machines</name>
       <useAutoFilter>true</useAutoFilter>
       <verticleSplitPoint>1</verticleSplitPoint>
       <horrizontalSplitPoint>1</horrizontalSplitPoint>
     </workSheet>
  </report>
</config>
//---END-config---
*/


// Here are some handy connect strings
/*

SQL Server 2000 - SQL Server Authentication
===========================================
Provider=sqloledb;Server=XP1;Database=DV;Uid=myUsername;Pwd=myPassword; 

SQL Server 2000 - Windows Authentication
========================================
Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes; 

SQL Server 2005 - SQL Server Authentication
===========================================
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
 - Or for Express -
Provider=SQLNCLI;Server=myComputerName\SQLEXPRESS;
         Database=myDataBase;Uid=myUsername;Pwd=myPassword;

SQL Server 2005 - Windows Authentication
========================================
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
 - Or for Express -
Provider=SQLNCLI;Server=myComputerName\SQLEXPRESS;
         Database=myDataBase;Trusted_Connection=yes;

MySQL - Using MyODBC
====================
DRIVER={MySQL ODBC 3.51 Driver};SERVER=myServer;
        DATABASE=myDataBase;UID=myUserName;PWD=myPassword;OPTION=3;

MS Jet (The old db behind MS Access)
====================================
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myFile.mdb;Mode=Share Exclusive;

*/

/*===============================*/
/** DO NOT EDIT BELOW THIS LINE **/
/*===============================*/

RunReports();

function RunReports()
{
    // Now we have the record set, we create an instance of Excel so that;
    // we can write the data to it;
    var excel=WScript.CreateObject("Excel.Application");
    var wb=excel.WorkBooks.Add();

    // Here - as this is a demo we will make Excel visible, this makes the script;
    // nicer for the user because they get to see what is happening but this has;
    // the disadvantage that if the user interacts with Excel during the running;
    // of the script, it can cause the script to fail.;
    excel.visible=true;

    // Get the config XML and parse it into a DOM
    var xml=IncludeGrabber('config');
    var dom = WScript.CreateObject("Microsoft.XMLDOM");
    xml=xml.replace(/^\s*/,'');
    xml=xml.replace(/\s*$/,'');
    dom.loadXML(xml); 
    var entryNode=dom.firstChild;
    var reportNode=entryNode.firstChild;
    // Hunt through and comments
    while(reportNode.nodeName!='report') reportNode=reportNode.nextSibling;
    
    // Loop over all the report
    while(reportNode!=null)
    {
        // 'Walk' the dom to get the data for the report
        var connect=reportNode.firstChild
        while(connect.nodeName!='connect') connect=connect.nextSibling;

        var sql=connect.nextSibling;
        while(sql.nodeName!='sql') sql=sql.nextSibling;

        var workSheet=sql.nextSibling;        
        while(workSheet.nodeName!='workSheet') workSheet=workSheet.nextSibling;

        var name=workSheet.firstChild;
        while(name.nodeName!='name') name=name.nextSibling;

        var useAutoFilter=name.nextSibling;
        while(useAutoFilter.nodeName!='useAutoFilter')
            useAutoFilter=useAutoFilter.nextSibling;

        var verticleSplitPoint=useAutoFilter.nextSibling;
        while(verticleSplitPoint.nodeName!='verticleSplitPoint')
            verticleSplitPoint=verticleSplitPoint.nextSibling;

        var horrizontalSplitPoint=verticleSplitPoint.nextSibling;
        while(verticleSplitPoint.nodeName!='verticleSplitPoint')
            verticleSplitPoint=verticleSplitPoint.nextSibling;
     
        // Extract the text data from the text child nodes
        connect=connect.firstChild.nodeValue;
        sql=sql.firstChild.nodeValue;
        name=name.firstChild.nodeValue;
        useAutoFilter=useAutoFilter.firstChild.nodeValue;
        verticleSplitPoint=verticleSplitPoint.firstChild.nodeValue;
        horrizontalSplitPoint=horrizontalSplitPoint.firstChild.nodeValue;

        RunReport
        (
            excel,
            wb,
            connect,
            sql,
            name,
            verticleSplitPoint,
            horrizontalSplitPoint,
            useAutoFilter
        )        

        // Move to the next report or to the end of the reports
        reportNode=reportNode.nextSibling;
        while(reportNode!=null && reportNode.nodeName!='report')
            reportNode=reportNode.nextSibling;
    }
}

function RunReport
(
    excel,
    wb,
    connectString,
    sqlString,
    name,
    VerticleSplitPoint,
    HorrizontalSplitPoint,
    UseAutoFilter
)
{
    // Some handy definitions;
    var adOpenStatic;
    var adLockOptimistic;
    var adCmdText;
    adOpenStatic = 3;
    adLockOptimistic = 3;
    adCmdText = 1;
    
    // These two variables will hold the necessary
    // objects to read the csv file;
    // using Microsoft//s formidable ADODB library;
    
    var objConnection;
    var objRecordSet;
    objConnection = WScript.CreateObject("ADODB.Connection");
    objRecordSet  = WScript.CreateObject("ADODB.Recordset");
    
    objConnection.Open(connectString);
    
    objRecordSet.Open
    (
        sqlString, 
        objConnection,
        adOpenStatic,
        adLockOptimistic,
        adCmdText
    );
        
    // Let us create a new workbook in excel and find the first;
    // spread sheet in that workbook;
    
    var ws;
    // By default, Excel always populates new,
    // blank workbooks with sheets Sheet1,;
    // Sheet2 and Sheet3;
    ws=wb.worksheets.Add();
    ws.name=name;
    
    var nColumns=RecSet2WorkSheet(objRecordSet,ws);
    objRecordSet.Close();
    
    ws.Activate();
    
    var rng=ws.range(ws.cells(1,1),ws.cells(1,nColumns));
    
    // Apply Filters
    if(UseAutoFilter)
    {
        rng.AutoFilter();
    }
    
    // Apply Colour etc
    with(rng.Font)
    {
        Name = "Arial";
        FontStyle = "Bold";
        Size = 12;
    }
    
    with(rng.Interior)
    {
        ColorIndex = 42;
        Pattern = 1;
        PatternColorIndex = -4105;
    }
    
    // Autofit to make look nice
    for(var i=1;i<=nColumns;++i)
    {
        ws.columns(i).AutoFit();
    }
    
    // Apply splits
    if(VerticleSplitPoint!=0)
    {
        excel.ActiveWindow.SplitColumn=VerticleSplitPoint;
    }
    if(HorrizontalSplitPoint!=0)
    {
        excel.ActiveWindow.SplitRow=HorrizontalSplitPoint;
    }
}

function RecSet2WorkSheet(objRecordSet,ws)
{
    // this variable will keep track of the row we are on;
    var row=1;
    // and this the column;
    var column=1;
    // and this a particular field;
    var field;
    
    var dict=WScript.CreateObject("Scripting.Dictionary");
    var l;
    while(!objRecordSet.eof)
    {
        dict.RemoveAll();
        l=objRecordSet.Fields.count;
        // First time around, put in the column headers;
        if(row===1)
        {
            for(var i=0;i<l;++i)
            {
                field = objRecordSet.Fields.item(i);
                ws.cells(row,column).value=field.name;
                ++column;
            }
            row=2;
        }
        column=1;
        for(var i=0;i<l;++i)
        {
            field = objRecordSet.Fields.item(i);
            dict.Add("_" + i, field);
            ++column;
        }
        ws.range(ws.cells(row,1),ws.cells(row,l)).value=dict.Items();
        objRecordSet.MoveNext();
        ++row;
    }
    return l;
}

/** Function to extract from the script file
  * its self block of text between
  *   //---START-definition---
  *          and
  *   //---END-definition---
  * lines where definition is the passed string
  * argument.
  */
function IncludeGrabber(definition)
{
    var lines=new Array();
    var fso=WScript.CreateObject('Scripting.FileSystemObject');
    var ts2=fso.OpenTextFile(WScript.ScriptFullName,1,false);
    while(true)
    {
        var l=ts2.ReadLine();
        if(l=='//---START-'+definition+'---')
        {
            break;
        }
    }
    while(true)
    {
        var l=ts2.ReadLine();
        if(l=='//---END-'+definition+'---')
        {
            break;
        }
        lines.push(l+"\r\n");
    }
    ts2.close();

    var ar2=new Array();
    for(;lines.length>1;)
    {
        var l=lines.length;
        for(var c=0;c<l;c+=2)
        {
            if(c+1==l)
            {
                ar2.push(lines[c]);
            }
            else
            {
                ar2.push(''+lines[c]+lines[c+1]);
            }
        }
        lines=ar2;
        ar2=new Array();
    }
    return lines[0];
}

For much more on similar topics, please visit Nerds-Central!