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
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:
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.
Oh, and this one came up several times, so worth noting. Just like any other file access (for example, using
Working With Spreadsheets In ColdFusion
40 responses to “Working With Spreadsheets In ColdFusion”
-
That’s is very sweet 🙂
-
*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? -
Ben, what support for SharePoint 3 will be built it? Will CF be able to read events directly out a SharePoint 3 database? Thanks.
-
Will you be able to add and read formula results as well?
-
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.
-
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 -
Can you read/write Excel xls and xlsx files on a Linux server? Or does this only work on Windows?
-
Supported on all platforms supported by ColdFusion.
—- Ben -
This is fantastic! I can finally turn off that pesky, expensive SQL server and store all my data in spreadsheets!
I kid, I kid… -
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? -
Paul, no, CF does not rely on the extension, so long as it is a valid spreadsheet file it’ll work.
— Ben -
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 … 🙂 ) -
This is huge. Here’s an important item – how about spreadsheet styles? And the ability to import a template and write to it?
-
Do the spreadsheet need to be on the cfmc server or can it be on the network (intranet)?
-
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 -
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!
-
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.
-
Does spreadsheets functions use apache POI ?
Does anyone know if a formula containing the "FORECAST" excel function can be evaluate ? -
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. -
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. -
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. -
is there a way/formating-function to remove the gridlines when we create a cfspreadsheet using coldfusion.
-
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? -
Have you tried #excelquery[‘Pay Period’]# type notation?
— Ben -
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 -
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"> -
I think you want COLUMNS, not COLUMNNAMES.
— 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? -
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!
-
Which of your books contain the instructions of how to use the CFSpreadsheet?
-
3rd one, in the Office integration chapter.
— Ben -
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.
-
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 -
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." -
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? -
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.
-
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 -
You’ll just loop through the spreadsheet, extract data, and then use SQL to insert those values just like any other values.
— Ben -
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?
-
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