none
Dynamic comments in Excel using Open XML

    Question

  • Is there a way to insert comments programmatically to an Excel template using Openxml?If I know the name of cell to which I have to insert a comment?

    • Moved by CoolDadTxMVP Thursday, May 01, 2014 3:00 PM Office related
    Thursday, May 01, 2014 9:23 AM

Answers

  • Hi,

    Based on the description, you want to add a comment programmatically. We can compared two files like figure below(one file was added comment, the other wasn’t):

    Then we can get the difference between two files, and I write a sample for you reference:
     

    public class InsertComment
    
        {
    
            public void AddComment(string sheetName, string comment,string cell,int cellRowIndex, int cellColumnIndex)
    
            {
    
    
                const string fileName = @"C:\Users\v-fexue\Desktop\Test.xlsx";
    
    
    
                // Open the spreadsheet document for read-only access.
    
                using (SpreadsheetDocument document =
    
                    SpreadsheetDocument.Open(fileName, true))
    
                {
    
                    // Retrieve a reference to the workbook part.
    
                    WorkbookPart wbPart = document.WorkbookPart;
    
    
    
                    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
    
       Where(s => s.Name == sheetName).FirstOrDefault();
    
                    WorksheetPart wsPart =
    
        (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    
    
    
                    WorksheetCommentsPart worksheetCommentsPart1 = wsPart.AddNewPart<WorksheetCommentsPart>("rId2");
    
                    GenerateWorksheetCommentsPart1Content(worksheetCommentsPart1, comment, cell);
    
    
                    VmlDrawingPart vmlDrawingPart1 = wsPart.AddNewPart<VmlDrawingPart>("rId1");
    
                    GenerateVmlDrawingPart1Content(vmlDrawingPart1,  cellRowIndex,  cellColumnIndex);
    
    
                    LegacyDrawing legacyDrawing1 = new LegacyDrawing() { Id = "rId1" };
    
                    wsPart.Worksheet.Append(legacyDrawing1);
    
                }
    
            }
    
    
            // Generates content of worksheetCommentsPart1.
    
            private void GenerateWorksheetCommentsPart1Content(WorksheetCommentsPart worksheetCommentsPart1, string comment,string cell)
    
            {
    
                Comments comments1 = new Comments();
    
    
                Authors authors1 = new Authors();
    
                Author author1 = new Author();
    
                author1.Text = "Author";
    
    
                authors1.Append(author1);
    
    
                CommentList commentList1 = new CommentList();
    
    
                Comment comment1 = new Comment() { Reference = cell, AuthorId = (UInt32Value)0U, ShapeId = (UInt32Value)0U };
    
    
                CommentText commentText1 = new CommentText();
    
    
                Run run1 = new Run();
    
    
                RunProperties runProperties1 = new RunProperties();
    
                FontSize fontSize1 = new FontSize() { Val = 9D };
    
                Color color1 = new Color() { Indexed = (UInt32Value)81U };
    
                RunFont runFont1 = new RunFont() { Val = "Tahoma" };
    
                RunPropertyCharSet runPropertyCharSet1 = new RunPropertyCharSet() { Val = 1 };
    
    
                runProperties1.Append(fontSize1);
    
                runProperties1.Append(color1);
    
                runProperties1.Append(runFont1);
    
                runProperties1.Append(runPropertyCharSet1);
    
                Text text1 = new Text();
    
                text1.Text = comment;
    
    
                run1.Append(runProperties1);
    
                run1.Append(text1);
    
    
                commentText1.Append(run1);
    
    
                comment1.Append(commentText1);
    
    
                commentList1.Append(comment1);
    
    
                comments1.Append(authors1);
    
                comments1.Append(commentList1);
    
    
                worksheetCommentsPart1.Comments = comments1;
    
            }
    
    
            // Generates content of vmlDrawingPart1.
    
            private void GenerateVmlDrawingPart1Content(VmlDrawingPart vmlDrawingPart1, int cellRowIndex, int cellColumnIndex)
    
            {
    
                System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(vmlDrawingPart1.GetStream(System.IO.FileMode.Create), System.Text.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><v:shape id=\"_x0000_s1025\" type=\"#_x0000_t202\" style=\'\r\n  margin-margin-width:108pt;height:59.25pt;\r\n  #ffffe1\" o:insetmode=\"auto\">\r\n  <v:fill color2=\"#ffffe1\"/>\r\n  <v:shadow color=\"black\" obscured=\"t\"/>\r\n  <v:path o:connecttype=\"none\"/>\r\n  <v:textbox style=\'\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    5, 15, 7, 10, 7, 31, 11, 9</x:Anchor>\r\n   <x:AutoFill>False</x:AutoFill>\r\n   <x:Row>" + cellRowIndex + "</x:Row>\r\n   <x:Column>" + cellColumnIndex + "</x:Column>\r\n  </x:ClientData>\r\n </v:shape></xml>");
    
                writer.Flush();
    
                writer.Close();
    
            }
    
    
    
    }
    
    
    static void Main(string[] args)
    
            {
    
                new InsertComment().AddComment("Sheet1","HelloWord11","E11",10,4);
    
            }
    

    Best regards

    Fei
     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 02, 2014 9:49 AM

All replies

  • Hi,

    The code part  VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>(); from the below given link is not working for me.I'm getting an exception saying that "only one instance of the type is allowed for this parent". Can anyone help??

     

    http://social.msdn.microsoft.com/forums/office/en-US/40a95862-9adc-492d-a046-97a5e6e20260/how-to-insert-comments-in-excel-using-openxml

    Thanks

    Thursday, May 01, 2014 9:13 AM
  • From the error message, it seems you reference the a part for mutiple times. Wolud you minding provide more code snippet?

    And please check link below wether it is helpful:
    http://stackoverflow.com/questions/3494856/how-to-add-a-comment-to-a-cell-in-excel-2007-using-the-open-xml-sdk-2-0

    Another way is you can operate Excel file manually and then you can use Open XML Producitivity Tools which contains from Open XML SDK to see the code generated by Excel application like below:

    Friday, May 02, 2014 1:44 AM
  • Hi Mr.BruseBoBo,

    Thanks for your reply.I'm checking on the code you provided.

    The method i'm using for adding comments is given below.

      public static void InsertComments(WorksheetPart worksheetPart, List<string> ColumnName, List<string> CellIndex, List<string> NewCommentList)

            {

                try

                {

                    if ((ColumnName.Count > 0 && CellIndex.Count > 0 && NewCommentList.Count > 0))

                    {

                        string commentsVmlXml = string.Empty;

                        // Create all the comment VML Shape XML

                        for (var i = 0; i < ColumnName.Count; i++)

                        {

                            commentsVmlXml += GetCommentVMLShapeXML(ColumnName[i], CellIndex[i]);

                        }

     

                           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

     

     

     

                        for (var j = 0; j < NewCommentList.Count; j++)

                        {

                            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 = ColumnName[j] + CellIndex[j], 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 = NewCommentList[j];

     

                            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;

                            }

                        }

     

                    }

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

    Thanks

    Friday, May 02, 2014 4:17 AM
  • Hi Mr.BruseBoBo,

    Comments are getting added.Now the problem is my Excel Template is getting corrupted and while opening the excel file  the Drawing part is getting removed.Do you have any idea why this is happening?

    Thanks,

    Elizabeth Abraham

    Friday, May 02, 2014 5:02 AM
  • Hi,

    Based on the description, you want to add a comment programmatically. We can compared two files like figure below(one file was added comment, the other wasn’t):

    Then we can get the difference between two files, and I write a sample for you reference:
     

    public class InsertComment
    
        {
    
            public void AddComment(string sheetName, string comment,string cell,int cellRowIndex, int cellColumnIndex)
    
            {
    
    
                const string fileName = @"C:\Users\v-fexue\Desktop\Test.xlsx";
    
    
    
                // Open the spreadsheet document for read-only access.
    
                using (SpreadsheetDocument document =
    
                    SpreadsheetDocument.Open(fileName, true))
    
                {
    
                    // Retrieve a reference to the workbook part.
    
                    WorkbookPart wbPart = document.WorkbookPart;
    
    
    
                    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
    
       Where(s => s.Name == sheetName).FirstOrDefault();
    
                    WorksheetPart wsPart =
    
        (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    
    
    
                    WorksheetCommentsPart worksheetCommentsPart1 = wsPart.AddNewPart<WorksheetCommentsPart>("rId2");
    
                    GenerateWorksheetCommentsPart1Content(worksheetCommentsPart1, comment, cell);
    
    
                    VmlDrawingPart vmlDrawingPart1 = wsPart.AddNewPart<VmlDrawingPart>("rId1");
    
                    GenerateVmlDrawingPart1Content(vmlDrawingPart1,  cellRowIndex,  cellColumnIndex);
    
    
                    LegacyDrawing legacyDrawing1 = new LegacyDrawing() { Id = "rId1" };
    
                    wsPart.Worksheet.Append(legacyDrawing1);
    
                }
    
            }
    
    
            // Generates content of worksheetCommentsPart1.
    
            private void GenerateWorksheetCommentsPart1Content(WorksheetCommentsPart worksheetCommentsPart1, string comment,string cell)
    
            {
    
                Comments comments1 = new Comments();
    
    
                Authors authors1 = new Authors();
    
                Author author1 = new Author();
    
                author1.Text = "Author";
    
    
                authors1.Append(author1);
    
    
                CommentList commentList1 = new CommentList();
    
    
                Comment comment1 = new Comment() { Reference = cell, AuthorId = (UInt32Value)0U, ShapeId = (UInt32Value)0U };
    
    
                CommentText commentText1 = new CommentText();
    
    
                Run run1 = new Run();
    
    
                RunProperties runProperties1 = new RunProperties();
    
                FontSize fontSize1 = new FontSize() { Val = 9D };
    
                Color color1 = new Color() { Indexed = (UInt32Value)81U };
    
                RunFont runFont1 = new RunFont() { Val = "Tahoma" };
    
                RunPropertyCharSet runPropertyCharSet1 = new RunPropertyCharSet() { Val = 1 };
    
    
                runProperties1.Append(fontSize1);
    
                runProperties1.Append(color1);
    
                runProperties1.Append(runFont1);
    
                runProperties1.Append(runPropertyCharSet1);
    
                Text text1 = new Text();
    
                text1.Text = comment;
    
    
                run1.Append(runProperties1);
    
                run1.Append(text1);
    
    
                commentText1.Append(run1);
    
    
                comment1.Append(commentText1);
    
    
                commentList1.Append(comment1);
    
    
                comments1.Append(authors1);
    
                comments1.Append(commentList1);
    
    
                worksheetCommentsPart1.Comments = comments1;
    
            }
    
    
            // Generates content of vmlDrawingPart1.
    
            private void GenerateVmlDrawingPart1Content(VmlDrawingPart vmlDrawingPart1, int cellRowIndex, int cellColumnIndex)
    
            {
    
                System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(vmlDrawingPart1.GetStream(System.IO.FileMode.Create), System.Text.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><v:shape id=\"_x0000_s1025\" type=\"#_x0000_t202\" style=\'\r\n  margin-margin-width:108pt;height:59.25pt;\r\n  #ffffe1\" o:insetmode=\"auto\">\r\n  <v:fill color2=\"#ffffe1\"/>\r\n  <v:shadow color=\"black\" obscured=\"t\"/>\r\n  <v:path o:connecttype=\"none\"/>\r\n  <v:textbox style=\'\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    5, 15, 7, 10, 7, 31, 11, 9</x:Anchor>\r\n   <x:AutoFill>False</x:AutoFill>\r\n   <x:Row>" + cellRowIndex + "</x:Row>\r\n   <x:Column>" + cellColumnIndex + "</x:Column>\r\n  </x:ClientData>\r\n </v:shape></xml>");
    
                writer.Flush();
    
                writer.Close();
    
            }
    
    
    
    }
    
    
    static void Main(string[] args)
    
            {
    
                new InsertComment().AddComment("Sheet1","HelloWord11","E11",10,4);
    
            }
    

    Best regards

    Fei
     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 02, 2014 9:49 AM