OpenXML, Large exports, and IsolatedStorageException

[matthew_taylor]

Hello!

I have just gone down the rabbit hole with a problem and found the solution, so I thought I'd share it here.

It's not 100% Revit API stuff, but I know someone will find it useful (and likely save them going down the same rabbit hole).

When exporting to an Excel file using OpenXML, small files will work okay.

When exporting larger files, you may run into an IsolatedStorageException with a stack trace something like this:

EXCEPTION TYPE: System.IO.IsolatedStorage.IsolatedStorageException
SOURCE: mscorlib
MESSAGE: Unable to determine the identity of domain.
STACK TRACE:    at System.IO.IsolatedStorage.IsolatedStorage._GetAccountingInfo(Evidence evidence, Type evidenceType, IsolatedStorageScope fAssmDomApp, Object& oNormalized)
   at System.IO.IsolatedStorage.IsolatedStorage.GetAccountingInfo(Evidence evidence, Type evidenceType, IsolatedStorageScope fAssmDomApp, String& typeName, String& instanceName)
   at System.IO.IsolatedStorage.IsolatedStorage._InitStore(IsolatedStorageScope scope, Evidence domainEv, Type domainEvidenceType, Evidence assemEv, Type assemblyEvidenceType, Evidence appEv, Type appEvidenceType)
   at System.IO.IsolatedStorage.IsolatedStorage.InitStore(IsolatedStorageScope scope, Type domainEvidenceType, Type assemblyEvidenceType)
   at System.IO.IsolatedStorage.IsolatedStorageFile.GetStore(IsolatedStorageScope scope, Type domainEvidenceType, Type assemblyEvidenceType)
   at MS.Internal.IO.Packaging.PackagingUtilities.ReliableIsolatedStorageFileFolder.GetCurrentStore()
   at MS.Internal.IO.Packaging.PackagingUtilities.ReliableIsolatedStorageFileFolder..ctor()
   at MS.Internal.IO.Packaging.PackagingUtilities.GetDefaultIsolatedStorageFile()
   at MS.Internal.IO.Packaging.PackagingUtilities.CreateUserScopedIsolatedStorageFileStreamWithRandomName(Int32 retryCount, String& fileName)
   at MS.Internal.IO.Packaging.SparseMemoryStream.EnsureIsolatedStoreStream()
   at MS.Internal.IO.Packaging.SparseMemoryStream.SwitchModeIfNecessary()
   at MS.Internal.IO.Packaging.SparseMemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at MS.Internal.IO.Packaging.CompressEmulationStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at MS.Internal.IO.Packaging.CompressStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at MS.Internal.IO.Zip.ProgressiveCrcCalculatingStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Write(Byte[] buffer, Int32 offset, Int32 count)
   at System.Xml.XmlUtf8RawTextWriter.FlushBuffer()
   at System.Xml.XmlUtf8RawTextWriter.WriteElementTextBlock(Char* pSrc, Char* pSrcEnd)
   at System.Xml.XmlUtf8RawTextWriter.WriteString(String text)
   at System.Xml.XmlWellFormedWriter.WriteString(String text)
   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
   at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
   at DocumentFormat.OpenXml.OpenXmlPartRootElement.WriteTo(XmlWriter xmlWriter)
   at DocumentFormat.OpenXml.OpenXmlPartRootElement.Save(Stream stream)
   at DocumentFormat.OpenXml.OpenXmlPartRootElement.SaveToPart(OpenXmlPart openXmlPart)
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.TrySavePartContent(OpenXmlPart part)
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.SavePartContents(Boolean save)
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose(Boolean disposing)
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose()
   at ...

This is a known issue with OpenXML and larger files.

"In scenarios where the OpenXML SDK is used in a hosted environment (such as add-ins or extensions), the AppDomain may not be set up to allow for IsolatedStorage. "

The workaround is detailed here:

https://github.com/OfficeDev/Open-XML-SDK/tree/main/samples/IsolatedStorageExceptionWorkaround 

This didn't exactly work for me.

I needed to use CreateInstanceFromAndUnwrap and pass it the full path to the assembly:

Dim setup As AppDomainSetup = New AppDomainSetup()
setup.ApplicationBase = AppDomain.CurrentDomain.BaseDirectory.ToString()
Dim evidence As Evidence = New Evidence()
evidence.AddHostEvidence(New Zone(SecurityZone.MyComputer))
Dim domain As AppDomain = AppDomain.CreateDomain("AnyNameForYourDomain", evidence, setup)

Dim path As String = IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) & "\" & GetType(YourClass).Assembly.GetName().Name & ".dll"

Dim exObj As Object = domain.CreateInstanceFromAndUnwrap(path, GetType(YourClass).FullName)
Dim excelNewAppDomain As YourClass = TryCast(exObj, YourClass)

Okay, so to this point, it got rid of the IsolatedStorageException exception! Good times!

Still some work to do though.

I got a bunch of SerializationException exceptions explaining that I had some of my classes that weren't marked as serializable.

Easy peasy, mark those classes as serializable using the SerializableAttribute:

<Serializable()>
Public Class YourClass

I also got a SerializationException like this:

Type 'Autodesk.Revit.DB.ProjectInfo' in Assembly 'RevitAPI, Version=22.0.0.0, Culture=neutral, PublicKeyToken=null' is not marked as serializable.

This was due to an auto property like this:

Property Element As DB.ProjectInfo

Not so easy, as it's not possible to add a NonSerializedAttribute to a property, especially an auto property. (And I certainly can't edit the RevitAPI!)

The solution was to convert the property to a full property and mark the backing field as NonSerialized (Link) -:

<NonSerialized()>
Private m_element As DB.ProjectInfo

Property Element As DB.ProjectInfo
   Get
       Return m_element
   End Get
   Private Set(value As DB.ProjectInfo)
       m_element = value
   End Set
End Property

And finally, since I had a bunch of functions creating DataTables in my class, as well as a function to create the actual spreadsheet, I created two instances of 'YourClass'.

The local one to create the DataTables, and the AppDomain version to write the spreadsheet. Doing it this way prevented a FileNotFoundException when using the AppDomain (as it was looking for a sub-assembly).

Note that others have reported this error and talk about 'large' exports being 4-10MB+. I found this issue occurred at around 450KB! Both are vaguely correct. An .xlsx file is actually a compressed file. Rename it with a .zip extension and take a look! When I did this I saw that my faulty worksheet was in fact 6MB+.

Take care of yourselves,

Edit: Oh, and you main need YourClass to inherit MarshalByRefObject:

Public Class YourClass : Inherits MarshalByRefObject

[jeremy_tammik]

Thank you very much for your research and documentation!

I hesitate putting this on the blog, since I am unsure how many people might be using this in conjunction with the Revit API. What do you think?

Cheers,

Jeremy

[matthew_taylor]

Thanks @jeremy_tammik!
It's definitely a bit fringe, and barely involved with Revit (more of a general addin/plugin issue).

I'd tend towards not on your blog...we don't want to bore your audience. 😉 Entirely up to you though.

[RPTHOMAS108]

I read this and found it quite interesting. I think it is an example of how we occasionally unwittingly end up becoming an expert in something we didn't intend to be.

My approach with Excel was a bit basic. I used a xlsx seed file in combination with the IO.Packaging namespace and changed the content. It worked quite well but all the Excel files that came from it had me as the creator.