VB.Net'te Excel Interop kullanarak bir datatable ın Excel'e aktarılmasını sağlayan metodumuz aşağıdaki gibidir. Metodu kullanabilmeniz için projenize Microsoft.Office.Interop.Excel referansını eklemeniz gerekmektedir.
Private Sub DatatableToExcel(ByVal dtTemp As DataTable)
Try
Dim myCultureInfo As CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
Dim _excel As New Microsoft.Office.Interop.Excel.Application
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
folderBrowser.ShowDialog()
If folderBrowser.SelectedPath <> "" Then
System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-us")
wBook = _excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()
Dim dt As System.Data.DataTable = dtTemp
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
_excel.Cells(1, colIndex) = dc.ColumnName
Next
For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
_excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
wSheet.Columns.AutoFit()
Dim strFilePath As String = folderBrowser.SelectedPath + "\"
Dim strFileName As String = "Sorgu_" + Date.Now.Year.ToString() + Date.Now.Month.ToString() + Date.Now.Day.ToString() + Date.Now.Second.ToString() + ".xlsx"
If System.IO.File.Exists(strFilePath + strFileName) Then
System.IO.File.Delete(strFilePath + strFileName)
End If
wBook.SaveAs(strFilePath + strFileName)
wBook.Close()
_excel.Quit()
System.Threading.Thread.CurrentThread.CurrentCulture = myCultureInfo
MsgBox("Sorgu Sonuç Ekranı Excel'e Aktarılmıştır." + vbCrLf + "Dosyanın Kaydedildiği Yol: " + strFilePath + vbCrLf + "Dosya Adı: " + strFileName)
End If
Catch ex As Exception
End Try
End Sub
