Stream Instead Of File?

Topics: Developer Forum
Oct 26, 2007 at 4:15 PM
I recently came upon ExcelPackage and desired to use to use it. My need is to allow a user of a web-based system to download spreadsheets which are created on the fly. To my dismay, I shortly discovered that ExcelPackage (as far I can tell) works only by saving files to disk. For obvious reasons, I don't really want to get into the headache of managing temporary files on the server. I want to simply create an in memory object on the server and pass it to the client.

So I grabbed the source code, and studied it a bit, and I thought I had found a way to get a MemoryStream. I noticed that Package.Open has an override that takes a Stream. Woo hoo! So I created an override of the constructor for ExcelPackage as follows:

public ExcelPackage(Stream stream)
{
_package = Package.Open(stream, FileMode.Create, FileAccess.ReadWrite);
}

Upon calling the Save method on the ExcelPackage, the stream object that I had passed in to the constructor was indeed populated. I used the stream to write a file. To my dismay Excel complained that the file was not recognized as a valid Excel file. The Excel sheet I created was dead simple -- "123" in row 1 cell 1. I verified the problem was not in my worksheet -- I created the same worksheet directly as file, and Excel was quite happy. (NOTE: I tried FileAccess.Write also, but that gave an error.)

I should admit that I have not schooled myself on the System.IO.Packaging, but it seems reasonable to assume that the Stream overload is for just the purpose I am seeking to meet.

Anyway, my first question is, does anyone else see the value in enhancing ExcelPackage for creating in-memory spreadsheets? Second, any ideas?
Nov 2, 2007 at 10:55 AM
I had the same problem, but I have a solution. I don't want to write a story about it, I can simply send you a source of the ExcelPackage with my changes. Send my your e-mail. BlackBar@RIMIDALV.lv - that's me ;)
Nov 2, 2007 at 3:42 PM
Yes, I will send you an email. Thanks. Seems like a compelling feature that should be incorporated into the trunk. Amy committers out there agree?
Nov 4, 2007 at 9:56 AM
I have send you the source. I hope it helps ;)
Nov 8, 2007 at 12:56 AM
hi BlackBar, can i have a solution of the probelm too? Thanks~~~
oliverwhk@gmail.com or oliverw@callplus.co.nz
Nov 27, 2007 at 2:45 PM
BlackBar > Please send the code to me as well.
cphmike@gmail.com
Thanks
Dec 10, 2007 at 3:28 PM
Hi BlackBar,

Could you please upload the code here or send me at
wajid_q@hotmail.com

Thanks in advance
Dec 10, 2007 at 11:27 PM
First, I must admit that I have not tried this code yet. Before BlackBar responded I had already proceeded with a different solution. I am very eager to try it out next time.

Anyhow, given the popularity of BlackBar's solution, maybe some committer out there can add this to the trunk???
Dec 17, 2007 at 7:44 PM
I posted a blog post on the changes I made to the excelpackage class to make it write to a stream. Hope this helps

http://www.onteorasoftware.net/post/Using-the-ExcelPackage-class-on-a-web-page.aspx
Jun 24, 2008 at 4:18 PM
The solution proposed is working but not if you would like to use a template.

Following the post provided by Wouter Van Vugt (http://blogs.infosupport.com/wouterv/archive/2006/05/31/Creating-Word-Documents-mailings-on-the-fly-_2800_mail-merge-scenario_2900_.aspx)
you can easily create a constructor to create an in memory copy of the template and then stream it to the client

/// <summary>
        /// Creates an ExcelPackage based on a template file, the package is copied in the provided stream
        /// </summary>
        /// <param name="template">The template</param>
        /// <param name="stream">The stream where a copy of the template will be stored</param>
        public ExcelPackage(FileInfo template, Stream stream)
        {
            // Open the document
            using (FileStream fs = new FileStream(template.FullName, FileMode.Open, FileAccess.ReadWrite))
            {
                // Load it into a memorybuffer
                using (MemoryStream ms = new MemoryStream())
                {
                    byte[] buffer = new byte[1024];

                    int bytesRead = fs.Read(buffer, 0, 1024);

                    while (bytesRead > 0)
                    {
                        ms.Write(buffer, 0, bytesRead);

                        bytesRead = fs.Read(buffer, 0, 1024);
                    }

                    // Create a copy of the document
                    // Copy the memorybufer, which copies the document
                    ms.WriteTo(stream);

                    // Open a Package from the copied stream
                    // Need to open with FileAccess.ReadWrite or get exceptions
                    _package = Package.Open(stream, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                }
            }   
        }
Aug 4, 2008 at 8:25 PM
I used Ken Tucker's sample and source code changes.  The problem I have now is that IE complains about an invalid file extension.  I've added this to the MIME types for my localhost IIS 6 properties:

xlsx
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Is that the correct MIME type for this solution?

Anybody else have this problem using Ken's sample?

thanks in advance
Aug 11, 2008 at 7:27 PM
I'm going to reply to myself (above) after getting the answer from Ken Tucker.  The correct mime type to add to IIS is

xlsx
application/vnd.openxmlformats


Dec 17, 2008 at 4:49 PM
hi klene,
    i tried your solution but i am able to copy the template file to stream but whatever i am writing going to the separate sheet .. do you have any code sample..
Feb 1, 2010 at 10:21 PM
Edited Feb 2, 2010 at 5:02 PM

Hi Timtas,

can you please post the solution that you found?

Thanks in advance.

Apr 18, 2011 at 2:43 PM

See this link:

http://excelpackage.codeplex.com/discussions/234877