How to Export datagridview to Excel
The DataGridView control provides a customizable table for displaying data. Displaying data in a tabular format is a task you are likely to perform frequently. The DataGridView control is highly configurable and extensible, and it provides many properties, methods, and events to customize its appearance and behavior.
The following vb.net source code shows how to Export the content of a datagridview to an Excel file.
Imports System.Data.SqlClient Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connectionString As String = "Data Source=.;Initial Catalog=pubs;Integrated Security=True" Dim sql As String = "SELECT * FROM Authors" Dim connection As New SqlConnection(connectionString) Dim dataadapter As New SqlDataAdapter(sql, connection) Dim ds As New DataSet() connection.Open() dataadapter.Fill(ds, "Authors_table") connection.Close() DataGridView1.DataSource = ds DataGridView1.DataMember = "Authors_table" End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim i As Int16, j As Int16 xlApp = New Excel.ApplicationClass xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") For i = 0 To DataGridView1.RowCount - 2 For j = 0 To DataGridView1.ColumnCount - 1 xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i).Value.ToString() Next Next xlWorkBook.SaveAs("c:\vb.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _ Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue) xlWorkBook.Close(True, misValue, misValue) xlApp.Quit() releaseObject(xlWorkSheet) releaseObject(xlWorkBook) releaseObject(xlApp) MessageBox.Show("Over") End Sub Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing MessageBox.Show("Exception Occured while releasing object " + ex.ToString()) Finally GC.Collect() End Try End Sub End Class
Related Topics
- VB.NET DataGridView binding - Sql Server
- DataGridView binding - OLEDB in VB.NET
- DataGridView Sorting/Filtering in VB.NET
- DataGridView adding rows and columns in VB.NET
- DataGridView hiding rows and columns in VB.NET
- DataGridView ReadOnly rows and columns in VB.NET
- Adding Button to DataGridView in VB.NET
- Adding CheckBox to DataGridView in VB.NET
- Adding ComboBox to DataGridView in VB.NET
- Adding Image to DataGridView in VB.NET
- Adding ViewLink to DataGridView in VB.NET
- How to Paging in DataGridView
- How to Formatting in DataGridView
- How to DataGridView Template
- How to DataGridView Printing
- How to Import data from Excel to DataGridView
- Database operations in DatagridView
- Delete row from datagridview by Right click
- DataGridView Autocomplete TextBox in VB.Net