cfspreadsheet

Last updated on Feb 25, 2025 | Also applies to ColdFusion

ColdFusion

Open app

CFML Reference User Guide

ColdFusion functions

ColdFusion functions by category
Functions a-b
1. Abs
2. ACos
3. AddSOAPRequestHeader
4. AddSOAPResponseHeader
5. AjaxLink
6. AjaxOnLoad
7. ApplicationStop
8. ArrayAppend
9. ArrayAvg
10. ArrayClear
11. ArrayContains
12. ArrayContainsNoCase
13. ArrayDelete
14. ArrayDeleteAt
15. ArrayDeleteNoCase
16. ArrayEach
17. ArrayFilter
18. ArrayFind
19. ArrayFindAll
20. ArrayFindAllNoCase
21. ArrayFindNoCase
22. ArrayInsertAt
23. ArrayIsDefined
24. ArrayIsEmpty
25. ArrayLen
26. ArrayMap
27. ArrayMax
28. ArrayMin
29. ArrayNew
30. ArrayPrepend
31. ArrayReduce
32. ArrayResize
33. ArraySet
34. ArraySetMetadata
35. ArraySlice
36. ArraySort
37. ArraySum
38. ArraySwap
39. ArrayToList
40. Asc
41. ASin
42. Atn
43. AuthenticatedContext
44. AuthenticatedUser
45. BinaryDecode
46. BinaryEncode
47. BitAnd
48. BitMaskClear
49. BitMaskRead
50. BitMaskSet
51. BitNot
52. BitOr
53. BitSHLN
54. BitSHRN
55. BitXor
56. BooleanFormat
Functions-c-d
Functions-e-g
Functions-h-im
Functions-in-k
Functions-l
Functions-m-r
Functions-s
Functions-t-z
1. Tan
2. ThreadJoin
3. ThreadTerminate
4. Throw
5. TimeFormat
6. ToBase64
7. ToBinary
8. ToScript
9. ToString
10. Trace
11. Transactionandconcurrency
12. TransactionCommit
13. TransactionRollback
14. TransactionSetSavePoint
15. Trim
16. UCase
17. URLDecode
18. URLEncodedFormat
19. URLSessionFormat
20. Val
21. ValueList
22. VerifyClient
23. Week
24. Wrap
25. WriteDump
26. WriteLog
27. WriteOutput
28. WSGetAllChannels
29. WSGetSubscribers
30. WSPublish
31. WSSendMessage
32. XmlChildPos
33. XmlElemNew
34. XmlFormat
35. XmlGetNodeType
36. XmlNew
37. XmlParse
38. XmlSearch
39. XmlTransform
40. XmlValidate
41. Year
42. YesNoFormat

ColdFusion tags

ColdFusion tag summary
ColdFusion tags by category
1. Application framework tags
  1. cfapplication
  2. cfassociate
  3. cferror
  4. cfimport
  5. cfinterface
  6. cflock
  7. cfscript
  8. cfthread
2. Communications tags
3. Database manipulation tags
4. Data output tags
  1. cfchart
5. Debugging tags
  1. cfdump
  2. cftimer
  3. cftrace
6. Exception handling tags
  1. cfcatch
  2. cferror
  3. cffinally
  4. cfrethrow
  5. cfthrow
  6. cftry
7. Extensibility tags
  1. cfchart
  2. cfchartdata
  3. cfchartseries
  4. cfcollection
  5. cfcomponent
  6. cfexecute
  7. cfftp
  8. function
  9. cfindex
  10. cfinterface
  11. cfinvoke
  12. cfinvokeargument
  13. cfobject
  14. cfproperty
  15. cfreport
  16. cfreportparam
  17. cfreturn
  18. cfsearch
  19. cfsharepoint
  20. cfspreadsheet
  21. cfwddx
  22. cfxml
8. File management tags
  1. cfdirectory
  2. cffile
  3. cffileupload
  4. cfftp
  5. cfzip
  6. cfzipparam
9. Flow-control tags
  1. cfabort
  2. cfbreak
  3. cfcase
  4. cfcontinue
  5. cfdefaultcase
  6. cfelse
  7. cfelseif
  8. cfexecute
  9. cfexit
  10. cfif
  11. cfinclude
  12. cflocation
  13. cfloop
  14. cfrethrow
  15. cfswitch
  16. cfthrow
  17. cftry
10. Forms tags
  1. cfapplet
  2. cfcalendar
  3. cffileupload
  4. cfform
  5. cfformgroup
  6. cfformitem
  7. cfgrid
  8. cfgridcolumn
  9. cfgridrow
  10. cfgridupdate
  11. cfinput
  12. cfpdf
  13. cfpdfform
  14. cfpdfformparam
  15. cfpdfparam
  16. cfpdfsubform
  17. cfselect
  18. cfslider
  19. cftextarea
  20. cftree
  21. cftreeitem
11. Internet Protocol tags
  1. cfajaximport
  2. cfajaxproxy
  3. cfftp
  4. cffeed
  5. cfimap
  6. cfhttp
  7. cfhttpparam
  8. cfldap
  9. cfmail
  10. cfmailparam
  11. cfmailpart
  12. cfpop
  13. cfsprydataset
12. Page processing tags
  1. cfcache
  2. cfcontent
  3. cfflush
  4. cfheader
  5. cfhtmlhead
  6. cfinclude
  7. cfprocessingdirective
  8. cfsetting
  9. cfsilent
13. Security tags
14. Variable manipulation tags
  1. cfcookie
  2. cfdump
  3. cfparam
  4. cfregistry
  5. cfsavecontent
  6. cfschedule
  7. cfset
  8. cfsetting
15. Other tags
  1. cfimage
  2. cflog
  3. cfregistry
Tags a-b
Tags c
Tags f
Tags g-h
1. cfgraph
2. cfgraphdata
3. cfgrid
4. cfgridcolumn
5. cfgridrow
6. cfgridupdate
7. cfheader
8. cfhtmlhead
9. cfhtmltopdf
10. cfhtmltopdfitem
11. cfhttp
12. cfhttpparam
Tags i
1. cfif
2. cfimage
3. cfimap
4. cfimapfilter
5. cfimpersonate
6. cfimport
7. cfinclude
8. cfindex
9. cfinput
10. cfinsert
11. cfinterface
12. cfinvoke
13. cfinvokeargument
Tags j-l
Tags m-o
Tags p-q
1. cfparam
2. cfpdf
3. cfpdfform
4. cfpdfformparam
5. cfpdfparam
6. cfpdfsubform
7. cfpod
8. cfpop
9. cfpresentation
10. cfpresentationslide
11. cfpresenter
12. cfprint
13. cfprocessingdirective
14. cfprocparam
15. cfprocresult
16. cfprogressbar
17. cfproperty
18. cfquery
19. cfqueryparam
Tags r-s
1. cfregistry
2. cfreport
3. cfreportparam
4. cfrethrow
5. cfreturn
6. cfsavecontent
7. cfschedule
8. cfscript
9. cfsearch
10. cfselect
11. cfservlet
12. cfservletparam
13. cfset
14. cfsetting
15. cfsharepoint
16. cfsilent
17. cfslider
18. cfspreadsheet
19. cfsprydataset
20. cfstoredproc
21. cfswitch
Tags t
1. cftable
2. cftextarea
3. cftextinput
4. cfthread
5. cfthrow
6. cftimer
7. cftooltip
8. cftrace
9. cftransaction
10. cftree
11. cftreeitem
12. cftry
Tags u-z
1. cfupdate
2. cfwddx
3. cfwebsocket
4. cfwindow
5. cfxml
6. cfzip
7. cfzipparam

CFML Reference

Cloud services

CFML Reference User Guide
ColdFusion functions
ColdFusion tags
1. ColdFusion tag summary
2. ColdFusion tags by category
  1. Application framework tags
    1. cfapplication
    2. cfassociate
    3. cferror
    4. cfimport
    5. cfinterface
    6. cflock
    7. cfscript
    8. cfthread
  2. Communications tags
  3. Database manipulation tags
  4. Data output tags
    1. cfchart
  5. Debugging tags
    1. cfdump
    2. cftimer
    3. cftrace
  6. Exception handling tags
    1. cfcatch
    2. cferror
    3. cffinally
    4. cfrethrow
    5. cfthrow
    6. cftry
  7. Extensibility tags
    1. cfchart
    2. cfchartdata
    3. cfchartseries
    4. cfcollection
    5. cfcomponent
    6. cfexecute
    7. cfftp
    8. function
    9. cfindex
    10. cfinterface
    11. cfinvoke
    12. cfinvokeargument
    13. cfobject
    14. cfproperty
    15. cfreport
    16. cfreportparam
    17. cfreturn
    18. cfsearch
    19. cfsharepoint
    20. cfspreadsheet
    21. cfwddx
    22. cfxml
  8. File management tags
    1. cfdirectory
    2. cffile
    3. cffileupload
    4. cfftp
    5. cfzip
    6. cfzipparam
  9. Flow-control tags
    1. cfabort
    2. cfbreak
    3. cfcase
    4. cfcontinue
    5. cfdefaultcase
    6. cfelse
    7. cfelseif
    8. cfexecute
    9. cfexit
    10. cfif
    11. cfinclude
    12. cflocation
    13. cfloop
    14. cfrethrow
    15. cfswitch
    16. cfthrow
    17. cftry
  10. Forms tags
    1. cfapplet
    2. cfcalendar
    3. cffileupload
    4. cfform
    5. cfformgroup
    6. cfformitem
    7. cfgrid
    8. cfgridcolumn
    9. cfgridrow
    10. cfgridupdate
    11. cfinput
    12. cfpdf
    13. cfpdfform
    14. cfpdfformparam
    15. cfpdfparam
    16. cfpdfsubform
    17. cfselect
    18. cfslider
    19. cftextarea
    20. cftree
    21. cftreeitem
  11. Internet Protocol tags
    1. cfajaximport
    2. cfajaxproxy
    3. cfftp
    4. cffeed
    5. cfimap
    6. cfhttp
    7. cfhttpparam
    8. cfldap
    9. cfmail
    10. cfmailparam
    11. cfmailpart
    12. cfpop
    13. cfsprydataset
  12. Page processing tags
    1. cfcache
    2. cfcontent
    3. cfflush
    4. cfheader
    5. cfhtmlhead
    6. cfinclude
    7. cfprocessingdirective
    8. cfsetting
    9. cfsilent
  13. Security tags
  14. Variable manipulation tags
    1. cfcookie
    2. cfdump
    3. cfparam
    4. cfregistry
    5. cfsavecontent
    6. cfschedule
    7. cfset
    8. cfsetting
  15. Other tags
    1. cfimage
    2. cflog
    3. cfregistry
3. Tags a-b
4. Tags c
5. Tags f
6. Tags g-h
  1. cfgraph
  2. cfgraphdata
  3. cfgrid
  4. cfgridcolumn
  5. cfgridrow
  6. cfgridupdate
  7. cfheader
  8. cfhtmlhead
  9. cfhtmltopdf
  10. cfhtmltopdfitem
  11. cfhttp
  12. cfhttpparam
7. Tags i
  1. cfif
  2. cfimage
  3. cfimap
  4. cfimapfilter
  5. cfimpersonate
  6. cfimport
  7. cfinclude
  8. cfindex
  9. cfinput
  10. cfinsert
  11. cfinterface
  12. cfinvoke
  13. cfinvokeargument
8. Tags j-l
9. Tags m-o
10. Tags p-q
  1. cfparam
  2. cfpdf
  3. cfpdfform
  4. cfpdfformparam
  5. cfpdfparam
  6. cfpdfsubform
  7. cfpod
  8. cfpop
  9. cfpresentation
  10. cfpresentationslide
  11. cfpresenter
  12. cfprint
  13. cfprocessingdirective
  14. cfprocparam
  15. cfprocresult
  16. cfprogressbar
  17. cfproperty
  18. cfquery
  19. cfqueryparam
11. Tags r-s
  1. cfregistry
  2. cfreport
  3. cfreportparam
  4. cfrethrow
  5. cfreturn
  6. cfsavecontent
  7. cfschedule
  8. cfscript
  9. cfsearch
  10. cfselect
  11. cfservlet
  12. cfservletparam
  13. cfset
  14. cfsetting
  15. cfsharepoint
  16. cfsilent
  17. cfslider
  18. cfspreadsheet
  19. cfsprydataset
  20. cfstoredproc
  21. cfswitch
12. Tags t
  1. cftable
  2. cftextarea
  3. cftextinput
  4. cfthread
  5. cfthrow
  6. cftimer
  7. cftooltip
  8. cftrace
  9. cftransaction
  10. cftree
  11. cftreeitem
  12. cftry
13. Tags u-z
  1. cfupdate
  2. cfwddx
  3. cfwebsocket
  4. cfwindow
  5. cfxml
  6. cfzip
  7. cfzipparam
CFML Reference
1. Reserved words and variables
2. Ajax JavaScript functions
3. ColdFusion ActionScript functions
4. ColdFusion mobile functions
5. Application.cfc reference
6. Script functions implemented as CFCs
7. ColdFusion Flash Form style reference
8. ColdFusion event gateway reference
9. ColdFusion C++ CFX Reference
10. ColdFusion Java CFX reference
11. WDDX JavaScript Objects
Cloud services

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"

autosize="true|false"

filename = "filepath"

format = "csv"

name = "text"

overwrite = "true | false"

password = "password"

query = "queryname"

sheetname = "text"

autosize = "true | false" >

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" autosize="true|false" filename = "filepath" format = "csv" name = "text" overwrite = "true | false" password = "password" query = "queryname" sheetname = "text" autosize = "true | false" >

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" 
    autosize="true|false" 
    filename = "filepath" 
    format = "csv" 
    name = "text" 
    overwrite = "true | false" 
    password = "password" 
    query = "queryname" 
    sheetname = "text" 
    autosize = "true | false" >

History

ColdFusion (2025 release): You can no longer read password-protected Excel files without specifying the password. If you skip the password attribute in the read action, you will get an exception. As a result, you’ll also be unable to update the file as the read action will produce the exception. View the examples for more information.

ColdFusion 11: Added the attribute autosize.

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.
autosize	write	Optional	true	By default the value of this attribute is true. The columns in a sheet resize to accommodate the contents. To avoid resizing the columns, set it to false. Note: Auto sizing can be relatively slow on large sheets.
filename	{{update, write}}r	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. Note: Setting a range higher than the actual number of columns in sheet results in an exception when accessing columns which are yet to be added.
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	updatewrite	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.

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

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

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

<h3>First sheet row 3 read as a CSV variable</h3>

<h3>Second sheet rows 5-10 read as an HTML variable</h3>

<h3>Second sheet read as a query variable</h3>

SpreadsheetAddRow(spreadsheetData,"03,ENGL,230,Poetry 1",8,1);

SpreadsheetAddColumn(spreadsheetData,

"Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,Advanced",

3,2,true);

</cfscript>

<cfspreadsheet action="write" filename="#theDir#updatedFile.xls" name="spreadsheetData"

sheetname="courses" overwrite=true>

<cfspreadsheet action="write" filename="#theDir#dataFromCSV.xls" name="CSVData"

format="csv" sheetname="courses" overwrite=true>

<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>  <cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="courses" overwrite=true> <cfspreadsheet action="update" filename="#theFile#" name="theSecondSheet" sheetname="centers">  <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#">  <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>  <cfspreadsheet action="write" filename="#theDir#updatedFile.xls" name="spreadsheetData" sheetname="courses" overwrite=true>  <cfspreadsheet action="write" filename="#theDir#dataFromCSV.xls" name="CSVData" format="csv" sheetname="courses" overwrite=true>

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

Let's take chunks of the above code and see each chunk in action. For example, consider a csv file is uploaded on the web, which you want to retrieve and perform some actions.

You can also jump to ths fiddle and try out the code chunks. Sign in with your Google or Facebook credentials and launch the file cfspreadsheet.cfm.

Step 1

Read the csv file and store the response in a variable.

cfhttp( name="mydata", url="https://raw.githubusercontent.com/sauravg94/test-repo/master/MOCK_DATA.csv", firstrowasheaders="true" ,method="GET");

writedump(mydata);

</cfscript>

<cfscript> cfhttp( name="mydata", url="https://raw.githubusercontent.com/sauravg94/test-repo/master/MOCK_DATA.csv", firstrowasheaders="true" ,method="GET"); writedump(mydata); </cfscript>

<cfscript>
 cfhttp( name="mydata", url="https://raw.githubusercontent.com/sauravg94/test-repo/master/MOCK_DATA.csv", firstrowasheaders="true" ,method="GET");
 writedump(mydata);
</cfscript>

Step 2

Set the destination for the xlsx or xls file.
Create an empty spreadsheet object.
Populate the object with data fetched with cfhttp.

Note:

SpreadsheetNew(true|false)

True or Yes: Creates an .xlsx file that is supported by Microsoft Office Excel 2007.
False or No: Creates an .xls file.

//Use an absolute path for the files.

theDir=GetDirectoryFromPath(GetCurrentTemplatePath());

theFile=theDir & "mock_data.xlsx";

//Create an empty ColdFusion spreadsheet objects.

theSheet = SpreadsheetNew(true);

//Populate the object with data fetched with cfhttp

SpreadsheetAddRows(theSheet,mydata,1);

</cfscript>

<cfscript> //Use an absolute path for the files. theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); theFile=theDir & "mock_data.xlsx"; //Create an empty ColdFusion spreadsheet objects. theSheet = SpreadsheetNew(true); //Populate the object with data fetched with cfhttp SpreadsheetAddRows(theSheet,mydata,1); </cfscript>

<cfscript> 
    //Use an absolute path for the files. 
    theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
    theFile=theDir & "mock_data.xlsx"; 
    //Create an empty ColdFusion spreadsheet objects. 
    theSheet = SpreadsheetNew(true); 
    //Populate the object with data fetched with cfhttp
    SpreadsheetAddRows(theSheet,mydata,1);
</cfscript>

Step 3

Write the sheet into a file.

<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="mock_data" overwrite=true>

Step 4

Read all or part of the file into a spreadsheet object, CSV string, HTML string, and query.

<cfspreadsheet action="read" src="#theFile#" sheetname="mock_data" name="spreadsheetData">

<cfspreadsheet action="read" src="#theFile#" sheet=1 rows="100-200" format="csv" name="csvData">

<cfspreadsheet action="read" src="#theFile#" format="html" rows="5-10" name="htmlData">

<cfspreadsheet action="read" src="#theFile#" sheetname="mock_data" query="queryData">

Step 5

#csvData#

</cfoutput>

<cfdump var="#spreadsheetData#" > <cfoutput > #csvData# </cfoutput> <cfdump var="#htmlData#" > <cfdump var="#queryData#" >

<cfdump var="#spreadsheetData#" >

<cfoutput >
 #csvData#
</cfoutput>

<cfdump var="#htmlData#" >

<cfdump var="#queryData#" >

Password protection

In the following examples, the script will attempt to read employee.xlsx, which is a password-protected file.

Example 1

Read the file with attribute password

<cfspreadsheet action="read" src="#expandPath('employee.xlsx')#" name="employee" password="abc123">
<cfdump var="#employee#" label="Result of cfspreadsheet read action (with password attribute)">

Example 2

Read the file without specifying the password attribute. The script will produce an exception.

cfspreadsheet action="read" src="#expandPath('employee.xlsx')#" name="employee">

cfspreadsheet action="read" src="#expandPath('employee.xlsx')#" name="employee"> <cfdump var="#employee#" label="Result of cfspreadsheet read action (without password attribute)">

cfspreadsheet action="read" src="#expandPath('employee.xlsx')#" name="employee">
<cfdump var="#employee#" label="Result of cfspreadsheet read action (without password attribute)">

Example 3

Read the file with an incorrect password, which will produce an exception.

<cfspreadsheet action="read" src="#expandPath('employee.xls')#" name="employee" password="1234">
<cfdump var="#employee#" label="Result of cfspreadsheet read action (with incorrect password attribute)">

Example 4

Now, attempt updating the file without specifying the password attribute. The read action will produce an exception.

<cfspreadsheet

action="update"

filename = "#expandPath('employee.xlsx')#"

name="employee"

sheetname = "sheet1"

password="abc123" >

<cfspreadsheet action="read" src="#expandPath('employee.xlsx')#" name="employee" >
<cfspreadsheet
        action="update"
        filename = "#expandPath('employee.xlsx')#"
        name="employee"
        sheetname = "sheet1" 
        password="abc123" > 
<cfdump var="#var#" >

Get help faster and easier

New user?

cfspreadsheet

Description

Category

Syntax

See also

History

Attributes

Usage

Example

Password protection

Get help faster and easier

Share this page

Ask the Community

Contact Us