#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    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?
    thanks
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    US
    Posts
    5
    Rep Power
    0
    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.

    Good luck!
  4. #3
  5. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,279
    Rep Power
    968
    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$`
    </cfquery>

    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.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    MOV @R1, P1 ; RAM (40H) <= OCAH
    Posts
    18
    Rep Power
    0
    You can also do it using COM ... CFComet has a lot of good resources for that ..

    Check this one http://www.cfcomet.com/Excel/index.c...E9004033E03EF9
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    18
    Rep Power
    0
    why would you want to do that
    when you can use a cf dsn ??
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    MOV @R1, P1 ; RAM (40H) <= OCAH
    Posts
    18
    Rep Power
    0
    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 !

    regards,
    idss
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    18
    Rep Power
    0
    Fair point
    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
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    MOV @R1, P1 ; RAM (40H) <= OCAH
    Posts
    18
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo