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 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 responses to “Working With Spreadsheets In ColdFusion”

  1. Emily Avatar

    That’s is very sweet 🙂

  2. Paul Avatar

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

  3. Joe Meboe Avatar
    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.

  4. Ian Avatar

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

  5. 6dust Avatar

    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.

  6. Ben Forta Avatar
    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

  7. Grant Avatar

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

  8. Ben Forta Avatar
    Ben Forta

    Supported on all platforms supported by ColdFusion.
    —- Ben

  9. Hamish Avatar

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

  10. Paul Avatar

    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?

  11. Ben Forta Avatar
    Ben Forta

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

  12. Thilo Hermann Avatar
    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 … 🙂 )

  13. Jeff Avatar

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

  14. Patrick Whittingham Avatar
    Patrick Whittingham

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

  15. Ben Forta Avatar
    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

  16. Christine Cruz Avatar
    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!

  17. chris l. Avatar
    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.

  18. beg Avatar

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

  19. Patrick Whittingham Avatar
    Patrick Whittingham

    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.

  20. Lisa Walker Avatar
    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.

  21. Doug Avatar

    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?

  22. suchet Avatar

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

  23. Anthony O Avatar
    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:
    action = "read"
    query="excelquery" headerrow="1" columns="1-9" rows="1-9">
    <cfdump var="#excelquery#" />
    <cfoutput query="excelquery">
    #excelquery.’Pay Period’#
    Any Ideas?

  24. Ben Forta Avatar
    Ben Forta

    Have you tried #excelquery[‘Pay Period’]# type notation?
    — Ben

  25. Anthony O Avatar
    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

  26. Andrew Avatar

    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="" name="a4" columnnames="A" rows="4">

  27. Ben Forta Avatar
    Ben Forta

    I think you want COLUMNS, not COLUMNNAMES.
    — Ben

  28. ben Avatar

    is it possible to return a query/run SQL against a sheet when using CFSPREADSHEET within a CFC?
    <cfspreadsheet action="read" src = "D:/farmers.xls" query="qObj" />
    but in cfscript syntax?

  29. Kurt Bunge Avatar
    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!

  30. Kofi Avatar

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

  31. Ben Forta Avatar
    Ben Forta

    3rd one, in the Office integration chapter.
    — Ben

  32. Kofi Avatar

    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.

  33. Ben Forta Avatar
    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

  34. Kofi Avatar

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

  35. J Christopher Avatar
    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?

  36. kailash Avatar

    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.

  37. fabiano Avatar

    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

  38. Ben Forta Avatar
    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

  39. Derek Avatar

    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?

  40. Gertting sheet name at runtime Avatar
    Gertting sheet name at runtime

    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