export dataset to excel using c# (Mvc)

Question

I tried to export the dataset which have 2 tables to the excel sheet, unfortunately I can't.

I have code to export data table to excel. So instead of dataset, I called the ExportToExcel function which I have in my code to export datatable to excel 4 times. But once it created the first sheet, it stops the control flow. Control doesn't call the second function

ExportToExcel(getReports.Tables[1], "ConsumerBookedSlots");

Here is the code:

public ActionResult GetCuratorsAvailability(string availabilitydate)
        {
            string fromDate = "";
            string endDate = "";
            if (availabilitydate != "")
            {
                 fromDate = availabilitydate.Split('-')[0];
                 endDate = availabilitydate.Split('-')[1];
                if (DateTime.Parse(endDate) >= DateTime.Parse(fromDate))
                {
                    DataSet getReports = AdminBLL.GetCuratorsAvailability(fromDate, endDate);
                    ExportToExcel(getReports.Tables[0], "CuratorsAvailableSlots");                    
                    ExportToExcel(getReports.Tables[1], "ConsumerBookedSlots");                                      
                }
            }            
            return View("Reports");
        }

          public void ExportToExcel(DataTable dt, string FileName)
        {
            if (dt.Rows.Count > 0)
            {
                if (System.Web.HttpContext.Current.Response.RedirectLocation == null)
                {                   
                    string filename = FileName + ".xls";
                    System.IO.StringWriter tw = new System.IO.StringWriter();
                    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
                    DataGrid dgGrid = new DataGrid();
                    dgGrid.DataSource = dt;
                    dgGrid.DataBind();
                    //Get the HTML for the control.
                    dgGrid.RenderControl(hw);
                    //Write the HTML back to the browser.
                    //Response.ContentType = application/vnd.ms-excel;                                         
                    Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
                    Response.ContentType = "application/vnd.ms-excel";                  
                    Response.Write(tw.ToString());                   
                    Response.Flush();                 
                    Response.End();
                }
            }   
        }       

I am unable to download getReports.Tables[1] data because I am getting this error:

server cannot append header after http headers have been sent. mvc

And it is downloading firstfile after error in the browser.

Answer 1

After the download of the first file, the execution hits this line-

Response.End();

That means, the response is ended, and headers have been sent to the client. There's no way you can initiate the download of the second file. If you want to download multiple files in a single button click, you need to zip it into one, and then initiate the download.

To zip the files you can do this-

using System;
using System.IO;
using System.IO.Compression;

namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            string startPath = @"c:\example\start";
            string zipPath = @"c:\example\result.zip";
            string extractPath = @"c:\example\extract";

            ZipFile.CreateFromDirectory(startPath, zipPath);

            ZipFile.ExtractToDirectory(zipPath, extractPath);
        }
    }
}

Snippet from here.

If you want to make both excel spreadsheets a part of single excel document by putting each one of them as a worksheet, you can use ClosedXML.

Answer 2

I've used ClosedXML (an OpenXML implementation) several times, including in an ASP.NET MVC application and it works like a charm.

Exporting data is a breeze:

using (var memoryStream = new MemoryStream())
{
    using (XLWorkbook workbook = new XLWorkbook())
    {
        using (IXLWorksheet worksheet = workbook.AddWorksheet("WorksheetName"))
        {
            var toExport = GetData();
            worksheet.Row(1).Style.Font.Bold = true;
            worksheet.Cell(1, 1).Value = "Column 1";
            worksheet.Cell(1, 2).Value = "Column 2";
            worksheet.Cell(1, 3).Value = "Column 3";

            // Export the data and set some properties           
            worksheet.Cell(2, 1).Value = toExport.AsEnumerable();
            worksheet.RangeUsed().SetAutoFilter();
            worksheet.Columns().AdjustToContents();
            workbook.SaveAs(memoryStream);
            memoryStream.Position = 0;

            return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "filename.xlsx");
        }
    }
}