wb = excelApp.Workbooks; bk = wb.Open("c:\temp\yourExcelFile.xls"); //get names sheets in file sheetList = ""; sheets = bk.sheets; for (s in sheets) { sheetList = listAppend(sheetList, s.name); } bk.close(); wb.close(); excelApp.quit(); sheet names are: #sheetList# [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Excel connection - Coldfusion - Advanced Techniques

Excel connection - Coldfusion - Advanced Techniques

Do you how to make sure that if the excel file have a specific sheet in it? <cfobject type="COM" name="ConnectionObject" class="ADODB.Connection" action="CREATE"> <cfscript> DataBasePath="#Form.brw#"; //DataBasePath="#expandpath('.')#/book2.xls"; if (#form.DeOrAs# eq "Yes") {sqlString = 'select * from [Design$]'; } else {sqlString = 'select * from [As-Built$]'; } adModeUnknown = 0; adFailIfNotExists = -1; adOpenRecordUnspecified = -1; Source= "Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=#DataBasePath#;"; ConnectionObject.Open("#Source#", adModeUnknown, adFailIfNotExists, adOpenRecordUnspecified); myResultSet = ConnectionObject.Execute("#sqlString#",0,8); </cfscript>...

Sponsored Links
  1. #1

    Default Excel connection

    Do you how to make sure that if the excel file have a specific sheet in it?

    <cfobject type="COM" name="ConnectionObject" class="ADODB.Connection"
    action="CREATE">


    <cfscript>
    DataBasePath="#Form.brw#";
    //DataBasePath="#expandpath('.')#/book2.xls";
    if (#form.DeOrAs# eq "Yes")
    {sqlString = 'select * from [Design$]'; }
    else
    {sqlString = 'select * from [As-Built$]'; }
    adModeUnknown = 0;
    adFailIfNotExists = -1;
    adOpenRecordUnspecified = -1;
    Source= "Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)};
    DBQ=#DataBasePath#;";
    ConnectionObject.Open("#Source#", adModeUnknown, adFailIfNotExists,
    adOpenRecordUnspecified);
    myResultSet = ConnectionObject.Execute("#sqlString#",0,8);
    </cfscript>

    Sponsored Links
    emmim44 Guest

  2. #2

    Default Re: Excel connection

    There are a few ways.

    - You could do it with java (using POI, JExcel, etc).
    - You could use automation. There are some good examples at cfcomet
    http://cfregex.com/cfcomet/excel
    - I don't really know the ADO syntax offhand.




    <!---- adapted from cfcomet example --->
    <!--- Try to connect to the Excel application object --->
    <CFTRY>
    <CFOBJECT ACTION="CONNECT" CLASS="Excel.Application" NAME="excelApp"
    TYPE="COM">
    <CFCATCH>
    <CFOBJECT ACTION="CREATE" CLASS="Excel.Application" NAME="excelApp"
    TYPE="COM">
    </CFCATCH>
    </CFTRY>
    <!--- Here we need to gather the 'Sheets' collection from the 'Workbooks'
    object --->
    <cfscript>
    wb = excelApp.Workbooks;
    bk = wb.Open("c:\temp\yourExcelFile.xls");
    //get names sheets in file
    sheetList = "";
    sheets = bk.sheets;
    for (s in sheets) {
    sheetList = listAppend(sheetList, s.name);
    }
    bk.close();
    wb.close();
    excelApp.quit();
    </cfscript>
    <cfoutput><b>sheet names are: </b>#sheetList#</cfoutput>

    mxstu Guest

  3. #3

    Default Re: Excel connection

    :brokenheart; I have tried the above code, however, show error page:

    "An exception occurred when executing a Com method. The cause of this
    exception was that: AutomationException: 0x80010105 - The server threw an
    exception..

    Please try the following:
    Enable Robust Exception Information to provide greater detail about the source
    of errors. In the Administrator, click Debugging & Logging > Debugging
    Settings, and select the Robust Exception Information option.
    Check the ColdFusion doentation to verify that you are using the correct
    syntax.
    Search the Knowledge Base to find a solution to your problem.


    Browser Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)
    Remote Address 172.20.39.41
    Referrer http://hfentweb2:8500/dev/Conny/testexcel2.cfm
    Date/Time 01-Nov-05 05:13 PM "

    Any tips for me ?



    hasbroconny Guest

  4. #4

    Default Re: Excel connection

    i am using CF MX 6.1 and database MS SQL
    hasbroconny Guest

  5. #5

    Default Re: Excel connection

    emmin44,

    What version of Excel is installed on the CF server?
    philh Guest

Similar Threads

  1. Replies: 3
    Last Post: January 14th, 11:47 PM
  2. Place Excel into table with ability to link when native Excel file is updated.
    By Nancy_Bubb@adobeforums.com in forum Adobe Indesign Windows
    Replies: 1
    Last Post: June 23rd, 03:23 PM
  3. Replies: 1
    Last Post: July 14th, 03:00 PM
  4. Replies: 1
    Last Post: July 14th, 02:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •