Download file with ClosedXML

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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();

Method 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();

Method 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" };
}

Method 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"
                   };
        }
    }

Method 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");
   }
 }

Method 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");
}

Method 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();
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0