次の方法で共有

Removing Comments from Excel and PowerPoint Files

  • [アーティクル]

In a previous post, I showed you how to remove comments from a Word file. In today's post, I am going to show you how to accomplish the same scenario, but this time with Excel and PowerPoint files. Excel and PowerPoint have a Document Inspector feature, which is able to remove multiple types of data/content, including comments. This feature works great for client side solutions, but how do you cleanse these files of comments on the server? The Open XML SDK can accomplish these scenarios in just a few lines of code.

Note: The code showed in this post is backwards compatible with version 1 of the SDK.

Solution for PowerPoint

Imagine I start off with a PowerPoint deck that has multiple slides and comments. Here is a screenshot of a comment within a PowerPoint slide:

To remove comments from a PowerPoint deck we need to take the following actions:

  1. Open up the PowerPoint deck via the Open XML SDK
  2. Access the main presentation part, which will give us access to all the slide parts within the package
  3. Delete the comment part associated with each slide part
  4. Save changes made to the deck

If you want to jump straight into the code, feel free to download this solution here.

The Code

The code is pretty easy and maps 1:1 to the steps I mentioned above:

static void RemovePowerPointComments(string filename) { using (PresentationDocument myPresentation = PresentationDocument.Open(filename, true)) { PresentationPart presentationPart = myPresentation.PresentationPart; foreach (SlidePart slide in presentationPart.SlideParts) { slide.DeletePart(slide.SlideCommentsPart); } } }

End Result

Running this method, I end up with a presentation void of comments. Pretty easy!

Here is a screenshot of the final presentation:

Solution for Excel

This solution is very similar to the PowerPoint solution.

Imagine I start off with an Excel workbook that has multiple worksheets, where each worksheet contains multiple comments. Here is a screenshot of a comment within a worksheet cell:

To remove comments from an Excel workbook we need to take the following actions:

  1. Open up the Excel workbook via the Open XML SDK
  2. Access the main workbook part, which will give us access to all the worksheet parts within the package
  3. Delete all comment parts associated with each worksheet part
  4. Save changes made to the workbook

If you want to jump straight into the code, feel free to download this solution here.

The Code

This code is very similar to the code used to remove comments from a PowerPoint deck:

static void RemoveExcelComments(string filename) { using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myWorkbook.WorkbookPart; foreach (WorksheetPart sheet in workbookPart.WorksheetParts) { sheet.DeleteParts<WorksheetCommentsPart>(sheet.GetPartsOfType<WorksheetCommentsPart>()); } } }

End Result

Running this method, I end up with a workbook void of comments.

Here is a screenshot of the final workbook:

Zeyad Rajabi

Comments

  • Anonymous
    April 20, 2009
    PingBack from http://microsoft-sharepoint.simplynetdev.com/removing-comments-from-excel-and-powerpoint-files/

  • Anonymous
    April 21, 2009
    FYI, the comments in Excel don't consist in only the Comments part. This kind of snippet is pretty bad given the actual work to do to delete comments properly. It's bad since it's wrong, and that developers tend to copy/paste someone else's code without checking first.

  • Anonymous
    April 22, 2009
    Anon, Are you talking about removing the legacy vml drawing parts as well? Zeyad Rajabi

  • Anonymous
    April 22, 2009
    Sure. And the reference to it in the sheet footer. And any singleton child part referenced by the legacy drawings. That makes a lot of difference with what you have posted...

  • Anonymous
    April 22, 2009
    Catching up on links to blog posts I’ve found interesting this month … I was on vacation the first week

  • Anonymous
    April 24, 2009
    Sorry for absolute off-topic Open Packaging Conventions question. Can anyone explain me the purpose of defining content types, when content can be determined from relationships? Thanks in advance and once more, sorry for OT question.

  • Anonymous
    April 26, 2009
    Brian, I have a question somewhat unrelated to this post.  But it's a simple one.  I have added some XML parts to an Excel spreadsheet via an Excel Application Add-in.  When I do this I use a namespace uri to identify the part that I added. But when I use the Open XML SDK this namespace uri that I used doesn't appear to be available.  How do I find this namespace uri, given this is what I used as the key to my content.

  • Anonymous
    April 29, 2009
    Anon – Is there any way you can share an example file? I want to make sure I fully understand your concerns. Ferou – Take a look at the following post: http://blogs.msdn.com/brian_jones/archive/2005/06/20/430892.aspx Kang – You can either iterate through all the parts related to a particular part and look for the uri yourself or you could use System.IO.Packaging to get access to the specific part you want.