Note:

You can find the CFFiddle demo of this function and other spreadsheet functions as part of a project that is shared with you.

Click the button below to launch CFFiddle.

To copy the project in your workspace in CFFiddle, follow the steps below:

  1. Log in with your Gmail or Facebook credentials.
  2. Navigate to the project in the left pane.
  3. Once you make some changes in any cfm in the project, a pop up displays asking you to save the project.
  4. Give the project a suitable name and click Save.

Description

Writes single sheet to a new XLS file from a ColdFusion spreadsheet object.

Category

Microsoft Office Integration

Function syntax

SpreadsheetWrite(spreadsheet,filePath,[password,overwrite,autosize])

SpreadsheetWrite(spreadsheet,filePath)

See also

SpreadsheetReadSpreadsheetAddRowSpreadsheetAddRowsSpreadsheetDeleteRowSpreadsheetDeleteRows

SpreadsheetFormatRowSpreadsheetFormatRowsSpreadsheetShiftColumns

History

ColdFusion (2018 release): Introduced named parameters.

ColdFusion 11: Added the autosize parameter.

ColdFusion 9: Added the function. 

Parameters

Parameter

Description

spreadsheet

The Excel spreadsheet object to which to write.

filePath

The pathname of the file that is written.

overwrite

A Boolean value specifying whether to overwrite an existing file. Specify yes to overwrite.

password

Password to protect the active sheet. This attribute is supported for both XLS and XLSX files.
autosize Auto re-size the column, if required. You can specify either a Boolean value or an array of integers denoting the columns to expand. If you specify an array, it must contain the column numbers that needs to be re-sized.

Usage

Use this function to:

  • Write multiple sheets to a single file.
  • Update an existing file, read all sheets in the file, modify one or more sheets, and to rewrite the entire file.

Example

spreadsheet = SpreadSheetRead("C:\Files\Report.xls","Annual Report");
SpreadSheetWrite(spreadsheet,"C:\Files\Report.xls","P@ssword","yes");
</cfscript>

Example 2

spObj = spreadsheetread("#dirname#SingleSheet.xls","Sheet2");
spreadsheetCreateSheet(spObj,"A");
spreadsheetaddrow(spObj,"x,x,x,x,x",3,1);
spreadsheetsetActiveSheet(spObj,"A");
spreadsheetaddrow(spObj,"z,z,z,z,z",3,1);
spreadsheetsetActiveSheetNumber(spObj,1);
spreadsheetaddrow(spObj,"a,b,c,d,e",3,1);
SpreadsheetWrite(spObj,"#dirname#SingleSheet1.xls","yes");
</cfscript>

Example 3

SpreadSheetWrite method

usage:
SpreadSheetWrite(SpreadsheetObj, filename, password, overwrite, autosize)
--->

<cfset colList ="col1,col2,col3,col4,col5,col6,col7,col8,co9,col0">
<cfset rowCount = 100>
<cfset qryObj = QueryNew("#colList#")>
<cfset QueryAddRow(qryObj, #rowCount#)>
<cfloop from="1" to="#rowCount#" index="r">
<cfloop from="1" to="#ListLen(colList)#" index="c">
<cfset QuerySetCell(qryObj, #ListGetAt(colList,c)#, "some random text r#r# c#c#", r)>
</cfloop>
</cfloop>

<cfset xlObj = spreadsheetNew("testsheet", true)>

<cfset spreadsheetAddRows(xlObj, "#qryObj#")>

<cfset spreadsheetwrite(xlObj, "#Expandpath("./")#test_autosize_ss_method.xlsx", "", true, false)>

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy