logo标签列表

如何关闭Excel应用程序？

c#excelautomation
3

我的代码如下：

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file);

Excel.Worksheet xlSheet = xlWorkbook.Sheets[1]; // get first sheet
Excel.Range xlRange = xlSheet.UsedRange;

这些是我函数中唯一使用的变量。

foreach (Excel.Worksheet XLws in xlWorkbook.Worksheets)
{
    // do some stuff 

    xlApp.UserControl = false;

    if (xlRange != null)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlRange);

    if (xlSheet != null)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlSheet);

    if (xlWorkbook != null)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);

    xlRange = null;
    xlSheet = null;
    xlWorkbook = null;
    xlApp.Quit();

    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}

但是在任务管理器中仍然出现EXCEL.EXE，请帮忙解决？

通常，退出命令会将进程从任务管理器中移除。您确定 Excel.exe 不是之前在代码测试期间卡在那里的进程吗？如果代码不好并且您必须在中途停止它，则 Excel 应用程序永远不会退出。 - Daniel Möller
2
我注意到你正在退出“for each”工作表的应用程序？？？听起来很奇怪，因为只有一个包含工作表的应用程序。 - Daniel Möller
@Daniel 是的，因为之前我在循环外尝试过它。现在发生的情况是，假设它正在读取一个名为ABC.xlsx的文件，它会在同一文件夹中创建ABC.xlsx，因此必须退出它（我不知道其他方法）...而且由于它正在循环，它会尝试读取ABC.xlsx并生成异常。 - Rohit
你正在使用 foreach 处理 ".xlsx" 文件吗？如果没有，那么关于那个 ~ABC 文件就没问题了，当你退出应用程序时它会被删除。我相信在某次调试中你遇到了一个错误，在那次运行中 Excel 应用程序被卡在了管理器中。任何其他时间运行都会创建一个新的 Excel 进程，如果它到达退出命令，它将从管理器中退出。但是之前的进程永远不会离开那里，必须手动将其移除。 - Daniel Möller
Excel互操作是一堆垃圾...最后我选择使用OpenOfficeXML / EPPlus代替！ - Paul Zahra
4个回答
7

结束MainWindowTitle为空值的Excel进程。以下是一个示例源代码。

    Microsoft.Office.Interop.Excel.Application oXL;
    Microsoft.Office.Interop.Excel._Workbook oWB;
    Microsoft.Office.Interop.Excel._Worksheet oSheet;
    Microsoft.Office.Interop.Excel.Range oRng;
    object misvalue = System.Reflection.Missing.Value;
    try
    {
        //Start Excel and get Application object.
        oXL = new Microsoft.Office.Interop.Excel.Application();
        oXL.Visible = true;

        //Get a new workbook.
        oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
        oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

        //Add table headers going cell by cell.
        oSheet.Cells[1, 1] = "First Name";
        oSheet.Cells[1, 2] = "Last Name";
        oSheet.Cells[1, 3] = "Full Name";
        oSheet.Cells[1, 4] = "Salary";

        //Format A1:D1 as bold, vertical alignment = center.
        oSheet.get_Range("A1", "D1").Font.Bold = true;
        oSheet.get_Range("A1", "D1").VerticalAlignment =
            Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

        // Create an array to multiple values at once.
        string[,] saNames = new string[5, 2];

        saNames[0, 0] = "John";
        saNames[0, 1] = "Smith";
        saNames[1, 0] = "Tom";

        saNames[4, 1] = "Johnson";

        //Fill A2:B6 with an array of values (First and Last Names).
        oSheet.get_Range("A2", "B6").Value2 = saNames;

        //Fill C2:C6 with a relative formula (=A2 & " " & B2).
        oRng = oSheet.get_Range("C2", "C6");
        oRng.Formula = "=A2 & \" \" & B2";

        //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
        oRng = oSheet.get_Range("D2", "D6");
        oRng.Formula = "=RAND()*100000";
        oRng.NumberFormat = "$0.00";

        //AutoFit columns A:D.
        oRng = oSheet.get_Range("A1", "D1");
        oRng.EntireColumn.AutoFit();

        oXL.Visible = false;
        oXL.UserControl = false;
        oWB.SaveAs("c:\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        oWB.Close(null, null, null);
        oXL.Quit();  //MainWindowTitle will become empty afer being close

        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oXL);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oWB);

        Process[] excelProcesses = Process.GetProcessesByName("excel");
        foreach (Process p in excelProcesses)
        {
            if (string.IsNullOrEmpty(p.MainWindowTitle)) // use MainWindowTitle to distinguish this excel process with other excel processes 
            {
                p.Kill();
            }
        }
    }
    catch (Exception ex2)
    {

    }
优秀的，手动结束Excel进程确认文件已释放。 - Kamran Qadir
5

您有一个隐式对象未关闭。请尝试如下操作：

Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
Excel.Workbook xlWorkbook = xlWorkbooks.Open(file);
....    

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbooks);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
....
3
这个问题在这篇文章中有更详细的描述：https://dev59.com/33VC5IYBdhLWcg3w4VNy。 - Colin Pickard
0

尝试：

xlWorkbook.Close(false); // if you Workbook should not be saved

而不是：

if (xlWorkbook != null)
   System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);

xlWorkbook = null;
0

好的...希望这可以帮到你...我花了很长时间来调整它，让它正常工作...

这是我的整个函数（VB - 但C#代码中的棘手部分也在其中（感谢太多其他stackoverflow巨人帮助我走到这一步！）

Private Function ImportWorksFile() As Integer

    Dim EndofSheet As Boolean
    Dim BlankRowCounter As Integer
    Dim rr As RowResult
    Dim SecCount As Integer = 0
    Dim SecRow As SecurityRow

    Dim uf As New UtilFunctions

    'If this has already been run, the instance of the excel object would have been 'killed' and needs to be reinstantiated
    If blnExcelProcessKilled Then 'Global boolean var
        xlApp = New Excel.Application()
        blnExcelProcessKilled = False
    End If
    Dim excelProcess(0) As Process
    excelProcess = Process.GetProcessesByName("excel")

    Dim tmp As Excel.Workbooks
    Try
        tmp = xlApp.Workbooks
        xlWorkBook = tmp.Open(WorkingFileName)
    Catch ex As Exception
        MessageBox.Show("There was a problem opening the workbook - please try again", CurAFLApp.AppName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Return 0
    End Try

    Using dc As New AFLData(CurAFLApp, True)

        Dim cmd As SqlCommand = DefineCommand()
        cmd.CommandType = CommandType.StoredProcedure

        For Each ws As Excel.Worksheet In xlWorkBook.Worksheets

            Dim row As Integer = 1
            EndofSheet = False
            BlankRowCounter = 0

            If ImpCols.ContainsKey(ws.Name) Then
                SecRow = New SecurityRow(ImpCols(ws.Name))

                Do Until EndofSheet
                    Try
                        SecRow.NewRow(ws.Rows(row))
                        rr = SecRow.IsValidRow

                        If rr = RowResult.Valid Then
                            ' read this row and process
                            With cmd
                                .Parameters("@AcctDate").Value = FileDate
                                .Parameters("@NewSub").Value = SecRow.GetStrCell("newsub")
                                RunProcedure(cmd)
                            End With

                            SecCount += 1

                            BlankRowCounter = 0

                        Else
                            BlankRowCounter += rr

                        End If

                    Catch ex As Exception
                        MessageBox.Show("There was a problem with row: " & row & " in workbook " & ws.Name)

                    End Try

                    ' if we've counted 50 blank A column values in a row, we're done.
                    If BlankRowCounter <= -50 Then
                        EndofSheet = True
                    End If

                    row += 1
                Loop
            End If
        Next
    End Using

    Try

        xlWorkBook.Close(SaveChanges:=False)
        xlApp.Workbooks.Close()
        xlApp.Quit()

        '// And now kill the process. C# Version (for reference)
        'if (processID != 0)
        '{
        '    Process process = Process.GetProcessById(processID);
        '    process.Kill();
        '}

        ' Reversed the order of release per  http://stackoverflow.com/questions/12916137/best-way-to-release-excel-interop-com-object


    Catch ex As Exception
        MessageBox.Show("There was a problem CLOSING the workbook - Please double check that the data was imported correctly. ", CurAFLApp.AppName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Return 0
    Finally

        releaseObject(tmp)
        releaseObject(xlWorkBook)
        releaseObject(xlApp)
        If Not excelProcess(0).CloseMainWindow() Then

            excelProcess(0).Kill()
            blnExcelProcessKilled = True
        End If

    End Try

    Return SecCount

End Function

Public Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
        'Not sure if the following line helps or hinders -- seems to lock things up once in a while
        'GC.WaitForPendingFinalizers()
    End Try
End Sub
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接