如何在C#中关闭Excel?

问题描述

我试图运行Excel宏并在运行后关闭excel.
不幸的是，它不起作用.我尝试了Stackoverflow的所有解决方案，但无法获得可靠的解决方案.请帮忙.如您所见，我正在尝试完全关闭COM对象，但似乎没有任何效果.

I am trying to run Excel macro and close excel after run.
Unfortunately it does not work. I tried all solutions from Stackoverflow and could not get reliable solution. Please help.As you can see I am trying to close, quite, release COM object but nothing seems to be working.

public static bool RunMacro(string Path, string MacroName, bool Close, ProgressForm ProgressForm, params object[] Arguments)
{
    Microsoft.Office.Interop.Excel.Application aApplication = null;
    bool aCloseApplication = true;
    bool aResult = false;

    try
    {
        aApplication = (Microsoft.Office.Interop.Excel.Application)Marshal.GetActiveObject("Excel.Application");
        aCloseApplication = false;
    }
    catch (COMException aCOMException)
    {
        aApplication = new Microsoft.Office.Interop.Excel.Application();
        aApplication.Visible = false;
    }

    if (aApplication != null)
    {
        aApplication.ScreenUpdating = false;

        Microsoft.Office.Interop.Excel.Workbook aWorkbook = null;
        Microsoft.Office.Interop.Excel.Worksheet aWorksheet = null;
        bool aCloseWorkbook = true;

        try
        {
            if (IsEdited(aApplication))
            {
                throw new Exception("Excel is in cell edit mode. Please stop editing cell and run import again");
            }
            else
            {
                for (int i = 0; i < aApplication.Workbooks.Count; i++)
                    if (aApplication.Workbooks[i + 1].FullName == Path)
                    {
                        aWorkbook = aApplication.Workbooks[i + 1];
                        aCloseWorkbook = false;
                        break;
                    }

                if (aWorkbook == null)
                    aWorkbook = aApplication.Workbooks.Open(Path);

                // Run macro here
                aApplication.Run(string.Format("{0}!{1}", System.IO.Path.GetFileName(Path), MacroName), Arguments);

                aResult = true;
            }
        }
        finally
        {
            if (aWorksheet != null)
            {
                Marshal.ReleaseComObject(aWorksheet);
            }

            //does not work here!!! I want to close excel here
            if (aWorkbook != null)
                aWorkbook.Close();
                aApplication.Quit();
                Marshal.ReleaseComObject(aWorkbook);
                Marshal.ReleaseComObject(aApplication);
        }
    }
    return aResult;
}

推荐答案

这是我在使用SSIS脚本任务刷新Excel文件时经常使用的方法.

This is something I've played around with a lot while using SSIS Script Tasks to refresh Excel files.

我已经阅读了有关使用Marshal.ReleaseComObject的各种文章，但是我也发现没有必要.对我来说，最终的解决方案是:

I've read mixed things about using Marshal.ReleaseComObject, but I've also found that it isn't necessary. For me, the ultimate solution was found to be the following:

using xl = Microsoft.Office.Interop.Excel;

...

public void Main()
{
    DoExcelWork();

    GC.Collect();
    GC.WaitForPendingFinalizers();
}

private void DoExcelWork()
{
    xl.Application app = null;
    xl.Workbooks books = null;
    xl.Workbook book = null;

    try
    {
        app = new xl.Application() { DisplayAlerts = false };

        books = app.Workbooks;
        book = books.Open("filename goes here");

        book.RefreshAll();
        // this is where you would do your Excel work

        app.DisplayAlerts = false; // This is for reinforcement; the setting has been known to reset itself after a period of time has passed.
        book.SaveAs("save path goes here");
        app.DisplayAlerts = true;

        book.Close();
        app.Quit();
    }
    catch
    {
        if (book != null) book.Close(SaveChanges: false);
        if (app != null) app.Quit();
    }
}

我不确定您的应用程序的布局，但是在使用SSIS时，我发现有必要在声明Excel Interop对象的范围之外调用GC.Collect，以避免使Excel实例保持打开状态在某些情况下，因此有两种方法.

I'm not sure how your application is laid out, but when using SSIS I found it was necessary to call GC.Collect outside of the scope where the Excel Interop objects were declared in order to avoid having the Excel instances left open on some occasions, hence the two methods.

您应该能够修改此代码以适合您的要求.

You should be able to adapt this code to suit your requirements.

这篇关于如何在C#中关闭Excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！