3

I apologize if this has been previously asked, but I wasn't able to find the answer.

The problem that I'm having is creating an Excel file using WinForms in Visual Studio Express 2013. I'm having it load as "Visible", but when I close the Excel workbook, Excel.exe stays open in my task manager processes. I've narrowed it down to my pivot table fields. Excel will close out of my processes just fine until I had a PivotField, then (I'm assuming) a Com object holds the Excel.exe open.

I was wondering if anyone might have an answer to this issue? As shown below, I've even tried to tell the Marshal to release the PivotField object, but that didn't seem to do the trick 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's 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();
    }
AstroCB
  • 12,337
  • 20
  • 57
  • 73
Brendon Thrash
  • 31
  • 1
  • I think I may have found an answer to this test. If I change: Excel.Range xlGroupRange = xlSheet2.Cells[2,1]; to Excel.Range xlGroupRange = xlSheet2.get_Range("B2"); It appears to fix the issue. Does anyone have an explanation as to why? I'm curious to know why this small difference would make a difference. – Brendon Thrash Aug 21 '14 at 18:37
  • 1
    It doesn't have anything to do with that pivot, you are "leaking" one WorkSheet and three Ranges. This is why it is such an extraordinary bad idea to write this kind of code. The garbage collector never gets this wrong. – Hans Passant Aug 21 '14 at 19:41
  • I had used Marshal.ReleaseComObject() on all of the objects that I had created, and nothing changed until I changed my reference to my range, which is why I had assumed that was the problem. I will definitely look into the IDisposable method, though. – Brendon Thrash Aug 21 '14 at 20:43

0 Answers0