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???
Developer
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