This project has moved and is read-only. For the latest updates, please go here.

Saving to a stream in ASP.NET MVC?

Mar 2, 2011 at 11:53 PM

Has anyone actually gotten ClosedXML to work saving to the OutputStream in ASP.NET MVC?  I'm trying to create an ExcelActionResult that returns an Excel file created via ClosedXML.  I always get this exception when calling SaveAs():

Cannot open package because FileMode or FileAccess value is not valid for the stream.

My ExecuteResult() method looks something like this:

HttpResponseBase response = context.HttpContext.Response;
response.Clear();
response.Charset = "UTF-8";
response.ContentEncoding = System.Text.Encoding.UTF8;
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.AddHeader("content-disposition", "attachment;filename=\"Excel.xlsx\"");

XLWorkbook excel = new XLWorkbook();
// fill in excel w/ data

excel.SaveAs(response.OutputStream);
response.End();
Mar 3, 2011 at 12:02 AM

To answer my own post, it looks like ClosedXML cannot save directly to the response because the stream is not readable/seekable.  Writing to a temporarly location and then copying that to the HTTP stream works fine.  Replace SaveAs() above with the following: 

using (MemoryStream ms = new MemoryStream())
{
	excel.SaveAs(ms);
	ms.Position = 0;
	ms.CopyTo(response.OutputStream);
}

 

Mar 23, 2011 at 7:49 PM
Edited Mar 23, 2011 at 7:50 PM

Yes. You answered your own question.

I do the same thing, but use the MVC File ActionResult rather than directly manipulating the Response object.

Here is the code that creates a spreadsheet and writes it into a MemoryStream

        public MemoryStream CreateExcelFile()
        {
            try
            {
                // Create an Excel Workbook
                XLWorkbook workbook = new XLWorkbook();
                // Add the "Product Index" worksheet
                IXLWorksheet sheet = workbook.Worksheets.Add("New Worksheet");
                // Add your data ...


                // run autofit on all the columns
                sheet.Columns().AdjustToContents();
               // Mark the first row as BOLD
                sheet.FirstRow().Style.Font.Bold = true;
                // All done
                MemoryStream ms = new MemoryStream();
                workbook.SaveAs(ms);
                return ms;
            }
            catch (Exception e)
            {
                string errmsg = String.Format("Failed to create Excel file: {0}", e.Message);
                throw new Exception(errmsg, e);
            }
        }

Here is the code from the HttpPost Action response
[HttpPost]
public ActionResult Download()
{
	MemoryStream ms = CreateExcelFile();
	if (ms != null)
	{
		// return the filestream
		// Rewind the memory stream to the beginning
		ms.Seek(0, SeekOrigin.Begin);
		string filename = "DownloadedExcelFile.xlsx";
		return File(ms, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", filename);
	}
	// No Excel file, reshow the same view
	return View();
}