Ask a Jedi: Working with Excel

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:

<FONT COLOR=MAROON><cfsetting showdebugoutput=false></FONT>

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

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

<FONT COLOR=MAROON><cfoutput></FONT>
<FONT COLOR=TEAL><table width=<FONT COLOR=BLUE>"100%"</FONT> border=<FONT COLOR=BLUE>"1"</FONT>></FONT>
<FONT COLOR=TEAL><tr></FONT>
   <FONT COLOR=TEAL><th></FONT>Name<FONT COLOR=TEAL></th></FONT><FONT COLOR=TEAL><th></FONT>Gender<FONT COLOR=TEAL></th></FONT><FONT COLOR=TEAL><th></FONT>Age<FONT COLOR=TEAL></th></FONT>
<FONT COLOR=TEAL></tr></FONT>
<FONT COLOR=MAROON></cfoutput></FONT>

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

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

<FONT COLOR=MAROON><cfoutput></FONT><FONT COLOR=TEAL></table></FONT><FONT COLOR=MAROON></cfoutput></FONT>

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:

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

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!

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Comments