User Guide Cancel

SpreadsheetSetCellComment

 

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

Specifies the comment for an Excel spreadsheet object cell.

Returns

Nothing

Category

Syntax

SpreadsheetSetCellComment (spreadsheetObj, comment, row, column)
SpreadsheetSetCellComment (spreadsheetObj, comment, row, column)
SpreadsheetSetCellComment (spreadsheetObj, comment, row, column)

See also

History

ColdFusion 9: Added the function.

Parameters

Parameter

Description

spreadsheetObj

The Excel spreadsheet object to which to add the comment.

comment

A structure containing the comment including text, formatting, and placement in the cell. See Usage for the structure contents.

row

The row number of the cell to which to add the comment.

column

The column number of the cell to which to add the comment.


Usage

The comment structure can have the following fields. Excel determines the default field values.

Field

Valid values

anchor

A comma separated list of integers specifying the position and size of the comment, in rows and columns, in the order top column, left row,bottom column, right row. For example: "4,8,6,11" specifies a comment with an upper left corner in row 4 column 8 and a lower right corner in row 6 column 11.

author

The author's name.

bold

A Boolean value specifying whether the text is bold.

color

The text color, Any value in the Apache 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.

comment

A string containing the comment text.

fillcolor

A java.awt class color value: white, lightGray, light_gray, gray, darkGray, dark_gray, black, red, pink, orange, yellow, green, magenta, cyan, blue. (Because ColdFusion is case independent, you do not need to specify the values if defined in the Java class.)

font

Any valid system font name.

horizontalalignment

The horizontal alignment of the text: left, center, right, justify, distributed.

italic

A Boolean value specifying whether the text is italic.

linestyle

The style of the top and right borders of the comment box: solid, dashsys, dotsys, dashdotsys, dashdotdotsys, dotgel, dashgel, longdashgel, dashdotgel, longdashdotgel, longdashdotdotgel.

linestylecolor

A Java color value.

size

The size of the text in points.

strikeout

A Boolean value specifying whether the text is struck out.

underline

A Boolean value specifying whether the text is underlined.

verticalalignment

The vertical alignment of the text: top, center, bottom, justify, distributed.

visible

A Boolean value specifying whether the text is visible.

Example

The following example sets a comment at row 10, column 2.

<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);
// Add rows to the spreadsheet
SpreadSheetAddRow(mySheet,"Order,First Name,Last Name,Address,Amount,City");
SpreadSheetAddRows(mySheet,ArtOrders);
// Set comment properties
mycomment=StructNew();
mycomment.author="John Doe";
mycomment.bold=true;
mycomment.color="blue";
mycomment.underline=true;
mycomment.comment="This is a comment.";
mycomment.linestyle="dotsys";
mycomment.linestylecolor="black";
mycomment.font="Adobe Clean";
// Write the comment to the specified location
SpreadsheetSetCellComment(mySheet,mycomment,10,2);
SpreadsheetWrite(mySheet,"#myFile#",true);
</cfscript>
<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); // Add rows to the spreadsheet SpreadSheetAddRow(mySheet,"Order,First Name,Last Name,Address,Amount,City"); SpreadSheetAddRows(mySheet,ArtOrders); // Set comment properties mycomment=StructNew(); mycomment.author="John Doe"; mycomment.bold=true; mycomment.color="blue"; mycomment.underline=true; mycomment.comment="This is a comment."; mycomment.linestyle="dotsys"; mycomment.linestylecolor="black"; mycomment.font="Adobe Clean"; // Write the comment to the specified location SpreadsheetSetCellComment(mySheet,mycomment,10,2); SpreadsheetWrite(mySheet,"#myFile#",true); </cfscript>
<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);
       // Add rows to the spreadsheet
       SpreadSheetAddRow(mySheet,"Order,First Name,Last Name,Address,Amount,City");
       SpreadSheetAddRows(mySheet,ArtOrders);
       // Set comment properties
       mycomment=StructNew();
       mycomment.author="John Doe";
       mycomment.bold=true;
       mycomment.color="blue";
       mycomment.underline=true;
       mycomment.comment="This is a comment.";
       mycomment.linestyle="dotsys";
       mycomment.linestylecolor="black";
       mycomment.font="Adobe Clean";
       // Write the comment to the specified location
       SpreadsheetSetCellComment(mySheet,mycomment,10,2);
       SpreadsheetWrite(mySheet,"#myFile#",true);
</cfscript>

Output

SpreadSheetSetCellComment output
SpreadSheetSetCellComment output

Get help faster and easier

New user?