Company Blog

ColdFusion CFSPREADSHEET

February 21, 2011
The cfspreadsheet tag was introduced in ColdFusion 9 and is proven to be very powerful. Before, you had to layout your data in XML format and write the result to an .XLS file format. Now, you are able to save your Excel data in one simple line of code.

Take, for instance, the names.xls file for this example. It includes two records with three columns: FirstName, LastName, and Email. The first row is our header row for our variable names, which I will explain in a bit. The next two rows are our data, which include the following:

<table class="data">
    <tr>
        <th>FirstName</th>
        <th>LastName</th>
        <th>Email</th>
    </tr>
    <tr>
        <td>Joshua</td>
        <td>Rowe</td>
        <td>joshua@nvisionweb.com</td>
    </tr>
    <tr>
        <td>nVision</td>
        <td>Support</td>
        <td>support@nvisionweb.com</td>
    </tr>
</table>

To view this data from our spreadsheet, use the following line of code:



In this section, we are telling ColdFusion to "read" the names.xls file on our server and dump the data to the qryData variable as a query. The headerRow attribute tells us that the first row of our spreadsheet contains the variable names for our data, and the rows attribute tells ColdFusion to pull rows two through four as our data. You can quickly view the data by dumping it:



It's as simple as that! If you want to go a step further, we can append a row to our query and write that the Excel file, as well. Simply add a new row to our query using the following snippet:



Dump the qryData query again to view the added row:



Then write our new results to our names.xls file:



You'll notice two new attributes in the cfspreadsheet tag when writing to an Excel file: The fileName and overwrite attributes. Filename specifics the location and name of the file you wish to output your results to and overwrite was added to make sure the original names.xls was overwritten. If you attempt to write data to an Excel file that already exists, an error will be thrown. Also, make sure that the Excel file is not open by another application or on your computer because this will also throw an error.

---

Add A Comment

Comment
Subscribe to this blog