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>
<report>
<connect><![CDATA[</connect>
<sql><![CDATA[</sql>
<workSheet>
<name>Users</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
<report>
<connect><![CDATA[</connect>
<sql><![CDATA[</sql>
<workSheet>
<name>UserGroups</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
<report>
<connect><![CDATA[</connect>
<sql><![CDATA[</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:
var reportNode=entryNode.firstChild;
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.
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;
while(reportNode.nodeName!='report')
reportNode=reportNode.nextSibling;
while(reportNode!=null)
{
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;
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));
if(UseAutoFilter)
{
rng.AutoFilter();
}
with(rng.Font)
{
Name = "Arial";
FontStyle = "Bold";
Size = 12;
}
with(rng.Interior)
{
ColorIndex = 42;
Pattern = 1;
PatternColorIndex = -4105;
}
for(var i=1;i<=nColumns;++i)
{
ws.columns(i).AutoFit();
}
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>
<report>
<connect><![CDATA[</connect>
<sql><![CDATA[</sql>
<workSheet>
<name>Users</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
<report>
<connect><![CDATA[</connect>
<sql><![CDATA[</sql>
<workSheet>
<name>UserGroups</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
<report>
<connect><![CDATA[</connect>
<sql><![CDATA[</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()
{
var excel=WScript.CreateObject("Excel.Application");
var wb=excel.WorkBooks.Add();
excel.visible=true;
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;
while(reportNode.nodeName!='report') reportNode=reportNode.nextSibling;
while(reportNode!=null)
{
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;
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
)
reportNode=reportNode.nextSibling;
while(reportNode!=null && reportNode.nodeName!='report')
reportNode=reportNode.nextSibling;
}
}
function RunReport
(
excel,
wb,
connectString,
sqlString,
name,
VerticleSplitPoint,
HorrizontalSplitPoint,
UseAutoFilter
)
{
var adOpenStatic;
var adLockOptimistic;
var adCmdText;
adOpenStatic = 3;
adLockOptimistic = 3;
adCmdText = 1;
var objConnection;
var objRecordSet;
objConnection = WScript.CreateObject("ADODB.Connection");
objRecordSet = WScript.CreateObject("ADODB.Recordset");
objConnection.Open(connectString);
objRecordSet.Open
(
sqlString,
objConnection,
adOpenStatic,
adLockOptimistic,
adCmdText
);
var ws;
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));
if(UseAutoFilter)
{
rng.AutoFilter();
}
with(rng.Font)
{
Name = "Arial";
FontStyle = "Bold";
Size = 12;
}
with(rng.Interior)
{
ColorIndex = 42;
Pattern = 1;
PatternColorIndex = -4105;
}
for(var i=1;i<=nColumns;++i)
{
ws.columns(i).AutoFit();
}
if(VerticleSplitPoint!=0)
{
excel.ActiveWindow.SplitColumn=VerticleSplitPoint;
}
if(HorrizontalSplitPoint!=0)
{
excel.ActiveWindow.SplitRow=HorrizontalSplitPoint;
}
}
function RecSet2WorkSheet(objRecordSet,ws)
{
var row=1;
var column=1;
var field;
var dict=WScript.CreateObject("Scripting.Dictionary");
var l;
while(!objRecordSet.eof)
{
dict.RemoveAll();
l=objRecordSet.Fields.count;
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 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!