#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    60
    Rep Power
    4

    Return Excel sheet to query


    Hi Peers,
    i am trying to use the code i found in one of or CF expert on how to return an excel sheet to a query but i am not able to run it ...any help will be appreciated
    :


    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>


    </head>

    <body>

    <cffunction name= "getExcelSheet" access= "public" output= "false" returntype= "query" >
    <cfargument name= "filename" required= "true" type="string" />
    <cfargument name= "sheetName" required= "true" type="string" />
    <cfscript>
    var c = "";
    var stmnt = "";
    var rs = "";
    var sql = "Select * from [#sheetName#$]" ;
    var myQuery = "";
    arguments.filename = expandPath(arguments.filename);
    if(len(trim(arguments.filename)) and fileExists(arguments.filename)){
    try{
    CreateObject( "java","java.lang.Class" ).forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
    c =
    CreateObject( "java","java.sql.DriverManager" ).getConnection( "jdbcdbcriver={Microsoft Excel Driver
    (*.xls)};DBQ=" & arguments.filename );
    stmnt = c.createStatement();
    rs = stmnt.executeQuery(sql);
    myQuery = CreateObject('java','coldfusion.sql.QueryTable').init(rs);
    }catch(any e){
    // e r r o r - h a n d l i n g c o d e
    }
    }
    return myQuery;
    </cfscript>
    </cffunction>

    <cfform name="tabform">
    <input type="button" onchange="getExcelSheet("exercices.xls","Pull")" />
    </cfform>


    </body>
    </html>
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    "Not able to run it" is insufficient. Is there an error?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    60
    Rep Power
    4
    Hi Kiteless,

    I am not getting any error.??
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    Remove the try/catch block. It is swallowing the error.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    60
    Rep Power
    4
    Hi Kiteless,
    i've commented the if try /catch block and still nothing happens when i click on the button ,,,also no errors are showing there
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    I would dump the value of myQuery at the end of the method and see what the value is.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    60
    Rep Power
    4
    dump(myQuery) does not give anything
  14. #8
  15. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    Post your modified code so I can try and see what you're doing wrong.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    Originally Posted by korssane
    <input type="button" onchange="getExcelSheet("exercices.xls","Pull")" />
    You cannot run a server side function from javascript. Not without using ajax. If you are just trying to test the function use:

    <cfset getExcelSheet("exercices.xls","Pull")>

    Originally Posted by korssane
    code i found
    Be sure to fix it so it always closes all of the objects (connection, statement and resultset). Even when an error occurs. Otherwise the open objects may just continue to pile up which can lead to big problems.
    Last edited by cfSearching; September 13th, 2011 at 08:58 PM.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    60
    Rep Power
    4
    thanks man for the advice.
  20. #11
  21. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    I didn't even notice that, because it never even occurred to me that he would be trying that. I was focused on the actual method.

    Korssane, I would really recommend that you stop here and either get a book on CF or read through the CF documentation. Because if this sort of thing is tripping you up, you are only going to have a ton of frustration going forward until you actually get a good grasp of the basics of CF.

IMN logo majestic logo threadwatch logo seochat tools logo