C # Excel Interop garbage collection with PivotField

I apologize if this was previously asked but I could not find an answer.

The problem I am facing is creating an Excel file using WinForms in Visual Studio Express 2013. I load it as "visible", but when I close the Excel workbook, Excel.exe remains open in my managers task. I have narrowed it down to pivot table fields. Excel shuts down my processes fine until I have a PivotField, then (I assume) the Com object keeps the Excel.exe open.

I was wondering if anyone could answer this question? As shown below, I even tried to inform the Marshal to release the PivotField object, but that didn't seem to be useful to you either.

Test code:

private void button1_Click(object sender, EventArgs e)
    {

        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlBook = xlApp.Workbooks.Add();
        Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
        Excel.Worksheet xlSheet2 = (Excel.Worksheet)xlBook.Worksheets[2];

        Excel.Range xlRange = xlSheet.get_Range("B1", "D3");
        xlRange.Value = "BAH";

        xlSheet.Cells[1, 1] = "BLAH";

        Excel.Range xlRange2 = xlSheet2.get_Range("A1", "A1");

        Excel.PivotCache xlPivotCache = (Excel.PivotCache)xlBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange);
        Excel.PivotTable xlPivotTable = (Excel.PivotTable)xlSheet2.PivotTables().Add(PivotCache: xlPivotCache, TableDestination: xlRange2, TableName: "Test");

        Excel.Range xlGroupRange = xlSheet2.Cells[2, 1];

        xlPivotTable.InGridDropZones = false;
        xlPivotTable.Format(Excel.XlPivotFormatType.xlReport9);

        // Here the problem child
        Excel.PivotField xlField = (Excel.PivotField)xlPivotTable.PivotFields("BAH");

        xlApp.Visible = true;

        Marshal.ReleaseComObject(xlField);
        Marshal.ReleaseComObject(xlSheet);
        Marshal.ReleaseComObject(xlBook);
        Marshal.ReleaseComObject(xlApp);

        GC.Collect();
    }

      

+3


source to share





All Articles