May 2018

          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
:: 04/21/2006 ::
CFML and Excel - Beauty and the Beast
Return   Back To Top
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 "" 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.


canada goose femme pas cher Soldes Louboutin Chaussures Soldes Louboutin louis vuitton taschen outlet louboutin outlet uk billig canada goose canada goose tilbud goyard pas cher longchamp bags outlet Monlcer udsalg billige parajumpers YSL replica sac louis vuitton pas cher Canada Goose Pas Cher Canada Goose Outlet UK Moncler Outlet uk goyard replica bags polo Lacoste pas cher Bolsos Longchamp España Moncler Jakker tilbud Doudoune moncler pas cher Parajumpers Jakker tilbud Ralph Lauren Soldes Parajumpers Outlet louis vuitton replica Moncler Jas sale Billiga Canada Goose Jacka Canada Goose outlet Billiga Moncler Doudoune Canada Goose Pas Cher Canada Goose Pas Cher Louboutin Soldes Canada Goose Pas Cher Hemers replica Doudoune Canada Goose Pas Cher prada replica Canada Goose Pas Cher Canada Goose Soldes Doudoune Canada Goose Pas Cher Canada Goose Pas Cher Canada Goose outlet Canada Goose outlet Canada Goose outlet