• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

cfml export to excel

I had code to export from a cfquery using cfcontent to export to a csv file and email it. It was working but now it overwrites my cfm page with the data.  I'm purplexed.  Anyone have sample code that works?
0
lantervj
Asked:
lantervj
  • 5
  • 3
  • 2
2 Solutions
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
Here is a Code sample for you if you want to do it in CSV and Excel:


<cfset tools = CreateObject("component","tools")>
<cfset endUsers = #tools.UsersArea(onlyend=1)#>
<cfset contacts = #CreateUUID()#>
<cfif endUsers.recordcount  GTE 66000>
  <h1>Too many records</h1>
  <cfelse>
  <cffile action="write" file="excelCSV\#contacts#.csv" output="FirstName,LastName,E-mail Address" addnewline="yes">
  <!--- Output the contents of the endUsers query --->
  <cfoutput>
    <cfloop query="endUsers">
      <cffile action="append" file="excelCSV\#contacts#.csv" 
      output="#TRIM(firstname)# , #TRIM(lastname)# , #TRIM(nemail)#" addnewline="yes">
    </cfloop>
  </cfoutput>
</cfif>
<cflocation url="excelCSV/#contacts#.csv">

Now if to Excel:

<cfset tools = CreateObject("component","tools")>
<cfset endUsers = #tools.UsersArea(onlyend=1)#>
<CFSETTING enablecfoutputonly="Yes">
<!--- set content type to invoke Excel --->
<CFCONTENT type="application/msexcel">
<!--- suggest default name for XLS file --->
<!--- use "Content-Disposition" in cfheader for Internet Explorer --->
<CFHEADER name="Content-Disposition" value="filename=AccessToExcel.xls">
<!--- output data using cfloop & cfoutput --->
<!---Column Headings--->
<!---this section will cover the first 3 rows in the spreadsheet--->

<CFOUTPUT>
  <table border="1" bordercolor="##000000">
</cfoutput> <CFOUTPUT>
  <tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Email Address</th>
  </tr>
</CFOUTPUT>
<!---Members payments info--->
<CFLOOP query="endUsers">
  <CFOUTPUT>
    <tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#">
      <td>#firstname#</td>
      <td>#lastname#</td>
      <td>#nemail#</td>
    </tr>
  </CFOUTPUT>
</CFLOOP>

Open in new window

0
 
lantervjAuthor Commented:
I was actually in the middle of editing my question when you replied. I have that code ( or a variation of it) and it works except the resulting excel sheet looks like;

<tr>                        
<td>Ernst & Young</td>                        
<td>58308</td>                        
<td>James Ardmore</td>                        
<td>Senior/Consultant</td>                        
<td>(888)-888-8888</td>                        
<td></td>                        
</tr>                        
                        
<tr>                        
<td>Safeway Incorporated</td>                        
<td>97756</td>                        
<td>James Brown</td>                        
<td>Manager</td>                        
<td>(999)-999-9999</td>                        
<td></td>                        
</tr>

My code is;

<cfquery name="qFindDupes" datasource="#request.dsn#">      
select *
from #query_name#
order by lastname,firstname,name

</cfquery>
<cfsetting enablecfoutputonly="Yes">
<!-- calls excel -->
<cfcontent type="application/vnd.ms-excel">

<!-- sets default excel file name -->
<cfheader name="Content-Disposition" value="filename=DupeIndivs.csv">
<cfset delim = 44>
<!-- build query loop -->

<table>
      <tr>
<td>Company</td>
<td>Indiv</td>
<td>Name</td>
<td>Title</td>
<td>Phone</td>
<td>State</td>
</tr>
<cfoutput query="qFindDupes">
<tr>
<td>#name#</td>
<td>#id#</td>
<td>#firstname# #lastname#</td>
<td>#description#</td>
<td>#FormatPhoneNumber("#qFindDupes.area_code##qFindDupes.phone#")#</td>
<td>#state#</td>
</tr>
</cfoutput>
</table>                        
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
Did you tried my version of excel
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
azadisaryevCommented:
if you want to EMAIL the formatted output of your query as .xls file, and you are on CF8, you can actually do it without writing the file to disk at all:

<cfquery name="qFindDupes" datasource="#request.dsn#">      
select *
from #query_name#
order by lastname, firstname, name
</cfquery>
<cfsavecontent variable="content"><table>
<tr>
<td>Company</td>
<td>Indiv</td>
<td>Name</td>
<td>Title</td>
<td>Phone</td>
<td>State</td>
</tr>
<cfoutput query="qFindDupes"><tr>
<td>#name#</td>
<td>#id#</td>
<td>#firstname# #lastname#</td>
<td>#description#</td>
<td>#FormatPhoneNumber("#qFindDupes.area_code##qFindDupes.phone#")#</td>
<td>#state#</td>
</tr></cfoutput>
</table></cfsavecontent>

<cfmail to="..." from="..." subject="...">
  <p>see attached DupeIndivs.xls file</p>
  <cfmailparam file="DupeIndivs.xls" type="application/vnd.ms-excel" content="#content#">
</cfmail>

that's all you need.

btw, if you save the output you create (the html table) as .csv file, it will have all your html mark-up in it.
if you need to create a .csv file, make sure you create a valid one - do not include any html markup in it and carefully mind the whitespace.

Azadi
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
use the <cfprocessingdirective supreesswhite="yes"> to remove whitespace
0
 
azadisaryevCommented:
>> use the <cfprocessingdirective supreesswhite="yes"> to remove whitespace

that is not a correct statement.

<cfprocessingdirective supreesswhite="yes"> does not REMOVE whitespace. it only prevents your cfml code from generating excessive whitespace (i.e. a multi-line <cfquery> would generate an empty line in resulting html code sent to browser for each line of the tag and sql statement).

<cfprocessingdirective supreesswhite="yes"> does NOT suppress whitespace in non-cfml code you have in your page.

Azadi
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
then use this:

<CFSETTING enablecfoutputonly="Yes">

anyways.
0
 
lantervjAuthor Commented:
I tried the code. I can't find the output. My code prompts to save the file or open in Excel.
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
Which code you actually tried, mine of azadi. Mine code will prompt you to save the file to disk or open it. but you can add a cfmail tag and store in a variable using savecontent and then email it afterwards.
0
 
lantervjAuthor Commented:
This is my final code that does what I need.  I will add the email function if needed.  Allowing the user to download/open in Excel lets them do what they want with it without emailing.

<cfset attributes.suppresslayout2 = "true">

<cfscript>
  function FormatPhoneNumber(number)
  {
   current_phone = #ReReplaceNoCase(number, '[^0123456789]', '', 'ALL')#;
   if (current_phone eq 0) {
               return '';
   }      else {
   areacode = left(current_phone, 3);
   firstthree = mid(current_phone, 4, 3);
   lastfour = right(current_phone, 4);
   phone_number = "(#areacode#)-#firstthree#-#lastfour#";
      Return phone_number;
      }
  }
</cfscript>

<cfquery name="qFindDupes" datasource="#request.dsn#">      
select *
from #query_name#
order by lastname,firstname,name

</cfquery>
<cfsetting enablecfoutputonly="Yes">
<!-- calls excel -->
<cfcontent type="application/vnd.ms-excel">

<!-- sets default excel file name -->
<cfheader name="Content-Disposition" value="filename=DupeIndivs.xls">
<!-- build query loop -->
<cfoutput>
 <table>
      <tr>
<td>Company</td>
<td>Indiv</td>
<td>Name</td>
<td>Title</td>
<td>Phone</td>
<td>State</td>
</tr>
<cfloop query="qFindDupes">
<tr>
<td>#name#</td>
<td>#id#</td>
<td>#firstname# #lastname#</td>
<td>#description#</td>
<td>#FormatPhoneNumber("#qFindDupes.area_code##qFindDupes.phone#")#</td>
<td>#state#</td>
</tr>
</cfloop>
</table>
</cfoutput>

The problem with the output was some default page header info. I suppressed it in the first statement.

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now