调用退出后Excel应用程序不退出 [英] Excel application not quitting after calling quit

问题描述

我有一个小问题，我似乎无法弄清楚。我正在保存一个DataGridView（它的内容）到一个xls文件。我没有任何问题，除了在我的任务管理器，它仍然显示它正在运行。我已经打电话：

  xlApp.Application.Quit（）
  

这被声明为：

  Dim xlApp As New excel.Application 
  

这似乎不起作用，但这是我让用户选择退出的方式将其导出到Word文档。我不知道我在哪里错了...

这是我的完整代码

 code>导入Word = Microsoft.Office.Interop.Word 
导入Excel = Microsoft.Office.Interop.Excel 
 
公共类Form1 
 
私有子Form1_Load（ByVal sender As System.Object，ByVal e As System.EventArgs）处理MyBase.Load 
对于x As Integer = 1 To 3500 
 DataGridView1.Rows.Add（New Object（）{r & x.ToString&c1，r& x.ToString&c2，r& x.ToString&c3，r& x.ToString& c4，r& x.ToString&c5}）
下一个
 End Sub 
 
 Private Sub Button2_Click（ByVal sender As System.Object，ByVal e作为System.EventArgs）Handles Button2.Click 
 exportToWord（DataGridView1）
 End Sub 
 
 Private Sub Button1_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）句柄Button1.Click 
 Dim xlApp As New Excel.Application 
 Dim xlWorkBook As Excel.Wo rkbook 
 Dim xlWorkSheet As Excel.Worksheet 
'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）_ 
选择header.Value）.ToArray（）
 Dim headerText（）As String = Array.ConvertAll（headers ，函数（v）v.ToString）
 
 Dim items（）=（From r In DataGridView1.Rows _ 
 Let row = DirectCast（r，DataGridViewRow）_ 
 Where not row.IsNewRow _ 
选择（从单元格在row.Cells _ 
让c = DirectCast（单元格，DataGridViewCell）_ 
选择c.Value）.ToArray（））。ToArray（）
 
 Dim table As String = String.Join（v bTab，headerText）&对于每个a在项目中
 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 
 
 Dim range As excel.Range = xlWorkSheet.Range（B2：& alphabet（headerText.Length）&（items.Length + 2）.ToString） 
 
 range.Select（）
 xlWorkSheet.Paste（）
 
 range.Borders（Excel.XlBordersIndex.xlDiagonalDown）.LineStyle = Excel.XlLineStyle.xlLineStyleNone 
 range.Borders（Excel.XlBordersIndex.xlDiagonalUp）.LineStyle = Excel.XlLineStyle.xlLineStyleNone 
带有range.Borders（Excel.XlBordersIndex.xlEdgeLeft）
 .LineStyle = Excel.XlLineStyle.xlContinuous 
 .ColorIndex = 1'black 
 .TintAndShade = 0 
 .Weight = Excel.XlBorderWeight.xlMedium 
 End with 
 With range.Borders（Excel.XlBordersIndex.xlEdgeTop）
 .LineStyle = Excel.XlLineStyle.xlContinuou s 
 .ColorIndex = 1'black 
 .TintAndShade = 0 
 .Weight = Excel.XlBorderWeight.xlMedium 
 End with 
带有range.Borders（Excel.XlBordersIndex。 xlEdgeBottom）
 .LineStyle = Excel.XlLineStyle.xlContinuous 
 .ColorIndex = 1'black 
 .TintAndShade = 0 
 .Weight = Excel.XlBorderWeight.xlMedium 
 End With 
 with range.Borders（Excel.XlBordersIndex.xlEdgeRight）
 .LineStyle = Excel.XlLineStyle.xlContinuous 
 .ColorIndex = 1'black 
 .TintAndShade = 0 
。 Weight = Excel.XlBorderWeight.xlMedium 
 End with 
 With range.Borders（Excel.XlBordersIndex.xlInsideVertical）
 .LineStyle = Excel.XlLineStyle.xlContinuous 
 .ColorIndex = 1'black 
 .TintAndShade = 0 
 .Weight = Excel.XlBorderWeight.xlThin 
结束
带范围.Borders（Excel.XlBordersIndex.xlInsideHorizo​​ntal）
 .LineStyle = Excel.XlLineStyle.xlContinuous 
 .ColorIndex = 1'black 
 .TintAndShade = 0 
 .Weight = Excel.XlBorderWeight.xlThin 
结束
 
'xlApp.Visible = True 
 
 xlWorkBook.SaveAs（C：\Users\CoDeXeR\Desktop\Word1.xls，True）
 xlWorkBook.Close（） 
 xlApp.Application.Quit（）
 
 ReleaseObject（xlWorkSheet）'< ~~~根据已删除的帖子添加评论
 ReleaseObject（xlWorkBook）
 ReleaseObject xlApp）
 
 
 End Sub 
 
 Public Sub exportToWord（ByVal dgv As DataGridView）
'创建Word应用程序
 Dim oWord As Word。应用程序= DirectCast（CreateObject（Word.Application），Word.Application）
 
'创建新单词文档
 Dim oDoc As Word.Document = oWord.Documents.Add（）
 
 
 Dim headers =（From ch In dgv.Columns _ 
 Let header = DirectCast（DirectCast（ch，Da taGridViewColumn）.HeaderCell，DataGridViewColumnHeaderCell）_ 
选择header.Value）.ToArray（）
 Dim headerText（）As String = Array.ConvertAll（headers，Function（v）v.ToString）
 
 Dim items（）=（From r In dgv.Rows _ 
 Let row = DirectCast（r，DataGridViewRow）_ 
 Where not row.IsNewRow _ 
选择（从单元格row.Cells _ 
让c = DirectCast（单元格，DataGridViewCell）_ 
选择c.Value）.ToArray（））。ToArray（）
 
 Dim table As String = String .Join（vbTab，headerText）&对于每个a在项目中
 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 oTable As Word.Table = oDoc.Tables.Add（oDoc.Bookmarks.Item（\endofdoc）。Range，items.Count + 1，headers.Count）
 
 oTable.Range.Paste（）
 
'使第一行加粗，fs 14 +更改textcolor 
 oTable.Rows.Item（1）.range.Font.Bold =& H98967E 
 oTable.Rows.Item（1） .range.Font.Size = 14 
 oTable.Rows.Item（1）.range.Font.Color = Word.WdColor.wdColorWhite 
 
'更改第一行的背景颜色
 oTable.Rows.Item（1）.range.Shading.Texture = Word.WdTextureIndex.wdTextureNone 
 oTable.Rows.Item（1）.range.Shading.ForegroundPatternColor = Word.WdColor.wdColorAutomatic 
 oTable。 Rows.Item（1）.range.Shading.BackgroundPatternColor = Word.WdColor.wdColorLightBlue 
 
''设置表边框
'with oTable.Range.Tables（1）
'与.Borders（Word.WdBo rderType.wdBorderLeft）
'.LineStyle = Word.WdLineStyle.wdLineStyleSingle 
'.LineWidth = Word.WdLineWidth.wdLineWidth100pt 
'.Color = Word.WdColor.wdColorAutomatic 
'End With 
'with .Borders（Word.WdBorderType.wdBorderRight）
'.LineStyle = Word.WdLineStyle.wdLineStyleSingle 
'.LineWidth = Word.WdLineWidth.wdLineWidth100pt 
'.Color = Word .WdColor.wdColorAutomatic 
'End With 
'With .Borders（Word.WdBorderType.wdBorderTop）
'.LineStyle = Word.WdLineStyle.wdLineStyleSingle 
'.LineWidth = Word.WdLineWidth .wdLineWidth100pt 
'.Color = Word.WdColor.wdColorAutomatic 
'End With 
'With .Borders（Word.WdBorderType.wdBorderBottom）
'.LineStyle = Word.WdLineStyle.wdLineStyleSingle 
'.LineWidth = Word.WdLineWidth.wdLineWidth100 pt 
'.Color = Word.WdColor.wdColorAutomatic 
'End With 
'With .Borders（Word.WdBorderType.wdBorderHorizo​​ntal）
'.LineStyle = Word.WdLineStyle.wdLineStyleSingle 
'.LineWidth = Word.WdLineWidth.wdLineWidth050pt 
'.Color = Word.WdColor.wdColorAutomatic 
'End With 
'With .Borders（Word.WdBorderType.wdBorderVertical）
'.LineStyle = Word.WdLineStyle.wdLineStyleSingle 
'.LineWidth = Word.WdLineWidth.wdLineWidth050pt 
'.Color = Word.WdColor.wdColorAutomatic 
'End With 
'.Borders （WORD） $ b'End With 
'保存此单词文档
 oDoc.SaveAs（C： \\用户\CoDeXeR\Desktop\Word1.doc，True）
 oDoc.Close（）
 oWord.Application.Quit（）
'oWord.Visible = True 
 
 End Sub 
 
 Public Sub exportToExcel（ByVal dgv As DataGridView）
 
 End Sub 
 
 Private Sub ReleaseObject（ByVal obj As Object ）
尝试
 System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
 obj = Nothing 
 Catch ex As Exception 
 obj = Nothing 
最后
 GC.Collect（）
结束尝试
 End Sub 
 
结束类
  

解决方案

只需调用 .Quit（）将不会从内存中删除应用程序。完成编码后关闭对象非常重要。这样可以确保所有对象都被正确释放，并且内存中没有任何内容。

看到这个例子

 导入Excel = Microsoft.Office.Interop.Excel 
 
公共类Form1 
'~~>定义你的Excel对象
 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 
'~~>添加新的工作簿
 xlWorkBook = xlApp.Workbooks.Add 
 
'~~>显示Excel 
 xlApp.Visible = True 
 
'~~>做一些东西在这里
 
'~~>保存文件
 xlWorkBook.SaveAs（文件名：=C：\Tutorial\SampleNew.xlsx，FileFormat：= 51）
 
'~~>关闭文件
 xlWorkBook.Close（）
 
'~~>退出Excel应用程序
 xlApp.Quit（）
 
'~~>清理
 releaseObject（xlApp）
 releaseObject（xlWorkBook）
 End Sub 
 
'~~>释放对象
 Private Sub releaseObject（ByVal obj As Object）
尝试
 System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
 obj = Nothing 
 Catch ex作为异常
 obj = Nothing 
最后
 GC.Collect（）
结束尝试
结束子
 
私有子按钮3_Click（ByVal发件人为System.Object，ByVal e As System.EventArgs）Handles Button3.Click 
 Me.Close（）
 End Sub 
结束类
  

另外值得一提的是 2 DOT规则。

如果你喜欢从VB自动化Excel .Net然后你可能还想看看这个链接 。

FOLLOWUP

问题是我上面提到的2 DOT规则。当您使用2 DOT规则（Ex： Excel.XlBordersIndex.xlDiagonalDown ）时，您必须使用 GC.Collect（）执行垃圾收集， 。所以所有你需要做的是添加这个部分

 最后
 GC.Collect（）
  

在 Private Sub ReleaseObject（ByVal obj As Object）

  Private Sub ReleaseObject（ByVal obj As Object）
尝试
 Dim intRel As Integer = 0 
 
 intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
循环while intRel> 0 
 MsgBox（Final Released obj＃& intRel）
 Catch ex As Exception 
 MsgBox（Error release object& ex.ToString）
 obj = Nothing 
最后
 GC.Collect（）
结束尝试
结束子
  

最终代码（试用和测试）

  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，Dat aGridViewColumn）.HeaderCell，DataGridViewColumnHeaderCell）_ 
选择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）_ 
其中不是row.IsNewRow _ 
选择（从单元格开始row.Cells _ 
让c = DirectCast（单元格，DataGridViewCell）_ 
选择c.Value）.ToArray（））。ToArray（）
 
 Dim table As String = String .Join（vbTab，headerText）&对于每个a在项目中
 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 
 
带有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 
与xlRange.Borders（excel.XlBordersIndex.xlEdgeRight）
 .LineStyle = excel.XlLineStyle.xlContinuous 
 .ColorIndex = 1'black 
 .TintAndShade = 0 
。重量= excel.XlBorderWeight.xlMedium 
结束与
与xlRange.Borders（excel.XlBordersIndex.xlInsideVertical）
 .LineStyle = excel.XlLineStyle.xlContinuous 
 .ColorIndex = 1'黑色
 .TintAndShade = 0 
 .Weight = excel.XlBorderWeight.xlThin 
结束
带xlRange.Borders（excel.XlBordersIndex.xlInsideHorizo​​ntal）
 .LineStyle = excel.XlLineStyle.xlContinuous 
 .ColorIndex = 1'black 
 .TintAndShade = 0 
 .Weight = excel.XlBorderWeight.xlThin 
 End with 
 
 xlWorkBook.SaveAs（文件名：=C：\Users\Siddharth Rout\Desktop\Word1.xls，FileFormat：= 56）
 xlWorkBook.Close（）
 xlApp 。$（$）
 
 $ b ReleaseObject（xlRange）
 ReleaseObject（xlWorkSheet）
 ReleaseObject（xlWorkBook）
 ReleaseObject（xlApp）
 End Sub 
 
 
 Private Sub ReleaseObject（ByVal obj As Object）
尝试
 Dim intRel As Integer = 0 
 Do 
 intRel = System.Runtime.InteropServices.Marshal .ReleaseComObject（obj）
循环while intRel> 0 
 MsgBox（Final Released obj＃& intRel）
 Catch ex As Exception 
 MsgBox（Error release object& ex.ToString）
 obj = Nothing 
最后
 GC.Collect（）
结束尝试
结束子
  




Hey guys I have a small problem that I can't seem to figure out. I am saving a DataGridView (it's contents) to an xls file. I have no problem in doing so except in my task manager its still showing up that it's running. I have called:

  xlApp.Application.Quit() 

This is declared as:

  Dim xlApp As New excel.Application

This seems to not work, BUT this is the same way I quit when I let the user choose to export it to a Word Document. Im not sure where I am going wrong...

Here is my complete code

Imports Word = Microsoft.Office.Interop.Word
 Imports Excel = Microsoft.Office.Interop.Excel

 Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    For x As Integer = 1 To 3500
        DataGridView1.Rows.Add(New Object() {"r" & x.ToString & "c1", "r" & x.ToString & "c2", "r" & x.ToString & "c3", "r" & x.ToString & "c4", "r" & x.ToString & "c5"})
    Next
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    exportToWord (DataGridView1)
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 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

    Dim range As excel.Range = xlWorkSheet.Range("B2:" & alphabet(headerText.Length) & (items.Length + 2).ToString)

    range.Select()
    xlWorkSheet.Paste()

    range.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone
    range.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone
    With range.Borders(Excel.XlBordersIndex.xlEdgeLeft)
        .LineStyle = Excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = Excel.XlBorderWeight.xlMedium
    End With
    With range.Borders(Excel.XlBordersIndex.xlEdgeTop)
        .LineStyle = Excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = Excel.XlBorderWeight.xlMedium
    End With
    With range.Borders(Excel.XlBordersIndex.xlEdgeBottom)
        .LineStyle = Excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = Excel.XlBorderWeight.xlMedium
    End With
    With range.Borders(Excel.XlBordersIndex.xlEdgeRight)
        .LineStyle = Excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = Excel.XlBorderWeight.xlMedium
    End With
    With range.Borders(Excel.XlBordersIndex.xlInsideVertical)
        .LineStyle = Excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = Excel.XlBorderWeight.xlThin
    End With
    With range.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
        .LineStyle = Excel.XlLineStyle.xlContinuous
        .ColorIndex = 1 'black
        .TintAndShade = 0
        .Weight = Excel.XlBorderWeight.xlThin
    End With

    'xlApp.Visible = True

    xlWorkBook.SaveAs("C:\Users\CoDeXeR\Desktop\Word1.xls", True)
    xlWorkBook.Close()
    xlApp.Application.Quit()

    ReleaseObject(xlWorkSheet) '<~~~ Added as per comment from deleted post
    ReleaseObject (xlWorkBook)
    ReleaseObject (xlApp)


End Sub

Public Sub exportToWord(ByVal dgv As DataGridView)
    ' Create Word Application
    Dim oWord As Word.Application = DirectCast(CreateObject("Word.Application"), Word.Application)

    ' Create new word document
    Dim oDoc As Word.Document = oWord.Documents.Add()


    Dim headers = (From ch In dgv.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 dgv.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 oTable As Word.Table = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, items.Count + 1, headers.Count)

    oTable.Range.Paste()

    'make the first row bold, fs 14 + change textcolor
    oTable.Rows.Item(1).range.Font.Bold = &H98967E
    oTable.Rows.Item(1).range.Font.Size = 14
    oTable.Rows.Item(1).range.Font.Color = Word.WdColor.wdColorWhite

    'change backcolor of first row
    oTable.Rows.Item(1).range.Shading.Texture = Word.WdTextureIndex.wdTextureNone
    oTable.Rows.Item(1).range.Shading.ForegroundPatternColor = Word.WdColor.wdColorAutomatic
    oTable.Rows.Item(1).range.Shading.BackgroundPatternColor = Word.WdColor.wdColorLightBlue

    ''set table borders
    'With oTable.Range.Tables(1)
    '    With .Borders(Word.WdBorderType.wdBorderLeft)
    '        .LineStyle = Word.WdLineStyle.wdLineStyleSingle
    '        .LineWidth = Word.WdLineWidth.wdLineWidth100pt
    '        .Color = Word.WdColor.wdColorAutomatic
    '    End With
    '    With .Borders(Word.WdBorderType.wdBorderRight)
    '        .LineStyle = Word.WdLineStyle.wdLineStyleSingle
    '        .LineWidth = Word.WdLineWidth.wdLineWidth100pt
    '        .Color = Word.WdColor.wdColorAutomatic
    '    End With
    '    With .Borders(Word.WdBorderType.wdBorderTop)
    '        .LineStyle = Word.WdLineStyle.wdLineStyleSingle
    '        .LineWidth = Word.WdLineWidth.wdLineWidth100pt
    '        .Color = Word.WdColor.wdColorAutomatic
    '    End With
    '    With .Borders(Word.WdBorderType.wdBorderBottom)
    '        .LineStyle = Word.WdLineStyle.wdLineStyleSingle
    '        .LineWidth = Word.WdLineWidth.wdLineWidth100pt
    '        .Color = Word.WdColor.wdColorAutomatic
    '    End With
    '    With .Borders(Word.WdBorderType.wdBorderHorizontal)
    '        .LineStyle = Word.WdLineStyle.wdLineStyleSingle
    '        .LineWidth = Word.WdLineWidth.wdLineWidth050pt
    '        .Color = Word.WdColor.wdColorAutomatic
    '    End With
    '    With .Borders(Word.WdBorderType.wdBorderVertical)
    '        .LineStyle = Word.WdLineStyle.wdLineStyleSingle
    '        .LineWidth = Word.WdLineWidth.wdLineWidth050pt
    '        .Color = Word.WdColor.wdColorAutomatic
    '    End With
    '    .Borders(Word.WdBorderType.wdBorderDiagonalDown).LineStyle = Word.WdLineStyle.wdLineStyleNone
    '    .Borders(Word.WdBorderType.wdBorderDiagonalUp).LineStyle = Word.WdLineStyle.wdLineStyleNone
    '    .Borders.Shadow = False
    'End With
    ' Save this word document
    oDoc.SaveAs("C:\Users\CoDeXeR\Desktop\Word1.doc", True)
    oDoc.Close()
    oWord.Application.Quit()
    'oWord.Visible = True

End Sub

Public Sub exportToExcel(ByVal dgv As DataGridView)

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

 End Class

解决方案

Just Calling .Quit() will not remove the Application from memory. It is very important to close the objects after you are done with your coding. This ensures that all objects are released properly and nothing remains in the memory.

See this example

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

Also worth mentioning is the 2 DOT Rule.

If you love automating Excel from VB.Net then you might also want to have a look at this link.

FOLLOWUP

The problem is the 2 DOT Rule as I mentioned above. When you use the 2 DOT Rule (Ex: Excel.XlBordersIndex.xlDiagonalDown) then you have to do the Garbage Collection by using GC.Collect(). So All you need to do is add this part

    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

FINAL CODE (Tried And Tested)

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

这篇关于调用退出后Excel应用程序不退出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！