saving dataset in excel and allow user to download it in the client machine.

Question

Hi,

I am developing an application where i want i am displaying a dataset in the datagrid view for the user.Now the user wants to download the data in the datagridview in an excel format.How can i do it ?

1) should i write the dataset in the excel and save it the server before the user download the file ?

2) Can i use a hyper link and set the path of the file that is saved in the server to the hyper link hRef property , so that the user can click and download the file ?

I am using C# ASP.net 2.0

Please help !

Answer 1

no need to save the file to the sever use the following code, will create the file on the fly:

Public Shared Sub DataTableToExcel(ByVal dt As DataTable, ByVal FileName As String
  HttpContext.Current.Response.Clear()
  HttpContext.Current.Response.Write(Environment.NewLine)
  For Each row As DataRow In dt.Rows
    For i As Integer = 0 To dt.Columns.Count - 1
      HttpContext.Current.Response.Write(row(i).ToString().Replace(";", String.Empty) + ";")
    Next

    HttpContext.Current.Response.Write(Environment.NewLine)
  Next

  HttpContext.Current.Response.ContentType = "application/ms-excel"
  HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName + ".xls")
  HttpContext.Current.Response.[End]()
End Sub

Answer 2

you can use this simple code to accomplish

StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);

    string attachment = "attachment; filename=SummaryReport" + DateTime.Now.ToString() + ".xls";

    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/ms-excel";


    grd.RenderControl(htw);

    Response.Write(sw.ToString());
    Response.End();

Answer 3

SpreadsheetGear for .NET will do it.

You can see ASP.NET (C# and VB) samples here and download a free trial here.

Disclaimer: I own SpreadsheetGear LLC