How to add a comment to a cell in Excel 2007 using the Open XML SDK 2.0?

Question

Has anyone ever had any luck figuring out how to add a comment to Excel using the Open XML SDK 2.0? I couldn't find any documentation on where to get started on this issue.

Exserviceman

Answer 1

The below code will take the worksheet that you want to add comments to and then iterate over the commentsToAdd dictionary. The dictionary key is the cell reference (ie. A1) and the value is the comment text to be added.

    /// <summary>
    /// Adds all the comments defined in the commentsToAddDict dictionary to the worksheet
    /// </summary>
    /// <param name="worksheetPart">Worksheet Part</param>
    /// <param name="commentsToAddDict">Dictionary of cell references as the key (ie. A1) and the comment text as the value</param>
    public static void InsertComments(WorksheetPart worksheetPart, Dictionary<string, string> commentsToAddDict)
    {
        if (commentsToAddDict.Any())
        {
            string commentsVmlXml = string.Empty;

            // Create all the comment VML Shape XML
            foreach (var commentToAdd in commentsToAddDict)
            {
                commentsVmlXml += GetCommentVMLShapeXML(GetColumnName(commentToAdd.Key), GetRowIndex(commentToAdd.Key).ToString());
            }                       

            // The VMLDrawingPart should contain all the definitions for how to draw every comment shape for the worksheet
            VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>();
            using (XmlTextWriter writer = new XmlTextWriter(vmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8))
            {

                writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n <o:shapelayout v:ext=\"edit\">\r\n  <o:idmap v:ext=\"edit\" data=\"1\"/>\r\n" +
                "</o:shapelayout><v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\"\r\n  path=\"m,l,21600r21600,l21600,xe\">\r\n  <v:stroke joinstyle=\"miter\"/>\r\n  <v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>\r\n </v:shapetype>"
                + commentsVmlXml + "</xml>");
            }

            // Create the comment elements
            foreach (var commentToAdd in commentsToAddDict)
            {
                WorksheetCommentsPart worksheetCommentsPart = worksheetPart.WorksheetCommentsPart ?? worksheetPart.AddNewPart<WorksheetCommentsPart>();                 

                // We only want one legacy drawing element per worksheet for comments
                if (worksheetPart.Worksheet.Descendants<LegacyDrawing>().SingleOrDefault() == null)
                {
                    string vmlPartId = worksheetPart.GetIdOfPart(vmlDrawingPart);
                    LegacyDrawing legacyDrawing = new LegacyDrawing() { Id = vmlPartId };
                    worksheetPart.Worksheet.Append(legacyDrawing);
                }

                Comments comments;
                bool appendComments = false;
                if (worksheetPart.WorksheetCommentsPart.Comments != null)
                {
                    comments = worksheetPart.WorksheetCommentsPart.Comments;
                }
                else
                {
                    comments = new Comments();
                    appendComments = true;
                }

                // We only want one Author element per Comments element
                if (worksheetPart.WorksheetCommentsPart.Comments == null)
                {
                    Authors authors = new Authors();
                    Author author = new Author();
                    author.Text = "Author Name";
                    authors.Append(author);
                    comments.Append(authors);
                }

                CommentList commentList;
                bool appendCommentList = false;
                if (worksheetPart.WorksheetCommentsPart.Comments != null &&
                    worksheetPart.WorksheetCommentsPart.Comments.Descendants<CommentList>().SingleOrDefault() != null)
                {
                    commentList = worksheetPart.WorksheetCommentsPart.Comments.Descendants<CommentList>().Single();
                }
                else
                {
                    commentList = new CommentList();
                    appendCommentList = true;
                }

                Comment comment = new Comment() { Reference = commentToAdd.Key, AuthorId = (UInt32Value)0U };

                CommentText commentTextElement = new CommentText();

                Run run = new Run();

                RunProperties runProperties = new RunProperties();
                Bold bold = new Bold();
                FontSize fontSize = new FontSize() { Val = 8D };
                Color color = new Color() { Indexed = (UInt32Value)81U };
                RunFont runFont = new RunFont() { Val = "Tahoma" };
                RunPropertyCharSet runPropertyCharSet = new RunPropertyCharSet() { Val = 1 };

                runProperties.Append(bold);
                runProperties.Append(fontSize);
                runProperties.Append(color);
                runProperties.Append(runFont);
                runProperties.Append(runPropertyCharSet);
                Text text = new Text();
                text.Text = commentToAdd.Value;

                run.Append(runProperties);
                run.Append(text);

                commentTextElement.Append(run);
                comment.Append(commentTextElement);
                commentList.Append(comment);

                // Only append the Comment List if this is the first time adding a comment
                if (appendCommentList)
                {
                    comments.Append(commentList);
                }

                // Only append the Comments if this is the first time adding Comments
                if (appendComments)
                {
                    worksheetCommentsPart.Comments = comments;
                }
            }
        }
    }

Helper method that will create the VML XML for the Shape:

    /// <summary>
    /// Creates the VML Shape XML for a comment. It determines the positioning of the
    /// comment in the excel document based on the column name and row index.
    /// </summary>
    /// <param name="columnName">Column name containing the comment</param>
    /// <param name="rowIndex">Row index containing the comment</param>
    /// <returns>VML Shape XML for a comment</returns>
    private static string GetCommentVMLShapeXML(string columnName, string rowIndex)
    {
        string commentVmlXml = string.Empty;

        // Parse the row index into an int so we can subtract one
        int commentRowIndex;
        if (int.TryParse(rowIndex, out commentRowIndex))
        {
            commentRowIndex -= 1;

            commentVmlXml = "<v:shape id=\"" +  Guid.NewGuid().ToString().Replace("-", "") + "\" type=\"#_x0000_t202\" style=\'position:absolute;\r\n  margin-left:59.25pt;margin-top:1.5pt;width:96pt;height:55.5pt;z-index:1;\r\n  visibility:hidden\' fillcolor=\"#ffffe1\" o:insetmode=\"auto\">\r\n  <v:fill color2=\"#ffffe1\"/>\r\n" +
            "<v:shadow on=\"t\" color=\"black\" obscured=\"t\"/>\r\n  <v:path o:connecttype=\"none\"/>\r\n  <v:textbox style=\'mso-fit-shape-to-text:true'>\r\n   <div style=\'text-align:left\'></div>\r\n  </v:textbox>\r\n  <x:ClientData ObjectType=\"Note\">\r\n   <x:MoveWithCells/>\r\n" +
            "<x:SizeWithCells/>\r\n   <x:Anchor>\r\n" + GetAnchorCoordinatesForVMLCommentShape(columnName, rowIndex) + "</x:Anchor>\r\n   <x:AutoFill>False</x:AutoFill>\r\n   <x:Row>" + commentRowIndex + "</x:Row>\r\n   <x:Column>" + GetColumnIndexFromName(columnName) + "</x:Column>\r\n  </x:ClientData>\r\n </v:shape>";
        }

        return commentVmlXml;
    }

Helpers to figure out the Column Index and coordinates for the comment Shape:

    /// <summary>
    /// Gets the coordinates for where on the excel spreadsheet to display the VML comment shape
    /// </summary>
    /// <param name="columnName">Column name of where the comment is located (ie. B)</param>
    /// <param name="rowIndex">Row index of where the comment is located (ie. 2)</param>
    /// <returns><see cref="<x:Anchor>"/> coordinates in the form of a comma separated list</returns>
    private static string GetAnchorCoordinatesForVMLCommentShape(string columnName, string rowIndex)
    {
        string coordinates = string.Empty;
        int startingRow = 0;
        int startingColumn = GetColumnIndexFromName(columnName).Value;

        // From (upper right coordinate of a rectangle)
        // [0] Left column
        // [1] Left column offset
        // [2] Left row
        // [3] Left row offset
        // To (bottom right coordinate of a rectangle)
        // [4] Right column
        // [5] Right column offset
        // [6] Right row
        // [7] Right row offset
        List<int> coordList = new List<int>(8) { 0, 0, 0, 0, 0, 0, 0, 0};

        if (int.TryParse(rowIndex, out startingRow))
        {
            // Make the row be a zero based index
            startingRow -= 1;

            coordList[0] = startingColumn + 1; // If starting column is A, display shape in column B
            coordList[1] = 15;
            coordList[2] = startingRow;
            coordList[4] = startingColumn + 3; // If starting column is A, display shape till column D
            coordList[5] = 15;
            coordList[6] = startingRow + 3; // If starting row is 0, display 3 rows down to row 3

            // The row offsets change if the shape is defined in the first row
            if (startingRow == 0)
            {
                coordList[3] = 2;
                coordList[7] = 16;
            }
            else
            {
                coordList[3] = 10;
                coordList[7] = 4;
            }

            coordinates = string.Join(",", coordList.ConvertAll<string>(x => x.ToString()).ToArray());
        }

        return coordinates;
    }

    /// <summary>
    /// Given just the column name (no row index), it will return the zero based column index.
    /// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ). 
    /// A length of three can be implemented when needed.
    /// </summary>
    /// <param name="columnName">Column Name (ie. A or AB)</param>
    /// <returns>Zero based index if the conversion was successful; otherwise null</returns>
    public static int? GetColumnIndexFromName(string columnName)
    {
        int? columnIndex = null;

        string[] colLetters = Regex.Split(columnName, "([A-Z]+)");
        colLetters = colLetters.Where(s => !string.IsNullOrEmpty(s)).ToArray();

        if (colLetters.Count() <= 2)
        {
            int index = 0;
            foreach (string col in colLetters)
            {
                List<char> col1 = colLetters.ElementAt(index).ToCharArray().ToList();
                int? indexValue = Letters.IndexOf(col1.ElementAt(index));

                if (indexValue != -1)
                {
                    // The first letter of a two digit column needs some extra calculations
                    if (index == 0 && colLetters.Count() == 2)
                    {
                        columnIndex = columnIndex == null ? (indexValue + 1) * 26 : columnIndex + ((indexValue + 1) * 26);
                    }
                    else
                    {
                        columnIndex = columnIndex == null ? indexValue : columnIndex + indexValue;
                    }
                }

                index++;
            }
        }

        return columnIndex;
    }

Don't forget to save your worksheet and workbook once you are done in order to see the changes.

Exserviceman

It would be really helpful to know which libraries are being used. Also I can not find out what the Letters object is. – Ostensive

Works for me! (thanks @amurra) @Andrew --> List<char> Letters = new List<char>(){'A','B','C','D','E','F','G','H','I','J'}; More letters can be implemented as needed :) – Racklin

The methods GetColumnName and GetRowIndex is not defined! – Summon

@Exserviceman how to insert a line break inside the Comments – Duff

This is great. I made some modifications -- but overall, super helpful. Thank you! – Larios

Answer 2

Many people ask "how to do this" / "how to do that" using OpenXML.

The most frequent answer say that OpenXML is painful to work with (I agree) refer to 3-rd party library (specifically ClosedXML).

If you are not using a 3-rd party library then I would like to answer with a general tip based on this thread: http://social.msdn.microsoft.com/Forums/office/en-US/81f767d0-15ac-42fe-b122-6c5c02b6c373/cell-color-and-add-comment?forum=oxmlsdk

You can create a empty workbook named "Unchanged.xlsx", then do the customization which you want to reflect to C# code in Open XML SaveAs the changed workbook named as "Changed.xlsx". Now open the Open XML SDK tool, use compare files feature, you then can see the changes made to the workbook and how can they been done with Open XML SDK via C#.

There is an option reflect code that gives you a lot of hints about what is going on, see (incomplete) example below:

As it is autogenerated code it can be simplified / inlined / reduced. After a week or two you'll get into speed.

Racklin