My First Forum - a bulletin board application
How to update/edit data using Cold Fusion
You are here: irt.org | Articles | Database | Exporting data to Word with Cold Fusion [ previous next ]
Published on: Sunday 21st March 1999 By: Andrew Shatwell
This article will show you how to create a Cold Fusion script that will export information from your database and deliver it to the user in a Word document. We are cheating slightly here since the file is not a .doc file. We export the information as a Rich Text Format (RTF) file instead.
RTF files are pure text files - just like HTML files. We already know how to display database information in a HTML file. We just take this approach one stage further.
The trick behind this method is planning ahead. It is a very simple process to create a script that will export data. However, once made, the script can be very tedious to modify.
Create a document in Word which contains the static text that you want to appear in every exported file. This could include things like an introduction, page headers, and field names. This document is a skeleton of the final version and so its layout and its formatting should reflect this.
Using an imaginary Personnel record as an example, you would have a document that follows similar lines to this:
Have created the form that we will populate with our data, the next stage is to add in the fields. Add the database fieldnames to the document just as you would if you were displaying the information in a browser. Your document should now be at this stage:
Personnel is the name of my query. You can use any text formatting instructions supported by the RTF format.
Use the Save As option in Word to save the file as a RTF file and load it into a normal text editor (e.g. Notepad).
On first impressions you will be presented with with an unintelligible file. Don't Panic!
At the top of the file, enter all the CFQUERY statements neccessary to retrieve information for the fields in your document. You can leave a couple of blank lines after the queries to make the document easier to read.
Now move your cursor to just in front of the first curly bracket { of the RTF code add the following tags:
<CFCONTENT TYPE="application/rtf"><CFOUTPUT>
There should be no spaces between these tags and the first curly bracket.
At the very end of the file, add a </CFOUTPUT> tag. Similarly there should be no spaces or line feeds after the last curly bracket and the end of the file.
You now should have a file looking like the one below. All Cold Fusion script is highlighted.
<CFQUERY NAME="personnel" DATASOURCE="MyDatabase">
SELECT * FROM Personnel WHERE ID = #MyID#
</CFQUERY>
<CFCONTENT TYPE="application/rtf"><CFOUTPUT>{\rtf1\ansi\ansicpg1252\uc1 \deff0\deflang1033\deflangfe1033{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;
\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;\red128\green128\blue128;
\red192\green192\blue192;}{\stylesheet{\widctlpar\adjustright \fs20\lang2057\cgrid \snext0 Normal;}{\*\cs10 \additive Default Paragraph Font;}}{\info{\title Record for:}{\author Andrew Shatwell}{\operator Andrew Shatwell}
{\creatim\yr1999\mo3\dy2\hr15\min11}{\revtim\yr1999\mo3\dy2\hr15\min11}{\version1}{\edmins0}{\nofpages1}{\nofwords0}{\nofchars0}{\*\company BT}{\nofcharsws0}{\vern89}}\paperw11906\paperh16838
\widowctrl\ftnbj\aenddoc\noextrasprl\prcolbl\cvmme\sprsspbf\brkfrm\swpbdr\hyphcaps0\fracwidth\viewkind1\viewscale104\viewzk2\pgbrdrhead\pgbrdrfoot \fet0\sectd \linex0\endnhere\sectdefaultcl {\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang{\pntxta .}}
{\*\pnseclvl2\pnucltr\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl3\pndec\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl4\pnlcltr\pnstart1\pnindent720\pnhang{\pntxta )}}{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}
{\*\pnseclvl6\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl8\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl9
\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}\pard\plain \widctlpar\adjustright \fs20\lang2057\cgrid {\fs28 Record for:\tab \tab }{\fs28\cf2 #personnel.name#}{\fs28
\par }{
\par }{\fs24 Job Title:\tab \tab }{\fs24\cf2 #personnel.jobtitle#}{\fs24
\par }{
\par }{\fs24 Work Location:\tab }{\fs24\cf2 #personnel.location#}{\fs24
\par
\par Skills:\tab \tab \tab }{\fs24\cf2 #personnel.skills#}{
\par
\par }{
\par }}</CFOUTPUT>Save your modified file as a CFM file and run it. Your browser should receive a RTF file and prompt you to open it in an application or save it to disk.
This method can be with used multiple queries and records. You can add CFOUTPUT, CFLOOP and CFIF statements throughout the RTF code just as you would in a normal Cold Fusion script. If your script has an error then Cold Fusion will export an invalid RTF file. Simply load it into a normal text editor and you will be able to read the error message.
The important fact to remember is to avoid any spaces between the end of the CFCONTENT tag and the start of the RTF code. Likewise, avoid any trailing spaces or line breaks after the final curly bracket at the end of the file.
My First Forum - a bulletin board application
How to update/edit data using Cold Fusion
Diary of a WebObjects Developer