<!------------------------------- STEP 3 --------------------------->
<!-- Add the article text. Please use simple formatting (
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?
The process runs in the following steps:
- JScript contacts the Server over the internet and requests a reply from the WebService. This is done using the XMLHTTP COM object.
- The WebServer run the service and returns the result (this is VERY simple in this example).
- JScript reads the output of the WebService and converts it into JScript data structures.
- JScript opens Excel as a COM object via the WScript.CreateObject method.
- JScript pumps the data from its internal data structures into an Excel spreadsheet.
- 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:
if(!ajax.status==200) throw('Got Wrong Status:'+ajax.status);
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:
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:
var dict = WScript.CreateObject("Scripting.Dictionary");
var rows = csvData.split("\n");
for(var row = 0;row<rows.length;++row)
dict.Add("_" + col, cols[col]);
var myDate=cols.split(' ');
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.
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';
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!):
var pcs = wb.PivotCaches();
var pc = pcs.Add
ptWs.Name='Hits By Day Summary Table';
Orientation = xlRowField;
Orientation = xlColumnField;
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:
ChartType = 51;
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