Blog

18Jun
2009
Working With Spreadsheets In ColdFusion

Spreadsheets are key to just about all businesses and organizations, and ColdFusion developers have long sought a way to access and manipulate spreadsheet data programmatically. The truth is, ColdFusion has supported spreadsheet access for a while in a variety of ways, it's been possible to access Excel files from an ODBC driver, and it's been possible to generate these files using CFReport as well as by generating HTML or CSV content and then setting the appropriate MIME type to force the browser to display the data using Excel.

But ColdFusion developers have been asking for more, greater and more control, and the ability to read and write specific parts of spreadsheet files. And as I demoed in my usergroup presentations last week, this is indeed planned for ColdFusion "Centaur".

Just like we did with the ColdFusion image manipulation functionality, spreadsheets in ColdFusion are manipulated using a tag or functions, or some combination thereof. The <CFSPREADSHEET> tag is used to read a sheet from a spreadsheet file (as a spreadsheet object, a query, a CSV string, or HTML), write sheets to XLS files, and add sheets to XLS files. The over 30 supporting functions like SpreadsheetSetCellValue(), SpreadsheetAddRow(), and SpreadsheetSetCellFormula() allow for more granular spreadsheet manipulation, and can be used in conjunction with supporting functions like SpreadsheetNew() to create a new spreadsheet object, and SpreadsheetInfo() which returns title, subject, sheet names, last saved date and time, and more.

Here are some of the examples I used at my presentations. This first example reads an entire spreadsheet as a query and dumps the contents:

view plain print about
1<!--- Read spreadsheet --->
2<cfspreadsheet action="read"
3                src="Sales.xls"
4                query="myQuery">

5<cfdump var="#myQuery#">

This next example reads a specific cell on a specific sheet and returns it in a variable:

view plain print about
1<!--- Read a spreadsheet cell --->
2<cfspreadsheet action="read"
3                src="Sales.xls"
4                name="myVar"
5                sheet="1"
6                rows="C"
7                columns="3">

8<cfdump var="#myVar#">

<CFSPREADSHEET> is also used to write (or overwrite) a spreadsheet, as seen here:

view plain print about
1<!--- Write spreadsheet --->
2<cfspreadsheet action="write"
3    overwrite="true"
4    filename="Sales.xls"
5    name="sObj" />

To update a specific cell, you need to read, update, and save, like this:

view plain print about
1<!--- Read spreadsheet --->
2<cfspreadsheet action="read"
3        src="Sales.xls"
4        name="sObj" />

5<!--- Set cell value --->
6<cfset spreadsheetSetCellValue(sObj, FORM.sales, FORM.row, FORM.col)>
7<!--- Write spreadsheet --->
8<cfspreadsheet action="write" overwrite="true"
9        filename="Sales.xls"
10        name="sObj" />

This example uses form fields to specify the row, column, and value.

There's a lot more to it, but you get the idea. <CFSPREADSHEET> supports Excel and Open Office spreadsheet files.

Oh, and this one came up several times, so worth noting. Just like any other file access (for example, using <CFFILE> or <CFIMAGE>), you'd not want concurrent access, and so you'd want to use <CFLOCK> calls when accessing and manipulating spreadsheet files, read locks when reading, exclusive locks when updating, using named locks (perhaps with the spreadsheet file name as the name).

Related Blog Entries

Comments (39)



  • Emily

    That's is very sweet :)

    #1Posted by Emily | Jun 18, 2009, 12:24 PM
  • Paul

    *sigh*, and I just finished an auto report e-mailer, using HTML saved as .xls, and had to explain Excel's 2007 security warning to the customer. This would have been great.

    a.) What about support for ODF?
    b.) Support for Office 2K7 (xlsx) format?

    #2Posted by Paul | Jun 18, 2009, 12:59 PM
  • Joe Meboe

    Ben, what support for SharePoint 3 will be built it? Will CF be able to read events directly out a SharePoint 3 database? Thanks.

    #3Posted by Joe Meboe | Jun 18, 2009, 02:37 PM
  • Ian

    Will you be able to add and read formula results as well?

    #4Posted by Ian | Jun 18, 2009, 03:16 PM
  • 6dust

    Very sweet! I've definitely got some projects that could use this. Any chance something similar is planned for Powerpoint? I know about CFPRESENTATION, but I have users that specifically want to download .ppt files.

    #5Posted by 6dust | Jun 18, 2009, 04:24 PM
  • Ben Forta

    Paul, ODF is not supported yet, but we may add that. XSLX is indeed supported.

    Joe, I'll post some specifics on Sharepoint integration in the next week or so, stay tuned.

    Ian, yep, SpreadSheetGetCellValue() will retrieve the value after evaluating the formula (if applicable). SpreadSheetGetCellFormula() will get the actual formula.

    6dust, enhancements to other Office document types are works in progress.

    --- Ben

    #6Posted by Ben Forta | Jun 18, 2009, 04:41 PM
  • Grant

    Can you read/write Excel xls and xlsx files on a Linux server? Or does this only work on Windows?

    #7Posted by Grant | Jun 18, 2009, 07:24 PM
  • Ben Forta

    Supported on all platforms supported by ColdFusion.

    ---- Ben

    #8Posted by Ben Forta | Jun 18, 2009, 07:28 PM
  • Hamish

    This is fantastic! I can finally turn off that pesky, expensive SQL server and store all my data in spreadsheets!

    I kid, I kid...

    #9Posted by Hamish | Jun 18, 2009, 09:27 PM
  • Paul

    Um, dumb question:

    Is file extension the only way CF knows what kind of file it's working with?

    Shouldn't there be a format or MIME type attribute or something?

    #10Posted by Paul | Jun 18, 2009, 10:11 PM
  • Ben Forta

    Paul, no, CF does not rely on the extension, so long as it is a valid spreadsheet file it'll work.

    --- Ben

    #11Posted by Ben Forta | Jun 18, 2009, 10:48 PM
  • Thilo Hermann

    would be a killer feature for our shop ... if it is working really well.

    (in other words: please do not make it a second cfreport or cfdocument or ... :) )

    #12Posted by Thilo Hermann | Jun 19, 2009, 06:28 AM
  • Jeff

    This is huge. Here's an important item - how about spreadsheet styles? And the ability to import a template and write to it?

    #13Posted by Jeff | Jun 19, 2009, 09:13 AM
  • Patrick Whittingham

    Do the spreadsheet need to be on the cfmc server or can it be on the network (intranet)?

    #14Posted by Patrick Whittingham | Jun 19, 2009, 01:08 PM
  • Ben Forta

    Thilo, I absolutely hope it'll work well. When public beta starts make sure to test it thoroughly.

    Jeff, no, I don't believe that stylesheets or templates are support, but we're not done yet.

    Patrick, so long as CF can access the network drive then the spreadsheet functionality will work, too.

    --- Ben

    #15Posted by Ben Forta | Jun 19, 2009, 08:11 PM
  • Christine Cruz

    I am trying to get CF to access a mapped network drive. Aren't there security implications to changing the account that the Application service uses? Are there better ways around this? Thanks so much!

    #16Posted by Christine Cruz | Aug 10, 2009, 04:15 PM
  • chris l.

    I have a spreadsheet that has formulas that look at other sheets. Is there a way to get the values from the cells rather than the formulas when reading the sheer? thanks.

    #17Posted by chris l. | Jan 20, 2010, 03:43 PM
  • beg

    Does spreadsheets functions use apache POI ?
    Does anyone know if a formula containing the "FORECAST" excel function can be evaluate ?

    #18Posted by beg | Feb 11, 2010, 04:36 AM
  • Patrick Whittingham

    Ben,

    I need to read a spreadsheet which has both images (charts) and data and write it to a pdf or powerpoint. I'm not having any success retrieving the images from the excel spreadsheet. If I can write it to a pdf, then I could always use "thumbnail" to write it to a powerpoint. Do you have any ideas.

    #19Posted by Patrick Whittingham | May 11, 2010, 07:56 AM
  • Lisa Walker

    This is so great. I've had a request to be able to interact with excel for an existing spreadsheet that is to be used in the automotive area.
    The trick will be to lock the cells once a cell update from a data query has been done.
    Sad for me that we're only on 8 and haven't upgraded to 9 yet.

  • Doug

    The big problem I am finding when working with CFSPREADSHEET is that if you have a large dataset, it times out the server if your time out value isn't high enough.

    Large being anything over 500 records.

    Because of my DB schema, I am reading and creating a query on the fly, creating a temp query in memory, and then dumping that info to the spreadsheet, it basically hangs the server.

    This line - <cfset SpreadsheetAddRows(schedRpt, myQuery)>

    Pushes the server to 100% utilization.

    Anybody come up with a way to improve performance for this yet?

    Thanks.

    #21Posted by Doug | Jan 17, 2011, 08:13 PM
  • suchet

    is there a way/formating-function to remove the gridlines when we create a cfspreadsheet using coldfusion.

    #22Posted by suchet | Mar 17, 2011, 12:44 PM
  • Anthony O

    This is a great tag, except for one small issue. I have an excel spread sheet where the column headers have spaces in them. I see that in the dump the headers are correctly named, but I cant figure out how to reference them in my code. Here is an example:

    <cfspreadsheet
    action = "read"
    src="TDAT_85G.xls"
    query="excelquery" headerrow="1" columns="1-9" rows="1-9">
    <cfdump var="#excelquery#" />

    <cfoutput query="excelquery">
    #excelquery.'Pay Period'#
    </cfoutput>


    Any Ideas?

    #23Posted by Anthony O | May 24, 2011, 02:26 PM
  • Ben Forta

    Have you tried #excelquery['Pay Period']# type notation?

    --- Ben

    #24Posted by Ben Forta | May 24, 2011, 02:29 PM
  • Anthony O

    I had tried that, but it just occured to me that It is a multi dimensional array:
    #excelquery['PAY Period'][2]#

    That fixed it. Thanks for your help

    #25Posted by Anthony O | May 24, 2011, 03:33 PM
  • Andrew

    Ben, Any ideas why I am getting a Structure returned from my <CFSpreadSheet> tag?

    The following line should return the value of one cell, but it is showing me a structure contain information about the spreadsheet, with no cell value in sight.

    <cfspreadsheet action="read" sheet="1" src="#path_attachments##files.name#" name="a4" columnnames="A" rows="4">

    #26Posted by Andrew | Jul 13, 2011, 01:41 PM
  • Ben Forta

    I think you want COLUMNS, not COLUMNNAMES.

    --- Ben

    #27Posted by Ben Forta | Jul 13, 2011, 04:12 PM
  • ben

    is it possible to return a query/run SQL against a sheet when using CFSPREADSHEET within a CFC?

    ie
    <cfspreadsheet action="read" src = "D:/farmers.xls" query="qObj" />

    but in cfscript syntax?

    #28Posted by ben | Aug 1, 2011, 03:27 PM
  • Kurt Bunge

    I had to use the cfspreadsheet tag in CF9 for the first time today. I LOVE it! Very powerful, borderline orgasmic! 12 years of programming in CF and it never stops being fun!

    #29Posted by Kurt Bunge | Nov 15, 2011, 02:47 PM
  • Kofi

    Which of your books contain the instructions of how to use the CFSpreadsheet?

    #30Posted by Kofi | Jan 9, 2012, 01:21 PM
  • Ben Forta

    3rd one, in the Office integration chapter.

    --- Ben

    #31Posted by Ben Forta | Jan 9, 2012, 01:40 PM
  • Kofi

    Thanks Ben. I got the book. One question though, how would you get the spreadsheet created to be saved on a client's local drive? Thanks.

    #32Posted by Kofi | Jan 13, 2012, 01:51 PM
  • Ben Forta

    You can't create files on the client side, no. But you can push it which will prompt the user to download it, look at the <cfcontent> tag.

    --- Ben

    #33Posted by Ben Forta | Jan 13, 2012, 02:00 PM
  • Kofi

    I get this error while using the cfspreadsheet. I do not get the error in the development environment but I get it in the staging environment.

    The error -- "coldfusion.excel.ExcelProcessException: An exception occurred while using action=write."

    #34Posted by Kofi | Mar 9, 2012, 02:52 PM
  • J Christopher

    I get the following error when using more than 65,000 records in CFSPREADSHEET:

    java.lang.IllegalArgumentException: Invalid row number (65536)

    How do we add more than 65,536 rows?

  • kailash

    HI,i need to display exel file data in a 2d-bar graph via coldfusionfree ; how do i do get coldfusion to read the excel rows and display the chart, am new to this , please help.

    #36Posted by kailash | Sep 3, 2012, 12:21 PM
  • fabiano

    Hi Ben,

    My doubt is? How upload the XLS file and insert the information inside the XLS in my DB?

    To I read and write its very clear for me, but how insert the information inside the XLS file?

    Tks, hugs from Brazil
    Fabiano Magno Pechibella

    #37Posted by fabiano | Oct 16, 2012, 04:43 PM
  • Ben Forta

    You'll just loop through the spreadsheet, extract data, and then use SQL to insert those values just like any other values.

    --- Ben

    #38Posted by Ben Forta | Oct 16, 2012, 04:54 PM
  • Derek

    I am on CF10 and writing large data sets still has performance issues and just times out. Is there every going to be an update to fix this?

    #39Posted by Derek | Dec 28, 2012, 08:54 AM