CODEWITHSUNDEEP
c#excelinteropcomobject
L Riley
L Riley

Reputation: 337

c# Interop Excel Close instances

I've got a section of code which opens an excel app dumps a range then attempts to close it.

Attempts being the operative word no matter what I do I cannot get the excel instance to shut down - I followed the 1 dot not 2 dot rule as far as I know can anyone help with what im missing.

    Excel.Application excelApp = new Excel.Application();

    Excel.Workbooks wbks = excelApp.Workbooks;
    Excel.Workbook currentWorkbook = wbks.Add();
    Excel.Worksheet newWks = new Excel.Worksheet();

    try
    {
        newWks = currentWorkbook.Sheets["Sheet2"];
        newWks.Delete();
        newWks = currentWorkbook.Sheets["Sheet3"];
        newWks.Delete();
        newWks=currentWorkbook.Sheets["Sheet1"];

        for (int col = 0; col < dt.Columns.Count; col++)
        {
            newWks.Cells[1, col + 1] = dt.Columns[col].ColumnName;
        }
        string[,] arr = new string[dt.Rows.Count, dt.Columns.Count];

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                arr[i, j] = dt.Rows[i][j].ToString();
            }
        }

        Excel.Range startCell = newWks.Cells[2, 1];
        Excel.Range endCell = newWks.Cells[dt.Rows.Count + 1, dt.Columns.Count];
        Excel.Range writeRange = newWks.Range[startCell, endCell];
        writeRange.Value2 = arr;

        Excel.Application cwa = currentWorkbook.Application;
        cwa.DisplayAlerts = false;

        Global.CheckDirectory(path);

        currentWorkbook.SaveAs(path + @"//filepath", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Missing.Value,
            Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
            Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
            Missing.Value, Missing.Value, Missing.Value);

        cwa.Quit();
        Marshal.ReleaseComObject(cwa);
        Marshal.ReleaseComObject(startCell);
        Marshal.ReleaseComObject(endCell);
        Marshal.ReleaseComObject(writeRange);
    }
    catch (Exception ex)
    {
       //err handler
    }
    finally
    {

        wbks.Close();
        excelApp.Quit();

        Marshal.ReleaseComObject(newWks);
        Marshal.ReleaseComObject(currentWorkbook);
        Marshal.ReleaseComObject(wbks);
        Marshal.ReleaseComObject(excelApp);

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

Upvotes: 0

Views: 560

Answers (1)

Pfeiff86
Pfeiff86

Reputation: 142

Hi,

Try to close and release everything in this order.

Marshal.ReleaseComObject(Range);
Marshal.ReleaseComObject(Worksheet);
//WRITE close and release
WWorkbook.Close();
Marshal.ReleaseComObject(Workbook);
//!WRITE quit and release
App.Quit();
Marshal.ReleaseComObject(App);
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();

Upvotes: 1

Related Questions