Twitter: raymondcamden


Address: Lafayette, LA, USA

ColdFusion Sample - Create an Excel File

06-01-2011 36,403 views ColdFusion 102 Comments

In this ColdFusion sample, I'll demonstrate a simple way to take form input and generate an Excel spreadsheet file. This is a simple example with a very basic form, but hopefully it will give you enough to go by to create your own similar application.

Let's begin by creating a simple form. This form is going to ask for 10 rows of data that mimics the Excel sheet I uploaded in my previous blog entry. It will ask for a name and the number of beers, vegetables, fruits, and meats consumed.

view plain print about
1<form action="test2.cfm" method="post">
2    <table>
3        <tr>
4            <th>Name</th>
5            <th>Beers</th>
6            <th>Vegetables</th>
7            <th>Fruits</th>
8            <th>Meats</th>
9        </tr>
10    <cfloop index="x" from="1" to="10">
11        <cfoutput>
12        <tr>
13            <td><input type="text" name="name_#x#"></td>
14            <td><input type="text" name="beers_#x#"></td>
15            <td><input type="text" name="veggies_#x#"></td>    
16            <td><input type="text" name="fruits_#x#"></td>    
17            <td><input type="text" name="meats_#x#"></td>
18        </tr>
19        </cfoutput>
20    </cfloop>
21    </table>
22    <input type="submit" name="doit" value="Create Excel File">
23</form>

This form could be more dynamic of course. You could present 3 rows and use jQuery (or another less capable JavaScript framework) to easily add additional rows for input. But to keep things simple the form will just create 10 rows. Here's how it looks - and again - more work could be done to make this friendlier to the end user.

Ok - now to generate the spreadsheet. Let's look at an example.

view plain print about
1<cfset q = queryNew("Name,Beers,Vegetables,Fruits,Meats", "cf_sql_varchar,cf_sql_integer,cf_sql_integer,cf_sql_integer,cf_sql_integer")>
2<cfloop index="x" from="1" to="10">
3    <cfset queryAddRow(q)>
4    <cfset querySetCell(q, "Name", form["name_#x#"])>
5    <cfset querySetCell(q, "Beers", form["beers_#x#"])>
6    <cfset querySetCell(q, "Vegetables", form["veggies_#x#"])>
7    <cfset querySetCell(q, "Fruits", form["fruits_#x#"])>
8    <cfset querySetCell(q, "Meats", form["meats_#x#"])>
9</cfloop>
10
11<cfset filename = expandPath("./myexcel.xls")>
12<cfspreadsheet action="write" query="q" filename="#filename#" overwrite="true">

We begin by turning the form data into a query. This is done by looping from 1 to 10 and grabbing the relevant values from the Form struct. You could do a trim and other checks here as well. The real magic of this code block is the last line. All it takes to create a spreadsheet in ColdFusion is one line. You pass in the query, filename, and... that's it! While this works, the output is a bit plain. Let's now look at a slight modification.

view plain print about
1<!--- Make a spreadsheet object --->
2<cfset s = spreadsheetNew()>
3<!--- Add header row --->
4<cfset spreadsheetAddRow(s, "Name,Beers,Vegetables,Fruits,Meats")>
5<!--- format header --->    
6<cfset spreadsheetFormatRow(s,
7        {
8            bold=true,
9            fgcolor="lemon_chiffon",
10            fontsize=14
11        },
12        1)
>

13
14<!--- Add query --->
15<cfset spreadsheetAddRows(s, q)>
16<cfset spreadsheetWrite(s, filename, true)>

This version is a bit more complex. We begin creating a blank spreadsheet object. Next we add the header row. We can format both cells and rows, and in this example I've gone ahead and formatted the header. You have many options but I used just bold, fgcolor, and fontsize. (No one make any comments about lemon chiffon, it rocks.)

Next I add the data using spreadsheetAddRows and write out the data. Here's a quick example of the result.

Here's the entire code template.

view plain print about
1<cfif not structKeyExists(form, "doit")>
2    
3    <form action="test2.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>
26        
27<cfelse>
28    
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>
38    
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)
>

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

Your Homework!

In the code template above, I save the file and use HTML to link to it. Modify this template to instead serve the content to the user immediately. Also - use another ColdFusion function to specify a numeric formatting for the columns (all of the ones but name).

Related Blog Entries

102 Comments

  • Commented on 06-01-2011 at 9:40 PM
    I like that, good for a basic spreadsheet. Much nicer than using CFHEADER and CFCONTENT to convert an HTML table into one ugly spreadsheet.

    But these spreadsheet tags aren't going to be able to work with a real stylized spreadsheet, are they? We have to create the template in Excel then save it as Excel XML and then have our CFML output filled in XML that the user can open in Excel and have look all pretty. It becomes a real pain though if you have to tweak anything, you can real easily mess up the Excel XML.
  • Joshua Miller #
    Commented on 06-01-2011 at 9:57 PM
    I was working with the new spreadsheet functions for the first time today, so this article is quite timely for me! I really like the power and flexibility of CFSpreadsheet, but I found that there appear to be some limitations to the amount of data that it can handle. I generated a minimally styled Excel spreadsheet using a sample dataset of about 100 rows and everything worked fine. I then used the full dataset of 11,000+ rows and, despite the CFSetting RequestTimeout being set unbelievably high, the task simply never finished. I waited literally hours and nothing ever happened. The page spun and spun and no error was generated, but no Excel file was generated either. I tested a few times using smaller datasets and it always worked fine until I ran with the full set. Do you know if there is a physical limit to how much data the functions can handle? I was well below Excel's limit, but obviously above ColdFusion's.

    I'm running a 64-bit install and have 12GB of memory allocated to CF. I don't think that memory is an issue - could there be something else?
  • Commented on 06-02-2011 at 9:26 AM
    @Rob: It depends on what you mean by "real stylized" ;) I'd check the docs to see what their formatting/etc options are. It seems pretty complex but I've just scratched the surface.

    @Joshua: I'm going to try to replicate that test later today. Do you really want one sheet with 11K rows though? What about multiple sheets?
  • Joshua Miller #
    Commented on 06-02-2011 at 9:42 AM
    @Ray: Granted, it's not ideal, but it's the business requirement I was given.

    As a follow-up to my post, I did receive an error message about 10 hours after kicking off the task:

    An exception occurred while calling the function formatColumn.
    An exception occurred while calling the function formatColumn. java.lang.ClassCastException: org.apache.poi.hssf.record.WindowOneRecord cannot be cast to org.apache.poi.hssf.record.ExtendedFormatRecord
    The error occurred on line 248.

    This is line 248: <cfset SpreadsheetFormatColumn(sheet,fCenter,5)>

    fCenter is a structure:

    fCenter = StructNew();
    fCtrNum.dataformat = "@";
    fCenter.alignment = "center";

    Probably has something to do with the datatype represented and the "@" formatting, although the bigger issue is the 10 hour time lapse between execution and error reporting.
  • Commented on 06-02-2011 at 9:44 AM
    You know, 11k really doesn't seem like a lot. How many columns were you using?
  • Scott Busche #
    Commented on 06-02-2011 at 9:55 AM
    I've ran into similar issues as Josh, except the page would finish, but never output the excel file. It would then never write an excel file until I restarted CF. I haven't had the problem occur since they released 9.0.1 though.

    @Rob you can read in a 'stylized' xlsx file into CF, but if you write to existing cells the formatting will be overwritten for that cell. In that case you're better off using the apache poi directly instead of the CF tags.
  • Michael Oliver #
    Commented on 06-02-2011 at 10:18 AM
    @Joshua: In regards to the speed, when you run the 11,000+ record attempt, have you watched the memory usage of jrun?

    A coworker had a similar problem when working with the apache POI in Java. He explained that the issue was that the JVM has to store the entire document in memory while it's building the file. As the file takes up more of the JVM's memory, everything will slow to a crawl. The more you do with the cells in memory (formatting, functions, etc), the more resources it pulls from the JVM.

    This behavior is different from when you write to a text file (to create a CSV), because the JVM writes the line to the file, but doesn't keep the file in memory.

    For us, the end result was to switch to CSV files exclusively when working with potentially large recordsets, and only writing to XLS when working with small sets of data. The writing to XLS just doesn't seem to scale well. We never tried using the built in functions though, but since it also uses the POI, I would think it would have the same problems.

    I have been wondering though if writing to XLSX would be any different since supposedly the file format is significantly different (I haven't tried though). Looking at the livedocs for spreadsheetNew(), it looks like you can tell it to do that using the "xmlFormat" parameter. If that's enabled, does the performance improve for you on the 11,000+ file?
  • James Moberg #
    Commented on 06-02-2011 at 11:23 AM
    Does "dataformat" work at all?
    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef...

    I converted one of the columns to a date datatype, but it is incorrectly outputted as a text string in the Excel file and doesn't retain the specified formatting. (If I click in the cell, Excel automagically converts it to a date datatype.)

    Here's the function I'm using:
    <cfset SpreadsheetFormatColumns(s, {dataformat="m/d/yy h:mm"}, 2)>
    I've done this before & after calling spreadsheetAddRows() and the results are the same. I've also removed the header row to no avail.

    Is this a bug, am I doing something wrong or do I just not properly understand how this should work? What's the recommended way to output formatted date values to Excel using CFSpreadsheet? (This seems trivial, but I haven't been able to figure it out.) Thanks.
  • Commented on 06-02-2011 at 12:02 PM
    Is this for does 9.0.1? and does it handle large sheets better than 9.0. Anything over a few hundred lines would take forever to run for me so I switched to Ben's POI code. I haven't had any problems with several thousands of lines using it. Coldfusion 9.0 not so much.
  • Commented on 06-02-2011 at 12:10 PM
    @rob Doing the HTML trick for EXCEL supports style sheets and formulas. The only downside really is that it's not "really" an excel file and I never figured out a way to do multiple sheets. Microsoft EXCEL works great with it, but Open Office and Google Docs doesn't like it :).
  • Joshua Miller #
    Commented on 06-02-2011 at 12:21 PM
    @Ray: There are a total of 7 columns - so I wouldn't really consider it a significant amount of data for Excel to handle.

    @Michael: Thanks for the note. My original version of this used no formatting whatsoever and in that case generating 11,000 rows took just a few seconds. It wasn't until I added the SpreadsheetX() functions to do custom formatting that things started slowing down. Since I need custom formatting the CSV option is a no-go.

    @James: I'm starting to wonder the same thing myself. None of the formatting that I've tried appears to have any effect whatsoever. I was attempting to protect leading zeros in a column, but no combination of formatting statements - even with only 20 records in the output - resulted in any change in display.
  • Ryan Harper #
    Commented on 06-03-2011 at 2:34 PM
    I too have had mixed success with the spreadsheet tags/functions. For a simple spreadsheet it works great.

    Dumping a query out to excel with thousands of rows also worked just fine (as long as you're ok with the column headers coming straight from the db).

    BUT opening or trying to format an excel file that has thousands of lines caused problems.

    Also trying to add 1000's of rows with any of the spreadsheet functions would also cause it to hang.
  • Commented on 06-04-2011 at 2:02 PM
    So I was going to do a demo of a huge spreadsheet, but it seems as if the agreement is that the issue is with custom formatting. That seems odd to me. If I were to apply a format to a column, I'd assume it wouldn't matter if I had 10 or a million rows.

    Is this something you've reported Joshua?
  • Commented on 06-04-2011 at 2:22 PM
    "Dumping a query out to excel with thousands of rows also worked just fine (as long as you're ok with the column headers coming straight from the db). "

    In my example, I did the headers manually. You don't have to use them from the query.
  • Joshua Miller #
    Commented on 06-06-2011 at 7:52 AM
    @Ray - I haven't reported as a bug yet. I've not worked with CFSpreadsheet much and before reporting this thought I would see if maybe I was doing something wrong.

    As a side note to the column names, you can also give them custom names in your query and then get the benefit of "pretty names" without having to specify them in the CFSpreadsheet tag:

    SELECT [foo] AS [My Pretty Name]
    FROM [dbo].[tblFoo]

    CFSpreadsheet seems to handle the column names properly when rendered. If all you want is a simple query and dump to Excel it works really well.
  • Ryan Harper #
    Commented on 06-06-2011 at 9:58 AM
    I should have clarified my comment in saying that the column headers come straight from the query. That's what I've been doing lately also.

    The only other problem that I've had with dealing with cfspreadsheet is when working with date/time fields. Opening a csv file into excel works better for interpreting the cells correctly as dates.
  • James Moberg #
    Commented on 06-06-2011 at 10:13 AM
    Here's a quick test that I wrote that allows you to format dates and download the Excel spreadsheet.
    http://pastebin.com/aQnembR3

    It doesn't appear as though spreadsheetFormatColumn() works when combined with spreadsheetAddRows(). The only time I've been able to successfully format a date cell is when the cell is manually populated using spreadsheetSetCellValue().
  • Joe #
    Commented on 06-10-2011 at 3:25 PM
    I've found a few things relating to cfspreadsheet and ColdFusion 9.0 vs ColdFusion 9.0.1.
    In CF9 formatting more than just a few rows and columns would result in an "...maximum number of fonts was exceeded" error. The solution was to install CF 9.0.1. If you are still getting the error try to change any loops which format by cell (spreadSheetFormatCell) to the SpreadsheetFormatColumns or SpreadsheetFormatRows (plural). From what I've read Excel stores formatting information for whole columns or for cells and nothing in between. Furthermore, try to use the 9.0.1 function SpreadsheetFormatCellRange which is also less memory intensive for Excel. See this thread for more detail: http://forums.adobe.com/message/3450807
  • James Moberg #
    Commented on 06-10-2011 at 3:29 PM
    Please test the following code on CF 9/9.01 and post regarding whether or not anyone is able to successfully format Excel dates using spreadsheetAddRows() and spreadsheetFormatColumn().
    http://pastebin.com/aQnembR3
  • Joe #
    Commented on 06-10-2011 at 3:54 PM
    Just from taking a quick glance at the code, I'm not seeing a spreadsheetWrite command anywhere, perhaps that method might be able to format correctly?
  • James Moberg #
    Commented on 06-10-2011 at 4:38 PM
    I've used spreadsheetWrite() and the result is the same. I'm using spreadsheetReadBinary() so that I can return the spreadsheet directly in the browser without having to resort to a file write, read and/or redirect function.

    Please change it to spreadsheetWrite() and let me know if the behavior is any different. I'm guessing that the results would be the same as that's what I was doing before I rewrote this to use an immediate download method.
  • Commented on 06-13-2011 at 11:05 AM
    In a small stroke of irony, the day after posting my comments on this I found a chance to use CFSPREADSHEET. A legacy application was timing out after 5 minutes creating a 1500 row HTML table and streaming it out as an Excel file using CFHEADER and CFCONTENT. It was a basic data dump so I gave CFSPREADSHEET a try. After a runtime of six seconds a native Excel file was created, something Excel doesn't have to parse and convert like it would an HTML table. Very happy with that.
  • Les Konley #
    Commented on 06-17-2011 at 1:36 PM
    I'm struggling with the issue that using CFSPREADSHEET to generate an Excel (.xls) file does not format date columns as DATES. I was hoping that using the new CFSPREADSHEET tag would eliminate our need to install MS Office on the production servers and retire the use of the CFX_EXCEL tag. Since we're only on CF 9.0, we can't use the AddSpreadsheetRow functions because it's too slow for pruduction environments. No matter how I format the date fields using QuerySetCell to reformat my query object before passing it to the CFSPREADSHEET tag, Excel just doesn't recognize dates AS DATES. What a bummer. I don't see any evidence that CF9.0.1 addresses this issue, except that I might then be able to create a spreadsheet object without performance issues and do formatting that way.
  • Commented on 06-18-2011 at 3:49 PM
    Dumb question - if your data is query based, can you ensure the column is a date type? And if not - maybe try a query object instead.
  • James Moberg #
    Commented on 06-18-2011 at 9:53 PM
    Ray... Been there, done that. Nothing works. Please research this yourself and see if you are successful. In CF9, you can't appear to format values (even date values) as dates in Excel unless you format each cell individually. Here's some proof on concept code where no SpreadsheetFormatColumn() functions seem to work.
    http://pastebin.com/aQnembR3

    If this hasn't been officially reported as a bug yet, please do so. It's kinda of a show stopper for me for using CFSpreadsheet to create database updates. (Ben Nadel's POI Utility CFC seems to work fine even though it's slower at times.)

    I always hope that it's me that's doing something wrong... since I've upgraded to CF9, it hasn't been me as often. :(
  • Commented on 06-19-2011 at 9:21 AM
    I can confirm it's not working. Just to be sure, I added a color key to your dfStyle structure and it worked ok. Best I can recommend is to ensure you've got it logged as a bug.
  • James Moberg #
    Commented on 06-20-2011 at 9:30 AM
    What is the code that you used and what is a color "key"? Did passing a "color" fix the date format and add the time or did it just pass the correct color? (I can live with the column being a different color if it is passing/formatting the correct data.) Thanks.
  • Commented on 06-20-2011 at 9:33 AM
    Sorry I wasn't clear. What I meant was - I modified your struct to ALSO include a color change. I verified the color DID change, but the date formatting did NOT work. This implies you were doing the right thing, but it didnt work. Hence me saying you should file a bug report. Your CFM is perfect too so be sure to include the link or the code itself.
  • James Moberg #
    Commented on 06-20-2011 at 10:49 AM
    ok. I wanted to make sure that I was reporting the correct issue & didn't need to add that date formats were correctly formatted only when additional color attributes were added. I've reported it. Thanks.
  • Les Konley #
    Commented on 06-20-2011 at 11:12 AM
    Ray & James, I have additional information about the CFSPREADSHEET and Excel not recognizing resulting date fields. First of all, our code is both using a query object as well as setting explicit date formats [i.e, cfset temp = QuerySetCell(GetExcelReport, "FollowUpDate", "DateFormat(GetResult.FollowUpDate, "MM/DD/YYYY")) ]. When we open the spreadsheet in Excel (built using CFSPREADSHEET), the date column is formatted as regular text or general (left-justified). On this site (http://www.appnitro.com/forums/topic/excel-does-no...) a work-around is offered, so the business users can highlight the column (within Excel), then select Data > Text to Columns, then click FINISH on the Wizard (without needing to step through it)... and date fields are now recognized by Excel as a DATE. We may need to revert back to installing MS Office on the production servers and continue using the old CFX_Excel tag if the business users decide they don't want this added minor inconvenience, at least until that particular bug is corrected on a future release. Thanks for submitting a bug report to Adobe about this issue.
  • Joshua Miller #
    Commented on 06-27-2011 at 2:13 PM
    Update on this discussion. I've installed the CF 9.0.1 update, removed all references to SpreadsheetFormatX functions and I still can't get more than ~400 rows before CF dies. I'm using grouped output and using SpreadsheetAddRow() for each row of data because I need a custom formatted "header" row before each section in the file.

    I know, I know, I could make each "section" it's own sheet or change the formatting of the Excel doc to make it work with CF, but the formatting is a business requirement and I'd rather CF just work that me have to work around it.

    It seems unlikely that someone didn't run into this when testing the new features in CF9. How is it that I can't even get 500 rows of data out without the server dying? I've got 12GB of RAM, all but 2 of which are dedicated to CF (64 bit) and on two different servers I see the same behavior.

    Using SpreadsheetAddRow() with 7 columns and 500 rows of data with no formatting would presumably not be outside of the testing parameters of the new functions.

    Has anyone else used group output with SpreadsheetAddRow()? Did it work for you?
  • James Moberg #
    Commented on 06-27-2011 at 8:08 PM
    I wrote a quick test script and it worked with 500 rows and 7 columns (using Windows 2003 with only 4Gb total RAM.)
    http://pastebin.com/VmF2r7Rz

    I added getTickCount() as one of the column values and generation does appear to start to slow down after about 150 rows. Here are the incremental averages to generate 500 rows using SpreadsheetAddRow():
    Rows 1-100 12.34ms per row. Total = 1,234ms
    Rows 101-200 37.66ms per row. Total = 3,766ms
    Rows 201-300 60ms per row. Total = 6,000ms
    Rows 301-400 83.9ms per row. Total = 8,390ms
    Rows 401-500 107.51ms per row. Total = 10,751ms

    It looks like CF9 increases processing by 2.5 seconds for each increment of 100 rows. I attempted to export 2,000 rows and it took 477,890 milleseconds. The final rows took 485 milleseconds each to individually add. The overall average is 239ms per row. Ouch!

    Maybe a better technique would be to format your rows and save them to a query object and then use SpreadsheetAddRows() (with the "s")... but you can then probably kiss your data format & styles good-bye. :(
  • Joshua Miller #
    Commented on 06-28-2011 at 10:13 AM
    FWIW I went back and rewrote the routine using Java POI and the entire 12,245 row file takes 742ms to generate with formatting and proper data types intact.
  • Commented on 06-28-2011 at 10:14 AM
    Bit behind on all this - but guys - don't forget to record/note issues with the public bug tracker. Adobe needs to be aware in order to reply.
  • James Moberg #
    Commented on 06-28-2011 at 4:21 PM
    In the process of reporting it, I discovered that it was reported back in December 2009 (bug #81154). I added a comment with the code I posted at PasteBin & voted it up.
    http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbu...

    Any idea if Adobe looks at CFbugs.Adobe.com? There's no status on this bug other than "verified" and I believe that this may have been provided by the submitter.
  • Commented on 06-28-2011 at 4:22 PM
    Can't speak for the CF team but as far as I know, yes, it IS taken seriously.
  • James Moberg #
    Commented on 06-28-2011 at 4:24 PM
    Wait a minute... I just realized that Adobe has the status on bug #81154 as "closed". I don't want to submit a duplicate bug. Any idea on how to get them to re-open a bug that hasn't been fixed? (Any idea regarding how to identify who prematurely marked this as "closed"?)
  • Commented on 06-28-2011 at 4:31 PM
    Hmm. If I were in a bad mood, I'd post a comment AND post a new bug. Just to make it a bit more... vocal.
  • Dan Murphy #
    Commented on 07-22-2011 at 10:44 AM
    James, Ray, and Les - I know I'm a bit late to the game here, but I think I just found a workaround for the date formats in Excel that has been working out well for me. First, I'm creating my own query with querynew based on another query and using that to export to Excel. If I make the datatype in the querynew to cfsqlvarchar instead of date or cfsqltimestamp and then output the query to the Excel spreadsheet, it will take whatever format you give it. If you stop there, Excel will still see the data as just text, not an actual date (although formatted correctly). Second, just set the dataformat using spreadsheetFormatColumns to match the format you specified in the query, and Excel now recognizes it all as a date. Ray, I'm using your DateTimeFormat function from CFLib, so here is how it all worked out for me. (this is just part of it all obviously, but you'll catch the idea).

    Here's the query...
    <cfset mys = QueryNew("OrderNumber,InScan,OutScan","cfsqlvarchar,cfsqlvarchar,cfsqlvarchar")>
    <cfloop query="qryThroughputScans">
    <cfset queryAddRow(mys)>
    <cfset querySetCell(mys,"OrderNumber",NumberFormat(qryThroughputScans.OrderNumber,000000))>
    <cfset querySetCell(mys,"InScan",DateTimeFormat(qryThroughputScans.InScan))>
    <cfset querySetCell(mys,"OutScan",DateTimeFormat(qryThroughputScans.OutScan))>
    </cfloop>

    And then the column format that I use after creating the Excel spreadsheet...
    <cfset spreadsheetFormatColumns(s,
    {
    alignment="center",
    dataformat="m/d/yyyy h:mm AM/PM"
    },
    "2-3")>

    I tested this out with all sorts of weird formats just to make sure it works, and I had great success with it. Maybe this is already well known but I couldn't find anything when I was looking for a fix. Hopefully this helps someone out when they go looking for an answer to the date issue.

    Thanks,
    Dan
  • Commented on 07-23-2011 at 5:16 PM
    Thanks for sharing this, Dan.
  • James Moberg #
    Commented on 07-24-2011 at 9:41 AM
    "m/d/yyyy h:mm AM/PM" isn't one of the allowable formats listed here:
    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef...

    Are you able to generate any custom format you desire? If so, is there any documentation anywhere? (Adobe doesn't provide any direction regarding this on their website.)

    Thanks.
  • Commented on 07-24-2011 at 9:46 AM
    Well, the docs do list supported formats. And his looks real close to what's listed. The time portion is definitely there. So maybe it's just implied that you can mix the date/time ones together like that. Either way - one of you guys should mention this format in a comment to the Livedoc edition.
  • Dan Murphy #
    Commented on 07-26-2011 at 9:38 AM
    So I'm not sure if this is a matter of allowable formats, or just a way to create whatever custom format you want. I tried this with several formats that were not defined on the docs (ie - "m/yyyy h AM/PM") and they all came in as dates to the Excel spreadsheet if I followed the same process I outlined previously. I'm not aware of any documentation on it - this was just me goofing around to try to get the format I wanted.

    I guess I'll just put something similar to my previous comment on this post in the comments of the docs as Ray suggested.
  • Commented on 07-29-2011 at 12:05 AM
    hey ray, have you had any luck adding a second sheet to an excel doc created by cfspreadsheet? im getting a null pointer exception on the update call?

    thanks
    tony
  • Commented on 07-29-2011 at 6:26 AM
    Haven't tried it. Create a simple CFM that replicates the issue and fire it off to me. I can try hacking it on it.
  • Jonathan #
    Commented on 11-27-2011 at 11:38 AM
    Anyone know how to make the spreadsheet recalculate?
  • Commented on 11-27-2011 at 1:41 PM
    What do you mean exactly?
  • Commented on 02-17-2012 at 4:12 PM
    in CF8; I need to export an excel document from a HUGE data set. The code is using a cfcontent variable to write the file to a folder.

    I keep getting a timeout 500 jRun servlet 500 error. I am thinking about appending 500-1000 records on an automated page refresh. Is there a better way?
  • Commented on 02-18-2012 at 11:05 AM
    You may not be able to use CF for this (or CF's cfspreadsheet tag). You may want to look at using POI (the underlying Java library) directly instead. I believe Ben Nadel has a wrapper library for it. You may also want to consider NOT sending the file to user. Instead, try using cfspreadsheet to create the file, save it, and then provide a link to it. That removes the 'stream' to the user where things may time out.
  • misty #
    Commented on 03-05-2012 at 3:29 PM
    Hi ray, I tried using an ajax request to the page and using the cfsavecontent variable to create the excel, but i am unable to either store the file as excel on the disk, am i missing something
  • Commented on 03-05-2012 at 10:59 PM
    You can't use Ajax to render binary data like that.
  • misty #
    Commented on 03-05-2012 at 11:39 PM
    btw, the things are happening on the server page, i am just getting a response from the page in the form of an alert, should i try with iframes or something
  • Commented on 03-05-2012 at 11:41 PM
    Not quite sure what you are asking then. Maybe back up and explain again?
  • Misty #
    Commented on 03-06-2012 at 6:43 AM
    As your previous commnet, u are saying i cannot render the cfsavecontent tag to generate an excel for me, but is there any way like using an iframe in the server page i mean where i am writing my cfsavecontent to generate the excel for me and save it to the disk and show the message to the user. The thing is i am using a serialize() of jquery to send form values to the submission page and using cfsavecontent to create a table and write that values in the excel file

    is this a possiblity
  • Commented on 03-06-2012 at 7:37 AM
    Ah, so you want to use an Ajax request that fires off a server side script to generate Excel, and then simply have that downloaded to the user, right?
  • Misty #
    Commented on 03-06-2012 at 10:32 AM
    Yes, it should just create an excel with cfsavecontent variable and store on the disk and show a message Thanks message to the user
  • Commented on 03-06-2012 at 11:26 AM
    If you use a simple document.location=X, where X is a CFM that uses cfcontent to serve up binary data, you get the same rule - a download.
  • Misty #
    Commented on 03-06-2012 at 3:13 PM
    Um, Getting little confused, if you tell me properly that will be gr8
  • Commented on 03-06-2012 at 5:37 PM
    Well, first off, are you aware of the technique of using cfcontent to serve up binary files to the user? (Don't think about Ajax, let's keep it simple first.)
  • Misty #
    Commented on 03-06-2012 at 11:52 PM
    yes, here is the code, i am using

    <cfsavecontent variable="a">

    </cfsavecontent>
    <cfset theDir = GetDirectoryFromPath(GetCurrentTemplatePath())>
    <cfcontent type="application/vnd.msexcel" variable="#ToBinary(ToBase64(a.Trim()))#" file="#theDir#/#Dateformat(now(),'ddmmyyyy')##TimeFormat(now(),'hhmmsstt')#.xls">
  • Commented on 03-07-2012 at 12:48 PM
    Ok - so you understand how that works, right? Like if that file was ray.cfm, if you link to it, when the user clicks the link, they get prompted to download. To them, it looks like they didn't leave the page.

    So to do the same in JavaScript, you would use document.location.href = 'ray.cfm';
  • Misty #
    Commented on 03-08-2012 at 1:21 AM
    Ok, i think thigs are going over the head now, you said use document.location.href:

    i am using like this,

    var str = $("form").serialize();
             $.ajax({
                type:"POST",
                url:"submit.cfm",
                data:str
             });

    where i use the document.location.href

    i do not want the user to get page reloaded and then show them a message
  • Commented on 03-08-2012 at 7:04 AM
    Hmm. If that isn't working - then I'll need to look at this later. I'm traveling today.
  • Commented on 03-08-2012 at 9:04 AM
    Ok, let's imagine test.cfm, a page that accepts dynamic input to generate a pdf.

    http://pastebin.com/QZ1LB7im

    Now let's look at front end code to hit this:

    http://pastebin.com/yrzk2DFx

    Notice I use document.location.href to "Push" the user to the CFM. What they experience though is simply a download.
  • Misty #
    Commented on 03-08-2012 at 2:48 PM
    Hi Ra, Made it work, No need to use the document.location: here is my version

    http://pastebin.com/vvqVhsju
  • Misty #
    Commented on 03-08-2012 at 2:49 PM
    oops! sorry missed (Y) from ur name, Apologies

    :)
  • Commented on 03-08-2012 at 7:36 PM
    So wait - that worked for you? Your success.cfm is exactly like my CFM? (Well, not exactly, but same technique of generating binary data and sending via cfcontent.)
  • Albert Aynbinder #
    Commented on 05-10-2012 at 10:32 AM
    Hi, Ray,
    Thanks for your example how to use <cfspreadsheet>
    Finally I’ve build and save excel file.
    But I have problem with headers, I have to use variables Like Current Year and Prior as a column name.
    Do you have any idea how it solve.
    Thanks for Help
  • Commented on 05-10-2012 at 11:07 AM
    If you wanted dynamic headers, you would change how you make the query. In mine, it is hard coded to Name, Beers, etc. Just make it dynamic.
  • Albert Aynbinder #
    Commented on 05-10-2012 at 11:37 AM
    Raymond,
    Is it posible to say:
    <cfset q = queryNew("RUN,DATE, TY#dataQueryR1.TAXYR#,CURRENTTYPOSTED,TY2009TOTAL,TY2010TOTAL,PR2009,PR2010,DIFFERENCE",
    "cf
    sqlvarchar,
    cf
    sqlvarchar,
    cf
    sqlvarchar,
    cf
    sqlinteger,
    cf
    sqlinteger,
    cf
    sqlinteger,
    cf
    sqldecimal,
    cf
    sqldecimal,
    cf
    sql_varchar" )>
  • Commented on 05-11-2012 at 6:12 AM
    Sure.
  • Monique #
    Commented on 08-23-2012 at 3:10 PM
    Very nice. Is it possible to have the file open up directly vs the user clicking on a link to open it?
  • Commented on 08-23-2012 at 3:39 PM
    Two ways. You could just cflocation to the XLS file you made, or, don't save to the file system at all. Just use cfcontent to serve up the binary data.
  • Commented on 09-04-2012 at 3:39 PM
    How can I generate a spreadsheet with:

    a. Colheads on Row 2?

    b. Filtering already ON (when downloaded, opened)?

    c. Print set to "Fit to Page"?

    Thanks?
  • Commented on 09-04-2012 at 4:29 PM
    I don't think you can do the colheads on row 2. Not as far as I know.
  • James Moberg #
    Commented on 09-04-2012 at 4:39 PM
    You may have to start with pre-existing empty Excel template w/Row 2 headers, filtering already enabled and print already set to "fit to page" and then use SpreadsheetAddRows() to populate the rest of it.
  • Commented on 12-03-2012 at 10:26 AM
    I see this post goes a bit way back but I've been working with a cfspreadsheet app for a classified company in which we're uploading allot of data via cfcfspreadsheet. My first iterations of the app dealt with loading the data and then looping through it as I've seen others talk about in this thread. I'm working here with a very smart guy who is literally a sql genious... He introduced me to a sql statement that is an "insert into tablename select statement". In this situation the coldfusion server makes one single call to the database to insert all the data without running separate loops. We've inserted thousands of rows in less than a second or two and then redirected to a review page all taking less than a few seconds... It's pretty amazing to see the performance gains!

    <br><br>
    Actually for more detail, we go to a global temporary table first with the data, and then move it to the actual destination table. While coming from the GTT so we can do the joins we need for the business right when we actually do the insert. To make all this happen, the SQL must be perfect and the load and destination field order has to be perfectly in sync to work. But when you do, the performance is off the charts! So I hope this tid bit helps someone else here...<br><br>

    The problem I'm trying to figure out is that some of our users are trying to upload encrypted spreadsheets and I need to be able to either detect this or ask for a password or somehow intelligently account for all of this.<br><br>

    Any suggestions?
  • Commented on 12-03-2012 at 3:54 PM
    As far as I can tell, we do not support opening password protected sheets. We support adding them to a sheet. I'd file an ER for it.

    You could handle it at least by wrapping your read with a try/catch.
  • Commented on 04-11-2013 at 2:23 PM
    For anyone looking for an answer to the SpreadsheetAddRows performance on medium to larger queries I thought I would add this:

    dont use SpreadsheetAddRows or any of those related functions like SpreadsheetFormatRows, the performance hit on the JVM is staggering

    My solution to this was to execute the query, create an xls file, use the tag cfspreadsheet to write to the newly created xls file, then serve to the browser, deleting after serving

    Using SpreadsheetAddRows, Runtime went from crashing server on 1000+ rows, 5+mins on 700 rows

    Using the method outlined above 1-1.5 secs
  • Commented on 05-31-2013 at 11:37 AM
    Has any one completed the Homework - Modify this template to instead serve the content to the user immediately. I can't seem to get it to render without saving it to my server first. Thanks
  • Commented on 05-31-2013 at 12:05 PM
    Nevermind... I found it under Quick followup to my CFSpreadSheet Samples
    Just what I was looking for!!!!
  • Russ #
    Commented on 06-06-2013 at 1:47 AM
    Jay,

    Does your solution allow formatting of individual cells? That's the reason I need to build the spreadsheet a cell at a time.

    Maybe construct the spreadsheet via Query, then go back through it and do the formatting in a 2nd pass?

    Thanks,
    Russ
  • Commented on 07-16-2013 at 9:18 AM
    Thank you for your random act of kindness in posting this sample code. It worked the first try and made it possible to complete my task of converting a table we have to excel simply using your sample and the example.

    Russ #, check the previous comments, there were suggestions of how to use other data types. If every cell in every row in your spreadsheet was to be of different types, then you would have to craft the code to be manual rows instead of a loop.
  • Faisal #
    Commented on 07-23-2013 at 10:41 AM
    Hi Ray, Thanks for the article. It was really helpful. Btw, at this moment I am stuck at one point. Actually was trying to get account numbers by a query and displaying those account numbers as column header after exporting into excel.

    The following code is working fine but displaying wrongly:

    <cfset variables.setHeader = "">
    <cfoutput query="myLoop">
    <cfset variables.setHeader = variables.setHeader & ", C#myLoop.acc#>
    </cfoutput>

    The above code is displaying column header (just one sample data I am showing here) but it is showing as 'C
    000391'. Which is wrong! For display, 'C' should not be with account number, it is the account number which should create the column header alone. However, as soon as I am taking off 'C' from infront of #myLoop.acc# it is giving the following error:

    The column name 000391 is invalid. If possible, please let me know what will be the best solution(s) to resolve this toughest problem on the earth :)

    Thanks always Sir
  • Commented on 07-23-2013 at 1:31 PM
    I'm afraid I do not have enough information here. What exactly is setHeader and how exactly are you using it?
  • Russ LaValle #
    Commented on 07-23-2013 at 2:54 PM
    I don't see a closing double-quote in the second cfset line in Faisal's code snippet...
  • Commented on 08-27-2013 at 10:12 AM
    I know this is an old thread but wanted to weigh in a solution I have come across for the date format issue.

    Ran into this issue and was hard to duplicate until I upgraded to 2013 excel and was able to duplicate. My first attempt was a success at upgrading the output for spreadsheetNew to output XLSX using the xmlFormat attribute. Bam, dates are properly formatted!

    <cfset sheet = SpreadSheetNew('CustomerData', "TRUE")

    <cfheader name="Content-Disposition" value="attachment; filename=CustomerData.xlsx">
    <cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#spreadSheetReadBinary(sheet)#">

    Any thoughts?
  • James Moberg #
    Commented on 08-27-2013 at 10:35 AM
    According to the ColdFusion 9 documentation, SpreadsheetNew() accepts a second optional boolean value (but the docs display it passed as a text string) and this doesn't seem to have any effect.

    I've posted sample code here. Adding XLSX support for Microsoft Office Excel 2007 and changing the cfcontent type didn't generate the desired date formatting I requested:
    http://pastebin.com/aQnembR3

    What datetime format are you using? According to Adobe, "m/d/yy h:mm" is a valid date format, but it returns date-only values (without the time) when using ColdFusion 9 or 10.
  • Commented on 08-27-2013 at 12:14 PM
    James, I am using the "m/d/yy" dataformat.

    I now understand your issue as times do not display for me either, I had a similar issue where my dates would display at 5 random numbers in excel.

    Using your test I still get a custom dataformat in Excel with the following in all rows: 8/27/2013 0:00
  • Laurie #
    Commented on 02-25-2014 at 4:49 PM
    Thanks for this code!
  • ion #
    Commented on 04-01-2014 at 9:19 AM
    i'm trying to avoid making an extra trip to the server just to get a spreadsheet. I mean, i already have the data and display it soon as the page loads. If user wants to save it as excel, i don't want to reload the page and rerun the query, but rather use the original data, saved in a form field. is there a way to translate that cfsavecontent mechanism into a javascript function? Generating the html table would be pretty straightforward, but how do i pop the last part       
    <cfheader name="Content-Disposition"
          value="attachment; filename=machineList.xls"   />
          
    <cfcontent type="application/msexcel"
          variable="#ToBinary( ToBase64( myVar) )#" />

    thanks
  • Commented on 04-01-2014 at 1:18 PM
    Modern browsers can create files on the client side and even stream them to download, but you would need to handle creating XLS-compatible binary data client-side, which is not something I think is possible. In theory you could make a CSV file, and I think most computers w/ Excel installed will open a CSV in Excel.

    But again - the "create a file you can download" is very much limited to modern browsers.
  • Commented on 04-03-2014 at 5:44 AM
    I created a demo of dynamically generating CSV and downloading it. I'll be blogging it today.
  • Commented on 04-18-2014 at 9:55 AM
    Hi ,

    We have migrated from Microsoft Office to Open Office. My current logic of writing to an excel sheet in MS Office is not working in Open Office . When i try to open the .xls file in the word pad, i am getting lot of blank spaces before the html content. once i have removed the blank spaces, i could be able to open the .xls file using Open Office. May i know what can be down to fix this issue ?
  • Commented on 04-18-2014 at 11:01 AM
    I'm sorry, but I don't quite understand what you are saying. In theory, the binary generated by cfspreadsheet should work in OpenOffice as well.
  • Commented on 04-18-2014 at 1:11 PM
    <cfsavecontent variable="a">

    </cfsavecontent>
    <cfcontent type="application/vnd.ms-excel" reset="no" variable="#ToBinary(ToBase64(a.Trim()))#">

    I have used the above logic from you blog discussion and the problem has been resolved now.

    HTML content had lot of blank spaces which i have trimmed off using the above logic from your blog.

    Thank You very much for you blog.
  • Commented on 04-18-2014 at 1:13 PM
    I did not use cfspreadsheet , but i have used the following logic

    <cfcontent type="application/vnd.ms-excel">

    <cfsavecontent variable="a">
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <html>
    <head>
    .......................
    ..........................
    ......................
    </head>
    .............
    ....................
    </body></html>
    </cfsavecontent>
    <cfcontent type="application/vnd.ms-excel" reset="no" variable="#ToBinary(ToBase64(a.Trim()))#">
  • Commented on 04-18-2014 at 4:24 PM
    Um, well, this blog post is about cfspreadsheet. If your question is not related to the topic, I'd ask you please post it to Stack Overflow.
  • Niva Le #
    Commented on 05-09-2014 at 7:42 PM
    I used the following code to create multiple sheets, but I can't get the getPrintSetup() function to work on these sheet.
    <cfset i=1>
    <CFLOOP query="getproducttypeid">
    <cfset sObj = "sObj"&"#i#">
    <cfset sObj=SpreadsheetNew("#getproducttypeid.description#")>
    <cfset poiSheet = sObj.getWorkBook().getSheet("#getproducttypeid.description#")>
    <cfset ps = poiSheet.getPrintSetup()>
    <cfset ps.setLandscape(true)>
    <cfset poiSheet.setAutobreaks(true)>
    <!--- fit to one page --->
    <cfset ps.setFitWidth(1)>
    <cfspreadsheet action="update" name="sObj" filename="#thefile#">
    <cfset i = i+1>
    </cfloop>
    Do you know why? Am I missing something here?
  • Commented on 07-09-2014 at 4:53 AM
    Hi Raymond,

    Firstly, thank you for this tutorial on the use of spreadsheetNew(). We have been able to implement this to provide us with an excel export in very few lines of code, unlike some of what we experienced with poiutility approaches.

    We have run into one issue with spreadsheetFormatRow. Is there a known issue in Coldfusion 10 for this? I have attempted to take an exact replica of what you have done in this tutorial above and run it upon Apache and ColdFusion 10,286680 with Tomcat 7.0.23.0.

    We are running into an issue however where the process fails at spreadsheetFormatRow with an issue here 500 org.apache.poi.ss.usermodel.Font.getCharSet()...

    It may be a long shot but hoping someone has experienced this and resolved it, i will keep investigating and reply when i know more.

    Thanks,

    Jarlath
  • Commented on 07-09-2014 at 6:39 AM
    I just tested and my code worked ok. Using build 10,0,13,287689 which I think is above yours. You may want to ensure you are 100% updated. If it still doesn't work you need to file a bug report.
  • Commented on 07-09-2014 at 10:45 AM
    Thank you Raymond,

    I have, as suggested, updated my CF10 and have discovered that there are 2 poi jar variants in my lib folder. The poi.jar distributed in update 12 and one named poi-3.8-20120326.jar. It turns out a fellow developer included this poi-3.8-20120326 for a specific purpose and it appears to be in conflict with the distributed jar.

    Using poi.jar alone allowed the above code to work correctly so it is now down to the infrastructure team to ascertain what the risk is of having the 2 different jars in the one lib folder.

    Thank you for your help and pointers in the right direction.

    Regards,

    Jarlath

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty