This project has moved. For the latest updates, please go here.

Streaming the output directly to the browser

Jul 19, 2010 at 5:39 AM

Hello everybody,

 

Hope that one of you have done this already and can share it with me.

I'm using this package to use an excel template file to generate monthly reports.

these reports should be exported by a click of a button in the browser.

 

current procedure is :

1- user clicks the button

2- template file is being filled with numbers

3- saved on the server (on a folder)

4- then, this file from the server is sent to the users browser for him to download.

 

 

 

what i would like to do is: eliminate step number 3, which is saving to the server

I want that file to be loaded (stream) in the memory then sent directly to the users browser.

 

 

Jul 19, 2010 at 5:41 AM

this is my current code:

==================================

protected void Button1_Click(object sender, EventArgs e)
{
	string dtnow = DateTime.Now.ToString("yy-mm-dd-_hh-mm-ss");
	string fnamenow = dtnow + ".xlsx";
	string filename = Request.PhysicalApplicationPath + @"finalReport\ExportedExcels\" + fnamenow;
	string templatename = Request.PhysicalApplicationPath + @"includes\ExcelTemplate\Book1.xlsx";

	newFile = new FileInfo(filename);
	template = new FileInfo(templatename);

	// ExcelPackage excel = new ExcelPackage(newFile, template);
	using (excely = new ExcelPackage(newFile, template))
	{
		#region WorkStation
		worksheet = excely.Workbook.Worksheets[3];
		grid = (GridView)GridView6;
		startRow = 6;
		row = startRow;
		if (worksheet != null)
		{
			foreach (GridViewRow y in grid.Rows)
			{
				worksheet.Cells[row, 1].Value = y.Cells[1].Text.ToString().Trim();//Business Line
				worksheet.Cells[row, 2].Value = Convert.ToInt32(y.Cells[2].Text.ToString().Trim());//Year End Actual
				worksheet.Cells[row, 3].Value = Convert.ToInt32(y.Cells[3].Text.ToString().Trim());//Additions
				worksheet.Cells[row, 4].Value = Convert.ToInt32(y.Cells[4].Text.ToString().Trim());//Returns
				worksheet.Cells[row, 6].Value = Convert.ToInt32(y.Cells[5].Text.ToString().Trim());//Additions
				row++;
			}
		}
		#endregion
		excely.Save();
	}

	//Set the appropriate ContentType.
	Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
	Response.AppendHeader("Content-Disposition", "attachment; filename=" + fnamenow);

	//Write the file directly to the HTTP content output stream.
	Response.WriteFile(filename);
	Response.End();
}

Coordinator
Jul 19, 2010 at 7:26 AM
Hi, Have a look at the web samples project ... Use the constructor like this... using (excely = new ExcelPackage(template, true)) { ... } Jan
Jul 19, 2010 at 11:39 AM

Hello Jan

 

I have used this method

and for saving i'm using:

//  Response.BinaryWrite(excely.GetAsByteArray());
excely.SaveAs(Response.OutputStream);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;  filename=Sample3.xlsx");
 

unfortunately, both the output stream and binary write produce an error while opening the file.

"Excel found unreadable content...." << because of the styling.

is there anything extra i can do ?

Editor
Jul 19, 2010 at 11:44 AM

                    Call response.clear before send stream back to client.

                    Response.Clear();
                    Response.AddHeader("content-disposition", "attachment;  filename=file.xlsx");
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";                   
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();

Jul 19, 2010 at 1:51 PM

If you are using MVC 2.0 it's simply as that:

 

 

        public ActionResult Export()
        {
            var template = new FileInfo(Path.Combine(Request.PhysicalApplicationPath, @"Content\template.xlsx"));
            using (ExcelPackage package = new ExcelPackage(template, true))
            {
                // fill cells
                //...

                return File(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Data.xlsx");
            }
        }

 

 

Jul 20, 2010 at 4:02 AM
mrxrsd wrote:

                    Call response.clear before send stream back to client.

                    Response.Clear();
                    Response.AddHeader("content-disposition", "attachment;  filename=file.xlsx");
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";                   
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();

Thanks mrxrsd

 

that worked with me.

 

and thanks MrBlueSky,

I hope that someone can use this someday, I'm using the traditional way of doing things.

Sep 9, 2010 at 2:02 PM
mo9a7i wrote:
mrxrsd wrote:

                    Call response.clear before send stream back to client.

                    Response.Clear();
                    Response.AddHeader("content-disposition", "attachment;  filename=file.xlsx");
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";                   
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();

Thanks mrxrsd

 

that worked with me.

 

and thanks MrBlueSky,

I hope that someone can use this someday, I'm using the traditional way of doing things.

 

am using the same code to stream an excel file to my browser. Its working fine on my development machine (windows 7, visual studio 2008 prof & IE 8) but is giving  error on the downloading client machine is windows vista with IE7 (error :excel found unreadable content in....Do you want to recover the contents of this workbook?If you trust the source of this workbook....

please, what is the better coding way to fix this?

thank you

Coordinator
Sep 9, 2010 at 8:11 PM

Hi,

That should work fine.

Try to save the file to disk (on the client) and then open it in notepad (or any other editor) to see if the server returns some kind of error.

Jan