Add comment for cell while exporting to excel using openxml library in c#

Question

We are generating excel using openxml library. Recently we added notes to appear as comment while exporting to excel for that particular column header.

I tried to add comments using below methods,but when I opened exported excel its not showing any comment

   public byte[] ExportToExcelWithComments()
    {

        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>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());

        var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "test" };
        sheets.Append(sheet);

        OpenXmlWriter DataWriter = OpenXmlWriter.Create(worksheetPart);
      
        DataWriter.WriteStartElement(worksheetPart.Worksheet);

        //Opendata
        DataWriter.WriteStartElement(new SheetData());
                
        WorksheetCommentsPart worksheetCommentsPart1 = worksheetPart.AddNewPart<WorksheetCommentsPart>();
        GenerateWorksheetCommentsPart1Content(worksheetCommentsPart1);

        //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" } };
        comments1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
        comments1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");

        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 missing or issue with any code snippet.I am not bale to see the comments in exported excel

Answer 1

There is an example of how to add threaded comments on the Open XML SDK here.

I've copied and pasted the code below for reference:

// Copyright (c) Microsoft. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.
#nullable enable

using Common;
using DocumentFormat.OpenXml.Office2019.Excel.ThreadedComments;
using DocumentFormat.OpenXml.Packaging;

// WORKBOOK
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.IO;

namespace ThreadedCommentsExample
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            string sheetName = "commentSheet";

            string column = "A";

            uint row = 1;

            string reference = string.Concat(column, row.ToString());

            int nRequiredArgs = 1;

            // Must have x arguments.
            if (args.Length < nRequiredArgs)
            {
                ExampleUtilities.ShowHelp(new string[] { $"ThreadedCommentExample requires {nRequiredArgs} arguments." });
                return;
            }

            string filePath = args[0];

            // start fresh with each run
            if (ExampleUtilities.FileCheck(filePath))
            {
                try
                {
                    File.Delete(filePath);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    return;
                }
            }

            // WORKBOOK
            using SpreadsheetDocument? sd = ExampleUtilities.CreateSpreadsheetWorkbook(filePath, sheetName);

            if (sd != null)
            {
                ExampleUtilities.InsertText(sd, sheetName, @"Please comment on this cell.", column, row);
            }

            if (!(sd is null))
            {
                CreateMiscellaneousParts(sd, sheetName);

                Worksheet? worksheet = ExampleUtilities.GetSheet(sd, sheetName)?.Worksheet;

                if (worksheet != null)
                {
                    worksheet.AddChild(new LegacyDrawing() { Id = "rId1" });

                    // NOTE: UserId, DisplayName, Id and ProviderId should be generated by querying the identity server used by this org and account.
                    string displayNameUser = "Jose Contoso";
                    string idUser = string.Concat("{", System.Guid.NewGuid().ToString().ToUpper(), "}");
                    string tcId = string.Concat("{", System.Guid.NewGuid().ToString().ToUpper(), "}");

                    // For the format of these userId parameter, please refer to [MS-XLSX] 2.6.203 CT_Person
                    // This can be a SID from an Active Directory provider, an email address using "PeoplePicker", and O365 id or a simple name.
                    string userIdJose = "[email protected]";
                    string providerIdAzure = "PeoplePicker";

                    WorkbookPart workbookPart = sd.WorkbookPart ?? sd.AddWorkbookPart();

                    WorkbookPersonPart pp = workbookPart.AddNewPart<WorkbookPersonPart>();

                    pp.PersonList = new PersonList(new Person() { DisplayName = displayNameUser, Id = idUser, UserId = userIdJose, ProviderId = providerIdAzure });

                    WorksheetPart wsp = ExampleUtilities.GetSheet(sd, sheetName);

                    WorksheetCommentsPart wscp = wsp.AddNewPart<WorksheetCommentsPart>();
                    wscp.Comments = new Comments(
                        new Authors(
                            new Author("tc=" + tcId)), new CommentList(
                                new Comment(
                                    new DocumentFormat.OpenXml.Spreadsheet.CommentText(
                                        new DocumentFormat.OpenXml.Spreadsheet.Text("Comment: Ok, here's a comment!")))

                                // Comment attributes
                                { Reference = reference, AuthorId = 0, ShapeId = 0, Guid = tcId }));

                    WorksheetThreadedCommentsPart wstcp = wsp.AddNewPart<WorksheetThreadedCommentsPart>();
                    wstcp.ThreadedComments = new ThreadedComments(
                        new ThreadedComment(
                            new ThreadedCommentText("Ok, here's a threaded comment!"))

                        // ThreadedComment attributes
                        { Ref = reference, PersonId = idUser, Id = tcId, DT = System.DateTime.Now });
                }
            }
            else
            {
                throw new Exception("SpreadsheetDocument is null");
            }
        }

        private static void CreateMiscellaneousParts(SpreadsheetDocument? sd, string sheetName)
        {
            if (sd == null)
            {
                return;
            }

            CoreFilePropertiesPart cfp = sd.AddCoreFilePropertiesPart();
            using (System.Xml.XmlTextWriter writer =
            new System.Xml.XmlTextWriter(cfp.GetStream(FileMode.Create), System.Text.Encoding.UTF8))
            {
                writer.WriteRaw("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?> <cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:dcmitype=\"http://purl.org/dc/dcmitype/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"> <cp:lastModifiedBy>Jose Contose</cp:lastModifiedBy> <dcterms:modified xsi:type=\"dcterms:W3CDTF\">2022-03-01T18:55:39Z</dcterms:modified> </cp:coreProperties>");
                writer.Flush();
            }

            ExtendedFilePropertiesPart efp = sd.AddExtendedFilePropertiesPart();
            using (System.Xml.XmlTextWriter writer =
            new System.Xml.XmlTextWriter(efp.GetStream(FileMode.Create), System.Text.Encoding.UTF8))
            {
                writer.WriteRaw("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?> <Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\" xmlns:vt=\"http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes\"> <Application>Microsoft Excel</Application> <DocSecurity>0</DocSecurity> <ScaleCrop>false</ScaleCrop> <HeadingPairs> <vt:vector size=\"2\" baseType=\"variant\"> <vt:variant> <vt:lpstr>Worksheets</vt:lpstr> </vt:variant> <vt:variant> <vt:i4>1</vt:i4> </vt:variant> </vt:vector> </HeadingPairs> <TitlesOfParts> <vt:vector size=\"1\" baseType=\"lpstr\"> <vt:lpstr>commentSheet</vt:lpstr> </vt:vector> </TitlesOfParts> <LinksUpToDate>false</LinksUpToDate> <SharedDoc>false</SharedDoc> <HyperlinksChanged>false</HyperlinksChanged> <AppVersion>16.0300</AppVersion> </Properties>");
                writer.Flush();
            }

            LabelInfoPart lip = sd.AddLabelInfoPart();
            lip.ClassificationLabelList = new DocumentFormat.OpenXml.Office2021.MipLabelMetaData.ClassificationLabelList(
                new DocumentFormat.OpenXml.Office2021.MipLabelMetaData.ClassificationLabel()
                {
                    Id = "{f42aa342-8706-4288-bd11-ebb85995028c}",     // Unique sensitivity label Id.
                    SiteId = "{72f988bf-86f1-41af-91ab-2d7cd011db47}", // Azure AD site Id.
                    Method = "Standard",
                    Enabled = true,
                    ContentBits = 0,
                    Removed = false,
                });

            VmlDrawingPart wsd = ExampleUtilities.GetSheet(sd, sheetName).AddNewPart<VmlDrawingPart>("rId1");
            using (System.Xml.XmlTextWriter writer =
            new System.Xml.XmlTextWriter(wsd.GetStream(FileMode.Create), System.Text.Encoding.UTF8))
            {
                writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"> <o:shapelayout v:ext=\"edit\"> <o:idmap v:ext=\"edit\" data=\"1\"/> </o:shapelayout><v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\" path=\"m,l,21600r21600,l21600,xe\"> <v:stroke joinstyle=\"miter\"/> <v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/> </v:shapetype><v:shape id=\"_x0000_s1025\" type=\"#_x0000_t202\" style='position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1; visibility:hidden' fillcolor=\"infoBackground [80]\" strokecolor=\"none [81]\" o:insetmode=\"auto\"> <v:fill color2=\"infoBackground [80]\"/> <v:shadow color=\"none [81]\" obscured=\"t\"/> <v:path o:connecttype=\"none\"/> <v:textbox style='mso-direction-alt:auto'> <div style='text-align:left'></div> </v:textbox> <x:ClientData ObjectType=\"Note\"> <x:MoveWithCells/> <x:SizeWithCells/> <x:Anchor> 1, 15, 0, 2, 3, 31, 4, 1</x:Anchor> <x:AutoFill>False</x:AutoFill> <x:Row>0</x:Row> <x:Column>0</x:Column> </x:ClientData> </v:shape></xml>");
                writer.Flush();
            }
        }
    }
}