Remove Excel sheet from background process immediately after data is fetched

Question

I am using below code to release the excel sheet from memory but after the code has run the excel sheet is still in the background. Please help...

public void loadSheet(bool export, string projectName) {

        xlApp = new Excel.ApplicationClass();
        int rCnt = 0;           
            {

            xlWorkBook = xlApp.Workbooks.Open(@"E:/try/" + DateTime.Now.ToString("M.d.yyyy")+@"/"+ projectName+ ".xlsx", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, true, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["Upload Data"];
            range = xlWorkSheet.UsedRange;
            totalt.Text = range.Rows.Count.ToString();
            currentID.Text = (range.Rows.Count+1).ToString();  
          Excel.Range).Value2;              

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

    }

private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                MessageBox.Show("Released"+obj.ToString());
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

I want the excel sheet removed from the background after I have fetched the required data from it.

can you paste all code used interop excel? p.s. It's a typical problem, see http://stackoverflow.com/a/17367570/976231 — Dzmitry Martavoi, Jul 02 '13 at 06:23
what does mean `Excel.Range).Value2;`. this code not compiled — Dzmitry Martavoi, Jul 02 '13 at 07:43

score 1 · Answer 1 · edited May 23 '17 at 12:21

Such double-dot-calling expressions:

xlWorkBook = xlApp.Workbooks.Open(/*args*/);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["Upload Data"];

you should replace with

var workbooks = excel.Workbooks;
xlWorkBook  = workbooks.Open(/*params*/)
var worksheets = xlWorkBook.Worksheets;
xlWorkSheet = worksheets["Upload Data"];

//business logic here

Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(worksheets);    
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);

for more info, see: https://stackoverflow.com/a/17367570/976231

upd:

xlApp = new Excel.ApplicationClass();
        int rCnt = 0;           
            {

            var workbooks = xlApp.Workbooks;
            xlWorkBook = xlApp.Workbooks.Open(@"E:/try/" + DateTime.Now.ToString("M.d.yyyy")+@"/"+ projectName+ ".xlsx", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, true, 0, true, 1, 0);
            var worksheets = xlWorkBook.Worksheets;
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["Upload Data"];
            range = xlWorkSheet.UsedRange;
            var rows = range.Rows;
            totalt.Text = rows.Count.ToString();
            currentID.Text = (rows.Count + 1).ToString();               

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();


            Marshal.ReleaseComObject(rows);
            Marshal.ReleaseComObject(range);
            Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(worksheets);
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(workbooks);
            Marshal.ReleaseComObject(xlApp);
        }

    }

tried but it doent seems to work. Can you please change the above code which i gave.. and help i did — user1747819, Jul 02 '13 at 07:16
`Excel.Workbooks a = xlApp.Workbooks; xlWorkBook = a.Open("E:\\try\\CL.xlsx", 0, false, 5 , "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 1, 0);` — user1747819, Jul 02 '13 at 07:17
and then releaseObject(a) was added at last still it doent works — user1747819, Jul 02 '13 at 07:17

Remove Excel sheet from background process immediately after data is fetched

1 Answers1