cfspreadsheet

Read and writes Microsoft Excel spreadsheet files.

 <cfspreadsheet>

 cfspreadsheet();

Attribute Reference for the cfspreadsheet tag

action

Required: Yes
read - Reads the contents of an XLS format file.
update - Adds a new sheet to an existing XLS file. You cannot use the update action to change a sheet in an existing file.
write - Writes a new XLS format file or overwrites an existing file. Values:
  • read
  • update
  • write

autosize

Required: No
Default: true
CF 11+ Toggles automatically ajusting the width of columns to accomodate their contents.

columnnames

Required: No
Comma-separated column names.

columns

Required: No
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.

excludeHeaderRow

Required: No
Default: false
CF 9.0.1+ If set to true, excludes the headerRow from being included in the query results of a spreadsheet read..

filename

Required: No
The pathname of the file that is written.

format

Required: No
Format of the data represented by the name variable. Values:
  • html
  • csv

headerrow

Required: No
Row number that contains column names.

name

Required: No
action="read" The variable in which to store the spreadsheet file data. You must specify name or query. If format="csv" then name will contain csv variable.
if format="html" then name will contain html content.
action="write|update" A variable containing CSV-format data or an ColdFusion spreadsheet object containing the data to write. You must specify name or query.

overwrite

Required: No
A Boolean value specifying whether to overwrite an existing file.

password

Required: No
Set a password for modifying the sheet.

query

Required: No
action="read" The query in which to store the converted spreadsheet file. You must specify format, name, or query.
action="write|update" A query variable containing the data to write. You must specify name or query.

rows

Required: No
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

Required: No
Number of the sheet.

sheetname

Required: No
Name of the sheet.

src

Required: No
The pathname of the file to read.

Examples sample code using the cfspreadsheet tag


Read an Excel file as variable

Reads the Excel file in src into a variable provided in name.

<cfspreadsheet action="read" src="#expandPath( 'example.xls' )#" name="foo" />

Read an Excel file as query (script syntax)

Reads the Excel file in src into a coldfusion query variable provided in query using the first row as column names.

cfspreadsheet( action="read", src=expandPath( 'example.xls' ), query="foo", headerrow=1, excludeHeaderRow=true );

Write a spreadsheet

Writes a query to an Excel file.

cfspreadsheet( action="write", fileName=expandPath( 'new-example.xls' ), query="foo", overwrite=true );

Fork me on GitHub