// Generates content of worksheetCommentsPart1.
        private void GenerateWorksheetCommentsPart1Content(WorksheetCommentsPart worksheetCommentsPart1)
        {
            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 = "V10", AuthorId = (UInt32Value)0U, ShapeId = (UInt32Value)0U };

            CommentText commentText1 = new CommentText();

            Run run14 = new Run();

            RunProperties runProperties14 = new RunProperties();
            Bold bold1 = new Bold();
            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 };

            runProperties14.Append(bold1);
            runProperties14.Append(fontSize1);
            runProperties14.Append(color1);
            runProperties14.Append(runFont1);
            runProperties14.Append(runPropertyCharSet1);
            Text text14 = new Text();
            text14.Text = "Author:";

            run14.Append(runProperties14);
            run14.Append(text14);

            Run run15 = new Run();

            RunProperties runProperties15 = new RunProperties();
            FontSize fontSize2 = new FontSize() { Val = 9D };
            Color color2 = new Color() { Indexed = (UInt32Value)81U };
            RunFont runFont2 = new RunFont() { Val = "Tahoma" };
            RunPropertyCharSet runPropertyCharSet2 = new RunPropertyCharSet() { Val = 1 };

            runProperties15.Append(fontSize2);
            runProperties15.Append(color2);
            runProperties15.Append(runFont2);
            runProperties15.Append(runPropertyCharSet2);
            Text text15 = new Text() { Space = SpaceProcessingModeValues.Preserve };
            text15.Text = "\nThis is a comment";

            run15.Append(runProperties15);
            run15.Append(text15);

            commentText1.Append(run14);
            commentText1.Append(run15);

            comment1.Append(commentText1);

            commentList1.Append(comment1);

            comments1.Append(authors1);
            comments1.Append(commentList1);

            worksheetCommentsPart1.Comments = comments1;
        }
        private static void GenerateWorksheetCommentsPartContent(WorksheetCommentsPart worksheetCommentsPart,
            XLWorksheet xlWorksheet)
        {
            var comments = new Comments();
            var commentList = new CommentList();
            var authorsDict = new Dictionary<String, Int32>();
            foreach (var c in xlWorksheet.Internals.CellsCollection.GetCells(c => c.HasComment))
            {
                var comment = new Comment {Reference = c.Address.ToStringRelative()};
                var authorName = c.Comment.Author;

                Int32 authorId;
                if (!authorsDict.TryGetValue(authorName, out authorId))
                {
                    authorId = authorsDict.Count;
                    authorsDict.Add(authorName, authorId);
                }
                comment.AuthorId = (UInt32)authorId;

                var commentText = new CommentText();
                foreach (var rt in c.Comment)
                {
                    commentText.Append(GetRun(rt));
                }

                comment.Append(commentText);
                commentList.Append(comment);
            }

            var authors = new Authors();
            foreach (var author in authorsDict.Select(a => new Author {Text = a.Key}))
            {
                authors.Append(author);
            }
            comments.Append(authors);
            comments.Append(commentList);

            worksheetCommentsPart.Comments = comments;
        }
        /// <summary>
        /// Adds all the comments defined in the List to the current worksheet.
        /// </summary>
        /// <param name="worksheetPart">Worksheet Part of file.</param>
        /// <param name="commentsToAddList">List of CellComment which contain cell coordinates and the text value to set as comment.</param>
        public static void InsertComments(WorksheetPart worksheetPart, List <CellComment> commentsToAddList)
        {
            if (commentsToAddList.Any())
            {
                string commentsVmlXml = string.Empty;

                // Create all the comment VML Shape XML
                foreach (var commentToAdd in commentsToAddList)
                {
                    commentsVmlXml += GetCommentVMLShapeXML(ConvertColumnNumberToName(commentToAdd.col), commentToAdd.row.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 commentsToAddList)
                {
                    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
                        {
                            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 = string.Concat(ConvertColumnNumberToName(commentToAdd.col), commentToAdd.row), 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 = commentToAdd.text
                    };

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