Article by Sheela E.N, Sonata Software Limited

 

This article explains about how to delete track changes (revision) details from the SpreadsheetML document using System.IO.Packaging API in C# project.

 

Whenever changes are made to the shared workbook, the information about the edit performed on each cell and the number of revisions made, list of users sharing the workbook are stored in separate parts mentioned below.

 

 

Shared Workbook Revision Headers Part—contains each edit sessions performed on the workbook at a worksheet level. A package can contain at most one Revision Header Part and has an implicit relationship from the workbook part.

 

Shared Workbook Revision Log Part—contains information about edits performed on cells in worksheet in each edit session. A package can contain more than one Revision Log Part and they have an explicit relationship from the Shared Workbook Revision Headers Part.

 

Shared Workbook User Data Part—contains all the users sharing the workbook. A package can contain at most one User Data Part. A package can contain at most one User Data Part and has an implicit relationship from the workbook part.

 

 

 

Steps to follow:

 

 

1.    Create a C# project and a reference to WindowsBase.dll located at C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0 (This is part of .Net 3.0) 

 

2.    Import the namespace to refer to the dll

using System.IO.Packaging 

 

3.    Open the SpreasheetML document using the packaging API and get the workbook part 

pack = Package.Open(PathOfTheFile, FileMode.Open, FileAccess.ReadWrite);

 

            foreach (System.IO.Packaging.PackageRelationship relationship in pack.GetRelationshipsByType(relationSchema))

            {

 

                workbookUri = PackUriHelper.ResolvePartUri(new Uri("/", UriKind.Relative), relationship.TargetUri);

 

                workbookPart = pack.GetPart(workbookUri);

      //only one workbook

                break;

               

            } 

 

4.      Get the Revision Header URI and ID by looping the relationship of the workbook 

PackageRelationshipCollection headerRevision = workbookPart.GetRelationshipsByType(revisionHeader);

            string RevisionHeaderID = null;

 

            Uri RevisionheaderUri=null;

 

       

            foreach (PackageRelationship var in headerRevision)

            {

                RevisionHeaderID = var.Id.ToString();

                RevisionheaderUri = PackUriHelper.ResolvePartUri(workbookUri, var.TargetUri);

 

                //only one part

                break;

            }

 

5.      Get the Revision Header part, loop through the relationship of the type Revision Log and delete the Revision Log part 

PackagePart headerRevisionPart1 = pack.GetPart(RevisionheaderUri);

 

            PackageRelationshipCollection revisonLog=headerRevisionPart1.GetRelationshipsByType(revisionLog);

 

            String[] revlog = new string[50];

          

            int i = 0;

 

            //loop each revisionLog part, note the id and delete the part

         

            foreach (PackageRelationship var in revisonLog)

            {

                revlog[i++] = var.Id.ToString();

                Uri test = PackUriHelper.ResolvePartUri(RevisionheaderUri, var.TargetUri);

                pack.DeletePart(test);

               

            }

 

 

 6.    Delete the relationship from the Revision Header part

 

for (int j = 0; j < i; j++)

            {

            

                headerRevisionPart1.DeleteRelationship(revlog[j]);

 

            }

 

7.    Get the User Data part and delete from the package 

PackageRelationshipCollection Usernames=workbookPart.GetRelationshipsByType(usernamerels);

            String strUsername1 = null;

          

            foreach (PackageRelationship var in Usernames)

            {

                strUsername1 = var.Id.ToString();

             

                Uri test = PackUriHelper.ResolvePartUri(workbookUri, var.TargetUri);

                pack.DeletePart(test);

                //only one part

                break;

               

            }

 

8.    Delete the User Data and Revision Header part relationship from the workbook

 workbookPart.DeleteRelationship(strUsername1);

            workbookPart.DeleteRelationship(RevisionHeaderID);

 

9.    Delete the Revision Header part

 

pack.DeletePart(RevisionheaderUri);   

 

10. Flush the package content and close the handler

 

pack.Flush();

pack.Close();  

 

This is a simple demo which demonstrates use of system.IO.Packaging API for deleting track changes in SpreadsheetML document. The example demo is attached with the article as a zip file. 

 

In my next article, I will explain how to achieve the same using Open XML SDK which reduce lots of code.