Modify Excel Spreadsheets with ColdFusion the easy way!

Update: This project has been released to RIA Forge as CFXL. Check it out at http://cfxl.riaforge.org/

A long time ago, for a previous company... back in the CFMX 6 days, I wrote code for leveraging JExcel API in ColdFusion. We wanted to have our excel reports professional. The reporting feature that is in ColdFusion today wasn't around, and even now that it is, I still really like this method.

For a session at MAX 2006 - Developing ColdFusion-Java Hybrid Applications, I rewrote this functionality as a quick demo of what could be done pretty easily. Looking back at even that code, I saw a lot of areas that needed improvement. So, I've rewritten the code again and am releaseing it as open source. Version .1 can do the following. Take an existing excel spreadsheet with images/logos, charts defined, etc., and modify the values of numerics, strings, dates, and booleans. The code makes modifications in a ByteArrayOutputStream and streams them to the browser... read that as... it doesn't write to a file unless you tell it to, so it's wicked FAST!

Anything that works more than that is just gravy at this point. :)

What you'll find in the zip file...

  • A component that abstracts and simplifies JExcelAPI
  • A pair of custom tags for simplifying things even further
  • Example files on how to use either of these methods

Get the JXLCFC code here.

If you think this is useful code, let me know! I may add it to RIAForge and beef it up if people actually use it. Otherwise, I will leave it as an interesting example of how to leverage and simplify an open source Java API with ColdFusion.

Update: Here is the code example in case you're too busy to download and configure all of this to see what it does. :)

<cfscript>
    jxl = createObject("component","com.cfinsider.jxl").init("#GetDirectoryFromPath(GetCurrentTemplatePath())#/demo.xls");
    jxl.setSheet("CFInsider Demo");
    jxl.setCell("A",13,"My Example Excel File");
    jxl.setCell("A",14,100);
    jxl.setCell("A",15,100);
    jxl.setCell("A",16,200);
    jxl.setCell("A",17,500);
    jxl.setCell("A",18,1000);
    jxl.setCell("B",36,now());
    jxl.setCell("B",37,True);
    jxl.viewWorkbook();
</cfscript>

or if you prefer tags

<cf_jxl action="view" source="#GetDirectoryFromPath(GetCurrentTemplatePath())#/demo.xls" sheet="0">
<cf_jxlparam column="A" row="13" value="My Example Excel File"/>
    <cf_jxlparam column="A" row="14" value="100"/>
    <cf_jxlparam column="A" row="15" value="100"/>
    <cf_jxlparam column="A" row="16" value="200"/>
    <cf_jxlparam column="A" row="17" value="500"/>
    <cf_jxlparam column="A" row="18" value="1000"/>
</cf_jxl>

The output is something like this... but probably nicer because you will make it look good. :)

Happy coding, :) Jason

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Raymond Camden's Gravatar Release this bad boy to RIAForge.
# Posted By Raymond Camden | 1/4/08 8:13 AM
todd sharp's Gravatar Looks like it definitely has potential Jason. If I may make a suggestion, would you consider using Mark Mandel's JavaLoader to load the JExcel API to avoid the requirement of dropping the API into the CF classpath? That way folks on shared hosting can take advantage of this awesome package. Just a thought...
# Posted By todd sharp | 1/4/08 9:02 AM
Phillip Senn's Gravatar 1.21 GIGAWATTS!!
Jason! Do you know what this means?
# Posted By Phillip Senn | 1/4/08 9:23 AM
Jason's Gravatar Well... 1.21 Gigawatts either means that this is taking you back in time... or that this gives you the power of a nuclear reaction? :)

Try it out and let me know what you guys think. :)
# Posted By Jason | 1/4/08 11:34 AM
Ben Nadel's Gravatar Cool stuff.
# Posted By Ben Nadel | 1/4/08 12:00 PM
jeffc's Gravatar I'm kind of standing here with my mouth half open trying to grasp what this could mean. I've spent weeks of my time trying to get stuff into Excel in a way that doesn't look like crap and maintains consistency in data types/presentation from app to Excel file. Could this by the answer for non-Java guys like me?
# Posted By jeffc | 1/7/08 3:05 PM
Jason's Gravatar Yep. Assuming you're at least a CF guy. :) I already have a more recent version that I haven't posted yet that has some more error handling. Although, if you don't do anything silly like try to set a date field with a non-date string, what's there should already do the trick.

Give it a whirl and post your questions or issues. I am also planning on having an updated version posted on RIAForge this week. I'd love to get feedback from some people who have used it so I can make it better.

Jason
# Posted By Jason | 1/7/08 4:30 PM
Bilal's Gravatar You are a life saver man... real good stuff.. but i have a question if you could tell me.. i need to pass decimal values to the excel sheet... any ideas?
# Posted By Bilal | 6/9/08 2:05 AM
Jason Delmore's Gravatar Hi Bilal,

It looks like my code has a bug. Go figure. :) You just need to change the code where it sets a Number value to cast as "Double" rather than "Int"

Here's what it looks like

<cfelseif cell.GetType().toString() eq "Number">
<cfset cell.setValue(JavaCast("double",arguments.newValue))/>

Also, don't forget to change the format of the existing spreadsheet cell to show beyond the decimal.

Hope that helps!
Jason
# Posted By Jason Delmore | 6/9/08 11:32 AM
Bret's Gravatar I was very excited when I found your post! Unfortunately it isn't working that well for me. When I run yourtag example I get the updates in column A but none in column B (the script version worked fine). With my own spreadsheet I'm having problems getting even that much working. Would macros or autofilter cause any problems with this?
# Posted By Bret | 7/10/08 9:16 AM
Dang Lim's Gravatar Great program, but I have a problem when I try to use variables it does not get passed through.

I.e(
<cf_jxlparam column="M" row="5" value="#acct3213.location# "/> )

Any suggestions would be appreciated.
Thanks
# Posted By Dang Lim | 7/11/08 4:43 PM
Jason Delmore's Gravatar I have later builds that have a bunch of bug fixes that I haven't had a chance to post. Feel free to contact me directly. I will post to RIAForge soon!

Jason
# Posted By Jason Delmore | 7/11/08 5:12 PM
Steve Adler's Gravatar Great...can you post the links to the updated builds? Thanks again for sharing such
# Posted By Steve Adler | 4/20/11 5:08 PM
Jason Delmore's Gravatar The project site is at http://cfxl.riaforge.org/
The Subversion Repository is at http://svn.riaforge.org/cfxl/

Cheers! :)
Jason
# Posted By Jason Delmore | 4/21/11 9:08 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.9. Contact Blog Owner