How to Release Excel Memory for Pagesetup Headers and Footers

576 views Asked by At

I'm working on some code that sets up some excel Spreadsheets. However I've come up with c# memory issues. I have a routine that does my garbage handling where I essentially use the interop release for COM objects.

However When setting the Headers and Footers I am still left with an excel task in my memory, I have tracked it down to this code. I have cut down this code a little, I have stored the pagesetup as an Excel.Pagesetup as to not cause problems with the known Excel TWO DOT RULE i have read about in many places on this forum.

Excel.Application oXL = null;
Excel.Workbook oWB = null;
Excel.Worksheet nSheet = null;

oXL = new Excel.Application(); 
oXL.Visible = false;

oXL.Workbooks.Add(Missing.Value);
oWB = oXL.Workbooks[1];
oXL.DisplayAlerts = false;
oXL.UserControl = false;

nSheet = oWB.ActiveSheet;

Excel.PageSetup thePageSetup = newSheet.PageSetup;   
oXL.ActiveWindow.View = Excel.XlWindowView.xlPageLayoutView;
thePageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;

thePageSetup.CenterHeader = "CH";
thePageSetup.LeftHeader = "LH";
thePageSetup.LeftFooter = "LF";
thePageSetup.RightFooter = "RF";

thePageSetup.LeftMargin = oXL.CentimetersToPoints(1.8);
thePageSetup.RightMargin = oXL.CentimetersToPoints(1.8);
thePageSetup.TopMargin = oXL.CentimetersToPoints(2.9);
thePageSetup.BottomMargin = oXL.CentimetersToPoints(2.9);
thePageSetup.FooterMargin = oXL.CentimetersToPoints(0.8);
thePageSetup.HeaderMargin = oXL.CentimetersToPoints(0.8);      

nSheet.Name = "FIRST SHEET";
nSheet.Select(Type.Missing);
Release(thePageSetup);

I clear the memory for sheets and workbooks later which works as expected but there is still a spot in memory when i include thepagesetup parts. Any ideas what i'm doing wrong?

edit: This is my release function incase anyone was interested. The obj is being sent in by reference and then is being made null. It seems to work on the other parts.

   private static void Release(Object obj) 
    {
        try
        {
            while (obj != null)
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj);
                obj = null;
            }
        }
        catch
        {

        }
        finally
        {
            GC.Collect();
        }
    } 
0

There are 0 answers