Font Properties of Cell Comments

Aspose.Cells Product Family
1

Hi,

I am unable to access the font properties of a cell comment that has been added manually within Excel itself. To reproduce my scenario:

  1. Within Excel, add a comment to cell A1.
  2. Clear the default comment text (author) and add new text eg. 'Hello'
  3. Set the font size of the entire comment text to 8 points, the colour to red and non-bold.

Open that workbook in Aspose.Cells and instantiate the comment eg (in VB):

oComment=ws.comments(0,0)

Nowhere in the properties of oComment can I find IsBold=False and the correct font size, colour etc. I'm sure it's there somewhere but I can't find it!

The problem is not encountered when the comment is created by Aspose.Cells like this:

ws.Comments.Add(1, 0)
ws.Comments(1, 0).Note = "Hello"
ws.Comments(1, 0).Font.IsBold = False
ws.Comments(1, 0).Font.Color = Color.Fuchsia

Unfortunately my application needs to manipulate comments in a workbook that were created manually within Excel. I hope you can enlighten me!

Glynn

2

Hi Glynn,

Thanks for your posting and using Aspose.Cells.

Please provide us your sample Excel file in which you have manually created your comment with your desired font settings.

I am able to change the font name and font size of the comment but I am not able to assign font color to comment using MS-Excel 2010.

Please see the screenshot illustrating this issue for your reference.

3

Hi Shakeel,

Thanks for looking into my problem. I have attached a simple workbook created in Excel 2010 which has a comment on Sheet1!A1, in calibri 8pt non-bold. I simply need to be able to query the font properties of the comment class, but the object properties all show Tahoma 9pt bold. For example, in Visual Studio have a look at Comment.Font.Isbold (value = true), Comment.Font.Name (value = Tahoma) etc. for an instance of that comment. The font properties in Comment.CommentShape.Font have the same values too. The only property to show the correct font is Comment.HtmlNote and Comment.CommentShape.HtmlNote.

As I explained in my original post, I will not know in advance what font and font properties have been used for each comment. I can reliably retrieve the properties using native Excel automation with, for example:

ws.Cells(iRow, iCol).Comment.Shape.TextFrame.Characters.Font.Size

but I cannot do the same with Aspose.Cells without parsing the HtmlNote property. I've also tried accessing the font of the first character of the note using the Characters examples given elsewhere in the forum but have been unsuccessful here too.

I hope this additional information is helpful.

Many thanks,

Glynn

4

Shakeel,

To change the font colour of a comment in Excel 2010, edit comment, select the comment text, right-click and choose 'Format Comment' from the context menu. You don't use the main toolbar for this.

Glynn

5

Hi Glynn,

Thanks for your posting and using Aspose.Cells.

Please use Comment.GetCharacters(), it will return the array of FontSettings, you can the use it to find the font color, font name and font size of the runs.

Please see the following code and its output. I have also attached the sample Excel file used in this code for your reference.

C#


Workbook workbook = new Workbook(“sample.xlsx”);


Worksheet worksheet = workbook.Worksheets[0];


Comment comment = worksheet.Comments[0];


ArrayList characters = comment.GetCharacters();


for (int i = 0; i < characters.Count; i++)

{

FontSetting fntSetting = characters[i] as FontSetting;


string text = comment.Note.Substring(fntSetting.StartIndex, fntSetting.Length);


Debug.WriteLine("Text: " + text);

Debug.WriteLine("Font Name: " + fntSetting.Font.Name);

Debug.WriteLine(“Font Size: " + fntSetting.Font.Size);

Debug.WriteLine(“Font Color: " + fntSetting.Font.Color);


Debug.WriteLine(”------------------------”);

}


Debug Output:
Text: Testing comment
Font Name: Arial Unicode MS
Font Size: 18
Font Color: Color [A=255, R=0, G=255, B=255]
------------------------
Text:

Font Name: Tahoma
Font Size: 8
Font Color: Color [Black]
------------------------
Text: some more text.
Font Name: Arial Black
Font Size: 8
Font Color: Color [A=255, R=255, G=0, B=0]
------------------------

6

Works like a charm! I appreciate your help, Shakeel.