Modify Excel Spreadsheets! CFXL Released to RIAForge
This is a bit of a repost, but the new big news is that I have finally had the time to clean up the code for posting to RIAForge! :) Take a look at CFXL on RIAForge.
The backstory: 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 rewrote the code... and now I've rewritten it once more. Version 1 can do the following. Take an existing excel spreadsheet with images/logos, charts defined, etc., and modify the values of empty cells, 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! This can also take an empty spreadsheet and fill it with anything the JExcelAPI lets you (pretty much everything).
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
Update: Here is the code example in case you're too busy to download and configure all of this to see what it does. :) (Note: the code highlighting here is from another project I have on RIAForge called ColdFish)
cfxl = createObject("component","org.delmore.cfxl").init("#GetDirectoryFromPath(GetCurrentTemplatePath())#/demo.xls");
cfxl.setSheet("CFInsider Demo");
cfxl.setCell("A",13,"My Example Excel File");
cfxl.setCell("A",14,100);
cfxl.setCell("A",15,100);
cfxl.setCell("A",16,200);
cfxl.setCell("A",17,500);
cfxl.setCell("A",18,1000);
cfxl.setCell("B",36,now());
cfxl.setCell("B",37,True);
// Note, you can go right in and grab the current sheet and use all of the JExcel APIs directly... same goes for cells and workbooks.
cfxl.getSheet().getSettings().setZoomFactor(200);
cfxl.viewWorkbook();
</cfscript>
or if you prefer tags
<cf_xlparam column="A" row="13" value="My Example Excel File"/>
<cf_xlparam column="A" row="14" value="100"/>
<cf_xlparam column="A" row="15" value="100"/>
<cf_xlparam column="A" row="16" value="200"/>
<cf_xlparam column="A" row="17" value="500"/>
<cf_xlparam column="A" row="18" value="1000"/>
</cf_xl>
The output is something like this... but probably nicer because you will make it look good. :)
Happy coding, :) Jason
I modified the cfxl.cfc to allow the init() method to take a second argument which would be an instance of Mark Mandel's JavaLoader inited with the jxl.jar. I noticed at least one comment from your previous post asking for this, plus I wanted it myself. So you can do something like this:
-------
if (not isdefined("server.javaloader.jxl")) {
aLib = [ expandpath("path/to/jxl.jar") ];
server.javaloader.jxl = createobject("component","javaloader.JavaLoader").init(aLib);
}
args = { javaloader = server.javaloader.jxl };
cfxl = createobject("component","cfxl").init(argumentCollection=args);
------
I'd be happy to send you a patch if you'd like to add this capability.