Quick followup to my CFSpreadSheet Samples

This post is more than 2 years old.

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>
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 Andrew Scott posted on 7/12/2011 at 8:16 AM

As much as this is a great tip, I would like to add that there are times you might want to keep a copy of the file as well. Most of the time these type of files are when a user has been logged in, so what you can do is use such things as the name of the user, as well as the name of the file to make it unique for the user as well.

But if you don't need to have a copy for archiving purposes, then this is a great tip.

Comment 2 by Raymond Camden posted on 7/12/2011 at 2:26 PM

Ahem....

"If you don't need to save the Excel file on the server,..."

;)

Comment 3 by Andrew Scott posted on 7/12/2011 at 4:32 PM

Yes you did, but in case others read this that are not familiar I thought I would mention it anyway.

Comment 4 by Raymond Camden posted on 7/12/2011 at 4:42 PM

Another option would be to store the binary in the db. I've never been a big fan of that, but you could save the BLOB too.

Comment 5 by Ben B posted on 7/13/2011 at 1:14 AM

Pros and cons of storing files in the DB... There's a discussion I'd be interested in seeing. Maybe even a separate post.

I've toyed with the idea but haven't tried it. I thought it might be an alternative way I could avoid storing sensitive files in web root.

Comment 6 by Raymond Camden posted on 7/13/2011 at 1:21 AM

I'm no DBA. My only real thought is that a file system is _built_ for storing files, so why _not_ use it. Web root shouldn't matter. A proper web app has access to more than just web root. :)

Comment 7 by Jason Fisher posted on 7/14/2011 at 7:15 PM

@Ben B

I have worked the storage of (image) files as CLOBs in the database, and it works pretty well, BUT the files become huge, as the string format is larger than the tight binary. Never had much luck with the actual binary in the BLOB, and CF made it easy to do the stream into the CLOB.

Abandoned it pretty quickly, though, as the DB size grew astronomically, and, as Ray points out, the filesystem really is quite good at storing and managing files.

Comment 8 by Rick Hazen posted on 7/14/2011 at 8:46 PM

Very helpful post, Ray. I am using this in my current project but have run into an error when trying to create spreadsheets with more than 256 columns: "An exception occurred while calling the function addRow. java.lang.IllegalArgumentException: Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV')". This is a definite problem because I need to generate some pretty large spreadsheets. Any ideas?

Comment 9 by Raymond Camden posted on 7/24/2011 at 6:53 PM

@Rick: Outside of filing a bug report - no. Sorry. You could try Nadel's CFPOI to see if that gets around it though. (Sorry for delay - was on vacation.)

Comment 10 by Walt posted on 4/16/2012 at 10:24 PM

I may or may not be confused about formatting functionality.
I'm wanting to add a background color to a row, irrespective of whether there is text or not.
I use 'fgcolor' as the attribute to change in the formatting structure I send to the formatting function (SpreadsheetFormatRow) but no formatting occurs unless there is text or a value in the cell itself.
Should I not be able to change the color of a row?
Confused and addled.

Comment 11 by Raymond Camden posted on 4/16/2012 at 10:36 PM

What about a value of ""?

Comment 12 by Tom Van Schoor posted on 6/19/2012 at 10:52 AM

Hi Raymond,

We met on the cf-objective 2012 ;)

SpreadsheetAddRow has proven to be very slow when handling a lot of rows, whereas the tag <cfspreadsheet> is very fast.

Therefore I would suggest:

<!--- create unique filename --->
<cfset filename = expandPath("./#createUUID()#-myexcel.xls")>

<!--- Create some query and fill it up --->
<cfset q = queryNew("Date,Description,Load,Deduct,Balance", "cf_sql_timestamp,cf_sql_varchar,cf_sql_decimal,cf_sql_decimal,cf_sql_decimal")>

<!--- Write the file to disc --->
<cfspreadsheet action="write" query="q" filename="#filename#" overwrite="true">

<!--- Read the file and pass it to cfcontent --->
<cffile action="readbinary" file="#filename#" variable="mySheet">

<!--- Delete the file in same request --->
<cffile action="delete" file="#filename#">

<!--- Pass the binary to cfcontent --->
<cfcontent type="application/x-msexcel" reset="true" variable="#mySheet#">

Comment 13 by Raymond Camden posted on 6/19/2012 at 5:42 PM

Interesting. What if you skip addRow and use addRows instead? It will take a query. You can also - probably - skip the 'real' file system and write to RAM using VFS instead.

Comment 14 by Tom Van Schoor posted on 6/20/2012 at 9:55 AM

addRows is supposedly just as slow, but I haven't tested it personally. I will benchmark it and will post the results.

Cheers,
Tom

Comment 15 by Dylan posted on 7/19/2012 at 9:41 PM

@Ray & @Tom:

I know it's late to the thread, but I wanted to post here so others don't run into the same issue I did. SpreadsheetAddRow() and SpreadsheetFormatRow() are not only crazy slow (even on relatively small spreadsheets, like 500 rows), but they also use HUGE amounts of memory. We kept running into an issue on the server where JRun would consume massive amounts of memory as it tried to build a large spreadsheet one row at a time. Refactoring the code the push all the content for the spreadsheet into a query, then using SpreadsheetAddRows() with the query (on 9.0.1), then running SpreadsheetFormatCellRange(), sped things up by a factor of 10-100x. Plus JRun is happy. So our clients are happy. So I'm happy. :)

Comment 16 by Charles Higgins posted on 2/9/2013 at 4:39 PM

Quick note about performance, it still sucks on CF10. As does the issue with Comma only demli.
I a found solution to my little problem, and just posting up for anybody else googling the issue.
It can generate a 10,000 row xls file (3mb+ in just over second with default server settings).

1. Generate a CSV file using coldfusion (cfsavecontent various outputs) (as i I have a few dynamic labels, and use a none COMMA delimiter!).
2. Convert CSV into a query (I used the cfx text2query as that allows you to spec a none comma delmi).
3. Use cfspreadsheet to convert the query into xls.

Comment 17 by Misty posted on 4/4/2013 at 4:28 PM

Hi ray, I am working on Spreadsheets, but i am stuck at two points. the one being give a hyperlink in one of my Workbook sheet to link and open the second sheet of the same workbook. I am using the following code, but it creates the link but always says, unable to load file

<cfset SpreadsheetSetCellFormula(s, 'HYPERLINK("[Tickets Summary]TicketID_#mainTickets.ticketID#!A1","Click Here")',#k#,12)>

#k# is the rowline number as this code runs inside the loop.

I am lost here why here link is not working

Another thing is: the Function <cfset spreadsheetAddRows(s, queryName)>

Can't I Pass the Column names of what i need to show and i do not want to show in this rather than it asks for complete queryName

Comment 18 by Raymond Camden posted on 4/8/2013 at 6:35 PM

1) Um, no idea. I don't use Excel that much and when I do, my formulas are super simple. Best I can say is to look at the result XLS file and compare it to one you made yourself where it works. See what's different.

2) You can either skip this function and do it more manually or use a Query of Query to create a new query with just the columns you want.

Comment 19 by Al Schwarz posted on 10/1/2013 at 5:57 PM

Thanks for your samples.
I have cloned them and tweaked to suit my needs.
I had some fields that in my data were ones or zeros.
the user wants to see yes or no in those columns.
a minor tweak to my code,

CASE
WHEN a.[MaintenanceAudit] ='1' THEN 'Yes'
ELSE 'No'
END as MaintenanceAudit

that works fine, when the spreadsheet shows on the screen,

but once it gets converted to the Excel, it has true or false in those fields instead of the yes or no...

any thoughts?

Comment 20 by Raymond Camden posted on 10/1/2013 at 6:20 PM

Hmm. I assume you are passing a query object to the Excel sheet. Check the metadata of the query columns and see if that column has a boolean type. If so, try casting it to varchar in a QofQ.

Comment 21 by Mike Rankin posted on 3/11/2014 at 9:49 PM

Hi Ray,

Have you had a chance to try out saving an excel spreadsheet with a password and some protected cells? From the docs, it looks like it should just work, but for me, nothing seems to do anything. Do you know if it's just really quirky or if it simply doesn't work?

Comment 22 by Raymond Camden posted on 3/12/2014 at 7:43 AM

I haven't tried it. Do you have a CFM I can run here to test?

Comment 23 by David Knighton posted on 3/14/2014 at 10:13 PM

Late to the game here, but wanted to add my two cents for posterity, and because the comments here REALLY helped me out.

I also had a problem with spreadsheet generation being extremely slow. Anything over 600 records or so would always timeout, and generate a really funky spreadsheet to boot. In the end, it was the same issue others described here with using SpreadsheetFormatCell vs. SpreadsheetFormatCellRange. Once I switched to the latter, large spreadsheets generate in just a few seconds with no funk.

Separate subject....with regard to the protected cells issue, I have not personally needed to implement password protection, but the sheets I'm generating do have several columns with information we do not want the users altering. Through a lot of painful googling, I found the only combination that seems to work is protecting the entire sheet by using the password parameter in cfspreadsheet, as well as explicitly locking and unlocking columns or cells within SpreadsheetFormatCell and SpreadsheetFormatCellRange, using locked=true or locked=false. In my case my password value is blank to simply accomplish locking the cells initially, but I would think in theory providing a password would result in the sheet being password protected.

Hope that helps, though admittedly I could be making it worse. :)

Comment 24 by Raymond Camden posted on 3/14/2014 at 10:37 PM

Thanks for sharing David.

Comment 25 by Brig posted on 5/21/2014 at 5:06 PM

Hi Ray,

Very helpful and clear post. I've been looking high and low with no luck - is there any way to create cfspreadsheet with password protection? I don't just mean cell protection - I mean the user must enter a password to open the spreadsheet.

Thanks for any help you can provide!

Comment 26 by Raymond Camden posted on 5/21/2014 at 11:19 PM

Afaik, you can password protect it from being modified, but not being opened. Does Excel even offer that? I don't think I've seen it. You could use cfzip in ColdFusion 11 to password protect a zip of the file.

Comment 27 by Ronan Campbell posted on 7/9/2014 at 12:47 PM

Hi,
this is a really nice piece of code but Im having a slight issue with this part in CF10

<!--- format header --->
48 <cfset spreadsheetFormatRow(s,
49 {
50 bold=true,
51 fgcolor="lemon_chiffon",
52 fontsize=14
53 },
54 1)>

I was wondering if you had any issues with applying formatting to the excel sheet. I have tried varying approaches but I can't seem to get the output to adhere to any styling at all.

Thanks in advance

Comment 28 by Raymond Camden posted on 7/9/2014 at 3:40 PM

Nope, the formatting works for me. Tested in CF10, build 10,0,13,287689.

Comment 29 by Ronan Campbell posted on 7/11/2014 at 4:24 PM

Thanks for the reply, it turns out we had the native cf10 poi, as well as poi3.8 in the lib, inherited from the transition from cf8 to c10, and it was causing issues, so scripts working great now, thanks again!

Comment 30 by dawesi posted on 7/27/2016 at 1:44 AM

For anyone who implemented this technique, from July 2016 msoffice will no longer open .xls files created as tables

http://www.infoworld.com/ar...