I am writing an ASP.Net application that is going to have the ability to export data to an excel document after writing.
Writing to the file is fine and works using Microsoft.Office.Interop.Excel however when after writing the file 1 or 100 times there is always one or more lingering process that simply will not go unless I open a command window and run tskill excel.
the code I am using is as follows
Protected Sub writeToExcel_Click(sender As Object, e As EventArgs) Handles writeToExcel.Click
Dim objExcel As New Excel.Application()
Dim objWorkbook As Excel.Workbook = Nothing
Dim objSheet As Excel.Worksheet = Nothing
objWorkbook = objExcel.Workbooks.Open(Server.MapPath("Files\testExcel.xlsx"))
objSheet = objWorkbook.Worksheets(1)
''this will find the last used row
Dim last As Excel.Range = objSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing)
Dim lastUsedRow As Integer = last.Row
Dim lastUsedColumn As Integer = last.Column
objSheet.Cells(lastUsedRow + 1, 1).value = "55555"
'objExcel.AlertBeforeOverwriting = false;
objExcel.DisplayAlerts = False
Dim newFile As String = String.Format("Files\testExcel-{0}.xlsx", DateTime.Now.ToString("yyyyMMdd"))
Dim path As String = Server.MapPath(newFile)
Try
objWorkbook.Save()
objWorkbook.Close()
objExcel.Application.Quit()
ReleaseObject(objSheet)
ReleaseObject(objWorkbook)
ReleaseObject(objExcel)
Catch ex As Exception
Console.Write(ex.Message)
Console.Write(ex.StackTrace)
Console.ReadKey()
End Try
End Sub
Private Sub ReleaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
I have seen a number of solutions on stack, none of which have worked for me at the moment
such as this one
and this one too which stack wont allow me to create as a link Excel application not quitting after calling quit
any and all help would very much be appreciated, as when this goes live, I don't want to run into out of memory exceptions if one or more processes linger.
