Export to Excel from Grid View

This code descibes how to export data from gridview to excel sheet.


Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=SalesPersonreport.xls");
Response.Charset = ""; // If you want the option to open the Excel file without saving then // comment out the line below //
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

GridView gvExport = new GridView();// grid a grid typr instance for exporting grid data to excel
if (dsReport.Tables[0].Rows.Count > 0)
{
gvExport.DataSource = dsReport;//objbus.ExporttoExcel(querryvalue);
gvExport.DataBind();
}

gvExport.RenderControl(htmlWrite); // rendering grid data to html writter
Response.Write(stringWrite.ToString()); //this will prompt Download message


Related Articles

Export Data to Excel in CSV format using .NET

Export Data to excel in CSV format. .NET code to export CSV data. Free .NET code to export data to excel. .NET way of exporting data to excel. Explanation of .NET code to export data to excel in CSV.

More articles: Export to Excel

Comments

Author: harjit01 Oct 2009 Member Level: Bronze   Points : 2

If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
Exit Sub
End If

'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
dset.Tables.Add()
'add column to that table
For i As Integer = 0 To DataGridView1.ColumnCount - 1
dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
Next
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To DataGridView1.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To DataGridView1.Columns.Count - 1
dr1(j) = DataGridView1.Rows(i).Cells(j).Value
Next
dset.Tables(0).Rows.Add(dr1)
Next

Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

wBook = excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()

Dim dt As System.Data.DataTable = dset.Tables(0)
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0

For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName
Next

For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

Next
Next

wSheet.Columns.AutoFit()
Dim strFileName As String = "employee_evaluation.xls"
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try

If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If

wBook.SaveAs(strFileName)
excel.Workbooks.Open(strFileName)
excel.Visible = True



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: