Using XMLHTTP Request Objects Excel Can Become A WebService Consumer - Easil

• Download source - 4 Kb

Using XMLHTTP Request Objects Excel Can Become A WebService Consumer - Easily

All you need to achieve this is Windows (Win98 or better should work), Excel (2000 or later, no need for Office, Office Pro etc) and Internet Explorer 6 (or better). There are no optional installs required. This is just 100% standard stuff! However, it has taken me 3 years of poking around to find the easiest and most effective way to pull the trick off. What I describe here is very simple and very powerful, but - up till now - shockingly underused

The code is written in JScript. I prefer to do coding in JScript to VBScript because I find it more flexible etc. However, if enough people ask, I am happy to write a VBScript version and post it.

The source for the code can be found here (right-click, SaveAs).

Do not click on Excel whilst the script it running. This is a demo - and it shows Excel running. In production Excel would be hidden until the script finished.

So: How Does It Work?

Excel<->JSCript<->Internet<->WebServer<->WebService

The process runs in the following steps:

1. JScript contacts the Server over the internet and requests a reply from the WebService. This is done using the XMLHTTP COM object.
2. The WebServer run the service and returns the result (this is VERY simple in this example).
3. JScript reads the output of the WebService and converts it into JScript data structures.
4. JScript opens Excel as a COM object via the WScript.CreateObject method.
5. JScript pumps the data from its internal data structures into an Excel spreadsheet.
6. JScript instructs Excel to process the data to produce management reports (in this case a Pivot Table and associated Pivot Chart).

As you can see, the process is fairly straight-forward. Some complex interactions involving multiple WebServices, multiple calls to one or more WebServices and/or interaction with local files are possible but beyond the scope of this article

Stepping Through The Code And Seeing The Results

First here is the function that contacts the WebService and gets the result:

function GetCSVFile()
{
    // Create an AJAX object
    var ajax=WScript.CreateObject('Microsoft.XMLHTTP');

    // Try to get the file 32 times then give up
    for(var i=0;i<32;++i)
    {
        try
        {
            // Set it to point to the web service
            // Note the last argument being false forces the script to
            // wait for the response
            ajax.open('GET','http://www.mightycrap.com/Logs/nerds.csv',false);
            
            // Set the request header so that the connection is closed right
            // away to avoid locking up server resource
            ajax.setRequestHeader
            (
                'Connection',
                'close'
            );
            
            // Send the request and wait
            ajax.send();

            // The server returned an error
            if(!ajax.status==200) throw('Got Wrong Status:'+ajax.status);
            
            // Get the reply from the service OK, return
            return ajax.responseText;
            
        }
        catch(e)
        {
            // Note the use of the () around the maths makes the numeric calculation
            // occure and the result be turned into a string by the + opperator 
            WScript.echo('Failed to get csv record from mightycrap: tries left='+(32-i));
        }
    }
}

The absolute most critical part to the whole approach is the use of the XMLHTTP object:

// Create an AJAX object<br/>
var ajax=WScript.CreateObject('Microsoft.XMLHTTP');<br/>

This is object which started the whole AJAX movement off. Its functions are replicated in FireFox and other major browsers, to it is a very useful object to learn about. Whilst it does have XML parsing functionality built in, we are not actually going to use that part of its interface in this example.

As the comments explain, we use a synchronous request. We have nothing else to do whilst waiting for the response so we may as well just wait. The other thing that is worth learning is that whilst users tolerate web pages that do not load first time (just tolerate) they totally despise applications which fail. The HTTP protocol talking to web services can fail, and does, a lot. So it is well worth placing web service requests in a loop and trying again if they fail. Just make sure the loop does exist eventually if the request never succeeds!

Once the data has come back from the service we pipe it to Excel. The script at the part does several things that might cloud the issue. So, as you can download that and look at the comments, here is a cut down version to discuss here:

// Create a new Excel in the usual way
var excel=WScript.CreateObject('Excel.Application');

...

// Now we get a workbook and then the first worksheet.
// We get rind of the other worksheets as they are not required
var wb=excel.WorkBooks.Add();
while(wb.WorkSheets.Count>1)
{
    wb.WorkSheets.Item(1).Delete();
}
ws=wb.WorkSheets.Item(1);
ws.name="Raw Data";

// Get the csv data and place it into Excel.
// The trick to making this work
// quickly is to do it one line at a time rather than one cell
// at a time.  We can do this in JScript by getting a Dictionary
// object to create arrays of variants for us.
var dict = WScript.CreateObject("Scripting.Dictionary");
var rows = csvData.split("\n");

...

for(var row = 0;row<rows.length;++row)
{
    // Chop each row into its fields
    var cols=rows[row].split(",");

    ...

    // Empty the dictionary each time we go around
    // the loop
    dict.RemoveAll();
    // Load it with the new fields
    for(var col=0;col<14;++col)
    {
        dict.Add("_" + col, cols[col]);
    }

    // Add in the date in a sortable format for Excel to use
    var myDate=cols[0].split(' ');
    myDate=myDate[1].substr(0,myDate[1].length-2)+' '+myDate[3]+myDate[4];
    dict.Add("_14",myDate);

    // Offset row by 1 because it is 1 indexed and another 1 because
    // of the headers
    var arr=dict.Items();
    ws.Range(ws.Cells(prow+2,1),ws.Cells(prow+2,15)).Value = arr;
}

Notice how we make the process of loading data into Excel quicker by doing it one row at a time. Loading data into Excel one field at a time is very very slow indeed! Excel will allow you to feed values in to a range of cells at once using the Range.Value property. To do this you have to pass Excel a COM 'SafeArray' which JScript cannot create directly. However, the Scripting.Dictionary object can, so we use this object as an intermediate step to loading Excel. This approach is still dominated by the speed of Excel, so the using the Dictionary object does not have any noticeable speed impact. Should all this work, you will get a spread sheet that looks like this:

Next the script puts in the column headers and autofits the columns. The column headers are CRITICAL because these are what the Pivot Table code in Excel uses to generate the table and chart.

// Add in the headers
var heads='Date,URL,Referrer,Browser Id,Browser Version,OS Id,OS Version,Screen Resolution,URL Scheme,URL Host,URL Path,Query,Client IP,Client Address,Date Day';
heads=heads.split(',');
for(var i=0;i<15;++i)
{
    ws.Cells(1,1+i).value=heads[i];
    // Auto Fit the Columns
    ws.Columns(i+1).AutoFit();
}

Once the headers and fitting have happened the spread sheet should look something like this:

Now we have the raw data in place, we need to create the Pivot Table. The code to do this is below (with comments!):

// To do this we first add a pivot cache to the work book.
// The pivot cache is used to hold and computer the data
var pcs = wb.PivotCaches();
var pc = pcs.Add
(
    xlDatabase,
    "'Raw Data'!R1C1:R"+prow+"C15"
)

// Now we create a new worksheet into which we
// can place the pivto table
var ptWs=wb.WorkSheets.Add();
ptWs.Name='Hits By Day Summary Table';

// Now we create the pivot table, remembering its name
// as we will use that later
var ptn='PivotTable1';
// This range is where the top left hand corner of the table
// will go
pc.CreatePivotTable
(
       ptWs.Range('A1'),
       ptn
   );

   // Now we can look up the pivot table be name when ever we want
   with(ptWs.PivotTables(ptn))
{
    // This section adds the data to the table
    // in a self explanatory way
    // NB: you cannot rename a field to a name
    // that already exists in the pivot data - even
    // if you are not showing that data. For example
    // you cannot make this field have the name 'Date'
    // as that is taken.
    with(PivotFields("Date Day"))
    {
        Orientation = xlRowField;
        Name='Day';
    }

    with(PivotFields("URL Host"))
    {
        Orientation = xlColumnField;
        Name='Host';
    }

    with(PivotFields("Date"))
    {
        Orientation = xlDataField;
        Name = '# Hits';
    }
}

You might have noticed that in the above code I have used some Excel constants like xlDataField. All these (and a load more) are defined at the start of the script (which you can download - see the start of the post). I have not included the definitions here as they are boring and just take up loads of space.

The resulting Pivot table should look something like this:

Now we have a Pivot Table we can create a Pivot Chart from it:

var chart=excel.Charts.Add();
with(chart)
{
    // Point the source data to the top left
    // non lable field of the pivto chart
    SetSourceData(ptWs.Range("B4"));
    // Make the chart be a new sheet
    Location(xlLocationAsNewSheet);
     // Column Clusterted
    ChartType = 51;
    // This alchemy sets up the lables are we want
    ApplyDataLabels
    (
        2,
      true,
      false,
      false,
      false,
      false,
      true,
      false,
      false
    );
}

Cool Things You Can Do With The Pivot Table

It is easy to think that the Pivot Table is just a means of creating the chart. Actually, it is a very powerful analysis tool. If you click on one of the data or total cells, Excel will create a new spreadsheet highlighting where that data came from. This can be seen in the following two images:

For more information and similar articles, visit nerds-central.blogspot.com