Export Data from Dataset or Datatable to Excel File

Save Data from DataSet or DataTable to Excel File (VB.NET)

Namespace that needs to import for excel:
Imports Microsoft.Office.Interop.Excel

Add instance of excel application
Excel = New Excel.Application
Excel.SheetsInNewWorkbook=3

Add Workbook to the Worksheet (We can Add More Workbooks Too in Similar Way)
Excel.Workbooks.Add()

Create new worksheet
createWorksheet("Workbook Name")

Show Dialog Box
Dim dlgsave As New SaveFileDialog
dlgsave.Filter = "Excel Spreadsheets (*.xls)|*.xls"
If dlgsave.ShowDialog() = DialogResult.OK Then

Insert Column Name into the excel sheet
For iDColumn = 0 To dtShareholders.Columns.Count - 2
Excel.Cells(iRRow, iRColumn).Value = dtShareholders.&_
Columns(iDColumn + 1).ColumnName.ToString
Excel.Cells(iRRow, iRColumn).Borders.Weight = 2
Excel.Cells(iRRow, iRColumn).Interior.ColorIndex = 49
Excel.Cells(iRRow, iRColumn).Font.ColorIndex = 2
Excel.Cells(iRRow, iRColumn).Font.Bold = True
Excel.Cells(iRRow, iRColumn).FormulaHidden = True
Excel.Cells(iRRow, iRColumn).RowHeight = 25
Excel.Cells(iRRow, iRColumn).VerticalAlignment = xlCenter
iRColumn = iRColumn + 1
Next

This loop will print all rows one by one into the excel sheet
iRRow = iRRow + 1
For iDRow = 0 To dtShareholders.Rows.Count - 2
iRColumn = 1
Excel.Cells(iRRow, iRColumn).Value = dtShareholders.&_
Rows(iDRow).ItemArray(0).ToString
Excel.Cells(iRRow, iRColumn).EntireColumn.AutoFit()
Excel.Cells(iRRow, iRColumn).Borders.Weight = 2
Excel.Cells(iRRow, iRColumn).Font.Bold = True
Excel.Cells(iRRow, iRColumn).RowHeight = 15
Excel.Cells(iRRow, iRColumn).HorizontalAlignment = xlLeft
iRRow = iRRow + 1
Next

Set the Selected Sheet in the Workbook
Excel.Worksheets(1).select()
Dim strFileName As String
strFileName = IO.Path.GetDirectoryName&_
(System.Reflection.Assembly.GetExecutingAssembly&_
.Location) & "\SheetName"
If IO.File.Exists(strFileName & ".xls") Then
IO.File.Delete(strFileName & ".xls")
End If

Save the active workbook into the given location and delete if there is any file with the same name on the same location already exist.
Excel.ActiveWorkbook().SaveAs(strFileName & ".xls")
Dim destination As String = dlgsave.FileName
''Check if the file Saved or not
If destination <> "" Then
If IO.File.Exists(destination) Then
IO.File.Delete(destination)
End If

System.IO.File.Copy(strFileName & ".xls", destination
MessageBox.Show("Excel Sheet saved successfully",&_
mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Report Is Not Saved, Operation Canceled By User",&_
mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
dlgsave.Dispose()