Memory growth in Resource Monitoring (Memory leak) due to Reading and Writing Excel docs in c #

Question

I have a program I wrote in c# During the program I am writing data to Excel files in different tasks, running macros, and reading data from those Excel files.

Following the Resource Monitoring View, I see that the Memory consumption increased as a result of reading and writing operations

I am attaching the functions of reading and writing I wrote and a screenshot of Resource Monitoring View - where I saw the memory grows

Is this a case of memory leak? Does anyone know what the source of the Memory Escape?

public string[] GettingValuesFromColumn(string excelSheetName, string excelTableColumnName)
{
    List<string> result = new List<string>();
    int colCount, rowCount, iColIndex = -1;
    string value;
    string[] result_arr = null;

    Microsoft.Office.Interop.Excel.Range xlRange = null;

    try
    { 
        xlRange = (ExcelBook.Worksheets[excelSheetName]).UsedRange;
        //Getting Requested Column:
        rowCount = xlRange.Rows.Count;
        colCount = xlRange.Columns.Count;

        //Getting Index of Requested Column:
        for (int j = 1; j <= colCount; j++)
            if (xlRange.Cells[1, j] != null)
            {
                if (xlRange.Cells[1, j].Value2 != null)
                {
                    value = Convert.ToString(xlRange.Cells[1, j].Value2);
                    if (value == excelTableColumnName)
                        iColIndex = j;
                }
            }

        //Getting Values of Requested Column:
        if (iColIndex == -1)
            //printing error
        else
        {
            for (int i = 2; i <= rowCount; i++)
            {
                if (xlRange.Cells[i, iColIndex] != null)
                {
                    if (xlRange.Cells[i, iColIndex].Value2 != null)
                    {
                        value = string.Empty;
                        value = Convert.ToString(xlRange.Cells[i, iColIndex].Value2);
                        result.Add(value);
                    }
                }
            }
            result_arr = result.ToArray();
        }
    }
    catch (Exception e)
    {
        //printing error
    }
    finally
    {
        if (xlRange != null)
        {
            Marshal.FinalReleaseComObject(xlRange);
            xlRange = null;
        }
        //cleanup
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }

    return result_arr;
}


public string SettingValuesToColumn(string excelSheetName, string excelTableColNameForKey, string excelTableColNameForValue, Dictionary<string, string> dataDict)
{
    int iColKeyIndex = -1, iColValueIndex = -1, colCount, rowCount;
    string value, key, strResult = "";

    Microsoft.Office.Interop.Excel.Range xlRange = null;

    try
    {
        xlRange = (ExcelBook.Worksheets[excelSheetName]).UsedRange;
        //Getting Requested Column:
        rowCount = xlRange.Rows.Count;
        colCount = xlRange.Columns.Count;

        for (int j = 1; j <= colCount; j++)
        {
            if (xlRange.Cells[1, j] != null)
            {
                if (xlRange.Cells[1, j].Value2 != null)
                {
                    value = Convert.ToString(xlRange.Cells[1, j].Value2);
                    if (xlRange.Cells[1, j].Value2 == excelTableColNameForKey)
                        iColKeyIndex = j;
                    if (xlRange.Cells[1, j].Value2 == excelTableColNameForValue)
                        iColValueIndex = j;
                }
            }
        }
        //Getting Values of Requested Column:
        if (iColKeyIndex == -1 || iColValueIndex == -1)
        {
           //printing error
        }
        else
        {
            for (int i = 2; i <= rowCount; i++)
            {
                if (xlRange.Cells[i, iColKeyIndex] != null)
                {
                    if (xlRange.Cells[i, iColKeyIndex].Value2 != null)
                    {
                        key = Convert.ToString(xlRange.Cells[i, iColKeyIndex].Value2);
                        value = dataDict[key];
                        xlRange.Cells[i, iColValueIndex].Value2 = value;
                    }
                }
            }
        }
    }
    catch (Exception e)
    {
        //printing error
    }
    finally
    {
        if (xlRange != null)
        {
            Marshal.FinalReleaseComObject(xlRange);
            xlRange = null;
        }
        //cleanup
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
    return strResult;
}

It looks like here you're working with the ExcelBook model in memory; adding data to this is naturally going to cause allocations. We don't see in the example how the book is saved/closed/etc outside of these calls that might cause it to get released for GC... — T2PS, Aug 18 '20 at 11:44
Side notes: I'd suggest avoiding `GC` calls unless explicitly trying to compact the Large Object Heap. I would also suggest avoiding explicit COM release calls unless the API you're calling insists upon it; the runtime will clean up the RCWs at some point when GC runs, if there aren't limited resources besides memory associated with them, treating them as regular .NET objects should be sufficient (and safer). — T2PS, Aug 18 '20 at 11:49
After reading and Writing I closed without saving: ExcelBook.Close(SaveChanges: false); Do you know how to avoid the allocated memory? — Reut Yfrach, Aug 19 '20 at 09:24
You might not be able to avoid it. However, how much data are you working with to/from Excel, and how much memory is involved? What do you see for the heaps and GC [using Process Explorer](https://stackoverflow.com/a/357727) to check their performance counters? What Windows says the process is using is distinct from the CLR's perspective on memory. — T2PS, Aug 19 '20 at 13:21

Memory growth in Resource Monitoring (Memory leak) due to Reading and Writing Excel docs in c #

0 Answers0