| A business requirement I ran across this week was to take the results from a query, generate an Excel file, and send the generated xls via cfmail. During the week I discovered that there was something called OpenXCF that would make my dreams come true. Personally, I found it was not the easiest to work with and that, for me, the benefits were not that useful - as noted in numerous comments scattered about a Google search, it was next to impossible to figure out how to install it, and once I did, I was able to read an XLS and generate a query, but never did get it to write an XLS file. In case you ever do want to go down that road, here's what I did to get it [javacfx] working (reading in an XLS) - Steps 1 - 6. For a shortened, simpler approach utilizing only the Apache POI Project, follow Steps 4 - 11: |
1) downloaded the javacfx.jar
2) expanded the jar file with winzip and extracted the ExcelQuery.class to the "your_cfmx_install_directory\WEB-INF\classes" directory maintaining the folder structure as that shown in the package in the class (e.g. something like C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\classes\net\sourceforge\openxcf\javacfx or C:\CFusionMX7\wwwroot\WEB-INF\classes\net\sourceforge\openxcf\javacfx)
3) register the CFX tag in the CF Admin (tag name: cfx_ExcelQuery, class name: net.sourceforge.openxcf.javacfx.ExcelQuery)
4) downloaded the POI-HSSF poi-2.5-final-20040302.jar
5) placed the jar file in the "your_CFMX_install_directory\WEB-INF\lib" directory
6) restarted CFMX service
After spending too many hours trying to get the javacfx "writing" part working, I ran across a blog comment at "d-ross.org" about POI and decided (since I already had POI installed) to leverage the code snippet I found to get the XLS written to the file system.
Here's what I did next:
7) created a CustomTag that incorporated the appropriate calls to the class methods inside the POI jar file.
8) wrote code to grab some query data, and passed my query to my CustomTag
9) shazam! I now had a new XLS that had the query data displayed.
10) now my cfmail tag could use cfmailparam to send my new, dynamically generated XLS file.
11) dream realized.
Try a demo
* A zip archive of the code I used to test as well as my CustomTag is included. |
![[Valid RSS]](/blogman/assets/images/valid-rss-rogers.png "Validate my RSS feed")
