SpreadSheet with Comments is not working with Open XML

Question

We are trying to export excel with comments using Open Xml. I have tried with below code snippet and its showing comments in the Sheet Object but when exported to excel the comments are not displaying in the cells after opening excel.

public byte[] ExportJsonDatatoExcelWithComments()
{

    MemoryStream memoryStream = new MemoryStream();
    SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
    WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();

    workbookPart.Workbook = new Workbook();
    Sheets sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    //OpenSheet
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    SheetData sheetData = new SheetData();
    worksheetPart.Worksheet = new Worksheet(sheetData);

    var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "test" };
    sheets.Append(sheet);
    
    WorksheetCommentsPart worksheetCommentsPart1 = worksheetPart.AddNewPart<WorksheetCommentsPart>();
    GenerateWorksheetCommentsPart1Content(worksheetCommentsPart1);

    OpenXmlWriter DataWriter = OpenXmlWriter.Create(worksheetPart);
    DataWriter.WriteStartElement(sheetData);
    DataWriter.WriteStartElement(worksheetPart.Worksheet);
    
    //closedata
    DataWriter.WriteEndElement();
    DataWriter.Close();
    //
    //render
    // worksheetPart = null;
    workbookPart.Workbook.Save();
    spreadsheet.Save();
    spreadsheet.Close();
    memoryStream.Position = 0;
    return memoryStream.ToArray();
}

public void GenerateWorksheetCommentsPart1Content(WorksheetCommentsPart worksheetCommentsPart)
{
    Comments comments1 = new Comments() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "xr" } };


    Authors authors1 = new Authors();
    Author author1 = new Author();
    author1.Text = "User1";

    authors1.Append(author1);

    CommentList commentList1 = new CommentList();

    Comment comment1 = new Comment() { Reference = "A3", AuthorId = (UInt32Value)12, ShapeId = (UInt32Value)10, Guid = Guid.NewGuid().ToString() };
    comment1.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{811649EF-4CB5-4311-BE14-228133003BE4}"));

    CommentText commentText1 = new CommentText();

    Run run1 = new Run();

    RunProperties runProperties1 = new RunProperties();
    FontSize fontSize3 = new FontSize() { Val = 9D };
    Color color3 = new Color() { Indexed = (UInt32Value)81U };
    RunFont runFont1 = new RunFont() { Val = "Tahoma" };
    RunPropertyCharSet runPropertyCharSet1 = new RunPropertyCharSet() { Val = 1 };

    runProperties1.Append(fontSize3);
    runProperties1.Append(color3);
    runProperties1.Append(runFont1);
    runProperties1.Append(runPropertyCharSet1);
    Text text1 = new Text() { Space = SpaceProcessingModeValues.Preserve };
    text1.Text = "This is my comment!\nThis is line 2!\n";

    run1.Append(runProperties1);
    run1.Append(text1);

    commentText1.Append(run1);

    comment1.Append(commentText1);

    commentList1.Append(comment1);

    comments1.Append(authors1);
    comments1.Append(commentList1);
   
    worksheetCommentsPart.Comments = comments1;
}

Please let me know what is the issue with the code and how can we show comments for particular cell.

Answer 1

public byte[] ExportJsonDatatoExcelWithComments()
{

    MemoryStream memoryStream = new MemoryStream();
    SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
    WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();

    workbookPart.Workbook = new Workbook();
    Sheets sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    //OpenSheet
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    SheetData sheetData = new SheetData();
    worksheetPart.Worksheet = new Worksheet(sheetData);

    var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "test" };
    sheets.Append(sheet);
    
    WorksheetCommentsPart worksheetCommentsPart1 = worksheetPart.AddNewPart<WorksheetCommentsPart>();
    GenerateWorksheetCommentsPart1Content(worksheetCommentsPart1);

    OpenXmlWriter DataWriter = OpenXmlWriter.Create(worksheetPart);
    DataWriter.WriteStartElement(sheetData);
    DataWriter.WriteStartElement(worksheetPart.Worksheet);
    
    //closedata
    DataWriter.WriteEndElement();
    DataWriter.Close();
    //
    //render
    // worksheetPart = null;
    workbookPart.Workbook.Save();
    spreadsheet.Save();
    spreadsheet.Close();
    memoryStream.Position = 0;
    return memoryStream.ToArray();
}

 /// <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(commentToAdd.Key.Substring(0,1), commentToAdd.Key.Substring(1));
                }

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

        /// <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> Letters = new List<char>() { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J' };
                    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;
        }