How to write data to excel file and download it in asp.net

Question

i have develop an application for online store in which different store keep there catalog online. but i have to develop an functionality for download there catalog in xls file for that i have my data in datatable which i have to write in dynamically generated xls file and download it.

for that i have try fallowing :

DataTable ProductDetails = sql.ExecuteSelectCommand("SELECT *  FROM Products_Details_View WHERE   Supp_Id = " + Session["SuppID"].ToString() + " and Is_Available = 1"); 
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename=Catalog.xls");
Response.ContentType = "application/excel";
Response.Write(ProductDetails);
Response.End();

i refer it here

but am not getting any thing

please help to get out of it.

Solution

I use the EPPlus package, which you can install via Nuget. It allows you to load data onto an Excel worksheet directly from your datatable, and it includes support for things like formatting on the worksheet (fonts, column widths etc). See their documentation page here on using it inside a web application.

For your case, I would suggest something like:

DataTable ProductDetails = sql.ExecuteSelectCommand("SELECT *  FROM Products_Details_View WHERE Supp_Id = " + Session["SuppID"].ToString() + " and Is_Available = 1"); 

using (ExcelPackage pck = new ExcelPackage())
{
    //Create the worksheet
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");

    //Load the datatable into the sheet, starting from cell A1. 
    //Print the column names on row 1
    ws.Cells["A1"].LoadFromDataTable(ProductDetails, true);

    //Write it back to the client
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;  filename=ProductDetails.xlsx");
    Response.BinaryWrite(pck.GetAsByteArray());
}