Coldfusion 8, .NET and Excel Example

On March 12, 2008, in ColdFusion, by Anuj Gakhar

In an attempt to do something with Coldfusion’s .NET functionality, I tried to consume any existing .NET component to do something useful with it. Ideally, in order for this exercise to be really termed as useful, I should have been writing my own .NET component and then consume it in CF, but I would leave that exercise for later on, given the fact that my .NET skills are not so good and I haven’t really done a lot of .NET except a few tidbits here and there.

So, having said that, I decided to do something with Excel and started looking for a publicly available .NET component for Excel. Although there are a lot of ways to do that already, via CF, as described by Charlie Arehart (which happens to be a really informative list), I still wanted to do it via a .NET component just for the sake of doing it.

So, I searched on Google for some Spreadsheet components written in .NET. To my surprise, the list is really big for the available components, but I could find only this one which offered a free version as well. All others were commercial products with a big (or not so big) price tag. So, I downloaded it, and played around with it a bit and I actually do the like the features it has to offer. Its a shame that the free version only supports the first 150 rows in the spreadsheet (whether you read or write). But that was not a reason to not to try it out.

So, I went ahead and wrote a little CFC that would consume the provided .dll (based on the version of .NET installed on your system) . The feature list that the component provides is really big but at the moment, I am only using the ExportToHtml functionality. I thought it would be a good start and I can always expand the code to have other functions when I get some time.

The CFC code and the sample usage file is below.  This might not be the best way to write .NET consumption code but I have given it a good try. In order to test run this code, you would need to place the CFC (call it excelNet.cfc) and the downloaded “Bin” folder in the same directory. Then just throw in a .xls file in the same folder and run the usage code below. You can give a name to the output .html file if you want , otherwise it generates a timestamp based .html file.

I am not sure if this is worth the effort or not, but if it is, I would like to know from folks who try this code out. Also, I would like to know if anyone else is using the .NET functionality in CF8 and if yes, how ?

The usage code :-

[xml]
<cfset obj = CreateObject("component","excelNet").init()>
<cfset myFile = "#ExpandPath(‘test.xls’)#">
<cfset obj.ExportToHtml(inFile="#myFile#",showGridLines="true",outFile="#Expandpath(‘testing.html’)#")>
[/xml]

And the CFC code :-

[xml]
<cfcomponent name="Excel .NET CFC Wrapper">

<cfscript>
this.dotnetVersion = "";
this.dllFolder = "";
</cfscript>

<cffunction name= "init" returntype="any" access="public">
<cfscript>
// because the Gembox software comes with 4 different version of DLL files, we need to
//find the version of .NET installed on the system and then pick up the correct DLL
findDotNetVersion();
setDllFolder();
return this;
</cfscript>
</cffunction>

<cffunction name="setDllFolder" access="private">
<cfscript>
currentFolder = Expandpath(‘.’) & "\bin";
switch(this.dotnetVersion)
{
case "1":
{
this.dllFolder = currentFolder & "\NET10\";
break;
}
case "2":
{
this.dllFolder = currentFolder & "\NET20\";
break;
}
case "3":
{
this.dllFolder = currentFolder & "\NET30\";
break;
}
case "35":
{
this.dllFolder = currentFolder & "\NET35\";
break;
}
}
</cfscript>
</cffunction>

<cffunction name="getDotNetVersion" returntype="string" access="public">
<cfscript>
return this.dotnetVersion;
</cfscript>
</cffunction>

<cffunction name="setDotNetVersion" returntype="string" access="public">
<cfargument name="dotnetVersion" type="string" required="true" default="2">
<cfscript>
this.dotnetVersion = arguments.dotnetVersion;
</cfscript>
</cffunction>

<cffunction name="findDotNetVersion" returntype="string" access="public">
<cfscript>
try
{
objEnv = CreateObject("dotnet","System.Environment");
majorVersion = objEnv.Get_Version().Get_Major();
this.dotnetVersion = JavaCast("string", majorVersion);
} catch (any Exception) {
this.dotnetVersion = "2"; // safely , silently set to version 2
}
</cfscript>
</cffunction>

<cffunction name="ExportToHtml" access="public" returntype="boolean">
<cfargument name="inFile" required="true" type="string" >
<cfargument name="outFile" required="false" type="string">
<cfargument name="showColumnLetters" type="boolean" required="false" default="false">
<cfargument name="showGridLines" type="boolean" required="false" default="false">
<cfargument name="showRowNumbers" type="boolean" required="false" default="false">
<cfscript>
var outputFilename = "";
var ret = false;
if(isdefined("arguments.outFile") and len(trim(arguments.outFile)))
{
outputFilename = arguments.outFile;
} else {
outputFilename = "excelNet_" & dateformat(now(),’yyyymmdd’) & timeformat(now(),’mmss’) & ".html";
outputFilename = ExpandPath(‘.’) & "\" & outputFilename;
}
// start the real work
objGem = createObject("dotnet", "GemBox.Spreadsheet.ExcelFile", "#this.dllFolder#\GemBox.SpreadSheet.dll");
objOptions = createObject("dotnet", "GemBox.Spreadsheet.HtmlExporterOptions", "#this.dllFolder#\GemBox.SpreadSheet.dll");
if (isDefined("arguments.showColumnLetters") )
{
objOptions.Set_ShowColumnLetters(Javacast("Boolean",arguments.showColumnLetters));
}
if (isDefined("arguments.showGridLines") )
{
objOptions.Set_ShowGridLines(Javacast("Boolean",arguments.showGridLines));
}
if (isDefined("arguments.showRowNumbers") )
{
objOptions.Set_ShowRowNumbers(Javacast("Boolean",arguments.showRowNumbers));
}
objGem.LoadXls(arguments.inFile);
// export to HTML
objGem.Get_Worksheets().Get_ActiveWorksheet().GetUsedCellRange().ExportToHtml(
outputFilename
, objOptions
, javacast("boolean","true")
);
ret = true;
return ret;
</cfscript>
</cffunction>
</cfcomponent>

[/xml]

Here is the component I used.

GemBox.Spreadsheet – .NET component for importing/exporting to Excel files (XLS, XLSX, CSV) and exporting to HTML.

Tagged with:  

2 Responses to Coldfusion 8, .NET and Excel Example

  1. […] of press is the .NET integration. Anuj Gakhar demonstrates a potential use for the feature in his Coldfusion 8, .NET and Excel Example. When playing around with new features, you’ll want to be able to access the documentation – […]

  2. […] of press is the .NET integration. Anuj Gakhar demonstrates a potential use for the feature in his Coldfusion 8, .NET and Excel Example. When playing around with new features, you’ll want to be able to access the documentation […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar
%d bloggers like this: