SpreadsheetFormatCell

SpreadsheetFormatCell(spreadsheetObj, format, row, column)

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.

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.

<cfscript>
sheet= SpreadSheetNew();
Spreadsheetformatcell(sheet,{dataformat="@"},1,1);
spreadsheetSetCellValue(sheet,'000006534',1,1);
</cfscript>

<!--- 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.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>