OpenXML-for-VSTO provides the ability to process content as OpenXML from VSTO-addins

• Introduction
• How it works?
• Where to use
• Where not to use
• Benchmark
• Issues
• Tips and tricks

One of the problems of creating VSTO add-ins for MS Office is the performance of access to a large number of elements. Access is through the boundaries of the AppDomains, with a lot of Reflection. Each call can read/write only one parameter at a time, therefore, for large tables/documents, the add-in have to make millions of such calls. Moreover, these calls are performed in the UI thread, blocking or slowing down user input and worsening the user experience. Using OpenXML would be a good solution, but unfortunately, the Office API does not provide easy access to OpenXML. This library provides the ability to get/set an OpenXML object directly from/to VSTO add-in.

• Copy an object to a file using Clipboard.
• Work with the file as with any other OpenXML file.
• Copy the object back.

In VSTO add-ins, in operations with thousands of objects, involving styles and formats.

• Office Interop except for VSTO add-ins. In this case the library does not give any advantage, as you can replace interop with any OpenXML library all at once.
• Operations with small sets of objects - each copy paste has significant overhead that does not worth it for small sets.
• Operations with values/formulas in Excel - these can be much faster accessed throuch 2d arrays.

Benchmark is based on writing and reading cells - 1, 100, 10,000 or 1,000,000 two different ways - using this library (and ClosedXML) and with pure Excel interop. Each individual cell value, background color, font color, font size, italic and bold status and number format. No optimizations.

Writing operations:

Reading operations:

Even though performance for Excel can be improved in some scenarios by using union ranges, ClosedXML algorithms are less than optimal in this scenario as well, and most likely in real scenario performance gain can be much bigger.

Benchmark is based on writing and reading runs of text - 1, 100, 10,000, 100,000 or 1,000,000 two different ways - using this library and with pure Word interop. Each individual run text, background color, font color, font size, italic and bold status. No optimizations.

Writing operations:

Reading operations:

• It uses the clipboard, so after each operation it looses data user potentiall saved there. There are workaronds, like save and restore clipboard for each operation, but these are not part of this library
• Beginning from certain size (around 12 mb) of objects, the library may cause IsolatedStorageException. This exception happens because VSTO appdomain has incorrect Evidence. It is easy to fix by calling this code once before using OpenXML-for-VSTO with big objects:

System.Security.Policy.Evidence newEvidence = new System.Security.Policy.Evidence();
newEvidence.AddHostEvidence(new System.Security.Policy.Zone(System.Security.SecurityZone.MyComputer));

System.AppDomain.CurrentDomain
    .GetType()
    .GetField("_SecurityIdentity", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic)?
    .SetValue(System.AppDomain.CurrentDomain, newEvidence);

You can save a copy of the whole workbook without using this library by using Workbook.SaveCopyAs method. This does not work with Word documents, but then you can cast Document to IPersistFile and use method Save

using System.Runtime.InteropServices.ComTypes;

IPersistFile persistFile = (IPersistFile) doc;
persistFile.Save(<path>, false);