Solved

Problem on cf_html2excel control

Posted on 2001-08-20
8
251 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Website being blocked? 3 145
ColdFusion Rereplace 3 94
Finding an Azure real-time monitoring dashboard 2 91
Domino Website - Redirection 12 119
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

740 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