Creating an Excel Spreadsheet on the Fly with Your Results

The great thing about ColdFusion is that you can create dynamic Microsoft Excel files easily. Use the following steps to do so:

1. Open a blank ColdFusion template and name it excel.cfm.

2. Insert the cfquery tag to return all of the information in the survey database.

   <cfquery name="getSurveyData" datasource="myQuiz">
    SELECT * FROM Captivate</cfquery>

3. Add the cfheader tag. This tag passes custom header information to the browser. I included a file name in this instance. For more information on additional parameters check RFC 2183.

   <cfheader name="Content-Disposition" value="inline;
   
    filename=mySurveyResults.xls">

4. Add the cfcontent tag. This tells the browser that it’s reading an Excel file, not a web page.

   <cfcontent type="application/msexcel" >

5. Create an HTML table with your information. Caution: Excel can be sensitive about formatting.
6. Copy and paste the table format from the following code snippet:

   <table border="1" cellspacing="0">
         <thead>
         <tr>
              <th width="200"><div align="left">Name</div></th> 
             <th width="200"><div align="left">Email</div></th>
    		  <th width="200"><div align="left">Total</div></th>
              <th width="200"><div align="left">Correct</div></th>
              <th width="10%"><div align="left">Accuracy</div></th>
           </tr>
      </thead>
    	  <tbody>
            <cfoutput query="getSurveyData">
              <tr align="left">
    	       <td>#name#</td>
    	       <td>#email#</td>
    		   <td>#total#</td>
    	       <td>#correct#</td>
    	       <td>#accuracy#%</td>
              </tr>
            </cfoutput>
          </tbody> 
   </table> 

7. Save the CFML template in the same folder as your other files.
8. Browse excel.cfm, for example at: http://localhost:8500/Captivate_scoring/excel.cfm, (your URL may be different based on where you placed your files in your directory structure and your host server port). When you browse the file, your browser will prompt you to download the Excel file, mySurveyResults.xls.

You have now completed all the steps for your Captivate quiz. Publish your files and ensure that the results tabulate accurately and that all the functionality works (such as the JavaScript close window feature).

Conclusion

Captivate is an extremely versatile tool that you can easily incorporate with powerful languages and, of course, Macromedia tools. In this tutorial, you created a quiz, stored your test results in a database, and exported the results automatically to an Excel spreadsheet, eliminating the need for more intricate solutions that spend precious resources. This article shows only a few of the ideas that are possible with Captivate, so spend some time exploring it and see what else you can come up with!