Manages Excel spreadsheet files:
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. Writes a query to an Excel file. Cfspreadsheet( action='write', fileName=expandPath( 'new-example. Code sample ColdFusion: Export data to CSV file in ColdFusion using EasyXLS library. Spreadsheets in ColdFusion Excel library for exporting data to Excel files in ColdFusion. Java version to create and export Excel files File formats: MS Excel 97 - 2003 MS Excel 2007 - 2010 MS Excel 2013 MS Excel 2016 MS Excel 2019.
- 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.
The tag syntax depends on the action attribute value:
ColdFusion 11: Added the autosize attribute.
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:
| |
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. |
| |
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. For update and write: Save 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. 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. | |
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. |
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.
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.
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.
![Coldfusion upload file Coldfusion upload file](https://i.stack.imgur.com/Fvf8P.png)
Step 1
Read the csv file and store the response in a variable.
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:
- True or Yes: Creates an .xlsx file that is supported by Microsoft Office Excel 2007.
- False or No: Creates an .xls file.
Step 4
Read all or part of the file into a spreadsheet object, CSV string, HTML string, and query.
Twitter™ and Facebook posts are not covered under the terms of Creative Commons.
Legal Notices | Online Privacy Policy