Using EPPlus with a MemoryStream

Question

I am using EPPlus to generate an XLSX file in C#. As soon as I instantiate the ExcelPackage with a memory stream - I get the error:

"A disk error occurred during a write operation. (Exception from HRESULT: 0x8003001D (STG_E_WRITEFAULT))"

Code is:

MemoryStream stream = new MemoryStream();

using (ExcelPackage package = new ExcelPackage(stream))
{
    ...
}

Has anyone else seen this?

Answer 1

None of the other answers quite got me there (the Excel worksheet was always empty), but this worked for me:

using (var package = new ExcelPackage())
{
    var worksheet = package.Workbook.Worksheets.Add("Worksheet Name");

    worksheet.Cells["A1"].LoadFromCollection(data);

    var stream = new MemoryStream(package.GetAsByteArray());
}

Answer 2

I know the question was answered months before, but this is how I do it for future reference to anyone trying:

In VB.NET:

Dim stream As New MemoryStream
Using package As New ExcelPackage(stream)
    'Here goes the ExcelPackage code etc 
    package.Save()
End Using

In C#:

MemoryStream stream = new MemoryStream();
using (ExcelPackage package = new ExcelPackage(stream))
{
    //Here goes the ExcelPackage code etc
    package.Save()
}

The C# code should be correct, as far as I know. And the ExcelPackage has built-in support for streams.

Answer 3

If you want to continue using a stream (e.g. Response.OutputStream) you can create an ExcelPackage with an empty constructor and use the SaveAs(Stream OutputStream) method.

Answer 4

It looks like you're hitting a bug in the error handler of the ExcelPackage constructor. If you try and give it an empty stream, System.IO.Packaging.Package.Open raises an exception indication that a package cannot be empty.

This code works, even if the file doesn't exist:

var file = new FileInfo("test.xlsx");
using (ExcelPackage package = new ExcelPackage(file))
{
}

Given that the documentation for the constructor overload indicates that the stream is allowed to be empty, I'd recommend raising this issue in the EPPlus issue tracker.

Answer 5

We had a similar issue when converting code that used the 4.1.1 version of EPPlus to the 4.5.1 version.

Originally, we were using the following pattern:

using (var ms = new MemoryStream())
{
    new ExcelBuilder().BuildResultFile(result, ms);
    ms.Position = 0;    // <-- Cannot access a closed Stream error thrown here
    // Send Excel file to Azure storage
}

And our ExcelBuilder class, BuildResultFile function:

public void BuildResultFile(List<ResultSet> resultSets, Stream stream)
{
    using (var package = new ExcelPackage(stream))
    {
        // Create Excel file from resultSets
        package.Save();
    }
}

In order to make this work with 4.5.1 we had to remove using block from the BuildResultFile function.

I can't seem to find any documentation on in GitHub w/r/t why this changed or if I am even implementing this correctly.

Answer 6

You can create an ExcelPackage with an empty constructor. It will handle its own internal buffer.

http://epplus.codeplex.com/wikipage?title=WebapplicationExample

Answer 7

I faced with the same issue when tried to open existing excel file and spent couple days with it. In my case I received mentioned exception "A disk error occurred during a write operation. (Exception from HRESULT: 0x8003001D (STG_E_WRITEFAULT))" due to encryption.

I was able to read .xlsx file by passing password. In my case empty string "" was enough.

in your case please try to initialize package using constructor with password:

public ExcelPackage(Stream newStream, string Password)

package = new ExcelPackage(stream, "");

Have a look into ExcelPackage source code http://epplus.codeplex.com/SourceControl/latest#EPPlus/ExcelPackage.cs

There is a method

private void Load(Stream input, Stream output, string Password) 

which is used to load excel file.

private void Load(Stream input, Stream output, string Password) 

...

if (Password != null)

{
  Stream encrStream = new MemoryStream();
  CopyStream(input, ref encrStream);
  EncryptedPackageHandler eph = new EncryptedPackageHandler();
  Encryption.Password = Password;
  ms = eph.DecryptPackage((MemoryStream)encrStream, Encryption);
}
else
{
  ms = new MemoryStream();
  CopyStream(input, ref ms);
 }

...

Code will try to decrypt excel stream even if password is empty, BUT NOT NULL.

However if you try to initialize package for file that is not encrypted you will have exception:

'The stream is not an valid/supported encrypted document.'

Answer 8

I was dealing with the same error, but none of the other answers provided any help.

In the end, the problem was solved after adding this code before trying to open the file:

Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

It seems that the root cause was that EPPlus could not open the ZIP because of a missing code page. I got this to work thank to this StackOverflow answer.