One more (I swear this is it) follow up to my CFSpreadSheet Example

This post is more than 2 years old.

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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Dan Murphy posted on 7/15/2011 at 12:16 AM

Well, I for one and happy you decided to take it a step further. Great cfspreadsheet posts - they've been an amazing resource. Thanks Ray!

Comment 2 by reinout posted on 8/17/2011 at 1:45 AM

I'm quite new on coldfusion, but your blog is a great resource for me. Thnx!

Comment 3 by Raymond Camden posted on 8/17/2011 at 3:08 AM

You (both) are welcome.

Comment 4 by Brent Michalski posted on 9/16/2011 at 12:13 AM

I have used your code and when I run it using IE, I get an error saying the file is corrupt once excel opens the file. If I use FireFox, it works just fine!

Do you have any suggestions? I've been playing around with it for a while now and still am getting nowhere!

Thank you for these awesome posts!

Comment 5 by Raymond Camden posted on 9/16/2011 at 12:21 AM

What version of IE? It's working for me in 9.

Comment 6 by Graeme posted on 11/19/2011 at 5:39 AM

Your examples are great, thank you for posting them as they've been a great help. One issue I'm coming across is if the row data in the query has a comma in it. It pushes everything over however many fields as there are commas and then the headers no longer match up. Is there a solution for this?

Comment 7 by Raymond Camden posted on 11/21/2011 at 1:51 AM

I'm not seeing this. I used "Camden, Ray" for my name in the form from the demo above and it worked fine.

Comment 8 by Graeme posted on 12/28/2011 at 8:56 AM

Hi Ray,

Thanks for the response, sorry for the late reply, I've had this tab open for 4 weeks now..

Anyways, I have to grab a bunch of fields from a query to create the row. When I do that and put the fields together into a string and then create the row with "spreadsheetAddRow(s spreadSheetRow)", where "spreadSheetRow" is set to the multiple fields from the DB, I then get the fields bleeding across columns if there are commas in the field values from the DB.

Does that make sense? How do I solve this issue?

Thanks
Graeme

Comment 9 by Graeme posted on 12/28/2011 at 10:16 AM

I figured it out! Ha.. just had to do it exactly how you wrote it. I thought I could get away with adding spreadsheet rows while going through the original query and making a long string with commas in it. Nope.

Thanks again Ray for your great examples you put up, they're very very helpful.

Graeme

Comment 10 by Raymond Camden posted on 12/28/2011 at 10:06 PM

Glad you got it!

Comment 11 by Brad Campbell posted on 12/18/2013 at 5:43 PM

Raymond,

Even two years later these posts are a great and useful resource. I was working with this functionality in CF 9 recently and thought I would share one thought that works in tandem with yours. I wanted to create my spreadsheets in the newer xlsx format. To do so, one could basically use your example above, but change your line 73 to read:

<cfset s = spreadsheetNew("", "true")> <!--- passing a text value for the first parameter will set the worksheet name to that value -- string cannot be longer than 30 chars -- the second parameter, set to "true", tells spreadsheetNew() to create the excel object using the newer xml-based (xlsx) format --->

And your line 89 to read:

<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#spreadsheetReadBinary(s)#" reset="true">

Thanks again for the awesome resource.
-Brad

Comment 12 by Raymond Camden posted on 12/18/2013 at 5:55 PM

Always happy to see these old posts still being useful. :)