how to save rows from datatable into excel and open excel file?

[sh2014]

hi

i used this codes for save and open excel file :

Public Sub ExportToExcel(ByVal dtTemp As DataTable, ByVal FilePath As String, ByVal strSheetName As String)
Dim strFileName As String = FilePath
If System.IO.File.Exists(strFileName) Then
If (MessageBox.Show("Do you want to replace from the existing file?", "Export to Excel", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = System.Windows.Forms.DialogResult.Yes) Then
System.IO.File.Delete(strFileName)
Else
Return
End If

End If

Dim _excel As New Excel.Application
Dim wBook As Excel.Workbook
Dim wSheet As Excel.Worksheet
Dim xlWorkBooks As Excel.Workbooks = Nothing

wBook = _excel.Workbooks.Add()

wSheet = wBook.ActiveSheet()
wSheet.Name = strSheetName

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
If CheckBox1.Checked Then


Export the Columns to excel file

For Each dc In dt.Columns
colIndex = colIndex + 1
wSheet.Cells(1, colIndex) = dc.ColumnName
Next
End If


Export the rows to excel file
For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
wSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
wSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName).ToString()
Next
Next
Save file in final path

wSheet.Columns.AutoFit()
wBook.SaveAs(strFileName)




open excel file and see rows
_excel.DisplayAlerts = False
xlWorkBooks = _excel.Workbooks
wBook = xlWorkBooks.Open(FilePath)
_excel.Visible = True
wSheet = wBook.Sheets



Some time Office application does not quit after automation:
so i am calling GC.Collect method.

GC.Collect()


Release the objects

ReleaseObject(wSheet)
wBook.Close(False)
ReleaseObject(wBook)
_excel.Quit()
ReleaseObject(_excel)

MessageBox.Show("File Export Successfully!")
End Sub
Private Sub ReleaseObject(ByVal o As Object)
Try
While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
End While
Catch
Finally
o = Nothing
End Try
End Sub

now please check my codes and edited it for best result

thanks all



Name of Allah, Most Gracious, Most Merciful and He created the human

Continue reading...