[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

coldfusion get excel worksheet name

I am letting the user select an excel worksheet and then I am uploading to server and reading the data to put into a database. I read the data and put into a query.

The problem is the worksheet name. Is there a way that I can get the worksheet name dynamically. If not the user will need to rename the worksheet name every time and they will have to know what to name it. Which can be time consuming and a pain the butt.

Below is the code that I am using:
<cfset myfilepath= #application.UploadPath#>

<!--- Upload Excel File --->
                        
<cffile action="upload"
      filefield="ExcelNumbers"
      destination="#myfilepath#/Reports"
      nameconflict="overwrite">      
                                                 
<cfset UploadedFileName = cffile.serverfile>      

<cfoutput>
      <cfset myQuery = getExcelSheet("#UploadedFileName#","Sheet1")>
</cfoutput>

As you can see I am looking for "Sheet1", but I need to be able to get the Worksheet name dynamically so I can get it no matter what the user names it.


0
cj_ervin
Asked:
cj_ervin
  • 8
  • 7
1 Solution
 
_agx_Commented:
Which version of CF? What are you using to read the excel file?
0
 
cj_ervinAuthor Commented:
I am using ColdFusion 8

I am using getExcelSheet function to return the data from the excel sheet to a query.

you can read about it here: http://www.coldfusioncookbook.com/entry/114/How-do-I-return-a-query-from-an-Excel-file

There should be some code or function to the worksheet name, I would think.
0
 
_agx_Commented:
I can't test it, but something like this

<cfset qry = getExcelSheetNames("c:\fullpath\to\MyFile.xls")>
<!--- sheet name column is called: TABLE_NAME --->
<cfdump var="#qry#">


<cffunction name="getExcelSheetNames" access="public" output="false" returntype="query">
    <cfargument name="fullFilePath" required="true" type="string" />
    <cfscript>
    var Local = structNew();
    getClass().forName("sun.jdbc.odbc.JdbcOdbcDriver");  
    Local.connectURL = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ="& arguments.filename;
    Local.conn = CreateObject("java", "java.sql.DriverManager").getConnection( Local.connectURL );
    Local.meta = Local.conn.getMetaData();
    Local.tables = Local.meta.getTables( javacast("null", ""), javacast("null", ""), "%", javacast("null", "") );
    Local.myQuery = CreateObject("java", "coldfusion.sql.QueryTable").init( Local.tables );
    Local.conn.close();
    return Local.myQuery;
    </cfscript>
</cffunction>
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
cj_ervinAuthor Commented:
Thanks aqk. It worked like a charm. Thank you
0
 
cj_ervinAuthor Commented:
Excellent Answer
0
 
cj_ervinAuthor Commented:
aqk, can I read the excel file by using Java like you did for the sheet name?
0
 
_agx_Commented:
How so? Your original function already uses java (jdbc/odbc bridge) to read the data from the file.
0
 
cj_ervinAuthor Commented:
Sorry for the brief question. what I meant by reading the excel file, is there a way to put the excel sheet into a query. So each row of the excel file would be persie a row in the query.
0
 
cj_ervinAuthor Commented:
I am using a CF Function called getExcelSheet (<cfset myQuery = getExcelSheet("#UploadedFileName#","#left(qry.TABLE_NAME,len(qry.TABLE_NAME)-1)#")>), but I would like to have more control
0
 
_agx_Commented:
It's probably possible :) What do you want it to do instead?
0
 
_agx_Commented:
(I haven't had enough coffee, so I'm a little slow on the pickup this morning ;)
0
 
_agx_Commented:
If you mean automatically read the 1st sheet in the file, just remove the sheetName argument (or make it optional). Then call getExcelSheetNames() from your other function.

...
var qSheetNames = getExcelSheetNames(arguments.filename);
var sql = "Select * from [#qSheetNames.TABLE_NAME#]"; // add error handling
...
0
 
cj_ervinAuthor Commented:
What you had above, is exactly what I needed for getting the sheet name. This is a new questoin, I guess I should have opened a new thread. Sorry.

What I need to do is insert the data from the excel sheet and put into a database table.  I just found this code on the itnea blog site and tried the code below, but it returns nothing. The returned query is empty and the excel sheet I am using has about 75 rows.

<cffunction name="getExcelSheetData" access="public" output= "true" returntype="any">
       <cfargument name="filename" required="true" type="string" />
      <cfargument name="sheetName" required="true" type="string" />
             <cfset var c = "">
             <cfset var stmnt = "">
             <cfset var rs = "">
             <cfset var sql = "Select * from [#arguments.sheetName#$]">
             <cfset var myQuery = QueryNew("")>
                                      
            <cftry>
             <cfscript>
                  arguments.filename = expandPath(arguments.filename);
                  if(len(trim(arguments.filename)) and fileExists(arguments.filename)){
                  CreateObject( "java","java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  c = CreateObject( "java","java.sql.DriverManager").getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.filename );
                   stmnt = c.createStatement();
                   rs = stmnt.executeQuery(sql);
                  myQuery = CreateObject('java','coldfusion.sql.QueryTable').init(rs);
                   rs.close();
                  c.close();
                   }
                   </cfscript>
                  <cfcatch type="any">
                         <cfdump var="#cfcatch#">            
                  </cfcatch>
            </cftry>
            <cfreturn myQuery />
</cffunction>

<cfset qry2 = getExcelSheetData("#myfilepath#/Reports/#UploadedFileName#", "#left(qry.TABLE_NAME,len(qry.TABLE_NAME)-1)#")>

<cfdump var="#qry2#">

The returned query is blank or empty. It is returning an empty string. Just wondering why I can not get any results.


0
 
_agx_Commented:
Yeah, open a new thread for this one. Post which db type you're using too.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now