All geek questions in one place

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

Has anyone managed to figure out how to add a comment in Excel using the Open XML SDK 2.0? I could not find documentation on where to start this problem.

+3
source share
2 answers

Below is the code to which you want to add comments, and then iterate over the dictionary of comments. The dictionary key is a cell reference (for example, 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;
                }
            }
        }
    }

A helper method that will create VML XML for the form:

    /// <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 a comment Form:

    /// <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;
    }

, , .

+14

" " / " " OpenXML.

, OpenXML ( ) 3- ( ClosedXML).

3- , : http://social.msdn.microsoft.com/Forums/office/en-US/81f767d0-15ac-42fe-b122-6c5c02b6c373/cell-color-and-add-comment?forum=oxmlsdk

"Unchanged.xlsx", , # Open XML SaveAs, "Changed.xlsx". SDK Open XML, , , , Open XML SDK #.

, , , . :

enter image description here

, //. .

+1

Source: https://habr.com/ru/post/1759912/

More articles:


All Articles