Excel with Comments is not working with Open XML SDK

[ChigurupatiAjay]

0

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.

[mikeebowen]

@ChigurupatiAjay , What version of the SDK are you using? Does your issue repro with the latest (3+) version of the SDK?

[mikeebowen]

Hi @ChigurupatiAjay, there are a few issues with your code:

• The code uses the removed SpreadsheetDocument.Close() method that must be replaced with the SpreadsheetDocument.Dispose() method.
• In addition to the comment part, the code needs to add a Vml Drawing Part for the comment part
• The cell must exist in the sheet for the comment to show, i.e. not just an empty SheetData element.

Also, for newer/current versions of Excel, it is better to use the new threaded comments. Here is a sample for threaded comments. If you need to use the old comments part, this Stack Overflow post has some helpful information.