cfspreadsheet

Read and writes Microsoft Excel spreadsheet files.

 <cfspreadsheet>

 cfspreadsheet();

cfspreadsheet Attribute Reference

action
Required

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
Default: true

CF 11+ Toggles automatically ajusting the width of columns to accomodate their contents.

columnnames

Comma-separated column names.

columns

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
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

The pathname of the file that is written.

format

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

headerrow

Row number that contains column names.

name

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

A Boolean value specifying whether to overwrite an existing file.

password

Set a password for modifying the sheet.

query

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

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

Number of the sheet.

sheetname

Name of the sheet.

src

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