[Looking for Charlie's main web site?]

How can I process Excel files in CFML? Let me count the ways

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.

Comments
Great article. However, I was looking for examples showing ways to import Excel files into a database from an uploaded file. Exporting is easy, but being able to upload, parse, and insert Excel data - to me, would be real useful.
# Posted By David Brannan | 1/14/08 1:35 PM
David, perhaps you read it too fast. :-) I said it was about "CF-Excel integration (both directions)", and then in the bullets some mentioned creating *and reading them*. I even said in one paragraph after the bulleted list:

"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.
# Posted By Charlie Arehart | 1/14/08 1:43 PM
I see your list on Excel files now - yes, I read "too fast".

http://www.carehart....

Thanks!
# Posted By David Brannan | 1/14/08 2:01 PM
OK, that scares me, that one could miss the 2 links to the "real" list with the details. I mean, that was the whole point of the work. :-) So I've taken your feedback and modified the entry a couple of ways, first changing the bullet for each approach so that it now links directly into the resource list. Second, to your comments above, I've bolded every reference to "reading them". Like I said in the entry, I welcome feedback (and act on it). Thanks, David.
# Posted By Charlie Arehart | 1/14/08 2:27 PM
Charlie,

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?
# Posted By Javier Julio | 1/21/08 1:47 PM
Javier, sorry, but I really don't have enough experience with each of them--let alone with generating such a large file--to say if any would be better for your requirements. You really just need to try each for yourself. Most are pretty easy to do, so shouldn't take too long to setup tests. Perhaps just do one large tab/worksheet at a time, since creating multiples should increase at the same rate (just more data).

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.
# Posted By Charlie Arehart | 1/21/08 8:11 PM
Thank you
# Posted By Yemek Tarifleri | 2/4/08 2:25 AM
Thank you for organizing this. Web searches can be aggravating and having so many options presented in one place is really nice.
# Posted By LionDog | 11/14/08 2:39 PM
You bet, LionDog. Glad to help.

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.
# Posted By Charlie Arehart | 11/15/08 1:40 AM
Charlie,

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.
# Posted By Punit | 10/7/09 11:48 AM
Punit, I'm not understanding. By "open an excel in a text box", do you mean have Excel itself appear to the user? I don't see how that would be possible. Excel is a desktop application. I don't know that you can get a desktop application to render its interface on a web page, even inside an iframe, let alone a text box.

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...
# Posted By Charlie Arehart | 10/7/09 12:01 PM
Charlie

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
# Posted By Punit | 10/8/09 4:09 AM
Well, Punit, now it sounds like you're saying that the first control won't show Excel or the file at all, but instead should cause the reading of an excel file, to show data to let a user select rows. That's certainly different.

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.
# Posted By Charlie Arehart | 10/8/09 9:49 AM
Charlie

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 :)
# Posted By Aiden | 9/20/11 6:26 AM
Glad to help, Aiden. Thanks for the feedback.
# Posted By Charlie Arehart | 9/20/11 11:29 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.005. (Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting