How can I process Excel files in CFML? Let me count the ways
Note: This blog post is from 2008. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.Most of us have seen over the years many requests and many ways to process (read or create) Excel (.xls) and other spreadsheet files. In fact, there have been so many different ways (some simple, some powerful; some new and some quite old) that I fear some may do a quick Google search and try the first thing they find. Sadly, what works for some may not work for all.
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).
Update
Again, note that since creating this blog entry, I may have updated the section on this in my CF411 tools list, "Excel File Processing Tools". Be sure to check that out to see if there are still more new options.
For more content like this from Charlie Arehart:Need more help with problems?
- Signup to get his blog posts by email:
- Follow his blog RSS feed
- View the rest of his blog posts
- View his blog posts on the Adobe CF portal
- If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
- See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed
"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.
And since posting this entry (in Jan 08), I've created a place where I list these and many other tools and resources. Check out http://www.cf411.com... This list is there, and expanded, along with over 100 other categories of tools and resources.
First of all a great article and good job done!I need to open an excel in one of the text boxes on cfm page as soon as the user selects a radio button. Now the user needs to select a value from it, which would then be needed for further form processing.Please let me know If you can help me on this.
Beyond that, how would you expect the user to "select a value from it" to then be passed on form submission?
It just seems you have a really unusual expectation of what's possible in a browser...or I could be showing my age and not know something that's indeed possible. But I'm afraid I can't help. I'd suggest you ask this on the Adobe ColdFusion forums or some other mailing list. I list links to these on my CF411 site: http://www.cf411.com...
Thanks for your prompt reply!
I understand the message posted by me was a bit obstruse, So I m writing in again to clarify my requirements.
All I needed to do is , as soon as a user selects a particular radio button, the page needed to read contents from an excel file. The user should then select one of the rows(which may be presented in any format on the web page) which would then be considered as his selection from the various options (the various rows).
Please let me know if I am still not clear.
thanks
Punit
In that case, though, I do have to leave you at the point of my simply pointing out the various alternative approaches to reading Excel files in CFML, as pointed to above. I have not used most of them. I'm just pointing them out. You'll have to explore them on your own, I'm afraid. (Do note the updated list I mention at the end, at http://www.cf411.com...)
I should note also that CF9 has added tremendous new support built-in to process Excel files, so you may want to consider that as an option. The developer edition is free, so it doesn't cost you anything but some setup time to determine if it may be just the ticket for you. If so, it may justify the upgrade cost (or if your CF setup is hosted, find a host who is offering CF9.)
Hope that helps.
Thanks for the list. Exactly what I was looking for. Not sure whether this is a complete list of all the possible ways to do CF-Excel integration but it will keep me busy this afternoon :)