CODEWITHSUNDEEP
c#excelepplus
dan
dan

Reputation: 5784

Using EPPlus with a MemoryStream

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?

Upvotes: 54

Views: 107734

Answers (8)

Reigo Hein
Reigo Hein

Reputation: 751

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.

Upvotes: 27

Electric_Wizard
Electric_Wizard

Reputation: 177

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.

Upvotes: 1

Kristof Verbiest
Kristof Verbiest

Reputation: 572

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.

Upvotes: 0

Artem
Artem

Reputation: 19

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.'

Upvotes: 0

Noah Heldman
Noah Heldman

Reputation: 6874

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());
}

Upvotes: 82

Danny
Danny

Reputation: 447

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.

Upvotes: 15

Hamid
Hamid

Reputation: 11

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

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

Upvotes: 1

Mike Goatly
Mike Goatly

Reputation: 7538

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.

Upvotes: 9

Related Questions