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.

Cooleshwar · Accepted Answer · 2017-09-01 11:51:43Z

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.

rickvdbosch · Accepted Answer · 2017-09-01 11:46:15Z

0

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

answered Sep 1, 2017 at 11:46

rickvdbosch

15.8k2 gold badges47 silver badges60 bronze badges

1 Comment

Cooleshwar Over a year ago

Rick, ClosedXML is a good solution to export data in excel spreadsheet, but your solution doesn't allow the download of second excel file (right after the first one is done), as asked in the question.

Collectives™ on Stack Overflow

export dataset to excel using c# (Mvc)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related