0

My code (successfully) reads from several worksheets, makes several lists, and puts them all in a dictionary for other threads to use. The createLists() function loops every minute to check for any new data in the excel workbook and updates the lists.

If I run my code without this function, I see no memory leak. But if I include this function, I can easily see memory getting eaten up every time it loops (every minute).

Here's my function:

    public static void createLists()
    {

        list_dictionary.Add(1, example_list1);                  
        list_dictionary.Add(2, example_list2);
        list_dictionary.Add(3, example_list3);
        list_dictionary.Add(4, example_list4);
        list_dictionary.Add(5, example_list5);
        list_dictionary.Add(6, example_list6);

        while (true)
        {
            try
            {
                Console.WriteLine("In create");
                excel = new Excel.Application();
                excel_workbook = excel.Workbooks.Open(@"C:\Local Code\Excel\Book1.xlsx");


                for (int s = 1; s <= 6; s++)
                {
                    Excel._Worksheet excel_worksheet = excel_workbook.Sheets[s];
                    Excel.Range excel_range = excel_worksheet.UsedRange;
                    list_dictionary[s].Clear();

                    int row_count = excel_range.Rows.Count;
                    int col_count = excel_range.Columns.Count;

                    for (int x = 1; x <= row_count; x++)
                    {
                        for (int y = 1; y <= col_count; y++)
                        {
                            if (excel_range.Cells[x, y] != null && excel_range.Cells[x, y].Value2 != null)
                            {
                                list_dictionary[s].Add(excel_range.Cells[x, y].Value2.ToString());
                            }
                        }
                    }

                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    Marshal.ReleaseComObject(excel_range);
                    Marshal.ReleaseComObject(excel_worksheet);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("MANTIS");
                Console.WriteLine(e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                Console.WriteLine("CLEAN UP");
                GC.Collect();
                GC.WaitForPendingFinalizers();
                Marshal.ReleaseComObject(excel_workbook);
                Marshal.ReleaseComObject(excel);


            }              
            Console.WriteLine("Out create");

            Thread.Sleep(10000);
        }
    }
}

There are 6 "sheets" in the workbook, for I have a for loop to iterate over them and update the corresponding list in the list_dictionary. I ReleaseComObject the Excel.Range and Excel._Worksheet every loop, then the entire Excel.Workbooks and Excel.Application at the end of the while loop.

Any idea where I'm failing to release memory?

MrDysprosium
  • 489
  • 9
  • 20
  • 2
    Why are you using raw COM instead of Excel Interop? And why use Excel at all instead of using the OLEDB provider, the Open XML SDK or a library like EPPlus? – Panagiotis Kanavos Oct 18 '16 at 14:18
  • 1
    Well, I personally don't like using COM to manipulate Excel, as I find it hard to truely clean after the execution, so I use EPPlus. I don't know your requirements, but maybe you could give it a try. – Magnetron Oct 18 '16 at 14:19
  • Alright, I'll give EPPlus a look-see. – MrDysprosium Oct 18 '16 at 14:21
  • 1
    You can't use 2 dots when You access a property like `excel_range.Cells[x, y].Value2.ToString()`. You have to `var variable = excel_range.Cells[x, y]; var variable2 = variable.Value2; ... `, and call `Marshal.ReleaseComObject(variableX)` on each level of variables – ntohl Oct 18 '16 at 14:21
  • I'm not familiar with "2 dots", what does that mean? – MrDysprosium Oct 18 '16 at 14:25
  • The 2 dots are the dot between range.Cells and Cells[x,y].Value2 and Value2.ToString(). In this case, there are 3 dots – Magnetron Oct 18 '16 at 14:27
  • Possible duplicate of [C# Excel automation causes Excel memory leak](http://stackoverflow.com/questions/13483523/c-sharp-excel-automation-causes-excel-memory-leak) – Magnetron Oct 18 '16 at 14:30
  • @Magnetron I've followed the example in that thread (as you can see in my above code) and the problem was no resolved. – MrDysprosium Oct 18 '16 at 14:53
  • No. You did not. At least 10-15 variables should be released. There is only 2. – ntohl Oct 18 '16 at 14:55
  • 1
    There is also the second answer in this post: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects and, again, that's why i prefer EPPlus, so I don't have to deal with releasing objects – Magnetron Oct 18 '16 at 15:00
  • @RezaAghaei How do I mark a comment as the answer? – MrDysprosium Oct 31 '16 at 18:41
  • 1
    You can't mark comments as answer, but you can vote for comments to help future readers to read just useful comments. Also for answers and questions, you can vote for useful answers and questions. This way you make them more useful for future readers. Also this way you get some reputation for those users and motivate them to post useful questions and answers :) – Reza Aghaei Oct 31 '16 at 20:28

0 Answers0