Using cfspreadsheet

The cfspreadsheet tag lets you manage Excel spreadsheets. The tag lets you do the following:

  • Read a spreadsheet file (XLS file) and store it in a ColdFusion spreadsheet object, query, CSV string, or HTML string.

  • Write a single sheet to a new XLS from a query, ColdFusion spreadsheet object, or CSV string variable.

  • Add a sheet to an existing XLS file.

Use the spreadsheet functions to manipulate rows and columns in a spreadsheet and the data in the rows and columns. You can also specify and get comments, values, and formulas for a cell in the spreadsheet.

Microsoft Office Excel 2007 is supported by cfspreadsheet and all the spreadsheet functions except the following:

  • SpreadSheetAddSplitPane

  • SpreadSheetAddFreezePane

For detailed information about cfspreadsheet and all the spreadsheet functions, see CFML Reference.

Examples

The following example reads the spreadsheet file - SingleSheet.xls and stores the spreadsheet data in a CSV string.

<cfspreadsheet action = "read" 
    format="csv" 
    src="C:\documents\SingleSheet.xls" 
    name="csvvar" 
    rows="1-4,5,6,7-8"> 
    <cfoutput>#csvvar#</cfoutput>

The following example reads the spreadsheet file - template_02.xls and stores the spreadsheet data in a query.

<cfspreadsheet 
        action = "read" 
        src="C:\dcuments\template_02.xls" 
        query="excelquery" 
        sheet="1" 
        rows="1-3,4-5" 
        columns="1,4"> 
<cfoutput 
        query="excelquery" 
        startrow="1" 
        maxrows="#excelquery.recordcount#"> 
        #excelquery.col_1# 
        #excelquery.col_2# 
</cfoutput>

The following example reads a spreadsheet file - template_08_Charts_Graph.xls and stores the spreadsheet data in an HTML string.

<cfspreadsheet 
        action = "read" 
        format="html" 
        src="C:\documents\template_08_Charts_Graph.xls" 
        name="report1" 
        rows="5-11" 
        columns="1-6"> 
<cfoutput> 
        #report1# 
</cfoutput>

The following example uses data from a query and writes it to a single sheet in the spreadsheet file - SingleSheet1.xls

<cfquery 
        name="excelquery" 
        datasource="cfdocexamples"> 
        SELECT PARKNAME, REGION, STATE FROM Parks WHERE STATE='WI' 
        ORDER BY ParkName, State 
</cfquery> 
<cfspreadsheet 
        action = "write" 
        filename="C:\SingleSheet1.xls" 
        query="excelquery" 
        overwrite="true">