Download file with ClosedXML

Question

All

How can I download a file so the user sees that it is downloading (like with a stream?)

I am currently using ClosedXML, but if I use the SaveAs method, I have to give a hard-coded URL, and if I just give the file name it does not automatically download to the download folder.

The method below works great, but I have to create my own excel file, which is based upon HTML, and the file grows way too large than it should, when I use ClosedXML the file is only 50% or less from the size of the code below: However, the download behaviour is how I would like it to be.

Is there a way I can convert the code below so I can give my 'workbook' as an object, and it just downloads this workbook?

HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
HttpContext.Current.Response.Charset ="UTF-8";    
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType = "application/ms-excel";
ctl.Page.EnableViewState =false;   
System.IO.StringWriter  tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();

Thanks

Outroar

Answer 1

The SaveAs() method supports stream, so to get the ClosedXml workbook as a stream I use:

public Stream GetStream(XLWorkbook excelWorkbook)
{
    Stream fs = new MemoryStream();
    excelWorkbook.SaveAs(fs);
    fs.Position = 0;
    return fs;
}

And then for downloading the file:

string myName = Server.UrlEncode(ReportName + "_" + DateTime.Now.ToShortDateString() + ".xlsx");
MemoryStream stream = GetStream(ExcelWorkbook);

Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();

Fallacy

Hi, I'm getting "System.OutOfMemoryException" on excelWorkbook.SaveAs(fs); – Sapless

@Sapless That should be a new question (or may already be answered elsewhere). Your file may be too big for ClosedXML. – Fallacy

Hi, is it necessary return a memory stream? what if you return an XLWorkbook object directly? – Pimple

Thank you for this. I don't know what I am doing wrong but on postman it is getting 200 OK but it doesn't stop. putting the Position of the Stream to 0 fixed it for me. – Tephra

Answer 2

Old thread, but I couldn't quite get the accepted solution to work right. Some more searching came up with this, which worked just great for me:

        // Create the workbook
        XLWorkbook workbook = new XLWorkbook();
        workbook.Worksheets.Add("Sample").Cell(1, 1).SetValue("Hello World");

        // Prepare the response
        HttpResponse httpResponse = Response;
        httpResponse.Clear();
        httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        httpResponse.AddHeader("content-disposition", "attachment;filename=\"HelloWorld.xlsx\"");

        // Flush the workbook to the Response.OutputStream
        using (MemoryStream memoryStream = new MemoryStream())
        {
            workbook.SaveAs(memoryStream);
            memoryStream.WriteTo(httpResponse.OutputStream);
            memoryStream.Close();
        }

        httpResponse.End();

Redeemable

Yes, still an old thread, but this answer seemed the best to me. Still useful. – Squires

this looks like the example on the project's wiki -- github.com/closedxml/closedxml/wiki/…" – Olmstead

Answer 3

The download can be done somewhat simpler and shorter, so the complete action in your controller could look like this - the download part is just one line instead of seven to ten

public ActionResult XLSX()
{
    System.IO.Stream spreadsheetStream = new System.IO.MemoryStream();
    XLWorkbook workbook = new XLWorkbook();
    IXLWorksheet worksheet = workbook.Worksheets.Add("example");
    worksheet.Cell(1, 1).SetValue("example");
    workbook.SaveAs(spreadsheetStream);
    spreadsheetStream.Position = 0;

    return new FileStreamResult(spreadsheetStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { FileDownloadName = "example.xlsx" };
}

Octagon

Answer 4

If using Asp.Net MVC, basically the same but slightly neater (well I think so:)).

public ActionResult DownloadFile(XXXModel model)
{
    using (var workbook = new XLWorkbook(XLEventTracking.Disabled))
    {
        // create worksheets etc..

        // return 
        using (var stream = new MemoryStream())
        {
            workbook.SaveAs(stream);
            stream.Flush();

            return new FileContentResult(stream.ToArray(),
                   "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                   {
                       FileDownloadName = "XXXName.xlsx"
                   };
        }
    }

Scuttle

hi, I am using asp.net core api and angular. Angular is giving error on receiving response, http failure during parsing – Kristakristal

Answer 5

If you are using MVC just use the File() method as follows:

using (XLWorkbook wb = new XLWorkbook())
 {
  //here you put your data in the WorkBook
  //then create a Memory Stream object
using (MemoryStream stream = new MemoryStream())
  {
   //save the workbook as a MemoryStream
     wb.SaveAs(stream);

   //use the File method to return a File
   return File(stream.ToArray(), "filetype","fileName.extension");
   }
 }

Acth

hi, how to handle the request in frontend angular, I get http failure during parsing for <api-url> – Kristakristal

Answer 6

public ActionResult SendFile()
{
    // Create the workbook
    XLWorkbook workbook = new XLWorkbook();
    workbook.Worksheets.Add("Sample").Cell(1, 1).SetValue("Hello World");

    // Send the file
    MemoryStream excelStream = new MemoryStream();
    workbook.SaveAs(excelStream);
    excelStream.Position = 0;
    return File(excelStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "MyFileName.xlsx");
}

Pilose

You need to explain why this works in order for it to be a good answer. – Lallans

Using this solution i have Encoding problems. – Hawfinch

antoprd has same answer. how is your answer helpfull. – Kristakristal

Answer 7

//Method for Export Excel using Closed Xml
public void ExportDataWithClosedXml_Method(DataTable table, string tabName, string fileType)
{
    var workbook = new XLWorkbook();
    var ws = workbook.Worksheets.Add(table, tabName);
    int row = 2 + table.Rows.Count;
    int col = table.Columns.Count;
    var redRow = ws.Row(1);
    //redRow.Style.Fill.BackgroundColor = XLColor.Red;
    redRow.InsertRowsAbove(1);
    ws.Cell(1, 1).Value = "Name of Report Type";
    ws.Cell(1, 1).Style.Font.Bold = true;
    ws.Table(0).ShowAutoFilter = false;
    //ws.Row(2).Style.Fill.BackgroundColor = XLColor.Red;
    ws.Range(2, 1, 2, col).Style.Fill.BackgroundColor = XLColor.Green;
    ws.Range(2, 1, 2, col).Style.Font.Bold = true;
    ws.Range(3, 1, row, col).Style.Font.Italic = true;

    HttpContext.Current.Response.Clear();
    using (MemoryStream memoryStream = new MemoryStream())
    {
        workbook.SaveAs(memoryStream);
        memoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
        memoryStream.Close();
    }
    if (fileType == "xlsx")
    {
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=\"Samplefile.xlsx\"");
    }
    else
    {
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=\"Samplefile.xls\"");
    }
    HttpContext.Current.Response.End();
}

Player

Copy all the code from starting and in the view ----------------------------------@using (Html.BeginForm("ExportDataWithClosedXml", "Client", FormMethod.Post, new { enctype = "multipart/form-data" })) { <table> <tr> <td></td> <td><input type="submit" name="ExportDataWithClosedXml" id="ExportDataWithClosedXml" value="ExportDataWithClosedXml" /></td> <td><input type="hidden" id="myHiddenId" runat="server" /></td> </tr> </table> }--------------- "ExportDataWithClosedXml" is the controller name. – Player