Imports Microsoft
Imports Microsoft.Office.Interop
and add reference dll "Microsoft.Office.Interop.Excel"
function to export data is given below
Public Function ExportToExcel(ByVal a_sFilename As String, ByVal a_sData As DataSet, ByVal a_sFileTitle As String, ByRef a_sErrorMessage As String) As Boolean
a_sErrorMessage = String.Empty
Dim bRetVal As Boolean = False
Dim dsDataSet As DataSet = Nothing
Try
dsDataSet = a_sData
Dim xlObject As Excel.Application = Nothing
Dim xlWB As Excel.Workbook = Nothing
Dim xlSh As Excel.Worksheet = Nothing
Dim rg As Excel.Range = Nothing
Try
xlObject = New Excel.Application()
xlObject.AlertBeforeOverwriting = False
xlObject.DisplayAlerts = False
xlWB = xlObject.Workbooks.Add(Type.Missing)
xlWB.SaveAs(a_sFilename, 56, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
xlSh = DirectCast(xlObject.ActiveWorkbook.ActiveSheet, Excel.Worksheet)
For j = 0 To dsDataSet.Tables(0).Columns.Count - 1
xlSh.Cells(1, j + 1) = _
dsDataSet.Tables(0).Columns(j).ToString()
xlSh.Cells(1, j + 1).Font.Bold = True
Next
For i = 1 To dsDataSet.Tables(0).Rows.Count
For j = 0 To dsDataSet.Tables(0).Columns.Count - 1
xlSh.Cells(i + 1, j + 1) = _
dsDataSet.Tables(0).Rows(i - 1)(j).ToString()
Next
Next
xlSh.Columns.AutoFit()
If String.IsNullOrEmpty(a_sFileTitle) Then
xlObject.Caption = "untitled"
Else
xlObject.Caption = a_sFileTitle
End If
xlWB.Save()
bRetVal = True
Catch ex As System.Runtime.InteropServices.COMException
If ex.ErrorCode = -2147221164 Then
a_sErrorMessage = "Error in export: Please install Microsoft Office (Excel) to use the Export to Excel feature."
ElseIf ex.ErrorCode = -2146827284 Then
a_sErrorMessage = "Error in export: Excel allows only 65,536 maximum rows in a sheet."
Else
a_sErrorMessage = (("Error in export: " & ex.Message) + Environment.NewLine & " Error: ") + ex.ErrorCode
End If
Catch ex As Exception
a_sErrorMessage = "Error in export: " & ex.Message
Finally
Try
If xlWB IsNot Nothing Then
xlWB.Close(Nothing, Nothing, Nothing)
End If
xlObject.Workbooks.Close()
xlObject.Quit()
If rg IsNot Nothing Then
Marshal.ReleaseComObject(rg)
End If
If xlSh IsNot Nothing Then
Marshal.ReleaseComObject(xlSh)
End If
If xlWB IsNot Nothing Then
Marshal.ReleaseComObject(xlWB)
End If
If xlObject IsNot Nothing Then
Marshal.ReleaseComObject(xlObject)
End If
Catch
End Try
xlSh = Nothing
xlWB = Nothing
xlObject = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
End Try
Catch ex As Exception
a_sErrorMessage = "Error in export: " & ex.Message
End Try
Return bRetVal
End Function
try this code
Happy Coding!
:)