如何打开、读取和关闭 Excel 互操作进程 (C#)

Question

我必须做一个程序来读取excel xlsx文件并将数据存储到数据库中。这是我实际的简单代码，我什至没有从 excel 文件中获取值，但我无法杀死该过程。我用谷歌搜索了很多，我尝试了很多东西，但 excel 过程仍然有效。

public void readFile(path)
        {
            try
            {
                Microsoft.Office.Interop.Excel.Application xlApp = null;
                Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
                Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;
                Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
                Microsoft.Office.Interop.Excel.Range xlRange = null;
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                workbooks = xlApp.Workbooks;
                xlWorkbook = workbooks.Open(path);
                xlWorksheet = xlWorkbook.Sheets[1];
                xlRange = xlWorksheet.UsedRange;
                //------Here is where I will read the data
                xlWorkbook.Close();
                workbooks.Close();
                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlRange);
                Marshal.FinalReleaseComObject(xlWorksheet);
                Marshal.FinalReleaseComObject(xlWorkbook);
                Marshal.FinalReleaseComObject(workbooks);
                Marshal.FinalReleaseComObject(xlApp);
                xlRange = null;
                xlWorksheet = null;
                xlWorkbook = null;
                workbooks = null;
                xlApp = null;
            }
            catch (Exception e)
            {
            }
        }

这个过程保持活力。我希望你能帮助我。

Answer 1

我尝试了很多东西...下面的代码似乎有效。

public void readFile(path)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Application xlApp = null;
                Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
                Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;
                Microsoft.Office.Interop.Excel.Sheets xlsheets = null;
                Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
                Microsoft.Office.Interop.Excel.Range xlRange = null;
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                workbooks = xlApp.Workbooks;
                xlWorkbook = workbooks.Open(path);
                xlsheets = xlWorkbook.Sheets;
                xlWorksheet = xlsheets[1];
                xlRange = xlWorksheet.UsedRange;
                //--------------------------------------------------------------------
                xlWorkbook.Close();
                workbooks.Close();
                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlRangeColumns);
                Marshal.FinalReleaseComObject(xlRangeRows);
                Marshal.FinalReleaseComObject(xlRange);
                Marshal.FinalReleaseComObject(xlWorksheet);
                Marshal.FinalReleaseComObject(xlsheets);
                Marshal.FinalReleaseComObject(xlWorkbook);
                Marshal.FinalReleaseComObject(workbooks);
                Marshal.FinalReleaseComObject(xlApp);
                xlRangeColumns = null;
                xlRangeRows = null;
                xlRange = null;
                xlWorksheet = null;
                xlsheets = null;
                xlWorkbook = null;
                workbooks = null;
                xlApp = null;
                }
                catch (Exception e)
                {
                }
            }

差别很小。一个月前，我会告诉你两个代码是相同的。有人可以告诉我为什么这段代码有效，而第一段代码不起作用吗？

Answer 2

试着把它放在代码的末尾，它会杀死你的excel

foreach (var process in Process.GetProcessesByName("myExcelFilename"))
{
    process.Kill();
}

Answer 3

您需要将

对象放入使用块中，或者将结束语句放入 finally 中。

每当应用程序引发异常时，它都会使互操作保持打开状态。相反，您应该做的是将关闭代码放在 finally 块中，这意味着代码将始终运行。这样：

    public void readFile(string path)
    {
        Microsoft.Office.Interop.Excel.Application xlApp = null;
        Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
        Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;
        Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
        Microsoft.Office.Interop.Excel.Range xlRange = null;
        try
        {

            xlApp = new Microsoft.Office.Interop.Excel.Application();
            workbooks = xlApp.Workbooks;
            xlWorkbook = workbooks.Open(path);
            xlWorksheet = xlWorkbook.Sheets[1];
            xlRange = xlWorksheet.UsedRange;
            //------Here is where I will read the data


        }
        catch (Exception e)
        {
        }
        finally
        {
            xlWorkbook?.Close();
            workbooks?.Close();
            xlApp?.Quit();
            Marshal.FinalReleaseComObject(xlRange);
            Marshal.FinalReleaseComObject(xlWorksheet);
            Marshal.FinalReleaseComObject(xlWorkbook);
            Marshal.FinalReleaseComObject(workbooks);
            Marshal.FinalReleaseComObject(xlApp);                
            xlRange = null;
            xlWorksheet = null;
            xlWorkbook = null;
            workbooks = null;
            xlApp = null;
        }
    }

另一种选择是不要搞砸互操作，而是使用另一个库，例如 EPPlus(在 NuGet 上可用(来处理 excel 文件的读取，这些文件将在程序关闭时关闭。