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 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:



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



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


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






This example uses form fields to specify the row, column, and value.
There’s a lot more to it, but you get the idea. 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 or ), you’d not want concurrent access, and so you’d want to use 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).

40 thoughts

  1. *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?

  2. 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.

  3. 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

  4. 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?

  5. 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 … 🙂 )

  6. 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

  7. 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!

  8. 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.

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

  10. 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.

  11. 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.

  12. 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.

  13. 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?

  14. 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

  15. 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">

  16. 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?

  17. 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!

  18. 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.

  19. 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."

  20. 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?

  21. 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.

  22. 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

  23. 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?

  24. Hi Ben – Is there a way of getting the "Sheet name" at run time ? I might be processing an excel file with multiple sheets, how can i get the sheet name thta am processing, at runtime ? Thanks !

Leave a Reply