September 8th, 2003, 10:48 AM
getting data from excel
Is it possible to get data from an excel spreadsheet and display it in a browser using coldfusion?
If yes, can anybody tell me how to do it?
September 17th, 2003, 06:24 AM
The way we do it is CFFILE the excel to some directory on your webserver (use makeunique option), then use CFHTTP to link the content to have your user validate their upload. Once that is done, then you can save your content out to your database.
September 17th, 2003, 09:30 AM
You can actually query the Excel spreadsheed just like you would query a database. In CF 5 there should be an option of 'Excel' when you define a data source. In CFMX since it uses JDBC you must first create a system ODBC connection, then in CFMX set it up as an ODBC bridge connection. One that is in place you can query the excel file like this:
<cfquery name="queryname" datasource="excelDSN">
select * from `sheet1$`
A quirky thing is that the quotes around the sheet name MUST BE LIKE THIS: ` and NOT LIKE THIS: '
But it does work. Hope that helps.
September 29th, 2003, 08:19 AM
September 29th, 2003, 09:22 AM
why would you want to do that
when you can use a cf dsn ??
September 29th, 2003, 09:38 AM
In two simple words .. raw power.
You can use a odbc conection, but you are tied up to the drivers and to what they support.
If you use a COM object to access the Excell datasheet you hv to your disposal all the Excell API. Like i said, it's just a option. But i recommend to use it only if you want to have such power in your hands. Why ?
[list=1][*]You have to access it through COM .. and that's not even close to fast. You'll use JIntegra for that and create a com2java/java2com bridge, wich like i said it's not very fast and/or stable[*]You'll hv to search Micorosft/s MSDN Library to find some docs to know the Excell API[*]Bloody more complicated[/list=1]
I never said you couldn't use a odbc source to the the work, all i said was that COM was an option, and options are never too many !
September 29th, 2003, 09:45 AM
but tbh how many times in any application have you found that cf's odbc drivers or even jdbc drivers in mx didnt do what you mostly required ?
- One thing to note this really is only an option if you have access to the server & cfadmin ( ie not a shared hosting option )
but yes its a valid option if you have access to the server
September 29th, 2003, 09:54 AM
Macromedia uses 3rd party drivers ( ODBC - Merant | Jet- infoZoom[ jadoZoom] ) , and some of them don't support all the things i sometimes have to use .. right now is appening that ... :\
It's not very usual .. but it appens
As for the cfadmin access, you don't need to hv access to it .. unless createObject() is disabled :\
but you're right ... using COM is not the best answer .. and it wil never be ! cause it's slow like hell and very unstable