cfspreadsheet

Description

Manages Excel spreadsheet files:

  • Reads a sheet from a spreadsheet file and stores it in a ColdFusion spreadsheet object, query, CSV string, or HTML string.

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

  • Add a sheet an existing XLS file.

Syntax

The tag syntax depends on the action attribute value:

Read 
<cfspreadsheet  
    action="read" 
    src = "filepath" 
    columns = "range" 
    columnnames = "comma-delimited list" 
    excludeHeaderRow = "true | false" 
    format = "CSV|HTML" 
    headerrow = "row number" 
    name = "text" 
    query = "query name" 
    rows = "range" 
    sheet = "number" 
    sheetname = "text"> 
 
Update 
<cfspreadsheet 
    action="update" 
    filename = "filepath" 
    format = "csv" 
    name = "text" 
    password = "password" 
    query = "query name" 
    sheetname = "text" > 
 
Write 
<cfspreadsheet  
    action="write" 
    filename = "filepath" 
    format = "csv" 
    name = "text" 
    overwrite = "true | false" 
    password = "password" 
    query = "queryname" 
    sheetname = "text" >

See also

Sreadsheet functions.

History

ColdFusion 9.0.1: Added the attribute excludeHeaderRow

ColdFusion 9: Added this tag.

Attributes

Attribute

Action

Req/Opt

Default

Description

action

All

Required

One of the following:

  • read Reads the contents of an XLS format file.

  • update Adds a new sheet to an existing XLS file. You cannot use the uppdate action to change an existing sheet in a file. For more information, see Usage.

  • write Writes a new XLS format file or overwrites an existing file.

filename

update, writer

Required

The pathname of the file that is written.

excludeHeaderRow

read

Optional

false

If set to true, excludes the headerrow from being included in the query results.The attribute helps when you read Excel as a query. When you specify the headerrow attribute, the column names are retrieved from the header row. But they are also included in the first row of the query. To not include the header row, set true as the attribute value.

name

All

name or query is required.

  • read action:  The variable in which to store the spreadsheet file data. Specify name or query.

  • write and update actions:  A variable containing CSV-format data or an ColdFusion spreadsheet object containing the data to write. Specify the name or query.

query

All

name or query is required.

  • read action:  The query in which to store the converted spreadsheet file. Specify format, name, or query.

  • write and update actions:  A query variable containing the data to write. Specify name or query.

src

read

Required

The pathname of the file to read.

columns

read

Optional

Column number or range of columns. Specify a single number, a hypen-separated column range, a comma-separated list, or any combination of these; for example: 1,3-6,9.

columnnames

read

Optional

Comma-separated column names.

format

All

Optional

For read, save as a spreadsheet object.

For update and write: Save a spreadsheet object.

Format of the data represented by the name variable.

  • All: csv On read, converts an XLS file to a CSV variable.

  • On update or write, Saves a CSV variable as an XLS file.

  • Read only: html Converts an XLS file to an HTML variable.

The cfspreadsheet tag always writes spreadsheet data as an XLS file. To write HTML variables or CSV variables as HTML or CSV files, use the cffile tag.

headerrow

read

Optional

Row number that contains column names.

overwrite

write

Optional

false

A Boolean value specifying whether to overwrite an existing file.

password

update

write

Optional

Set a password for modifying the sheet.

Note: Setting a password of the empty string does no unset password protection entirely; you are still prompted for a password if you try to modify the sheet.

rows

read

Optional

The range of rows to read. Specify a single number, a hypen-separated row range, a comma-separated list, or any combination of these; for example: 1,3-6,9.

sheet

read

Optional

Number of the sheet. For the read action, you can specify sheet or sheetname.

sheetname

All

Optional

Name of the sheet For the read action, you can specify sheet or sheetname.

For write and update actions, the specified sheet is renamed according to the value you specify for sheetname.

Usage

Each ColdFusion spreadsheet object represents Excel sheet:

  • To read an Excel file with multiple sheets, use multiple cfspreadsheet tags with the read option and specify different name and sheet or sheetname attributes for each sheet.

  • To write multiple sheets to a single file, use the write action to create the file and save the first sheet and use the update action to add each additional sheet.

  • To update an existing file, read all sheets in the file, modify one or more sheets, and use the contents, and use the write action and Update actions (for multiple sheet files) to rewrite the entire file.

The cfspreadsheet tag writes only XLS format files. To write a CSV file, put your data in a CSV formatted string variable and use the cffile tag to write the variable contents in a file.

Use the ColdFusion Spreadsheet* functions, such as SpreadsheetNew and SpreadsheetAddColumn to create a new ColdFusion Spreadsheet object and modify the spreadsheet contents.

Example

The following example uses the cfspreadsheet tag to read and write Excel spreadsheets using various formats. It also shows a simple use of ColdFusion Spreadsheet functions to modify a sheet.

<!--- Read data from two datasource tables. ---> 
<cfquery 
       name="courses" datasource="cfdocexamples" 
       cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
       SELECT CORNUMBER,DEPT_ID,COURSE_ID,CORNAME 
       FROM COURSELIST 
</cfquery> 
 
<cfquery 
       name="centers" datasource="cfdocexamples" 
       cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
       SELECT * 
       FROM CENTERS 
</cfquery> 
     
<cfscript> 
    //Use an absolute path for the files. ---> 
       theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
    theFile=theDir & "courses.xls"; 
    //Create two empty ColdFusion spreadsheet objects. ---> 
    theSheet = SpreadsheetNew("CourseData"); 
    theSecondSheet = SpreadsheetNew("CentersData"); 
    //Populate each object with a query. ---> 
    SpreadsheetAddRows(theSheet,courses); 
    SpreadsheetAddRows(theSecondSheet,centers); 
</cfscript> 
 
<!--- Write the two sheets to a single file ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet"  
    sheetname="courses" overwrite=true> 
<cfspreadsheet action="update" filename="#theFile#" name="theSecondSheet" 
    sheetname="centers"> 
 
<!--- Read all or part of the file into a spreadsheet object, CSV string, 
      HTML string, and query. ---> 
<cfspreadsheet action="read" src="#theFile#" sheetname="courses" name="spreadsheetData"> 
<cfspreadsheet action="read" src="#theFile#" sheet=1 rows="3,4" format="csv" name="csvData"> 
<cfspreadsheet action="read" src="#theFile#" format="html" rows="5-10" name="htmlData"> 
<cfspreadsheet action="read" src="#theFile#" sheetname="centers" query="queryData"> 
 
<h3>First sheet row 3 read as a CSV variable</h3> 
<cfdump var="#csvData#"> 
 
<h3>Second sheet rows 5-10 read as an HTML variable</h3> 
<cfdump var="#htmlData#"> 
 
<h3>Second sheet read as a query variable</h3> 
<cfdump var="#queryData#"> 
 
<!--- Modify the courses sheet. ---> 
<cfscript> 
    SpreadsheetAddRow(spreadsheetData,"03,ENGL,230,Poetry 1",8,1); 
    SpreadsheetAddColumn(spreadsheetData, 
    "Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,Advanced", 
    3,2,true); 
</cfscript> 
 
<!--- Write the updated Courses sheet to a new XLS file ---> 
<cfspreadsheet action="write" filename="#theDir#updatedFile.xls" name="spreadsheetData"  
    sheetname="courses" overwrite=true> 
<!--- Write an XLS file containing the data in the CSV variable. --->     
<cfspreadsheet action="write" filename="#theDir#dataFromCSV.xls" name="CSVData"  
    format="csv" sheetname="courses" overwrite=true>