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
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. :)
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_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
Jason! Do you know what this means?
Try it out and let me know what you guys think. :)
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
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
I.e(
<cf_jxlparam column="M" row="5" value="#acct3213.location# "/> )
Any suggestions would be appreciated.
Thanks
Jason
The Subversion Repository is at http://svn.riaforge.org/cfxl/
Cheers! :)
Jason