Reading from an Excel file through ColdFusion

Hi, I want to read and parse through the excel files from coldfusion. Banged my head on the net but couldn't find anything :-( Even in CF documentation it's nowhere mentioned about reading an excel file from CF. This is the last hope of finding a solution. Thanks in advance. Hussain.

Here is a tag that will do just what you would like...hope it helps http://www.emerle.net/programming/display.cfm/t/cfx_excel2query Doug B. > Hi, > I want to read and parse through the excel files from coldfusion. > Banged my head on the net but couldn't find anything :-( > Even in CF documentation it's nowhere mentioned about reading an excel file from CF. > This is the last hope of finding a solution. > > Thanks in advance. > Hussain.

Even better...the Apache Jakarta project as a set of Java classes that can read/create/modify/delete office docs...including Excel.  It is called POI. In testing that I did at a former client, this process blows the pants off of using an Excel com object.  We testing this with the com object on a dual processor box and the POI code on a single processor box (the processors were all the same speed and both boxes had the same amount of memory and plenty of drive space and they were producing the same report).  The com object maxed out the processors for about 20 seconds...obviously stopping anything else from happening on the box.  The POI code blipped for about 4 seconds and was done.  If you do a search on CF and POI, you will find a site (d-ross.org I think) that shows you how to do this in CF. Eric Here is a tag that will do just what you would like...hope it helps http://www.emerle.net/programming/display.cfm/t/cfx_excel2query Doug B. > Hi, > I want to read and parse through the excel files from coldfusion. > Banged my head on the net but couldn't find anything :-( > Even in CF documentation it's nowhere mentioned about reading an excel file from CF. > This is the last hope of finding a solution. > > Thanks in advance. > Hussain. > >

p.s http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:49463 Doug B. > Hi, > I want to read and parse through the excel files from coldfusion. > Banged my head on the net but couldn't find anything :-( > Even in CF documentation it's nowhere mentioned about reading an excel file from CF. > This is the last hope of finding a solution. > > Thanks in advance. > Hussain.

Actually I finally found the link I was looking for. Forgot they changed cfcomets website. Anyhow Dain Anderson goes over this quite well. He was one of the co-authors of the advanced coldfusion books with Ben Forta http://cfregex.com/cfcomet/Excel/index.cfm?ArticleID=0239B2E5-5FED-11D3-B3E9004033E03EF9 Doug B. > Hi, > I want to read and parse through the excel files from coldfusion. > Banged my head on the net but couldn't find anything :-( > Even in CF documentation it's nowhere mentioned about reading an excel file from CF. > This is the last hope of finding a solution. > > Thanks in advance. > Hussain.

Hussain, I have created a early beta for a ColdFusion component that wraps around a limited set of POI functionality for reading / writing Excel files. The best part of this is that POI is built into ColdFusion so there is nothing too sneaky going on here, nor does anything new need to be installed. Check it out: http://bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Rea d-And-Write-Excel-Files.htm OR http://www.bennadel.com/index.cfm?dax=blog:474.view Let me know if you have any questions. I am working on improving it as we speak, but this is what I have so far. ..................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ Hi, I want to read and parse through the excel files from coldfusion. Banged my head on the net but couldn't find anything :-( Even in CF documentation it's nowhere mentioned about reading an excel file from CF. This is the last hope of finding a solution. Thanks in advance. Hussain.

POI is built in?  Since when?  Last time I checked you had to add the java classes to CF... Eric Hussain, I have created a early beta for a ColdFusion component that wraps around a limited set of POI functionality for reading / writing Excel files. The best part of this is that POI is built into ColdFusion so there is nothing too sneaky going on here, nor does anything new need to be installed. Check it out: http://bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Rea d-And-Write-Excel-Files.htm OR http://www.bennadel.com/index.cfm?dax=blog:474.view Let me know if you have any questions. I am working on improving it as we speak, but this is what I have so far. ...................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ Hi, I want to read and parse through the excel files from coldfusion. Banged my head on the net but couldn't find anything :-( Even in CF documentation it's nowhere mentioned about reading an excel file from CF. This is the last hope of finding a solution. Thanks in advance. Hussain.

Thanks Ben. I am now able to parse through the excel file sucessfully. But how can I parse an excel file which is located in different server? Hussain.

You just need to give the permissions for that file to whatever user coldfusion is running as on your server.  I don't know about best practices in this, but I created a 'coldfusion' domain user with very specific permissions, and I run all my services as this user. Chris Peterson Thanks Ben. I am now able to parse through the excel file sucessfully. But how can I parse an excel file which is located in different server? Hussain.

One more thing Ben, Why does your piece of code gives error when we have more than 1 sheet embedded in the excel file(Why the assumption that the excel should have a single sheet in it)? Hussain.

Hussain, I am not sure why that would be erroring. I have tested with with multiple sheets and it seems to work nicely. It is, however, very early in the development stage, so it is bound to be buggy. If you send me the XLS file you are using, I can play around with it (ben [ .at. ] bennadel.com). ..................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ One more thing Ben, Why does your piece of code gives error when we have more than 1 sheet embedded in the excel file(Why the assumption that the excel should have a single sheet in it)? Hussain.

Ben, It was a mistake on my part. Actually I implemented your code picked up from this link http://www.bennadel.com/index.cfm?dax=blog:472.view I guess the code present here is the older version with limited capabilities. I will try-out the stuff present in the link which you have posted here. Thanks, Hussain. ----- Excess quoted text cut - see Original Post for more -----

No worries dude. ..................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ Ben, It was a mistake on my part. Actually I implemented your code picked up from this link http://www.bennadel.com/index.cfm?dax=blog:472.view I guess the code present here is the older version with limited capabilities. I will try-out the stuff present in the link which you have posted here. Thanks, Hussain. ----- Excess quoted text cut - see Original Post for more -----

Here is what we do in CF7. http://www.adobe.com/go/tn_18656 ----- Excess quoted text cut - see Original Post for more -----

Ben, This is a very impressive piece of coding.  Do you know if this will work under BlueDragon 6.2. Thanks Mario Hussain, I have created a early beta for a ColdFusion component that wraps around a limited set of POI functionality for reading / writing Excel files. The best part of this is that POI is built into ColdFusion so there is nothing too sneaky going on here, nor does anything new need to be installed. Check it out: http://bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Rea d-And-Write-Excel-Files.htm OR http://www.bennadel.com/index.cfm?dax=blog:474.view Let me know if you have any questions. I am working on improving it as we speak, but this is what I have so far. ...................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ Hi, I want to read and parse through the excel files from coldfusion. Banged my head on the net but couldn't find anything :-( Even in CF documentation it's nowhere mentioned about reading an excel file from CF. This is the last hope of finding a solution. Thanks in advance. Hussain.

Mario, Thanks. I have no idea if it will work under Blue Dragon. I am guessing that it will not, but I cannot say for sure. ..................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ Cc: Ben Nadel Subject: RE: Reading from an Excel file through ColdFusion. Ben, This is a very impressive piece of coding.  Do you know if this will work under BlueDragon 6.2. Thanks Mario

It is indeed built in to ColdFusion, since 6.1 I think - maybe 7 but it is still in the lib, as is iText. "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540.  It contains information which is confidential and may also be privileged.  It is for the exclusive use of the intended recipient(s).  If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful.  If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910.  The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com POI is built in?  Since when?  Last time I checked you had to add the java classes to CF... Eric Hussain, I have created a early beta for a ColdFusion component that wraps around a limited set of POI functionality for reading / writing Excel files. The best part of this is that POI is built into ColdFusion so there is nothing too sneaky going on here, nor does anything new need to be installed. Check it out: http://bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Rea d-And-Write-Excel-Files.htm OR http://www.bennadel.com/index.cfm?dax=blog:474.view Let me know if you have any questions. I am working on improving it as we speak, but this is what I have so far. ....................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ Hi, I want to read and parse through the excel files from coldfusion. Banged my head on the net but couldn't find anything :-( Even in CF documentation it's nowhere mentioned about reading an excel file from CF. This is the last hope of finding a solution. Thanks in advance. Hussain.

Is there any docs on that...I never heard that before... Eric [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Wednesday, 31 January 2007 01:56 To: CF-Talk Subject: Re: Reading from an Excel file through ColdFusion. It is indeed built in to ColdFusion, since 6.1 I think - maybe 7 but it is still in the lib, as is iText. "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540.  It contains information which is confidential and may also be privileged.  It is for the exclusive use of the intended recipient(s).  If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful.  If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910.  The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com POI is built in?  Since when?  Last time I checked you had to add the java classes to CF... Eric Hussain, I have created a early beta for a ColdFusion component that wraps around a limited set of POI functionality for reading / writing Excel files. The best part of this is that POI is built into ColdFusion so there is nothing too sneaky going on here, nor does anything new need to be installed. Check it out: http://bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Rea d-And-Write-Excel-Files.htm OR http://www.bennadel.com/index.cfm?dax=blog:474.view Let me know if you have any questions. I am working on improving it as we speak, but this is what I have so far. ........................ Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ Hi, I want to read and parse through the excel files from coldfusion. Banged my head on the net but couldn't find anything :-( Even in CF documentation it's nowhere mentioned about reading an excel file from CF. This is the last hope of finding a solution. Thanks in advance. Hussain.

Not sure there are any docs but it is in CF at the following location: it is being used by cfdocument (possibly other things) C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\lib\ Is there any docs on that...I never heard that before... Eric [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Wednesday, 31 January 2007 01:56 To: CF-Talk Subject: Re: Reading from an Excel file through ColdFusion. It is indeed built in to ColdFusion, since 6.1 I think - maybe 7 but it is still in the lib, as is iText. "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540.  It contains information which is confidential and may also be privileged.  It is for the exclusive use of the intended recipient(s).  If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful.  If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910.  The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com POI is built in?  Since when?  Last time I checked you had to add the java classes to CF... Eric Hussain, I have created a early beta for a ColdFusion component that wraps around a limited set of POI functionality for reading / writing Excel files. The best part of this is that POI is built into ColdFusion so there is nothing too sneaky going on here, nor does anything new need to be installed. Check it out: http://bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Rea d-And-Write-Excel-Files.htm OR http://www.bennadel.com/index.cfm?dax=blog:474.view Let me know if you have any questions. I am working on improving it as we speak, but this is what I have so far. ......................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ Hi, I want to read and parse through the excel files from coldfusion. Banged my head on the net but couldn't find anything :-( Even in CF documentation it's nowhere mentioned about reading an excel file from CF. This is the last hope of finding a solution. Thanks in advance. Hussain.

That's pretty cool...they should list that as a feature so we know about it...thx Eric [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Thursday, 01 February 2007 04:46 To: CF-Talk Subject: RE: Reading from an Excel file through ColdFusion. Not sure there are any docs but it is in CF at the following location: it is being used by cfdocument (possibly other things) C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\lib\ Is there any docs on that...I never heard that before... Eric [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Wednesday, 31 January 2007 01:56 To: CF-Talk Subject: Re: Reading from an Excel file through ColdFusion. It is indeed built in to ColdFusion, since 6.1 I think - maybe 7 but it is still in the lib, as is iText. "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540.  It contains information which is confidential and may also be privileged.  It is for the exclusive use of the intended recipient(s).  If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful.  If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910.  The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com POI is built in?  Since when?  Last time I checked you had to add the java classes to CF... Eric Hussain, I have created a early beta for a ColdFusion component that wraps around a limited set of POI functionality for reading / writing Excel files. The best part of this is that POI is built into ColdFusion so there is nothing too sneaky going on here, nor does anything new need to be installed. Check it out: http://bennadel.com/blog/474-ColdFusion-Component-Wrapper-For-POI-To-Rea d-And-Write-Excel-Files.htm OR http://www.bennadel.com/index.cfm?dax=blog:474.view Let me know if you have any questions. I am working on improving it as we speak, but this is what I have so far. .......................... Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ Hi, I want to read and parse through the excel files from coldfusion. Banged my head on the net but couldn't find anything :-( Even in CF documentation it's nowhere mentioned about reading an excel file from CF. This is the last hope of finding a solution. Thanks in advance. Hussain.