Twitter: raymondcamden

Address: Lafayette, LA, USA

Quick followup to my CFSpreadSheet Samples

07-11-2011 20,860 views ColdFusion 29 Comments

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:

view plain print about
1<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:

view plain print about
1<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:

view plain print about
1<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.

view plain print about
1<cfif not structKeyExists(form, "doit")>
3    <form action="test.cfm" method="post">
4        <table>
5            <tr>
6                <th>Name</th>
7                <th>Beers</th>
8                <th>Vegetables</th>
9                <th>Fruits</th>
10                <th>Meats</th>
11            </tr>
12        <cfloop index="x" from="1" to="10">
13            <cfoutput>
14            <tr>
15                <td><input type="text" name="name_#x#"></td>
16                <td><input type="text" name="beers_#x#"></td>
17                <td><input type="text" name="veggies_#x#"></td>    
18                <td><input type="text" name="fruits_#x#"></td>    
19                <td><input type="text" name="meats_#x#"></td>
20            </tr>
21            </cfoutput>
22        </cfloop>
23        </table>
24        <input type="submit" name="doit" value="Create Excel File">
25    </form>
29    <cfset q = queryNew("Name,Beers,Vegetables,Fruits,Meats", "cf_sql_varchar,cf_sql_integer,cf_sql_integer,cf_sql_integer,cf_sql_integer")>
30    <cfloop index="x" from="1" to="10">
31        <cfset queryAddRow(q)>
32        <cfset querySetCell(q, "Name", form["name_#x#"])>
33        <cfset querySetCell(q, "Beers", form["beers_#x#"])>
34        <cfset querySetCell(q, "Vegetables", form["veggies_#x#"])>
35        <cfset querySetCell(q, "Fruits", form["fruits_#x#"])>
36        <cfset querySetCell(q, "Meats", form["meats_#x#"])>
37    </cfloop>
39    <cfset filename = expandPath("./myexcel.xls")>
40    <!---
41    <cfspreadsheet action="write" query="q" filename="#filename#" overwrite="true">
42    --->

43    <!--- Make a spreadsheet object --->
44    <cfset s = spreadsheetNew()>
45    <!--- Add header row --->
46    <cfset spreadsheetAddRow(s, "Name,Beers,Vegetables,Fruits,Meats")>
47    <!--- format header --->    
48    <cfset spreadsheetFormatRow(s,
49            {
50                bold=true,
51                fgcolor="lemon_chiffon",
52                fontsize=14
53            },
54            1)

56    <!--- Add query --->
57    <cfset spreadsheetAddRows(s, q)>
59    <cfset spreadsheetWrite(s, filename, true)>
61    Your spreadsheet is ready. You may download it <a href="myexcel.xls">here</a>.

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

Related Blog Entries


These comments will soon be imported into Disqus. To add a comment, use Disqus above.
  • Commented on 07-11-2011 at 11:16 PM
    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.
  • Commented on 07-12-2011 at 5:26 AM

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

  • Commented on 07-12-2011 at 7:32 AM
    Yes you did, but in case others read this that are not familiar I thought I would mention it anyway.
  • Commented on 07-12-2011 at 7:42 AM
    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.
  • Ben B #
    Commented on 07-12-2011 at 4:14 PM
    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.
  • Commented on 07-12-2011 at 4:21 PM
    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. :)
  • Jason Fisher #
    Commented on 07-14-2011 at 10:15 AM
    @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.
  • Rick Hazen #
    Commented on 07-14-2011 at 11:46 AM
    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?
  • Commented on 07-24-2011 at 9:53 AM
    @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.)
  • Walt #
    Commented on 04-16-2012 at 1: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.
  • Commented on 04-16-2012 at 1:36 PM
    What about a value of ""?
  • Tom Van Schoor #
    Commented on 06-19-2012 at 1: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#">
  • Commented on 06-19-2012 at 8:42 AM
    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.
  • Commented on 06-20-2012 at 12:55 AM
    addRows is supposedly just as slow, but I haven't tested it personally. I will benchmark it and will post the results.

  • Commented on 07-19-2012 at 12: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. :)
  • Charles Higgins #
    Commented on 02-09-2013 at 5:39 AM
    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.
  • Misty #
    Commented on 04-04-2013 at 7:28 AM
    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
  • Commented on 04-08-2013 at 9:35 AM
    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.
  • Al Schwarz #
    Commented on 10-01-2013 at 8:57 AM
    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,

        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?
  • Commented on 10-01-2013 at 9:20 AM
    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.
  • Mike Rankin #
    Commented on 03-11-2014 at 12: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?
  • Commented on 03-11-2014 at 10:43 PM
    I haven't tried it. Do you have a CFM I can run here to test?
  • Commented on 03-14-2014 at 1: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. :)
  • Commented on 03-14-2014 at 1:37 PM
    Thanks for sharing David.
  • Brig #
    Commented on 05-21-2014 at 8:06 AM
    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!
  • Commented on 05-21-2014 at 2: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.
  • Ronan Campbell #
    Commented on 07-09-2014 at 3:47 AM
    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
  • Commented on 07-09-2014 at 6:40 AM
    Nope, the formatting works for me. Tested in CF10, build 10,0,13,287689.
  • Ronan Campbell #
    Commented on 07-11-2014 at 7:24 AM
    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!