Comments (Notes)
You can add/edit/delete Comments (in later Excel versions called Notes) either via the ExcelRange class or via the Comments Property on the ExcelWorksheet class.
EPPlus supports Comments/Notes with plain as well as richtext, see examples below.
When adding/editing comments you will be working with the OfficeOpenXml.ExcelComment class. Here are the most important properties of this class:
| Property | Datatype | Description |
|---|---|---|
Author |
string |
Author of the comment |
Text |
string |
Plain text of the comment |
RichText |
ExcelRichTextCollection |
Richtext (set Font, Font size/style, Color, etc on the whole text or different parts of it) of the comment, see the Richtext section below |
From |
ExcelVmlDrawingPosition |
From position. For comments only when Visible=true |
To |
ExcelVmlDrawingPosition |
To position. For comments only when Visible=true |
Autofit |
bool |
Autofits the comment |
BackgroundColor |
System.Drawing.Color |
Background color of the comment |
LineStyle |
eLineStyleVml |
Border style |
LineColor |
System.Drawing.Color |
Border color |
LineWidth |
float |
Border width |
Available via the ExcelComment.RichText property, which is of the type OfficeOpenXml.Style.ExcelRichTextCollection.
| Property | Datatype | Description |
|---|---|---|
Text |
string |
The plain text |
HtmlText |
string |
Returns the rich text as html |
Count |
int |
Number of items in the collection |
| Method | Return type | Description |
|---|---|---|
Add(string text, bool NewParagraph=false) |
ExcelRichText |
Adds a rich text string to the collection |
Clear() |
void |
Removes all items from the collection |
Remove(ExcelRichText item) |
void |
Removes an item from the collection |
RemoveAt(int index) |
void |
Removes an item from the collection by its index |
Insert(int index, string text) |
ExcelRichText |
Inserts an item into the collection |
this[int index] |
ExcelRichText |
Returns an item by its index |
Namespace: OfficeOpenXml.Style
| Property | Datatype | Description |
|---|---|---|
Text |
string |
The plain text |
HtmlText |
string |
Returns the rich text as html |
Color |
System.Drawing.Color |
Color of the text |
Size |
float |
Size of the font |
Bold |
bool |
If true the text is bold |
Italic |
bool |
If true the text is italic |
Underline |
bool |
If true the text will be underlined |
Strike |
bool |
If true the text will be striked out |
FontName |
string |
Name of the font |
PreserveSpace |
bool |
Preserves whitespace. Default is true |
using(var package = new ExcelPackage(@"c:\temp\commentstest.xlsx"))
{
var ws = package.Workbook.Worksheets.Add("comments");
//Adds comments using the range class
var comment = ws.Cells["A3"].AddComment("Jan Källman:\r\n", "JK");
comment.Font.Bold = true;
var rt = comment.RichText.Add("Here is some text that isn't bold...");
rt.Bold = false;
rt.FontName = "Helvetica";
rt.Color = Color.Purple;
comment.AutoFit = true;
//Adds a comment using the worksheet's Comments collection
var comment2 = ws.Comments.Add(ws.Cells["B3"],"Jan Källman:", "JK");
//This sets the size and position. (The position is used when the comment is visible and AutoFit isn't set to true)
comment2.From.Column = 7;
comment2.From.Row = 3;
comment2.To.Column = 16;
comment2.To.Row = 8;
comment2.BackgroundColor = Color.White;
comment2.RichText.Add("\r\nThis comment has white background and size/position set...\r\n");
}
The code below is using the workbook that was created in the Add new comments section above.
using (var package = new ExcelPackage(@"c:\temp\commentstest.xlsx"))
{
var ws = package.Workbook.Worksheets["comments"];
// Edit Comment using the range class
var comment = ws.Cells["A3"].Comment;
comment.RichText[0].Color = Color.DarkGreen;
comment.RichText[1].Color = Color.Blue;
package.Save();
}
You can also edit comments via the Comments collection on the worksheet.
using (var package = new ExcelPackage(@"c:\temp\commentstest.xlsx"))
{
var ws = package.Workbook.Worksheets["comments"];
// Edit Comment using the Comments collection on the worksheet
var comment = ws.Comments["A3"];
comment.RichText[0].Color = Color.DarkRed;
comment.RichText[1].Color = Color.Orange;
package.Save();
}
The code below is using the workbook that was created in the Add new comments section above.
using (var package = new ExcelPackage(@"c:\temp\commentstest.xlsx"))
{
var ws = package.Workbook.Worksheets["comments"];
// Remove comment in cell B3 using the Comments collection on worksheet.
ws.Comments.Remove(ws.Comments["B3"]);
package.Save();
}