In the past few days, I met many people in Classic and 5D asking how to use CF to generate EXCEL files. Last night I wrote a demonstration program for your reference.
I suggest that you experience it yourself slowly. The program itself is not complicated. If you don’t understand it, then ask. It is best not to ask questions without even reading it...
OK, I will use my CCF forum database to do experiments, with the purpose of opening EXCEL files online and generating EXCEL files online. The file content is the name and number of all sections of the CCF forum. I made a code that generates a WORD file. I changed it. Everyone should take a closer look.
To open the OFFICE file online, you must have OFFICE installed in your system. Remember this or you will prompt for download.
<!---File Code--->
<CFAPPLICATION NAME="CfToOffice"
sessionmanagement="Yes">
<CFSET="CCF"><!---Database DSN--->
<!--------------->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfquery name="viewlist" datasource="##">
select boardID,boardNAME from board
order by boardID
</cfquery>
<html>
<head>
<title>CCForum column list</title>
</head>
<body>
<center><h3>CCForum column list</h3></center><br><br>
<table border="1" align="center">
<tr><td align="center"><b>No.</b></td><td align="center"><b>Name</b></td></tr>
<cfoutput query="viewlist">
<tr><td align="center">#boardID#</td><td align="left">#boardname#</td></tr>
</cfoutput>
</table>
<p>
<center>
<a href="" target="_blank">Open with EXCEL in WEB</a><br><br>
<a href="" target="_blank">Open in word in WEB</a><br><br>
<a href="" target="_blank">Generate EXCEL file and download</a><br><br><br>
CopyRight(C)wait
</center>
</body>
</html>
<!-----This file is a code used to open online as an EXCEL file ---->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfsetting enablecfoutputonly="Yes">
<cfquery name="getboard" datasource="##">
select boardID,boardNAME from board
order by boardID
</cfquery>
<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=">
<cfset tabchar=chr(9)>
<cfset newline=chr(13)&chr(10)>
<cfloop query="getboard">
<cfoutput>#boardID##tabchar##boardname##newline#</cfoutput>
</cfloop>
<!---------Add a Generated WORD Demonstration... Note that only one change is made.--------->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfsetting enablecfoutputonly="Yes">
<cfquery name="getboard" datasource="##">
select boardID,boardNAME from board
order by boardID
</cfquery>
<cfcontent type="application/msword">
<cfheader name="Content-Disposition" value="filename=">
<cfset tabchar=chr(9)>
<cfset newline=chr(13)&chr(10)>
<cfloop query="getboard">
<cfoutput>#boardID##tabchar##boardname##newline#</cfoutput>
</cfloop>
<!---There are everyone's concerns, and we will do the important part next---->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfsetting enablecfoutputonly="Yes"><!-- Delete blanks to improve performance -->
<cfparam name="filebody" default="">
<cfset tabchar=chr(9)><!-- Set the TAB variable -->
<cfset newline=chr(13)&chr(10)><!-- Set newline variable -->
<cfset filepath=expandpath("file/")><!-- Swap the relative path to the absolute path -->
<cfset writepath="#filepath###.xls"><!-- Absolute path plus file name -->
<!-- Query data ->
<cfquery name="getboard" datasource="##">
select boardID,boardNAME from board
order by boardID
</cfquery>
<!-- Set all information as a variable filebody -->
<cfloop query="getboard">
<cfoutput>
<cfset filebody=filebody & "#boardID##tabchar##boardname#" & newline>
</cfoutput>
</cfloop>
<!-- Write filebody to file -->
<cffile action="WRITE" file="#writepath#" output="#filebody#" addnewline="No">
<cfoutput>The information you need has generated an EXCEL format file.
File address is:<br>
<a href="file/##.xls">http<cfif neq 'off'>s</cfif>
://#CGI.HTTP_HOST#/CF/Excel/file/##.xls</a>
</cfoutput>
It is worth noting that I store the dynamically generated XLS files in a folder called FILE. When you run this code, you must first create a FILE folder... Otherwise, there will be an error.
I suggest that you experience it yourself slowly. The program itself is not complicated. If you don’t understand it, then ask. It is best not to ask questions without even reading it...
OK, I will use my CCF forum database to do experiments, with the purpose of opening EXCEL files online and generating EXCEL files online. The file content is the name and number of all sections of the CCF forum. I made a code that generates a WORD file. I changed it. Everyone should take a closer look.
To open the OFFICE file online, you must have OFFICE installed in your system. Remember this or you will prompt for download.
<!---File Code--->
<CFAPPLICATION NAME="CfToOffice"
sessionmanagement="Yes">
<CFSET="CCF"><!---Database DSN--->
<!--------------->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfquery name="viewlist" datasource="##">
select boardID,boardNAME from board
order by boardID
</cfquery>
<html>
<head>
<title>CCForum column list</title>
</head>
<body>
<center><h3>CCForum column list</h3></center><br><br>
<table border="1" align="center">
<tr><td align="center"><b>No.</b></td><td align="center"><b>Name</b></td></tr>
<cfoutput query="viewlist">
<tr><td align="center">#boardID#</td><td align="left">#boardname#</td></tr>
</cfoutput>
</table>
<p>
<center>
<a href="" target="_blank">Open with EXCEL in WEB</a><br><br>
<a href="" target="_blank">Open in word in WEB</a><br><br>
<a href="" target="_blank">Generate EXCEL file and download</a><br><br><br>
CopyRight(C)wait
</center>
</body>
</html>
<!-----This file is a code used to open online as an EXCEL file ---->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfsetting enablecfoutputonly="Yes">
<cfquery name="getboard" datasource="##">
select boardID,boardNAME from board
order by boardID
</cfquery>
<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=">
<cfset tabchar=chr(9)>
<cfset newline=chr(13)&chr(10)>
<cfloop query="getboard">
<cfoutput>#boardID##tabchar##boardname##newline#</cfoutput>
</cfloop>
<!---------Add a Generated WORD Demonstration... Note that only one change is made.--------->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfsetting enablecfoutputonly="Yes">
<cfquery name="getboard" datasource="##">
select boardID,boardNAME from board
order by boardID
</cfquery>
<cfcontent type="application/msword">
<cfheader name="Content-Disposition" value="filename=">
<cfset tabchar=chr(9)>
<cfset newline=chr(13)&chr(10)>
<cfloop query="getboard">
<cfoutput>#boardID##tabchar##boardname##newline#</cfoutput>
</cfloop>
<!---There are everyone's concerns, and we will do the important part next---->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfsetting enablecfoutputonly="Yes"><!-- Delete blanks to improve performance -->
<cfparam name="filebody" default="">
<cfset tabchar=chr(9)><!-- Set the TAB variable -->
<cfset newline=chr(13)&chr(10)><!-- Set newline variable -->
<cfset filepath=expandpath("file/")><!-- Swap the relative path to the absolute path -->
<cfset writepath="#filepath###.xls"><!-- Absolute path plus file name -->
<!-- Query data ->
<cfquery name="getboard" datasource="##">
select boardID,boardNAME from board
order by boardID
</cfquery>
<!-- Set all information as a variable filebody -->
<cfloop query="getboard">
<cfoutput>
<cfset filebody=filebody & "#boardID##tabchar##boardname#" & newline>
</cfoutput>
</cfloop>
<!-- Write filebody to file -->
<cffile action="WRITE" file="#writepath#" output="#filebody#" addnewline="No">
<cfoutput>The information you need has generated an EXCEL format file.
File address is:<br>
<a href="file/##.xls">http<cfif neq 'off'>s</cfif>
://#CGI.HTTP_HOST#/CF/Excel/file/##.xls</a>
</cfoutput>
It is worth noting that I store the dynamically generated XLS files in a folder called FILE. When you run this code, you must first create a FILE folder... Otherwise, there will be an error.