Format Excel File using C#

Question

The Bellow function is used to formate the excel file but after running the function the Application Excel is not closeing from the Try.. (not able to kill the application) Please Help me on this

private void FormateExcelFile()
{
    try
    {
        int nI = 0;//For Loop
        string nFieldName = string.Empty;
        nUserName=  WindowsIdentity.GetCurrent().Name; //Get Windows Login User
        string reportFilenPath = Application.StartupPath + "\\OutPutFiles\\" + "NewTempFile.xls";
        string connString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + reportFilenPath + "';Extended Properties=Excel 8.0;";
        DataTable parts = new DataTable();
        using (OleDbConnection conn = new OleDbConnection(connString))
        {
            string sqlParts = "Select * from [" + nSheetName + "]";
            OleDbDataAdapter adapter = new OleDbDataAdapter(sqlParts, conn);
            adapter.Fill(parts);
        }

        for (nI = 0; nI < parts.Columns.Count; nI++)
        {
            DataColumn column = parts.Columns[nI];
            if (nI == 0) { nFieldName = column.ColumnName; }
            else { nFieldName = nFieldName + "," + column.ColumnName; }
        }
        parts.Dispose(); parts = null;

        oExcel = new Excel.Application();
        oBook = oExcel.Workbooks.Open(reportFilenPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
        oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets.get_Item(nSheetName.Replace("$", ""));
        oExcel.DisplayAlerts = false;
        oExcel.Visible = true;



        //Check the Field Is Avilable in the Sheet if not then Add
        if (nFieldName.Contains("Sub Device") == false)
        {
            nRng = oSheet.get_Range("A1", oMissing);
            nRng.EntireColumn.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, false);
            oSheet.Cells[1, 1] = "Sub Device";
        }
        if (nFieldName.Contains("Brand") == false)
        {
            nRng = oSheet.get_Range("A1", oMissing);
            nRng.EntireColumn.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, false);
            oSheet.Cells[1, 1] = "Brand";
        }
        if (nFieldName.Contains("Model") == false)
        {
            nRng = oSheet.get_Range("A1", oMissing);
            nRng.EntireColumn.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, false);
            oSheet.Cells[1, 1] = "Model";
        }
        if (nFieldName.Contains("Product Details") == false)
        {
            nRng = oSheet.get_Range("A1", oMissing);
            nRng.EntireColumn.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, false);
            oSheet.Cells[1, 1] = "Product Details";
        }
        if (nFieldName.Contains("Price") == false)
        {
            nRng = (Excel.Range)oSheet.Cells[1, 1];
            //nRng = oSheet.get_Range("A1", oMissing);
            nRng.EntireColumn.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, false);
            oSheet.Cells[1, 1] = "Price";
        }

        oBook.Save();
        oBook.Close(false, oMissing, oMissing);
        oExcel.DisplayAlerts = true;
        releaseObject(oSheet);
        releaseObject(oBook);
        oExcel.Quit();
        releaseObject(oExcel);
        releaseObject(nRng);
        nRng = null;
        oExcel = null;
        oSheet = null;


    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
        releaseObject(oSheet);
        releaseObject(oBook);
        //oExcel.Quit();
        releaseObject(oExcel);
    }
}

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

I Did it rng,book,sheet & excel all objects but still application is open — user1632718, Oct 09 '12 at 09:04
private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); } finally { GC.Collect(); } } — user1632718, Oct 09 '12 at 09:24
this post might be useful. (http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp) be careful with the order of the releasing of objects. For example, before releasing oSheet, you must release nRng first. (children before parents) — artsylar, Oct 10 '12 at 07:37

score 1 · Answer 1 · answered Oct 09 '12 at 13:48

You need to release all the Excel objects you're referencing. For example:

if (nFieldName.Contains("Sub Device") == false)
{
    nRng = oSheet.get_Range("A1", oMissing);
    nRng.EntireColumn.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, false);
    oSheet.Cells[1, 1] = "Sub Device";
}

should be something like (leaving out try/finally for simplicity)

if (nFieldName.Contains("Sub Device") == false)
{
    nRng = oSheet.get_Range("A1", oMissing);
    var col = nRng.EntireColumn
    col.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, false);
    var cells = oSheet.Cells;
    var firstCell = cells[1,1];
    firstCell.Value = "Sub Device";

    Marshal.ReleaseComObject(nRng);
    Marshal.ReleaseComObject(col);
    Marshal.ReleaseComObject(cells);
    Marshal.ReleaseComObject(firstCell);

}

Similarly:

oBook = oExcel.Workbooks.Open(reportFilenPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets.get_Item(nSheetName.Replace("$", ""));

needs to be:

oBooks = oExcel.Workbooks
oBook = oBooks.Open(...);
oSheets = oBook.Worksheets
oSheet = oSHeets.get_Item(...);

and you need to release oBooks and oSheets.

score 0 · Answer 2 · answered Oct 09 '12 at 09:20

Try to end your excel task with these commands:

        Marshal.FinalReleaseComObject(sheet);
        app.DisplayAlerts = false; //Very important!
        range = null;
        sheet = null;
        // Garbage collecting
        GC.Collect();
        GC.WaitForPendingFinalizers();
        book.Close(false, Missing.Value, Missing.Value);
        Marshal.FinalReleaseComObject(book);
        book = null;
        app.Quit();
        Marshal.FinalReleaseComObject(app);
        app = null;

I had the same problem accessing my excel files, but with the shown code it closes all the time.

Of course not if the program crashes before it reaches the code, check your code with the debugger.

In your case: book --> oBook, app --> oExcel, sheet --> oSheet.

I don't think calling `GC.Collect();` is the right course of action here. — JMK, Oct 09 '12 at 09:23
I thought so, too. But after millions of tries to end the Excel-tasks I tried it with GC.Collect and Marshal.FinalReleaseComObject and it worked. It's possible that GC.Collect is useless and the kill of the Excel-Task is done by the Marshal.FinalReleaseComeObject-method. — jAC, Oct 09 '12 at 09:41

JMK · Answer 3 · 2012-10-09T09:38:49.700

Take away these lines:

nRng = null;
oExcel = null;
oSheet = null;

I think you are releasing your Excel object, and then by making it equal to null afterwards you are doing something with Excel which starts a new instance on your machine.

After releasing the object, you don't need to set variables to null, or run GC.Collect();, the garbage collector will take care of this for you, and I think in this instance trying to clean up the managed objects yourself (after correctly cleaning up the unmanaged Excel object) is actually causing your problems.

score 0 · Answer 4 · answered Oct 09 '12 at 13:32

I had the same kind of issue working with Excel interop. The problem should be originated by this kind of lines (at least it was in my case):

oBook = oExcel.Workbooks.Open(reportFilenPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

In the end you reales oBook, but this is what's really happening here: in order to get oBook pointing to your opened workbook, you accessed a Workbooks object (using oExcel.Workbooks). This is never released, and keeps Excel from quitting.

I solved the problem by rewriting that line as:

Microsoft.Interop.Excel.Workbooks oBooks = oExcel.Workbooks;
oBook = oBooks.Open(reportFilenPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); 
releaseObject(oBooks);

Of course this has to be done for every line of this kind (for instance oBook.Worksheets.get_Item(...), nRng.EntireColumn.Insert(...), and so on).

Format Excel File using C#

4 Answers4