ColdFusion 9.0 Resources

SpreadsheetFormatCell

Description

Formats the contents of a single cell of an Excel spreadsheet object.

Returns

Does not return a value.

Function syntax

SpreadsheetFormatCell(spreadsheetObj, format, row, column)

History

ColdFusion 9: Added the function.

Parameters

Parameter	Description
`spreadsheetObj`	The Excel spreadsheet object to which to set the format.
`format`	A structure containing the format information. For details see Usage.
`row`	The row number of the cell.
`column`	The column number of the cell.

Usage

The format structure can specify any or all of the following values

Name	Valid values
`alignment`	Any of the following: left (default), right, center, justify, general, fill, center_selection, vertical_top, vertical_bottom, vertical_center, vertical_justify
`bold`	A Boolean value. The default value is false.
`bottomborder`	A border format, any of the following: none (default), thin, medium, dashed, hair, thick, double, dotted, medium_dashed, dash_dot, medium_dash_dot, dash_dot_dot, medium_dash_dot_dot, slanted_dash_dot
`bottombordercolor`	See the color field for the complete list of colors.
`color`	Any value in the `org.apache.poi.hssf.util.HSSFColor` class: black, brown, olive_green, dark_green, dark_teal, dark_blue, indigo, grey_80_percent, orange, dark_yellow, green, teal, blue, blue_grey, grey_50_percent, red, light_orange, lime, sea_green, aqua, light_blue, violet, grey_40_percent, pink, gold, yellow, bright_green, turquoise, dark_red, sky_blue, plum, grey_25_percent, rose, light_yellow, light_green, light_turquoise, light_turquoise, pale_blue, lavender, white, cornflower_blue, lemon_chiffon, maroon, orchid, coral, royal_blue, light_cornflower_blue
`dataformat`	An Excel data format. Most of the formats supported by MS Excel are supported. The following are the built-in formats: General 0 0.00 #,##0 #,##0.00 ($#,##0_($#,##0) ($#,##0_[Red]($#,##0) ($#,##0.00($#,##0.00) ($#,##0.00_[Red]($#,##0.00) 0% 0.00% 0.00E+00 # ?/? # ??/?? m/d/yy d-mmm-yy d-mmm mmm-yy h:mm AM/PM h:mm:ss AM/PM h:mm h:mm:ss m/d/yy h:mm (#,##0_(#,##0) (#,##0_[Red](#,##0) (#,##0.00_(#,##0.00) (#,##0.00_[Red](#,##0.00) _(#,##0__((#,##0_(* \-\__(@_) _($#,##0__($(#,##0_($* \-\__(@_) _(#,##0.00__((#,##0.00_(\-\??__(@_) _($#,##0.00__($(#,##0.00_($\-\??__(@_) mm:ss [h]:mm:ss mm:ss.0 ##0.0E+0 @
`fgcolor`	See the color field for the complete list of colors.
`fillpattern`	Any of the following: big_spots (default), squares, nofill, solid_foreground, fine_dots, alt_bars, sparse_dots, thick_horz_bands, thick_vert_bands, thick_backward_diag, thick_forward_diag, diamonds, less_dots, least_dots
`font`	A valid system font name.
`fontsize`	An integer point value.
`hidden`	A Boolean value. The default value is false.
`indent`	A positive integer number of default character spaces.
`italic`	No value required.
`leftborder`	A border format. See bottomborder for valid values.
`leftbordercolor`	See the color field for the complete list of colors.
`locked`	A Boolean value. The default value is false.
`rightborder`	A border format. See bottomborder for valid values.
`rightbordercolor`	See the color field for the complete list of colors.
`rotation`	An integer number of degrees in the range -90 — 90.
`strikeout`	No value required.
`textwrap`	A Boolean value. The default value is false.
`topborder`	A border format. See bottomborder for valid values.
`topbordercolor`	See the color field for the complete list of colors.
`underline`	A Boolean value. The default value is false.

Example

The following example creates a sheet, sets a simple format for the cell at row 3 column 4, and writes the result to a file:

<!--- Get the spreadsheet data as a query. ---> 
<cfquery 
       name="courses" datasource="cfdocexamples" 
       cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
       SELECT CORNUMBER,DEPT_ID,CORLEVEL,COURSE_ID,CORNAME,CORDESC,LASTUPDATE 
       FROM COURSELIST 
</cfquery> 
 
<cfscript> 
    ///We need an absolute path, so get the current directory path. 
       theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "courses.xls"; 
    //Create a new Excel spreadsheet object and add the query data. 
    theSheet = SpreadsheetNew("CourseData"); 
    SpreadsheetAddRows(theSheet,courses); 
    // Define a format for the cell. 
    format1-SructNew(); 
    format1.font="serif"; 
    format1.size="12"; 
    format1.color="dark_green"; 
    format1.bold="true"; 
    format1.alignment="center"; 
    SpreadsheetFormatCell(theSheet,format1,3,4); 
</cfscript> 
 
<!--- Write the spreadsheet to a file, replacing any existing file. ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet"  
    sheet=1 sheetname="courses" overwrite=true>

The following examples show how to use dataformat:

<cfset a = spreadsheetnew()> 
<cfset format = structnew()> 
<cfset format.dataformat = "0.00"> 
<cfset spreadsheetaddrow(a,"1,2,3,4",2,1)> 
<cfset spreadsheetformatrow(a,format,2)> 
<cfset format.dataformat = "0.00%"> 
<cfset spreadsheetaddrow(a,"1,2,3,4",4,1)> 
<cfset spreadsheetformatrow(a,format,4)> 
<cfset format.dataformat = "0.00E+00"> 
<cfset spreadsheetaddrow(a,".00000000000001",5,1)> 
<cfset spreadsheetformatrow(a,format,5)> 
<cfset format.dataformat = "## ??/??"> 
<cfset spreadsheetaddrow(a,"3.33",7,1)> 
<cfset spreadsheetformatrow(a,format,7)> 
<cfset format.dataformat = "m/d/yy"> 
<cfset spreadsheetaddrow(a,"01/06/09",8,1)> 
<cfset spreadsheetformatrow(a,format,8)> 
<cfset format.dataformat = "##,##0.00"> 
<cfset spreadsheetaddrow(a,"2100000",13,1)> 
<cfset spreadsheetformatrow(a,format,13)> 
<cfset format.dataformat = " (##,##0_);(##,##0) "> 
<cfset spreadsheetaddrow(a,"-300",14,1)> 
<cfset spreadsheetformatrow(a,format,14)> 
<cfspreadsheet action="write" filename="#expandpath('.')#/test.xls" name="a" overwrite="true">