Solved

Problem on cf_html2excel control

Posted on 2001-08-20
8
212 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
Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

707 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

14 Experts available now in Live!

Get 1:1 Help Now