First we must declare these in top of the class file.
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Now, just copy this code in Button Click event
Private Sub BtnSql2Excel_Click(sender
As System.Object,
e As System.EventArgs)
Handles Button2.Click
ProgressBar1.Value = 0
ProgressBar1.Maximum = DataGridView1.RowCount
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")
'Excel Header Row
Dim columnsCount As Integer = DataGridView1.Columns.Count
For Each column In DataGridView1.Columns
xlWorkSheet.Cells(1, column.Index + 1).Value = column.Name
Next
'Excel Header Row End
'Excel Details Row
For i = 0 To
DataGridView1.RowCount - 1
For j = 0 To
DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
Next
ProgressBar1.Value = ProgressBar1.Value + 1
Next
'Excel Details Row End
xlWorkBook.Activate()
FolderBrowserDialog1.ShowDialog()
Dim path As String = FolderBrowserDialog1.SelectedPath
xlWorkBook.SaveAs(path & "\SqltoExcel.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)
MsgBox("Find Excel File at "
& path & "\SqltoExcel.xls",
MsgBoxStyle.Information, "Convert Excel Successfully")
End Sub
Then just create a function named ReleaseObject
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
Now, execute the program...
If there is any other better option,
Thank You,
Arka Gupta.
