 |
Web Development »
Client side scripting »
General
Intermediate
Using XMLHTTP Request Objects Excel Can Become A WebService Consumer - EasilBy alex turnerAn article on feeding data into Excel from web services and formating it into management reports |
Javascript, Windows, Visual-Studio, Dev
|
||||||||
|
Advanced Search Add to IE Search |
|
|
|
||||||||||||||||
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.
The process runs in the following steps:
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
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.
var ajax=WScript.CreateObject('Microsoft.XMLHTTP');
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
);
}
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
| You must Sign In to use this message board. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads.
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 24 Jan 2007 Editor: |
Copyright 2007 by alex turner Everything else Copyright © CodeProject, 1999-2010 Web09 | Advertise on the Code Project |
