ColdFusion Sample – Create an Excel File

In this ColdFusion sample, I’ll demonstrate a simple way to take form input and generate an Excel spreadsheet file. This is a simple example with a very basic form, but hopefully it will give you enough to go by to create your own similar application.

Let’s begin by creating a simple form. This form is going to ask for 10 rows of data that mimics the Excel sheet I uploaded in my previous blog entry. It will ask for a name and the number of beers, vegetables, fruits, and meats consumed.


<form action="test2.cfm" method="post">
<table>
<tr>
<th>Name</th>
<th>Beers</th>
<th>Vegetables</th>
<th>Fruits</th>
<th>Meats</th>
</tr>
<cfloop index="x" from="1" to="10">
<cfoutput>
<tr>
<td><input type="text" name="name_#x#"></td>
<td><input type="text" name="beers_#x#"></td>
<td><input type="text" name="veggies_#x#"></td>
<td><input type="text" name="fruits_#x#"></td>
<td><input type="text" name="meats_#x#"></td>
</tr>
</cfoutput>
</cfloop>
</table>
<input type="submit" name="doit" value="Create Excel File">
</form>

This form could be more dynamic of course. You could present 3 rows and use jQuery (or another less capable JavaScript framework) to easily add additional rows for input. But to keep things simple the form will just create 10 rows. Here’s how it looks – and again – more work could be done to make this friendlier to the end user.

Ok – now to generate the spreadsheet. Let’s look at an example.


<cfset q = queryNew("Name,Beers,Vegetables,Fruits,Meats", "cf_sql_varchar,cf_sql_integer,cf_sql_integer,cf_sql_integer,cf_sql_integer")>
<cfloop index="x" from="1" to="10">
<cfset queryAddRow(q)>
<cfset querySetCell(q, "Name", form["name_#x#"])>
<cfset querySetCell(q, "Beers", form["beers_#x#"])>
<cfset querySetCell(q, "Vegetables", form["veggies_#x#"])>
<cfset querySetCell(q, "Fruits", form["fruits_#x#"])>
<cfset querySetCell(q, "Meats", form["meats_#x#"])>
</cfloop>

<cfset filename = expandPath("./myexcel.xls")>
<cfspreadsheet action="write" query="q" filename="#filename#" overwrite="true">

We begin by turning the form data into a query. This is done by looping from 1 to 10 and grabbing the relevant values from the Form struct. You could do a trim and other checks here as well. The real magic of this code block is the last line. All it takes to create a spreadsheet in ColdFusion is one line. You pass in the query, filename, and… that’s it! While this works, the output is a bit plain. Let’s now look at a slight modification.


<!--- Make a spreadsheet object --->
<cfset s = spreadsheetNew()>
<!--- Add header row --->
<cfset spreadsheetAddRow(s, "Name,Beers,Vegetables,Fruits,Meats")>
<!--- format header --->
<cfset spreadsheetFormatRow(s,
{
bold=true,
fgcolor="lemon_chiffon",
fontsize=14
},
1)>

<!--- Add query --->
<cfset spreadsheetAddRows(s, q)>
<cfset spreadsheetWrite(s, filename, true)>

This version is a bit more complex. We begin creating a blank spreadsheet object. Next we add the header row. We can format both cells and rows, and in this example I’ve gone ahead and formatted the header. You have many options but I used just bold, fgcolor, and fontsize. (No one make any comments about lemon chiffon, it rocks.)

Next I add the data using spreadsheetAddRows and write out the data. Here’s a quick example of the result.

Here’s the entire code template.


<cfif not structKeyExists(form, "doit")>

<form action="test2.cfm" method="post">
<table>
<tr>
<th>Name</th>
<th>Beers</th>
<th>Vegetables</th>
<th>Fruits</th>
<th>Meats</th>
</tr>
<cfloop index="x" from="1" to="10">
<cfoutput>
<tr>
<td><input type="text" name="name_#x#"></td>
<td><input type="text" name="beers_#x#"></td>
<td><input type="text" name="veggies_#x#"></td>
<td><input type="text" name="fruits_#x#"></td>
<td><input type="text" name="meats_#x#"></td>
</tr>
</cfoutput>
</cfloop>
</table>
<input type="submit" name="doit" value="Create Excel File">
</form>

<cfelse>

<cfset q = queryNew("Name,Beers,Vegetables,Fruits,Meats", "cf_sql_varchar,cf_sql_integer,cf_sql_integer,cf_sql_integer,cf_sql_integer")>
<cfloop index="x" from="1" to="10">
<cfset queryAddRow(q)>
<cfset querySetCell(q, "Name", form["name_#x#"])>
<cfset querySetCell(q, "Beers", form["beers_#x#"])>
<cfset querySetCell(q, "Vegetables", form["veggies_#x#"])>
<cfset querySetCell(q, "Fruits", form["fruits_#x#"])>
<cfset querySetCell(q, "Meats", form["meats_#x#"])>
</cfloop>

<cfset filename = expandPath("./myexcel.xls")>
<!---
<cfspreadsheet action="write" query="q" filename="#filename#" overwrite="true">
--->
<!--- Make a spreadsheet object --->
<cfset s = spreadsheetNew()>
<!--- Add header row --->
<cfset spreadsheetAddRow(s, "Name,Beers,Vegetables,Fruits,Meats")>
<!--- format header --->
<cfset spreadsheetFormatRow(s,
{
bold=true,
fgcolor="lemon_chiffon",
fontsize=14
},
1)>

<!--- Add query --->
<cfset spreadsheetAddRows(s, q)>
<cfset spreadsheetWrite(s, filename, true)>

Your spreadsheet is ready. You may download it <a href="myexcel.xls">here</a>.

</cfif>

Your Homework!

In the code template above, I save the file and use HTML to link to it. Modify this template to instead serve the content to the user immediately. Also – use another ColdFusion function to specify a numeric formatting for the columns (all of the ones but name).

  • haysfluid

    Using the code above, how can you also repeat the header row on each printed page. This is needed if the number of rows exceeds the size of a page when printing.

    • http://www.raymondcamdencom/ Raymond Camden

      Hmm, not sure. If you build a simple XLS sheet in Excel itself, does it do this automatically?

      • haysfluid

        Thanks for getting back to me. I was hoping to build off of this example. I have seen other places where they used XML, then opened that XML in Excel, added the repeating header and then compared XML to see what XML code was added. They then added that code to their ColdFusion and I am guessing it worked. I prefer this example, I am not very familiar with XML. That’s why I was hoping you or someone else had accomplished this using your example as a base.

        I was abled to change the layout from portrait to landscape with some code I found. (POI??) It looks like using this same code will work, but I can’t figure it out. Just can’t seem to get it to work. Here is the Code for both the Landscape change and the Repeating code that I can’t get to work.

        __________________________________________________________


        ______________________________________________________

        It doesn’t like the setRepeatingRowsAndColumns(….) It either says it’s not a defined function or tells me it’s expecting (sheetIndex) which is there, but has the int in front of it. But if I remove the int from all of the items, it then tells me they are not defined. With just this code added to your code I get this error when trying to load the page.

        Invalid CFML construct found on line 578 at column 48.ColdFusion was looking at the following text:sheetIndexThe CFML compiler was processing:An expression beginning with poiSheet.setRepeatingRowsAndColumns, on line 578, column 8.This message is usually caused by a problem in the expressions structure.A cfset tag beginning on line 578, column 2.A cfset tag beginning on line 578, column 2.

        • http://www.raymondcamdencom/ Raymond Camden

          Yeah, sorry, I can’t really help here. I haven’t worked with Poi in years. Best I can do is wish you luck. :)

          • haysfluid

            Thanks!

      • haysfluid

        Here is the information on the POI that I talked about.
        Webpage:
        )https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setRepeatingRows(org.apache.poi.ss.util.CellRangeAddress)

        Below is the content that I think will do the trick, just can’t figure out how to get it working in my ColdFusion code.
        setRepeatingRowsAndColumns

        void setRepeatingRowsAndColumns(int sheetIndex,
        int startColumn,
        int endColumn,
        int startRow,
        int endRow)

        Deprecated. use Sheet.setRepeatingRows(CellRangeAddress) or Sheet.setRepeatingColumns(CellRangeAddress)

        Sets the repeating rows and columns for a sheet (as found in File->PageSetup->Sheet). This is function is included in the workbook because it creates/modifies name records which are stored at the workbook level.

        To set just repeating columns:

        workbook.setRepeatingRowsAndColumns(0,0,1,-1-1);
        To set just repeating rows:

        workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
        To remove all repeating rows and columns for a sheet.

        workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);

        Parameters:sheetIndex – 0 based index to sheet.startColumn – 0 based start of repeating columns.endColumn – 0 based end of repeating columns.startRow – 0 based start of repeating rows.endRow – 0 based end of repeating rows.