SpreadsheetWrite

Description

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

Function syntax

SpreadSheetWrite(SpreadsheetObj, fileName)
SpreadSheetWrite(SpreadsheetObj, fileName [,overwrite])
SpreadSheetWrite(SpreadsheetObj, fileName [, password])
SpreadSheetWrite(SpreadsheetObj, fileName [, password,overwrite])
SpreadSheetWrite(SpreadsheetObj, fileName [, password,overwrite,autosize])

History

ColdFusion 11: Added the autosize attribute.

ColdFusion 9: Added the function.

Parameters

Parameter	Description
spreadSheetObj	The Excel spreadsheet object to which to write.
fileName	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. Password is applicable only for Excel 97 - 2003 file formats. It will be ignored for XML file format (Excel 2007).
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

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

Example 2

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

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