Solved

Problem on cf_html2excel control

Posted on 2001-08-20
8
256 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 

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

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

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

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…
What You Need to Know when Searching for a Webhost Provider
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

729 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