Ask a Jedi: Working with Excel

September 12, 2005 coldfusion

(This post is more than 2 years old.)

A reader asks:

I'm having trouble creating excel spreadsheets. can't get the line feeds to work right. it's all one blob.

Generating Excel files from ColdFusion, in general, is simple enough. Getting Excel to properly render the data, however, can be a real pain in the rear. Luckily, modern versions of Excel (Excel 2000 and higher) allow you to generate Excel files in simple HTML. This makes Excel reports about as simple as they can be. You can even include formulas and conditional formatting. Let's take a quick look at a simple example:

<cfsetting showdebugoutput=false>

<cfset data = queryNew("name,age,gender")>
<cfloop index="x" from="1" to="50">
   <cfset queryAddRow(data)>
   <cfset querySetCell(data,"name","Name #x#")>
   <cfset querySetCell(data,"age",randRange(20,40))>
   <cfif randRange(0,1) is 1>
      <cfset querySetCell(data,"gender","Male")>
   <cfelse>
      <cfset querySetCell(data,"gender","Female")>
   </cfif>
</cfloop>

<cfcontent TYPE="application/msexcel">
<cfheader name="content-disposition" value="attachment;filename=report.xls">

<cfoutput>
<table width="100%" border="1">
<tr>
   <th>Name</th><th>Gender</th><th>Age</th>
</tr>
</cfoutput>

<cfoutput query="data">
   <tr
      <cfif currentRow mod 2>bgcolor="##ffff80"</cfif>
   >
   <td>#name#</td>
   <td>#gender#</td>
   <td>
   <cfif age gte 30>
      <b>#age#</b>
   <cfelse>
      #age#
   </cfif>
   </td>
   </tr>
</cfoutput>

<cfoutput>
<tr>
   <td align="right" colspan="2"><b>Average:</b></td>
   <td>=AVERAGE(c2:c#data.recordCount+1#)</td>
</tr>
</cfoutput>

<cfoutput></table></cfoutput>

I'll skip over the first few lines as all it is doing is creating my data. The important lines begin with the cfcontent and cfheader tags. These are the tags that tell the browser to expect Excel data from the response.

The rest of the code, in general, is nothing more than a simple HTML tag. I create a header. I loop over my query. I even do a bit of conditional formatting. You will notice that I change the bgcolor every other row, which makes things a bit easier to read. For the heck of it, I bolded the age of anyone over 30 (Logan's Run anyone?). The only real "special" code I included was this bit:

<tr>
<td align="right" colspan="2"><b>Average:</b></td>
<td>=AVERAGE(c2:c#data.recordCount+1#)</td>
</tr>

The =AVERAGE line is a simple Excel formula that generates an average on the age of the people in my data. I knew that my age values were in column C, and that they started on the second line (the first line is the header). I then used data.recordCount+1 to ensure the average covered all the values from my data.

What is cool about this result is that, like any other Excel sheet, you can change the data and see the average update automatically.

Now - there is a lot more you can do with Excel then just averages, and do not forget that using this syntax requires a more modern version of Excel (although I think requiring a version from five years ago isn't so bad), but it is certainly a heck of a lot easier to generate the output.

One more tip. Sometimes waiting for Excel to launch, even on a zippy system, can be a pain. Especially if you are just trying to modify the formatting a bit. One nice thing about the "HTML option" for generating Excel is - you can simply comment out the cfcontent/cfheader tags and render your table on screen. Once you get it perfect there, you can return the lines back in and double check to make sure it is still good in Excel.

Edited: Readers of my blog made multiple mentions of the POI project. Dave Ross has two good URLs in the comments section of this entry!