Insert and Format Comments with OpenXML SDK
The example shows how to insert a comment and set its border color to Lime with OpenXML SDK.
Output: CommentsFormatting.xlsx
public void Run()
{
using (var spreadsheetDocument = SpreadsheetDocument.Create("CommentsFormatting.xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet();
worksheetPart.Worksheet = worksheet;
VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>();
WorksheetCommentsPart worksheetCommentsPart = worksheetPart.AddNewPart<WorksheetCommentsPart>();
LegacyDrawing legacyDrawing = new LegacyDrawing() { Id = worksheetPart.GetIdOfPart(vmlDrawingPart) };
worksheetPart.Worksheet.SheetDimension = new SheetDimension(){Reference = "A1:D5"};
worksheetPart.Worksheet.Append(new SheetData());
worksheetPart.Worksheet.Append(legacyDrawing);
var comments = new Comments();
Authors authors = new Authors();
Author author = new Author();
author.Text = "DR-IT";
authors.Append(author);
comments.Append(authors);
CommentList commentList = new CommentList();
Comment comment = new Comment() { Reference = "A2", AuthorId = (UInt32Value)0U };
CommentText commentTextElement = new CommentText();
Run run = new Run();
RunProperties runProperties = new RunProperties();
FontSize fontSize = new FontSize() { Val = 9D };
Color color = new Color() { Rgb = new HexBinaryValue("FF000000") };
var family = new FontFamily(){Val = 2};
RunFont runFont = new RunFont() { Val = "Tahoma" };
runProperties.Append(fontSize);
runProperties.Append(color);
runProperties.Append(runFont);
runProperties.Append(family);
Text text = new Text();
text.Text = "Comment";
run.Append(runProperties);
run.Append(text);
commentTextElement.Append(run);
comment.Append(commentTextElement);
commentList.Append(comment);
comments.Append(commentList);
worksheetCommentsPart.Comments = comments;
worksheetCommentsPart.Comments.Save();
this.BuildVmlDrawingPart(vmlDrawingPart);
worksheetPart.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = workbookPart.GetIdOfPart(worksheetPart);
sheets.Append(sheet);
workbook.Append(sheets);
spreadsheetDocument.WorkbookPart.Workbook = workbook;
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
}
internal void BuildVmlDrawingPart(VmlDrawingPart vmlDrawingPart)
{
var writer = new XmlTextWriter(vmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8);
writer.WriteStartElement("xml");
var shapeType = new DocumentFormat.OpenXml.Vml.Shapetype();
shapeType.Id = "_x0000_t202";
shapeType.CoordinateSize = "21600,21600";
shapeType.OptionalNumber = 202;
shapeType.EdgePath = "m,l,21600r21600,l21600,xe";
var stroke = new DocumentFormat.OpenXml.Vml.Stroke();
stroke.JoinStyle = new EnumValue<StrokeJoinStyleValues>(StrokeJoinStyleValues.Miter);
var path = new DocumentFormat.OpenXml.Vml.Path();
path.AllowGradientShape = TrueFalseValue.FromBoolean(true);
path.ConnectionPointType = new EnumValue<ConnectValues>(ConnectValues.Rectangle);
shapeType.Append(stroke);
shapeType.Append(path);
var shape = new DocumentFormat.OpenXml.Vml.Shape();
shape.Id = "_x0000_s1025";
shape.Style =
"position:absolute;margin-left:86.25pt;margin-top:1.5pt;width:60pt;height:37.5pt;z-index:1;visibility:visible;mso-wrap-style:tight";
shape.InsetMode = new EnumValue<InsetMarginValues>(InsetMarginValues.Auto);
shape.FillColor = "#FFFFE1";
shape.StrokeColor = "#00ff00";
shape.StrokeWeight = "0.75pt";
shape.Type = "#_x0000_t202";
shape.Append(new Vml.Fill(){Color2 = "#FFFFE1" });
shape.Append(new Vml.Stroke() { LineStyle = new EnumValue<StrokeLineStyleValues>(StrokeLineStyleValues.Single),DashStyle = "solid"});
shape.Append(new Shadow
{On = TrueFalseValue.FromBoolean(true), Obscured = TrueFalseValue.FromBoolean(true), Color = "black"});
shape.Append(new Vml.Path() { ConnectionPointType = new EnumValue<ConnectValues>(ConnectValues.None) });
var textBox = new Vml.TextBox();
shape.Append(textBox);
var clientData = new Vml.Spreadsheet.ClientData();
//Set the Note Type
clientData.ObjectType = new EnumValue<ObjectValues>(ObjectValues.Note);
clientData.Append(new MoveWithCells("True"));
clientData.Append(new ResizeWithCells("True"));
clientData.Append(new Anchor("1, 15, 0, 8, 3, 33, 4, 7"));
clientData.Append(new AutoFill("False"));
clientData.Append(new CommentRowTarget("1"));
clientData.Append(new CommentColumnTarget("0"));
clientData.Append(new Visible());
shape.Append(clientData);
shapeType.WriteTo(writer);
shape.WriteTo(writer);
writer.WriteEndElement();
writer.Flush();
writer.Close();
}