About GridView Data To Excel C#

This Article will show on how to Transfer the GridView Data to Excel.

1. Suppose We have Data in GridView. Code Below will show on how to Load a Data in GridView.

//A sql query to get data from database
string sSQL = "Select * from TableName";

//Set DataTable
DataTable dt = new DataTable();

//Set SqlConnection
//yourconnectionstring ===> the sample of this string:Data Source=testserver;Initial Catalog=sampledatabase;User ID=sa;Password=sa
SqlConnection conn = New SqlConnection("yourconnectionstring");

//Set Command and DataAdapter and fill DataAdapter
SqlCommand cmd = new Sqlcommand(sSQL,conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.fill(dt);

//This Session("LoadGrid") we will use it in excel
Session("LoadGrid") = dt;

//Datasource of your Grid and Bind it.
GridView1.DataSource = Session("LoadGrid");
GridView1.DataBind();

2. Suppose we have Download Button to Excel. In the event of this button copy and paste the code Below.

Response.ClearContent()
Response.Write("NAME OF YOUR REPORT
")
Response.Write("As of " & Date.Now.ToLongDateString & "
")
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment;filename=FILENAME OF YOUR REPORT_" & Date.Now.ToString("(MM dd yyyy)") & ".xls")

'Prepare to export the DataGrid
Dim oStringWriter As New System.IO.StringWriter
Dim oHtmlTextWriter As New HtmlTextWriter(oStringWriter)

Dim dgExport As New GridView
dgExport.DataSource = Session("LoadGrid")
dgExport.DataBind()

dgExport.RowStyle.VerticalAlign = VerticalAlign.Top
dgExport.HeaderRow.Style.Value = "background-color: #00acc0; color: #fff;"
dgExport.Style.Value = "font-family: Verdana;"

'Use the DataGrid control to add the details
dgExport.RenderControl(oHtmlTextWriter)

'Finish the Excel spreadsheet and send the response
Response.Write(oStringWriter.ToString())
dgExport.Dispose()

Response.End()




Quick Info: http://dotnetspider.com/sites/450/Forum-914-GridView-To-Excel.aspx

Reference: http://dotnetspider.com/sites/450/Forum-914-GridView-To-Excel.aspx