How can I process Excel files in CFML? Let me count the ways
One place to list all the approaches
I don't recall ever seeing any single listing of all the possible ways to do CFML to Excel integration, so I decided to create one. I've created a new section in my Tools to Consider for CFML developers on the topic of "Excel File Processing Tools".
You'll see I've gathered a range of articles and tools from over the years showing how to do CF-Excel integration (both directions) any of several of the following ways (the links go to the section within my tools list on this topic):
- You can easily create them using HTML tables and CSV generation (as well as read them using CSV)
- You can create them with the more powerful XML-based approach
- You can create and read them using Java-based APIs like POI, JExcel, and jXLS
- You can create and read them using COM and now in CF8, .NET
- You can read them using ODBC and JDBC drivers
- Let's not forget also that you can create them using the Report Builder in CF7 and 8, which can output as Excel
- And finally there's the old Excel Web Query feature for populating a spreadsheet from a CFML request
In the listing, I offer dozens of links to articles, blog entries, downloadable tools, any relevant CF docs, and more, all broken down by the approaches listed above.
Different Strokes for Different Folks
So you see, there are not only different solutions but different needs (create a spreadsheet CFML, read one into CFML, or populate one within Excel from a CFML request). It seems helpful to gather a single resource to organize them all and help people decide.
I need your feedback
So let me know what you think of all this. Did I leave out any other important approaches? I'm not claiming to "know it all". This is just a result of some research I did this morning (and/or recollection from past experiences). I'm sure I could have missed something. Certainly I know I'm not listing *all* possible references on each of the subjects. I just wanted to give folks something to start with, but I'm certainly open to adding any useful references or tools that I left out. Please leave a comment below.
Update: Indeed, already, in response to a comment, I've tried to make more clear the fact that I'm not just talking here about creating spreadsheets from CFML but was referring to reading them as well. That's why I chose "processing" in the title, and I referred several times to "reading them", but to make it stand out, I've bolded all the references to reading them.
More important, because it seems he had missed the 2 links to the lists where I offer more details, I've also changed the words in the list above to link into the discussion of the topic in the tools list. Hope that helps avoid people missing the real point of the listing. I was torn about just offering the listing itself as a blog entry and went with this approach instead of a summary that points to the details. I know some don't like really long blog entries. Also, listing them in the bigger tools list seemed helpful, but I may split it out into its own file. We'll see.
Not just about Excel or even Office
BTW, one may argue that I could have a generic section on processing all sorts of file types (or even just all kinds of MS Office) file types, but there are so many requests for Excel file processing specifically, and so many approaches/tools to suit those needs, that I just figured I'd start out with this and consider other filetypes later (and may rename the section then). It's also certainly true that most of the techniques/tools shown here can be used with any spreadsheet tool that reads xls files or processes CSV/HTML table files in a similar way (like OpenOffice).
"So you see, there are not only different solutions but different needs (create a spreadsheet CFML, read one into CFML, or populate one within Excel from a CFML request). It seems helpful to gather a single resource to organize them all and help people decide."
Are you saying you reviewed the complete list of resources (not just this blog entry, but the resource list I created and pointed to)? If it's still somehow not clear that this was not just about exporting (note I used "processing" in the title), or more important if you don't find a solution to what you sought, do let me know. It should be there, in more than one approach.
http://www.carehart....
Thanks!
Do you have any suggestions on any of the above approached to generate a large Excel file from CF? I'm currently using Ben Nadel's POIUtility which works great but since the Excel file is very large it takes about a minute or two to complete.
I don't need any CSS or styling, just a simple create multiple sheets with a data dump (including header row). The client requires 11 tabs in a resulting Excel file. 3 of those 11 are the large ones. One has about 4000 rows, the second has 6000, and the third around 30,000. It's a lot of data. I'm wondering if any of the approaches listed above would perform better then using the POIUtility library?
In that regard,I'll say that you ought to separate your expectations for response time into two components: generating the file on the server, and sending it down to the browser. Being so large, part of the delay is simply going to be in the latter.
Also, the different approaches don't all result in the same thing being created on the server and sent to the browser, so you should keep that in mind in comparing alternatives.
Finally, if part of the problem is the delay in waiting for it to be created (on the server), perhaps another approach is to use a mechanism that creates the file before the user requests it and then sends it to them on demand. You could look at CFTHREAD in CF8, or the Asynch Gateway in or 8, or perhaps even a scheduled task in any release of CF. Just some thoughts.