Two Functions for populating Excel sheet from a Dataset:
Public Sub populatesheetforRunners(ByVal
dt As DataTable, ByVal
worksheet As String,
ByVal range1 As
String, ByVal
range2 As String,
ByVal i As Integer)
'Here dt is a
datatable from the dataset, worksheet is the name of Active Work Sheet, range1
and range two is the range of Excel sheet like from A2 to Z65000, i is for
starting column position
Dim
xlApp As
Microsoft.Office.Interop.Excel.Application = CType(CreateObject("Excel.Application"),
Microsoft.Office.Interop.Excel.Application)
Dim
xlWorkbook As
Microsoft.Office.Interop.Excel.Workbook = CType(xlApp.ActiveWorkbook,
Microsoft.Office.Interop.Excel.Workbook)
Dim
xlWorksheet As Microsoft.Office.Interop.Excel.Worksheet
Dim
oRng As Microsoft.Office.Interop.Excel.Range
Dim
File As String
= txtFilepath.Text
'txtFilepath.Text
is the Excel File path where the file is located
xlWorkbook = xlApp.Workbooks.Open(File)
xlWorksheet =
xlWorkbook.Worksheets(worksheet)
xlWorksheet.Activate()
xlApp.Visible = True
oRng = xlWorksheet.Range(range1,
range2)
oRng.Value = ""
'Clearing the
range of sheet. All the previous value will be removed
Dim dc As New DataColumn
Dim dr As DataRow
Dim
colIndex As Integer
= 0
Dim
rowIndex As Integer
= 0
Try
For
Each dr In
dt.Rows
rowIndex = rowIndex + 1
colIndex = 0 + (i * 2)
For
Each dc In
dt.Columns
colIndex = colIndex + 1
xlApp.Cells(rowIndex + 3,
colIndex) = dr(dc.ColumnName)
Next
Next
Catch
ex As Exception
End Try
'Data is
writing by the loop to the sheet
xlWorksheet.Cells.Select()
xlWorksheet.Columns.AutoFit()
xlWorksheet.Rows.AutoFit()
Try
xlApp.DisplayAlerts = False
xlWorkbook.Save()
xlApp.ActiveWorkbook.Close()
xlApp.DisplayAlerts = True
xlApp.Quit()
Catch
ex As Exception
Finally
ExcelCleanUp(xlApp, xlWorkbook,
xlWorksheet)
xlWorkbook = Nothing
xlApp = Nothing
dr = Nothing
oRng = Nothing
Dim
process As System.Diagnostics.Process
For
Each process In
System.Diagnostics.Process.GetProcessesByName("EXCEL")
If
(process.MainModule.ModuleName.ToUpper().Equals("EXCEL.EXE"))
Then
process.Kill()
End
If
Next
End Try
End Sub
Private Shared Sub
ExcelCleanUp( _
ByVal oXL As Microsoft.Office.Interop.Excel.Application, _
ByVal oWB As Microsoft.Office.Interop.Excel.Workbook, _
ByVal
oSheet As
Microsoft.Office.Interop.Excel.Worksheet)
GC.Collect()
GC.WaitForPendingFinalizers()
Runtime.InteropServices.Marshal.FinalReleaseComObject(oXL)
Runtime.InteropServices.Marshal.FinalReleaseComObject(oSheet)
Runtime.InteropServices.Marshal.FinalReleaseComObject(oWB)
oSheet = Nothing
oWB = Nothing
oXL = Nothing
End Sub
