Manages Excel spreadsheet files:
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" >
Sreadsheet functions.
ColdFusion 11: Added the attribute autosize.
ColdFusion 9.0.1: Added the attribute {{excludeHeaderRow}}
ColdFusion 9: Added this tag.
Attribute |
Action |
Req/Opt |
Default |
Description |
---|---|---|---|---|
action |
All |
Required |
|
One of the following:
|
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. |
name |
All |
name or query is required. |
|
|
query |
All |
name or query is required. |
|
|
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. |
Format of the data represented by the name variable.
|
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. |
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. |
Each ColdFusion spreadsheet object represents Excel sheet:
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>
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.
<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
Note:
SpreadsheetNew(true|false)
<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
<cfdump var="#spreadsheetData#" > <cfoutput > #csvData# </cfoutput> <cfdump var="#htmlData#" > <cfdump var="#queryData#" >
Sign in to your account