A few weeks ago I blogged a few quick and simple CFSpreadSheet examples. (You can find links to them at the bottom of this blog post.) I was having an email conversation with a reader when an interesting technique came up. Given that you might want to generate random Excel files for different users, how would you ensure that one user doesn't get another user's file? For example, if you follow my first sample you can see that the resultant Excel is saved to a file called myexcel.xls. Great, now what happens when two people run the application at the same time?

If you guessed mass chaos and destruction, you win. You can get around it by using a random name, maybe something like this:


<cfset filename = createUUID() & ".xls">

But then you've got the job of cleaning up the files later on. (Not too difficult with a scheduled task.) But is there an even simpler way? If you don't need to save the Excel file on the server, then just pass it to the user via cfcontent. Given my first example uses a variable called S, you can just do this:


<cfcontent type="application/msexcel" variable="#s#" reset="true">

Right? Nope. It gives you:

Attribute validation error for tag cfcontent.

coldfusion.excel.ExcelInfo is not a supported variable type. The variable is expected to contain binary data.

Ugh. This should just work. Luckily you can just wrap the variable with SpreadSheetReadBinary:


<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Here's the entire modified version of app. It now sends the Excel sheet directly to the user. Note I also added a cfheader to give it a proper name.


<cfif not structKeyExists(form, "doit")>
	
	<form action="test.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>.
--->

	<cfheader name="content-disposition" value="attachment; filename=myexcel.xls">
	<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">
</cfif>