How to export datagridview to excel sheet?

This resource is about datagridview to excel sheet exporting. In many application clients are demanding for such facility to store their data in excel sheet. This article shows the automatic arrangement of data into excel sheet. User can also create multiple sheets if he/she requires.

One way for fulfilling this requirement is first have to create crystal report and then by using export facility of crystal report we can export our whole data into excel sheet.

Instead of doing such method we are having another way to export whole data into excel sheet by using datagridview so No need to design crystal report

sample of code is given below and .dlls are also use to complete this task.

Imports System.IO
Imports System.Data.SqlClient

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

Dim i, j As Integer

Dim ds As DataSet
Dim sda As New SqlDataAdapter
Dim scon As SqlConnection
Dim str As String = "DATABASE PATH"
Dim scom As SqlCommand
Dim sql As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
sql = "select *from detail"
scon = New SqlConnection(str)
scom = New SqlCommand(sql, scon)
sda = New SqlDataAdapter(scom)
ds = New DataSet


DataGridView1.DataSource = ds.Tables(0)
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim exc As New Excel.ApplicationClass
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

wb = exc.Workbooks.Add()
ws = wb.ActiveSheet()

Dim dt As System.Data.DataTable = DataGridView1.DataSource()
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
exc.Cells(1, colIndex) = dc.ColumnName

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


Dim strFileName As String = "D:\view_all.xls"
Dim blnFileOpen As Boolean = False
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
Catch ex As Exception
blnFileOpen = False
End Try

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

exc.Visible = True

End Sub
End Class


No responses found. Be the first to comment...

  • 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: