asked on

Problem on cf_html2excel control

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.

dash420

CF_HTML2Excel is a custom tag. better u go through that. if u want csv file i can help u.

LiquidShadow

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

cheekycj

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

tleish

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

suelow

ASKER

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.

LiquidShadow

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/

suelow

ASKER

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

ASKER CERTIFIED SOLUTION

LiquidShadow

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial