EPPlus - Saving to Memory Stream results in empty file, Saving to File works ok

Question

I have what appears to be a strange problem with C# that I've not been able to figure out, hoping for some help here.

I've written the below demo that creates what is essentially the same file, but one Saves the ExcelPackage to a file using ExcelPackage.SaveAs(FileInfo(filePath)) (which works just fine and as expected), while the other saves to a MemoryStream (which results in a completely empty file).

Interestingly I do have other applications that utilise the same MemoryStream pattern and the file saves just fine, but it seems "temperamental" and can't figure out when it works and when it does not.

Anyone knows why this happens and how I can get it to work via a MemoryStream?

    class Program
    {
        static void Main(string[] args)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            TestXLToFile($@"Export\Test-{DateTime.Now.ToString("dd-MM-yy.H.mm.ss")}.xlsx");

            var stream = TestXLToMemStream();

            stream.CopyTo(new FileStream($@"Export\TestMs-{DateTime.Now.ToString("dd-MM-yy.H.mm.ss")}.xlsx", FileMode.Create));
        }

        public static void TestXLToFile(string FilePath)
        {
            using (ExcelPackage xl = new ExcelPackage())
            {
                var ws = xl.Workbook.Worksheets.Add("TEST");
                ws.Cells[1, 1].Value = "abc123";

                xl.SaveAs(new FileInfo(FilePath));
            }
        }

        public static MemoryStream TestXLToMemStream()
        {
            ExcelPackage xl = new ExcelPackage();

            var ws = xl.Workbook.Worksheets.Add("TEST");
            ws.Cells[1, 1].Value = "abc123";

            MemoryStream ms = new MemoryStream();
            xl.SaveAs(ms);

            return ms;
        }
    }

You've got several differences between these two functions. One calls `Dispose` on the `ExcelPackage` (via `using`-block), one doesn't. One passes a `FileInfo`, the other passes a `Stream`. What happens if you use `FileStream` instead of `FileInfo` ? — Ben Voigt, Aug 31 '21 at 22:15
What if you use `GetAsByteArray()` instead of `SaveAs(MemoryStream)` ? (naturally you can make a `MemoryStream` from the byte array, afterward) — Ben Voigt, Aug 31 '21 at 22:16
Also, your code using the `MemoryStream` leaks the `FileStream` it copies into, **without ever flushing**. That's a big no-no. Just changing `stream.CopyTo(new FileStream(...))` to `using (var fs = new FileStream(...)) stream.CopyTo(fs);` may solve your problems. — Ben Voigt, Aug 31 '21 at 22:18
Thanks @BenVoigt appreciate your help. Passing FileStream works, but that means the `SaveAs` saves to disk, which I would like to avoid (I need to pass on a MemoryStream to a service). I did flush the `MemoryStream` too but still get a 0kb file. Thanks for the heads-up here though. Using `GetAsByteArray()` results in data being written, but the file is corrupted. There are instances when this very same code words (if I add more data to the worksheet), which leads me to suspect that it is an `EPPlus` issue? Let me see if I can demo it. — Matthew Scerri, Aug 31 '21 at 22:30
Apologies, just saw the edit `using (var fs = new FileStream($@"", FileMode.Create)) stream.CopyTo(fs);` still results in an empty file — Matthew Scerri, Aug 31 '21 at 22:33
Thanks @BenVoigt - Ryan's answer below worked and you were, inevitably, correct that it relates to flushing of the streams. Thanks for your help, just needed to flush the MemoryStream too (which you alluded to in your comment) — Matthew Scerri, Aug 31 '21 at 23:30

score 2 · Accepted Answer · answered Aug 31 '21 at 23:16

2

The problem is because you are not calling the Flush and Close methods for the FileStream. You should make use of the Using statement when using Streams as follows:

using(var stream = TestXLToMemStream())
using(var fileStream = new FileStream($@"Export\TestMS-{DateTime.Now.ToString("dd-MM-yy.H.mm.ss")}.xlsx", FileMode.Create, FileAccess.Write))
{
    stream.WriteTo(fileStream);
}

answered Aug 31 '21 at 23:16

Ryan Falzon

329
4
15

1

It looks like changing from `CopyTo` to `WriteTo` was actually a very important detail: https://stackoverflow.com/a/10659887/103167 – Ben Voigt Sep 01 '21 at 15:12

EPPlus - Saving to Memory Stream results in empty file, Saving to File works ok

1 Answers1