Solved

Problem on cf_html2excel control

Posted on 2001-08-20
8
224 Views
Last Modified: 2013-12-24
Hi,

I have problem in output formatted data to excel file.

The following is the problem code:

<CFQUERY NAME="EmpQuery" DATASOURCE="cfsnippets">
    SELECT Emp_ID, FirstName, LastName
    FROM Employees
</CFQUERY>

<CF_HTML2Excel
    FILENAME="test1"
    SHEETNAME="cfsnippets">
<table width="75%" border="1">
  <tr>
    <td><b>Aetna Universal Insurance - Status Report - June 2001 - INDIVIDUAL LIFE </b></td>
  </tr>
</table>
<table></table>
<table width="75%" border="1">
  <tr>
    <td><b>Cases Received In March 2001</b></td>
  </tr>
</table>
<TABLE width="50%" BORDER="1" CELLSPACING="3" CELLPADDING="3" BORDERCOLOR="Black">    
    <TR>
        <TD>
            <B>Employee ID</B>
        </TD>
        <TD>
            <B>First Name</B>
        </TD>
        <TD>
            <B>Last Name</B>
        </TD>
    </TR>  

    <CFOUTPUT QUERY="EmpQuery">
    <TR>
        <TD BGCOLOR="FFFFCC">
            #Emp_ID#
        </TD>
        <TD BGCOLOR="FF99FF">
            #FirstName#
        </TD>
        <TD BGCOLOR="66CCFF">
            #LastName#
        </TD>
    </TR>
    </CFOUTPUT>
</TABLE>
</CF_HTML2Excel>

<CFHEADER NAME="Content-Disposition" VALUE="inline;filename=test1.xls">
<CFCONTENT TYPE="application/unknown" FILE="#ExcelFile#" DELETEFILE="No">


With the above code, the employee ID column got expanded very widely coz of the main title of the report on top.

Is there any method to solve this so that the excel report would look normal ?

Thank you.

0
Comment
Question by:suelow
8 Comments
 
LVL 6

Expert Comment

by:dash420
ID: 6405990
CF_HTML2Excel is a custom tag. better u go through that. if u want csv file i can help u.
0
 

Expert Comment

by:LiquidShadow
ID: 6406029
Dont bother with the custom tag. use cffile to write a file with html, then name it whatever.XLS. when the page is pulled up, run the query, use cfoutput to write the data to the file then use cflocation to redirect to the .xls file you create. Excel will automatically convert to html to an excel report....
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6406146
I agree with LiquidShadow.. I didn't use any custom tags.. just formatted my query/data into html tables.

Then I put a CFCONTENT header on top of the file with type excel and with a filename of whatever.xls

That was it.

CJ
0
 
LVL 2

Expert Comment

by:tleish
ID: 6406739
Try adding COLSPAN to your <TD> tags in in the first table(s).  Here's an example.


<CFQUERY NAME="EmpQuery" DATASOURCE="cfsnippets">
   SELECT Emp_ID, FirstName, LastName
   FROM Employees
</CFQUERY>

<CF_HTML2Excel
   FILENAME="test1"
   SHEETNAME="cfsnippets">

<TABLE BORDER="1">
 <TR>
   <TD COLSPAN="3"><b>Aetna Universal Insurance - Status Report - June 2001 - INDIVIDUAL LIFE </b></TD>
 </TR>
<TR>
     <TD COLSPAN="3"></TD>
</TR>
 <TR>
   <TD COLSPAN="3"><b>Cases Received In March 2001</b></TD>
 </TR>
</TABLE>

<TABLE BORDER="1" CELLSPACING="3" CELLPADDING="3" BORDERCOLOR="Black">    
   <TR>
       <TD>
           <B>Employee ID</B>
       </TD>
       <TD>
           <B>First Name</B>
       </TD>
       <TD>
           <B>Last Name</B>
       </TD>
   </TR>  

   <CFOUTPUT QUERY="EmpQuery">
   <TR>
       <TD BGCOLOR="FFFFCC">
           #Emp_ID#
       </TD>
       <TD BGCOLOR="FF99FF">
           #FirstName#
       </TD>
       <TD BGCOLOR="66CCFF">
           #LastName#
       </TD>
   </TR>
   </CFOUTPUT>
</TABLE>
</CF_HTML2Excel>

<CFHEADER NAME="Content-Disposition" VALUE="inline;filename=test1.xls">
<CFCONTENT TYPE="application/unknown" FILE="#ExcelFile#" DELETEFILE="No">
0
Give your grad a cloud of their own!

With up to 8TB of storage, give your favorite graduate their own personal cloud to centralize all their photos, videos and music in one safe place. They can save, sync and share all their stuff, and automatic photo backup helps free up space on their smartphone and tablet.

 

Author Comment

by:suelow
ID: 6408230
hi LiquidShadow,

Can u tell me exactly how to do it by using cffile and  cflocation? I need to generate the excel report dynamically after the query.
For cheekycj's method, it will only work if I have created an excel file.

by the way, may I know where to find the syntax for cffile and cflocation ?? I'm a newbie in cfm.
0
 

Expert Comment

by:LiquidShadow
ID: 6409707
try this,


<CFQUERY NAME="EmpQuery" DATASOURCE="cfsnippets">
   SELECT Emp_ID, FirstName, LastName
   FROM Employees
</CFQUERY>

<cfset excelContent = "

   <TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3"">
   <TR><TD><B>Employee ID</B></TD>
       <TD><B>First Name</B></TD>
       <TD><B>Last Name</B></TD>
   </TR>  

">   

<CFOUTPUT QUERY="EmpQuery">
<cfset excelContent = excelContent & "

   <TR><TD BGCOLOR=""FFFFCC"">#Emp_ID#</TD>
       <TD BGCOLOR=""FF99FF"">#FirstName#</TD>
       <TD BGCOLOR=""66CCFF"">#LastName#</TD>
   </TR>

">
</CFOUTPUT>
</TABLE>


<cffile action="WRITE" output="#excelContent#" file="c:/whatever/tempfile#dateformat(now(), 'yyyy_mm_dd')#_#timeformat(now(), 'HH_mm_ss')#.xls">

<cflocation url="tempfile#dateformat(now(), 'yyyy_mm_dd')#_#timeformat(now(), 'HH_mm_ss')#.xls">


as for ColdFusion syntax, try any one of the links on this page:
http://directory.google.com/Top/Computers/Programming/Internet/ColdFusion/
0
 

Author Comment

by:suelow
ID: 6412296
hi LiquidShadow,

The following is the code that works..but let say I need to create another subtitle "Cases Received In April 2001" and to display another set of result in the same report. How should I define the cfset excelContent ??

--------------------------------------------------------
<CFQUERY NAME="EmpQuery" DATASOURCE="cfsnippets">
SELECT Emp_ID, FirstName, LastName, Email, Phone
FROM Employees
</CFQUERY>
<cfset excelContent = "
<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3"">
<tr>
<td colspan=5><b>Aetna Universal Insurance - Status Report - June 2001 - INDIVIDUAL LIFE </b></td>
</tr>
<tr>
<td colspan=5><b>Cases Received In March 2001</b></td>
</tr>
<TR><TD><B>Employee ID</B></TD>
<TD><B>First Name</B></TD>
<TD><B>Last Name</B></TD>
<TD><B>Email</B></TD>
<TD><B>Phone No</B></TD>
</TR>
"> 

<CFOUTPUT QUERY="EmpQuery">
<cfset excelContent = excelContent & "
<TR><TD BGCOLOR=""FFFFCC"">#Emp_ID#</TD>
<TD BGCOLOR=""FF99FF"">#FirstName#</TD>
<TD BGCOLOR=""66CCFF"">#LastName#</TD>
<TD BGCOLOR=""66CCFF"">#Email#</TD>
<TD BGCOLOR=""66CCFF"">#Phone#</TD>
</TR>
">
</CFOUTPUT>
</TABLE>

<cffile action="WRITE" output="#excelContent#" file="c:/inetpub/wwwroot/report/tempfile#dateformat(now(), 'yyyy_mm_dd')#_#timeformat(now(), 'HH_mm_ss')#.xls">

<cflocation url="report/tempfile#dateformat(now(), 'yyyy_mm_dd')#_#timeformat(now(), 'HH_mm_ss')#.xls">

---------------------------------------------------------

thanks ..
0
 

Accepted Solution

by:
LiquidShadow earned 100 total points
ID: 6413241
just repeat the previous code like this:

<CFQUERY NAME="NewQuery" DATASOURCE="cfsnippets">
new query syntax
</CFQUERY>

<cfset excelContent = "
<TR><TD><B>NewQueryCol_1</B></TD>
<TD><B>NewQueryCol_2</B></TD>
<TD><B>NewQueryCol_3</B></TD>
<TD><B>NewQueryCol_4</B></TD>
<TD><B>NewQueryCol_5</B></TD>
</TR>
"> 
<CFOUTPUT QUERY="NewQuery">
<cfset excelContent = excelContent & "
<TR><TD BGCOLOR=""FFFFCC"">#NewQueryCol_1#</TD>
<TD BGCOLOR=""FF99FF"">#NewQueryCol_2#</TD>
<TD BGCOLOR=""66CCFF"">#NewQueryCol_3#</TD>
<TD BGCOLOR=""66CCFF"">#NewQueryCol_4#</TD>
<TD BGCOLOR=""66CCFF"">#NewQueryCol_5#</TD>
</TR>
">
</CFOUTPUT>
<cfset excelContent = excelContent & "
</TABLE>
">


put it before the cffile tag

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now