When I export grid data to excel ,Open an excel on messagebox saying Yes
Otherwise I have saved it on some path.
Case when excel object is not getting removed from task manager-
1)Export to excel and Say yes on messagebox saying("Do you want to open an excel")
2)Close an excel and then it is not getting removed from task manager.
I have used following code-
Private Function Export_To_Excel(ByVal I_StrFileName As String, ByVal I_strSheetName As String, ByVal I_strSheetNumber As Integer) As Integer
Dim dicExcel As New Dictionary(Of Integer, Integer)
Dim MyExcelProcessId As Integer = 0
Dim AllProcesses() As Process
Dim blnFileExist As Boolean
Dim i As Long
Dim grdExportGrid As New DataGridView
Dim dtExport As New DataTable
Dim iRow As Integer
Dim ColUpdatedIndex As Integer
Dim ColAmountIndex As Integer
Try
AllProcesses = Process.GetProcessesByName("excel")
For Each ExcelProcess As Process In AllProcesses
dicExcel.Add(ExcelProcess.Id, 1)
Next
oExcel = CreateObject("Excel.Application")
If Dir(I_StrFileName) <> "" Then
oExcel.Workbooks.Open(I_StrFileName)
blnFileExist = True
Else
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(I_strSheetNumber)
oSheet.Name = I_strSheetName
End If
AllProcesses = Process.GetProcessesByName("excel")
For Each ExcelProcess As Process In AllProcesses
If Not dicExcel Is Nothing Then
If dicExcel.ContainsKey(ExcelProcess.Id) = False Then
MyExcelProcessId = ExcelProcess.Id
Exit For
End If
End If
Next
With oSheet
i = 1
For iCol = 0 To grdAccDetails.Columns.Count - 1
If grdAccDetails.Columns(iCol).Visible Then
.Cells(1, i) = grdAccDetails.Columns(iCol).HeaderText.Replace(vbCrLf, "")
.Cells(1, i).EntireRow.Font.Bold = True
.Cells(1, i).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
.Cells(1, i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
.Cells(1, i).Borders.Value = True
If grdAccDetails.Columns(iCol).HeaderText = "Updated At" Then
ColUpdatedIndex = i
End If
If grdAccDetails.Columns(iCol).HeaderText = "Amount" Then
ColAmountIndex = i
End If
i = i + 1
End If
Next
For iRow = 0 To grdAccDetails.Rows.Count - 1
i = 1
For iCol = 0 To grdAccDetails.Columns.Count - 1
If grdAccDetails.Columns(iCol).Visible Then
If grdAccDetails.Columns(iCol).HeaderText = "Updated At" Then
.Cells(iRow + 2, i).numberformat = "dd/mm/yyyy HH:mm:ss"
End If
If grdAccDetails.Rows(iRow).Cells("Operation").Value = "DELETE" Then
.Cells(iRow + 2, i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 128, 128))
End If
If grdAccDetails.Columns(iCol).HeaderText = "Amount" Then
.Cells(iRow + 2, i).numberformat = "###,##0.00"
End If
.Cells(iRow + 2, i).Borders.Value = True
.Cells(iRow + 2, i) = grdAccDetails.Rows(iRow).Cells(iCol).Value
i = i + 1
End If
.Cells(iRow + 1, i).EntireColumn.AutoFit()
Next iCol
Next iRow
End With
oBook.SaveAs(I_StrFileName)
Dim strMSG As String = "Data exported to " & I_StrFileName & "." & vbCrLf & "Do you want to open it?"
If MessageBox.Show(strMSG, appname, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
oExcel.visible = True
Else
AllProcesses = Process.GetProcessesByName("excel")
For Each ExcelProcess As Process In AllProcesses
If ExcelProcess.Id = MyExcelProcessId Then
ExcelProcess.Kill()
Exit For
End If
Next
AllProcesses = Nothing
End If
Export_To_Excel = 1
Catch ex As Exception
Export_To_Excel = 0
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
End Try
End Function