Ok, I promise, pinky swear, etc, that this is the last version of the little Excel generator I've now mentioned in two blog posts. Yesterday's blog post demonstrated a modification to the application that dynamically generated the Excel data instead of saving it to a hard coded file. This worked great - but than a reader asked for one more slight modification - a preview.

In the original script, the file is basically split into two parts. In the top portion, a form with ten rows of columns allows for basic user input. When the form is submitted the second half simply converts the form data into a query and passes it to the relevant spreadsheet functions.

I modified the script not to act in three parts. It isn't terribly long so I'll paste the entire script and explain the difference.

<cfif structIsEmpty(form)>
	
	<form 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="preview" value="Preview Excel File">
	</form>
		
<cfelseif not structKeyExists(form, "doit")>
	
	<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">
		<cfif len(trim(form["name_#x#"]))>
			<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#"])>
		</cfif>
	</cfloop>

	<h2>Preview</h2>
	<table border="1">
		<tr>
			<th>Name</th>
			<th>Beers</th>
			<th>Vegetables</th>
			<th>Fruits</th>
			<th>Meats</th>
		</tr>
		<cfoutput query="q">
		<tr>
			<td>#name#</td>
			<td>#beers#</td>
			<td>#vegetables#</td>
			<td>#fruits#</td>
			<td>#meats#</td>
		</tr>
		</cfoutput>
	</table>

	<cfoutput>	
	<form method="post">
	<input type="hidden" name="q" value="#htmlEditFormat(serializeJSON(q,true))#">
	<input type="submit" name="doit" value="Generate Excel">
	</form>
	</cfoutput>

<cfelse>

	<cfset q = deserializeJSON(form.q,false)>

	<!--- 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)>

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


</cfif>

So the top portion is pretty much the same as before, except now we've changed the submit button to explicitly say it's a Preview. The second branch converts the form data into a query. I modified it a bit to require a name to exist before a row is added to the query. I then use a simple (and somewhat ugly) table to render the preview.

To handle the third and final part, I used JSON to serialize the query and place it in a hidden form field. This then let's me just deserialize it in the final step and pass it right on to the ColdFusion spreadsheet functions. That's it. No more blog posts on spreadsheets. Not this week anyway.