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 |
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"> |
Sign in to your account