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:
<!--- Read spreadsheet --->
<cfspreadsheet action="read"
src="Sales.xls"
query="myQuery">
<cfdump var="#myQuery#">
This next example reads a specific cell on a specific sheet and returns it in a variable:
<!--- Read a spreadsheet cell --->
<cfspreadsheet action="read"
src="Sales.xls"
name="myVar"
sheet="1"
rows="C"
columns="3">
<cfdump var="#myVar#">
<CFSPREADSHEET> is also used to write (or overwrite) a spreadsheet, as seen here:
<!--- Write spreadsheet --->
<cfspreadsheet action="write"
overwrite="true"
filename="Sales.xls"
name="sObj" />
To update a specific cell, you need to read, update, and save, like this:
<!--- Read spreadsheet --->
<cfspreadsheet action="read"
src="Sales.xls"
name="sObj" />
<!--- Set cell value --->
<cfset spreadsheetSetCellValue(sObj, FORM.sales, FORM.row, FORM.col)>
<!--- Write spreadsheet --->
<cfspreadsheet action="write" overwrite="true"
filename="Sales.xls"
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).
a.) What about support for ODF?
b.) Support for Office 2K7 (xlsx) format?
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
---- Ben
I kid, I kid...
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?
--- Ben
(in other words: please do not make it a second cfreport or cfdocument or ... :) )
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
Does anyone know if a formula containing the "FORECAST" excel function can be evaluate ?
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.
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.
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.
<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?
--- Ben
#excelquery['PAY Period'][2]#
That fixed it. Thanks for your help
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">
--- Ben
ie
<cfspreadsheet action="read" src = "D:/farmers.xls" query="qObj" />
but in cfscript syntax?
--- Ben
--- Ben
The error -- "coldfusion.excel.ExcelProcessException: An exception occurred while using action=write."
java.lang.IllegalArgumentException: Invalid row number (65536)
How do we add more than 65,536 rows?