只是打电话.Quit()不会从内存中删除应用程序。完成编码后关闭对象非常重要。这确保了所有对象都被正确释放并且内存中没有任何内容。
看这个例子
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
'~~> Define your Excel Objects
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'~~> Add a New Workbook
xlWorkBook = xlApp.Workbooks.Add
'~~> Display Excel
xlApp.Visible = True
'~~> Do some stuff Here
'~~> Save the file
xlWorkBook.SaveAs(Filename:="C:\Tutorial\SampleNew.xlsx", FileFormat:=51)
'~~> Close the File
xlWorkBook.Close()
'~~> Quit the Excel Application
xlApp.Quit()
'~~> Clean Up
releaseObject (xlApp)
releaseObject (xlWorkBook)
End Sub
'~~> Release the objects
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
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub
End Class
另外值得一提的是2 点规则 http://www.siddharthrout.com/2012/08/06/vb-net-two-dot-rule-when-working-with-office-applications-2/.
如果您喜欢从 VB.Net 自动化 Excel 那么您可能还想看看这个link http://www.siddharthrout.com/vb-dot-net-and-excel/.
FOLLOWUP
问题在于我上面提到的 2 DOT 规则。当您使用 2 DOT 规则时（例如：Excel.XlBordersIndex.xlDiagonalDown）那么你必须使用以下方法进行垃圾收集GC.Collect()。所以你需要做的就是添加这部分
Finally
GC.Collect()
in the Private Sub ReleaseObject(ByVal obj As Object)
Private Sub ReleaseObject(ByVal obj As Object)
Try
Dim intRel As Integer = 0
Do
intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Loop While intRel > 0
MsgBox("Final Released obj # " & intRel)
Catch ex As Exception
MsgBox("Error releasing object" & ex.ToString)
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
最终代码
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As New excel.Application
Dim xlWorkBook As excel.Workbook
Dim xlWorkSheet As excel.Worksheet
Dim xlRange As excel.Range
'Dim misValue As Object = System.Reflection.Missing.Value
xlWorkBook = xlApp.Workbooks.Add
xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)
xlApp.Visible = True
Dim headers = (From ch In DataGridView1.Columns _
Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
Select header.Value).ToArray()
Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)
Dim items() = (From r In DataGridView1.Rows _
Let row = DirectCast(r, DataGridViewRow) _
Where Not row.IsNewRow _
Select (From cell In row.Cells _
Let c = DirectCast(cell, DataGridViewCell) _
Select c.Value).ToArray()).ToArray()
Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
For Each a In items
Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
table &= String.Join(vbTab, t) & Environment.NewLine
Next
table = table.TrimEnd(CChar(Environment.NewLine))
Clipboard.SetText(table)
Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray
xlRange = xlWorkSheet.Range("B2:" & alphabet(headerText.Length) & (items.Length + 2).ToString)
xlRange.Select()
xlWorkSheet.Paste()
xlRange.Borders(excel.XlBordersIndex.xlDiagonalDown).LineStyle = excel.XlLineStyle.xlLineStyleNone
xlRange.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone
With xlRange.Borders(excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlMedium
End With
With xlRange.Borders(excel.XlBordersIndex.xlEdgeTop)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlMedium
End With
With xlRange.Borders(excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlMedium
End With
With xlRange.Borders(excel.XlBordersIndex.xlEdgeRight)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlMedium
End With
With xlRange.Borders(excel.XlBordersIndex.xlInsideVertical)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlThin
End With
With xlRange.Borders(excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = excel.XlLineStyle.xlContinuous
.ColorIndex = 1 'black
.TintAndShade = 0
.Weight = excel.XlBorderWeight.xlThin
End With
xlWorkBook.SaveAs(Filename:="C:\Users\Siddharth Rout\Desktop\Word1.xls", FileFormat:=56)
xlWorkBook.Close()
xlApp.Quit()
ReleaseObject(xlRange)
ReleaseObject(xlWorkSheet)
ReleaseObject(xlWorkBook)
ReleaseObject(xlApp)
End Sub
Private Sub ReleaseObject(ByVal obj As Object)
Try
Dim intRel As Integer = 0
Do
intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Loop While intRel > 0
MsgBox("Final Released obj # " & intRel)
Catch ex As Exception
MsgBox("Error releasing object" & ex.ToString)
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
