Import and Export Data From Excel Using ColdFusion
page 4 of 5
by Debjani Mallick
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 48731/ 87

Writing to an excel sheet (method 2)
<cffunction name="writeExcelSheet" access="public" 
  output="false" returntype="void">
      <cfargument name="tableName" required="true" type="string" />
      <cfargument name="dsnName" required="true" type="string" />
      <cfargument name="fileName" required="true" type="string" />
      <cfscript>
            var getRecords = '';
            var listOfColumns = '';
            var tabChar = '';
            var newLine = '';
            var index = '';
            var value = '';
      </cfscript>
      <cfsetting enablecfoutputonly="Yes">
      <cfquery name="getRecords" datasource="#arguments.dsnName#">
            select * from #arguments.tableName#
      </cfquery>
 
      <cfif getRecords.RecordCount>
            <cfsetting showdebugoutput="false">
            <cfset listOfColumns = getRecords.columnList>
            <cfset TabChar = Chr(9)>
            <cfset NewLine = Chr(13) & Chr(10)>
            <cfsavecontent variable="value">
                  <cfloop list="#listOfColumns#" index="index">
                        <cfoutput>#index##TabChar#</cfoutput>
                  </cfloop>
            </cfsavecontent>
            <cffile action="write" file="#arguments.fileName#" output="#value#" >
 
            <!--- output query data using cfloop & cfoutput --->
            <cfset value = ''>
            <cfsavecontent variable="value">
                  <cfloop query="getRecords">
                        <cfloop list="#listOfColumns#" index="index">
                              <cfoutput>#getRecords['#index#'][CurrentRow]##TabChar#</cfoutput>
                        </cfloop>
                        <cfoutput>#NewLine#</cfoutput>
                  </cfloop>
            </cfsavecontent>
            <cffile action="append" file="#arguments.fileName#" output="#value#">
      </cfif>
</cffunction>

Explanation

In the above function, we need three arguments – tableName which is the name of the table from which we need to extract the data and save to the excelsheet, dsnName is the datasource name for connecting to the database where the table is present, fileName is the full path along with the excelsheet name where the data is to be written. This uses all the ColdFusion functions to write to an excelsheet. Here we are simply querying the database and if we have records, we are setting the first row of the sheet with the column names of the table and then we are looping over the records, and creating one row for each record. We have used cfsavecontent tag to first save the contents in a variable and then we are writing the value of the variable to the sheet. The above method is much simpler and has an easy to understand code than that for the first method.


View Entire Article

User Comments

Title: Mr.   
Name: John Glynn
Date: 2011-09-14 10:45:00 AM
Comment:
Thank you Debjani Mallick!
This is a terrific tutorial that worked right out of the box. I was wondering though, is there a way to return the table columns in a different order than alphabetical, order by current column order or something like that?

Thanks again!
John
Title: readExcelSheet   
Name: HP
Date: 2011-06-13 11:43:57 AM
Comment:
I got this error when trying to use the readExcelSheet function. NOt sure why I got this error. Any idea?
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Title: missing columns after read op'n   
Name: sooraj
Date: 2010-08-24 10:10:15 AM
Comment:
hi Debjani,
great stufff for cf'ers...
I have an interesting issue when I used ur functioanlity to read excel file, the problem is my file contyains 4 columns/fields and after upload and read operation, dumping shows only 3 columns...why this happens?

Any ideas..Is I am missing anything...

thanks
Title: Hi   
Name: Smith
Date: 2010-03-31 11:54:50 AM
Comment:
Yes waiting for it
Title: Where R ur Articles???   
Name: Abhinash
Date: 2010-03-31 10:57:19 AM
Comment:
Waiting for ue next big article
Title: Data not being written to new columns   
Name: Chuck
Date: 2010-01-14 3:49:00 PM
Comment:
We are trying method 2 and we are not writing data to new columns, instead its writing to a new line for every column. Also it's sorting the data in alphabetical order.
Title: instance, a FileOutputStream or ServletOutputStream   
Name: Lance
Date: 2009-06-17 3:00:53 PM
Comment:
Do have an example of how to use ServletOutputStream
Title: Reply: How do you specify sheetname   
Name: Debjani Mallick
Date: 2009-06-11 12:16:21 PM
Comment:
For specifying the arguments using the second write method, the syntax is:
writeExcelSheet('table name','datasource name','path to the excel sheet with sheet name');
This will create a sheet with the default name. Using the second write method, its first of all not possible to specify worksheet names and secondly, its also not possible to write to multiple tabs of an excel sheet through this method. But the first method specified, accomplishes both the tasks.
Title: How do you specify sheetname   
Name: jtansey
Date: 2009-06-11 9:19:14 AM
Comment:
So, in example two of writing to an excel worksheet, it says to specify the fileName as the full path along with the excelsheetname. I need to write to multiple sheets. How exactly do I specify the sheetname?
Title: Good one   
Name: Abx-68
Date: 2009-05-07 4:26:03 AM
Comment:
Good one!! Keep Going
Title: Helpful article   
Name: John
Date: 2009-04-22 11:15:24 AM
Comment:
Thank you for the article. It is really helping.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-08-25 3:02:25 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search