Code sample Coldfusion: Export List to Excel file in Coldfusion by EasyXLS library. XLS, XLSX, XLSM, XLSB spreadsheets in Coldfusion Excel library for exporting data to Excel files in ColdFusion. Still it's not working. I am getting same output. Requirement - Export query result to excel sheet. But now I am just trying to open excel file on click of button.
Aug 27, 2010 Thanks for the response. I am presently generating a PDF file and using HTML formatting to format a table inside it so that the generated contents can be placed properly inside a table.
I'm using the code below to export data from a database to an Excel sheet. What I want to do is have a certain set of fields be exported to a different sheet in the same workbook. The end result being that each person's data is on a separate sheet rather than all combined on the same sheet like this code is doing. Any thoughts as to how I can write a formula or a piece of ColdFusion than will let me do this?
Also I'm using ColdFusion 8 so I can't use the cfspreadsheet function.
If you can't use
<cfspreadsheet>, then I suggest using the Apache POI project instead of the simple 'HTML as Excel via CFCONTENT' approach. POI enables you to create actual Excel spreadsheets with all of the fun that's associated with them.
Ben Nadel has a CFC wrapper that exposes the multi-sheet parts of the API.Adrian J. MorenoAdrian J. Moreno
I know this is an old topic, but this solution would have been helpful if I had found it earlier. However, here is a basic function call that should allow any number of queries put into an excel sheet with multiple tabs.
Apache POI is def the way to go. http://poi.apache.org/
Take the time to read the documentation and do some tests bc it gives you total control rather than trying to fudge HTML tables with CSS.
If you can avoid using this method, it would be best. Excel is smart enough to output your HTML in an 'spreadsheet' looking output but it is still an HTML document with an xls extension. I've had several problems using spreadsheets created like this that expect an actual Excel file. In ColdFusion, if you try reading this file with cfspreadsheet you will most likely get a an error that says something like
expected binary input file (or something close to that) and you'll have to use excel to
save as to make it an actual Excel spreadsheet. Not to mention this is a static output. You can't include any formulas or any fun stuff like iKnowKungFoo indicated.
Since that doesn't answer your question, in addition to the POI tools you could also look into other BI tools, like JasperSoft, Crystal Reports, or if you're using Oracle or MS SQL Server you could look into Oracle's BI tools or SSRS for SQL Server. There are several BI tools that are free without a support option, I believe JasperSoft is one of them.
Back to ColdFusion, I've never tried using CFReport with report builder to create an Excel formated report but in CF 8 the excel format is available. I'm not sure if you can create multiple sheets with it, I would assume not considering the below snipit but it may be worth looking into. I couldn't find anything but it's early, I'm sick, and my google-foo is a bit slow this morning.
Note: The Excel report output format type provides limited support for the formatting options available in ColdFusion Reporting. Images and charts are not supported and numeric data containing formatting (commas, percents, currency, and so on) appear as plain text in Excel. The Excel output format supports simple reports only and Adobe recommends that you give careful design and layout consideration to reports designed for Excel output.Travis