C# memory leak using exel and interop COM

Question

UPDATED 3/12/20. I am struggling with a problem where the Excel application is not releasing following my function call below. I'm not sure what references I'm screwing up but I assume I'm somehow leaving a reference to the application because I see multiple Excel processes in my task manager as the program runs, not to mention large amounts of memory use that don't improve with GC. Thoughts?

    static void ParseExcel(string file, SqlConnection conn)
    {
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file);
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        Excel.Range rng = xlWorksheet.UsedRange;
        SqlCommand cmd = conn.CreateCommand();
        double colCount = rng.Columns.Count;
        double rowCount = rng.Rows.Count;
        //iterate over the rows and columns
        //start on row 2 since row 1 is column headers
        for (int i = 2; i <= rowCount; i++)
        {
            string companyName = "";
            for (int j = 1; j <= colCount; j++)
            {
                //write the value to the console
                if (rng.Cells[i, j] != null && rng.Cells[i, j].Value2 != null)
                {
                    string tst = rng.Cells[i, j].Value2.ToString();
                    switch (j)
                    {
                        case 2:
                            companyName = tst;
                            break;
                    }
                }    //end of column
            }//end of row
            //write to database
            string sql = "insert into dbo.company( ";
            sql += DBI(companyName) + ")";
            Console.WriteLine("sql = " + sql);
            try
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }catch(Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
        xlApp.Quit();
        Marshal.ReleaseComObject(rng);
        Marshal.ReleaseComObject(xlWorksheet);
        Marshal.ReleaseComObject(xlWorkbook);
        Marshal.ReleaseComObject(xlApp);            
        System.Threading.Thread.Sleep(3000);
    }

and outside call looks like this:

 ParseExcel(@"c:\testfile.xls", conn);
 //cleanup
 GC.Collect();
 GC.WaitForPendingFinalizers();

Why would you try to do GC cleanup *before* telling the marshalling system that a whole load of COM objects are no longer required? — Damien_The_Unbeliever, Mar 12 '20 at 10:29
OK, I took the GC calls and put them after my excel method in the outer call. Still having a lot of memory usage as the program iterates through each cell. I read something about a hidden range object reference? Could that be the issue? I see the GC getting called repeatedly but nothing is reducing the memory/heap. — tuj, Mar 12 '20 at 16:13
I'm snap-shooting the heap and it's growing by like 1MB every few seconds. It's like every time it moves to a new cell, there's a memory leak. I don't understand why. — tuj, Mar 12 '20 at 19:40

score 1 · Answer 1 · answered Mar 26 '20 at 18:13

1

I should not have made my parse method static. That was the cause of the memory leak.

answered Mar 26 '20 at 18:13

tuj

575
2
9
25

C# memory leak using exel and interop COM

1 Answers1