Whatever message this page gives is out now! Go check it out!
SpreadsheetFormatCell
Last update:
May 18, 2026
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:
- Log in with your Gmail or Facebook credentials.
- Navigate to the project in the left pane.
- Once you make some changes in any cfm in the project, a pop up displays asking you to save the project.
- 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 the table below. |
row | The row number of the cell. |
column | The column number of the cell. |
The format structure can specify any or all of the following values
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:
| |
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. | |
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
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-StructNew();
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 example shows 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">
We're glad. Tell us how this page helped.
We're sorry. Can you tell us what didn't work for you?
Share additional feedback to help us improve.
0/255
|
