Saturday, July 20, 2013

Export spreadsheet data using spreadsheet object from a query object in ColdFusion

In one of my previous post for exporting data from cfgrid I had used table formatting method for a spreadsheet file you can see here.
Export cfgrid Data or Table Data in Excel, Pdf and CSV Format(ColdFusion - 9)

But, I found another efficient way to export a query object to excelsheet rather than using that table formatting.

Here, in the above code first I am creating a spread sheet object in ColdFusion then I am adding the required header names for each column then I am adding data into the spreadsheet object using the query object.

What are the Benefits of using this method rather than using table formatting like in my previous posts.
  1. In first case we were looping over the query object and creating each row. So, if our query object will contain more records then we will end with looping over the query object. Which is not present here. Here we are creating spreadsheet object from the query object directly.
  2. If we will edit the spreadsheet file downloaded in NotePad++( ) then we will find the <table> and <tr> tags but practically a spreadsheet file doesn't contain such data. So, the formatting is wrong. When we will export spreadsheet file using spreadsheet object then we can see the correct format.
  3. Another benefit is we can create multiple sheets within a single excel file for different query objects.


  1. Can you show an example of creating a second sheet in the same Excel file?