Creating cell comments in an Excel Spreadsheet with OpenXML SDK

Question

I'm trying to add comments to cells in an Excel 2007 spreadsheet. I'm using the OpenXml SDK 2.0 to do so.

My use case is this: I've created a template Excel file that I copy and use that as my starting point, rather than create an OpenXML document from scratch. My template file has a comment in cell A1 so that Excel has already created a WorksheetCommentPart for me.

Now my problem is that when I add Comment nodes to the Comments part the spreadsheet doesn't load and Excel asks if I want to recover.

What really bothers me is that my original comment in A1 is still there, but any comments I added programmatically are gone!

Here's the code I'm working with:

using (MemoryStream spreadsheetStream = new MemoryStream()) { GetGradebookSpreadsheetTemplate(spreadsheetStream);

using (SpreadsheetDocument spDoc = SpreadsheetDocument.Open(spreadsheetStream, true))
{
    WorkbookPart wbPart = spDoc.WorkbookPart;
    WorksheetPart wsPart = wbPart.WorksheetParts.First();
    SheetData sheet = wsPart.Worksheet.GetFirstChild<SheetData>();
    Comments comments = wsPart.WorksheetCommentsPart.Comments;
    comments.Descendants<Author>().First().Text = string.Format("{0}, {1}", instructor.LastName, instructor.FirstName);
    comments.Descendants<Text>().First().Text = string.Format("{0}, {1}", instructor.LastName, instructor.FirstName);
    List<DefinedName> definedNames = new List<DefinedName>();
    definedNames.Add(CreateDefinedName("COLWeb_Gradebook", sheet.NamespaceURI, "Gradebook", "1", "A"));

    uint index = 4;
    foreach (User u in users)
        CreateUserDataRow(index++, definedNames, comments.CommentList, sheet, u, coursesForUsers[u], assignments, submissions[u]);
    Cell lastCell = sheet.Descendants<Cell>().Last();
    OpenXmlElement dimensionsElement = wsPart.Worksheet.Elements().Where(x => x.LocalName == "dimension").First();
    dimensionsElement.SetAttribute(new OpenXmlAttribute("ref", null, "A1:" + lastCell.CellReference));

    comments.Save();
    wsPart.Worksheet.Save();
    wbPart.Workbook.Save();
}

return spreadsheetStream.ToArray();

}

And "CreateUserDataRow" creates a new row, but the relevant part is (where "comment" is my comment string and "c" is my Cell that I want to create the comment about):

if (!string.IsNullOrEmpty(comment))
{
    List<OpenXmlElement> runs = new List<OpenXmlElement>();

    foreach (string row in comment.Split(new string[] { "<p>", "</p>" }, StringSplitOptions.RemoveEmptyEntries))
    {
        string trimmed = row.Trim();
        if (!string.IsNullOrEmpty(trimmed))
        {
            string escaped = System.Security.SecurityElement.Escape(trimmed);
            runs.Add(new Run(new RunProperties(), new Text(escaped)));
        }
    }

    Comment commentCell = new Comment();
    commentCell.Reference = c.CellReference;
    commentCell.AuthorId = 0;
    commentCell.AppendChild(new CommentText(runs));
    comments.AppendChild(commentCell);
}

Now as far as my eye can see, and KDiff3 for that matter, my files are pretty much identical to the files that would be output if I were to open Excel and put the comments into the cells by hand in Excel.

Does anyone have a good example of attaching a comment to a cell with OpenXml? Is there something I should know about maybe a relationship? Does it have something to do with using an Excel file that I created and then I'm using as a template (maybe some dimensions aren't set)?

Thanks for any help I can get.

Answer 1

Unfortunately, it is not that simple.

Cell comments also have a graphics object which is in a VML drawing part. VML is a cryptic legacy specification and is not in the approved ECMA standard. You can find documentation on it in Microsoft's Open XML documents, but it is not pretty. Hopefully Microsoft will address this in Excel 14 by adding full cell comment support as well as support for controls which are also written to VML.

Having said that, I have not used the Open XML SDK and I cannot say whether or not it is possible to add comments with it. I just thought this might help get you pointed in the right direction.

Answer 2

There appears to be a code sample at http://openxmldeveloper.org/forums/thread/7396.aspx which shows how to create the requisite VML Drawing. I'm trying this code now, and seem to have progressed, though I'm still getting some sort of unspecified error in which Excel decides to drop the comments before opening...