0

According to this article OpenXML is not thread safe when it's "MemoryStreams reach the high water mark" and has to switch to IsolatedStorage.

This occurs on workbooks smaller than even 1mb because the UNCOMPRESSED data is likely 10x that size.

I really need to create xlsx files concurrently, in particular, abnormally large ones with concurrency. The MS solution is to implement something like the following(converted from C#), but I'm not sure what to do with this.

Public Class PackagePartStream
    Private _stream As Stream
    Private Shared _m As New Mutex(False)

    Public Sub New(ByVal Stream As Stream)
        _stream = Stream
    End Sub

    Public Function Read(ByVal buffer() As Byte, ByVal offset As Integer, ByVal count As Integer) As Integer
        Return _stream.Read(buffer, offset, count)
    End Function

    Public Sub Write(ByVal buffer() As Byte, ByVal offset As Integer, ByVal count As Integer)
        _m.WaitOne(Timeout.Infinite, False)
        _stream.Write(buffer, offset, count)
        _m.ReleaseMutex()
    End Sub

    Public Sub Flush()
        _m.WaitOne(Timeout.Infinite, False)
        _stream.Flush()
        _m.ReleaseMutex()
    End Sub
End Class

My best guess so far would be something like this but I have a feeling I'm over simplifying this and the mutex needs to be closer to the function handling OpenXML's WriteElement

Dim stream As New PackagePartStream()
Using document As SpreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, True)
    WriteExcelFile(ds, document)
End Using

I haven't done much threading in .NET but hopefully i can be pointed in the right direction by someone.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Shawn K
  • 779
  • 5
  • 13

1 Answers1

1

I hate to answer my own question but don't use the OpenXML SDK if you're building dynamic reports on a webserver. Use EPplus instead. It uses an alternative packaging library that does not use IsolagedStorage. It's a shame Microsoft hasn't addressed this issue.

Shawn K
  • 779
  • 5
  • 13