Introduction
The object of this post is to embed an image into a script which can then load it into MS Excel!
A few months ago, I would have been totally stumped as to how one could do this. However, it is actually dead easy. The key to messing around with applications like this
is to think of the script as a 'brain' that sits in the middle of different applications and data whilst controlling them all.
Step One, Building a JScript Archiver
As I have discussed in previous posts, it is possible to read and write binary files using JScript and the ADODB.Stream
object. Once the binary file is loaded into
a JScript string, it is then possible to use the Base64 codec (see previous posts) to encode this into a string which can be inserted into a JScript script.
All that has the upshot that we can load the image and create a JScript script that when run will regenerate it.
My script 'MakeArchive.js' does just this. All you have to do is 'drag and drop' a binary file on it and it will archive it into a js file with the same file
name with '_archive_' postfixed. This is a long script so I will just highlight parts of it here for you:
function BinaryFile(name)
{
...
}
var fso=WScript.CreateObject('Scripting.FileSystemObject');
for(var i=0;i<WScript.arguments.count();++i)
{
var chunks;
var fn=WScript.arguments.item(i);
var bf1=new BinaryFile(fn);
var b64=new Base64();
chunks=b64.encode(bf1.ReadAll()).split('\n');
var ts=fso.CreateTextFile(fn+'_archive_.js',true);
ts.WriteLine('function UNArchive(name){');
ts.WriteLine('var encArray=new Array();');
for(var j=0;j<chunks.length;)
{
var inner='';
for(var k=0;k<8 && j<chunks.length;++j,++k)
{
inner+=chunks[j];
}
ts.WriteLine('encArray.push("'+inner+'");');
...
var fso=WScript.CreateObject('Scripting.FileSystemObject');
var ts2=fso.OpenTextFile(WScript.ScriptFullName,1,false);
while(true)
{
var l=ts2.ReadLine();
if(l=='//---START-ARCHIVE-CODE---')
{
break;
}
}
while(true)
{
var l=ts2.ReadLine();
if(l=='//---END-ARCHIVE-CODE---')
{
break;
}
ts.WriteLine(l);
}
ts2.close();
ts.close();
Once the archiver has run, you can take the output file and use the source code in it to embed binary data into a new script. Below is an outtake of the script I created
to place the WebNerd image into Excel. Once it has opened Excel and inserted the image, it also adds a command button to the spreadsheet. The script listens for the 'Click' event
of this button and upon receiving it, closes Excel and then itself. The script segment below is well commented, so please read the comments to understand how it works.
var excel=RunExcel();
while(true)
{
WScript.Sleep(20);
try
{
var test=excel.WorkBooks.Item(1).name;
}
catch(e)
{
WScript.Quit(1);
}
}
function RunExcel()
{
var excel=WScript.CreateObject('Excel.Application');
excel.visible=true;
var wb=excel.WorkBooks.Add();
var ws=wb.WorkSheets.Item(1);
ws.name="Image Page";
wb.WorkSheets.Item(2).Delete();
wb.WorkSheets.Item(2).Delete();
var tempf=GetTempFile();
tempf=tempf+'.png';
UNArchive(tempf);
ws.Cells(1,1).Select();
ws.Pictures.Insert(tempf);
var objContainer = ws.OLEObjects.Add
(
"Forms.CommandButton.1",
undefined,
false,
false,
undefined,
undefined,
undefined,
400*0.75,
190*0.75,
80,
20
);
var button1=objContainer.Object;
button1.caption='Click To Close';
WScript.ConnectObject(button1,'Button1Event_');
return excel;
}
function Button1Event_Click()
{
try
{
excel.DisplayAlerts=false;
excel.Quit();
}
catch(e){}
WScript.Quit(0);
}
Storing Binary Data, Loading an Image Into Excel, and Reacting to Excel Object Events
So what have we achieved here? In the face of it, not much possibly. The reality is that we have made a huge step towards creating a framework in which enterprise level,
distributed applications can be built from MS Office and Windows Scripting Host. If we couple this work with the previous work on creating management reports using Excel and AJAX,
we have removed the boundary between Web Applications and Thick Clients. What we have achieved is a full integration of the familiar MS Office interface with the modern
Service Oriented Architecture approach for enterprise applications.
Let us review what we have so far in support of the above statement. We can link MS Office with a remote Web Service and create complex reports. We can drive binary data
into MS Office applications. We can respond to MS Office events. This means we can create complex MS Office forms with first rate graphics using images and OLE objects.
We can then listen to events created by those forms and from those events trigger communication to Web Services. We can then use Web Services to drive back first rate management
information and reports. That sounds like a 21st first century enterprise application interface! But this time it is 100% familiar to the end user, already on their computers
and easy to code up.
Exceed Expectations With Exsead
All this put together, along with a few new tricks to fill in the gaps is what I am now calling Exsead - EXtreme Scripting Enterprise Application
Development. I shall be covering more on this subject over the coming weeks.
For more like this from me, see Nerds-Central (my blog).