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

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

Returns

Nothing

Category

Syntax

SpreadsheetFormatCell (spreadsheetObj, format, row, column)

See also

History

ColdFusion (2018 release): Added the key styleCellwise in the format struct.

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

Name

Valid values

alignment

left (default), right, center, justify, general, fill, and center_selection

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.

styleCellwise

If true, formatting is applied to every cell in the sheet.

NOTE: The formatting is not cached.

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.

verticalalignment

Any of the following: vertical_top, vertical_bottom, vertical_center, vertical_justifyFor example,<cfscript>SpreadsheetFormatCellRange(theSheet,{verticalalignment="VERTICAL_TOP"}, 3,4,30,10);</cfscript>

underline

A Boolean value. The default value is false.

 

Example

<cfscript>
	ArtOrders=QueryExecute("SELECT orderid,customerfirstname,customerlastname,address,total,city FROM orders
	ORDER BY orderid",[],{datasource="cfartgallery"});
	// Set the file path in the same location as this cfm
	myFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "myexcelfile.xls";
	// create spreadsheet object. Set xml to true as this is an xlsx file
	mySheet=SpreadsheetNew("ColdFusion",false);
	// Create another worksheet coldfusion1
	SpreadsheetCreateSheet(mySheet,"ColdFusion1");
	// Set headers for the xlsx file
	SpreadSheetAddRow(mySheet,"Order,First Name,Last Name,Address,Amount,City");
	SpreadSheetAddRows(mySheet,ArtOrders);
	
		
	// set coldfusion as active sheet
	SpreadsheetSetActiveSheet(mySheet,"ColdFusion1");
	// new query for the new sheet
	Artists=QueryExecute("Select * from APP.ARTISTS",[],{datasource="cfartgallery"});
	SpreadSheetAddRow(mySheet,"ARTISTID,First Name,Last Name,Address,CITY,STATE,POSTALCODE,EMAIL,PHONE,FAX,THEPASSWORD");
	SpreadSheetAddRows(mySheet,Artists);
	
		
		
	// Define the structure for formatting a cell
	myFormat=StructNew();
	myFormat.color="blue";
	myFormat.bold="true";
	myFormat.underline="true";
	myFormat.alignment="center";
	myFormat.rotation=45;
	
	// Set formatting to cell (10,5)
	SpreadsheetFormatCell(mySheet,myFormat,10,5);
	
	// Write spreadsheet to file
	SpreadsheetWrite(mySheet,"#myFile#",true);
	
</cfscript>

Output

spreadsheetformatcell output
spreadsheetformatcell output

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:

<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.fontsize="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 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">

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