cfspreadsheet

Read and writes Microsoft Excel spreadsheet files.

  <cfspreadsheet>

 cfspreadsheet();

cfspreadsheet Attribute Reference

action string
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 boolean
Default: true

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

columnnames string

Comma-separated column names.

columns string

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 boolean
Default: false

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

filename string

The pathname of the file that is written.

format string

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

headerrow numeric

Row number that contains column names.

name string

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 boolean

A Boolean value specifying whether to overwrite an existing file.
Values:
  • true
  • false

password string

Set a password for modifying the sheet.

query string

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 string

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 numeric

Number of the sheet.

sheetname string

Name of the sheet.

src string

The pathname of the file to read.

Examples sample code using the cfspreadsheet tag

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

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

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

Writes a query to an Excel file.

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

Fork me on GitHub