Show / Hide Table of Contents

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();
}
Back to top Generated by DocFX