[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Problem on cf_html2excel control

Posted on 2001-08-20
8
Medium Priority
?
265 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 300 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
What You Need to Know when Searching for a Webhost Provider
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

656 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