C# - how to kill excel process running in the background programmatically

Question

I have a created a program to auto fill a invoice excel file so that it can be later printed. I am able to fill the cells and format them. But the excel process is still running in the background.

public void Write(tbl_Sale sale,List<SalesModel> saleCollection)
{  
    Mouse.SetCursor(Cursors.Wait);
    Excel.Application application = null;
    Workbook workbook = null;
    Worksheet inputSheet = null;
    Range range = null;

    try
    {
        if (sale != null)
        {
            if (saleCollection.Count != 0)
            {
                application = new Excel.Application();
                FileInfo fileInfo = new FileInfo("Sample.xlsx");
                var sheet = "Invoice";
                workbook = application.Workbooks.Open(fileInfo.FullName);
                inputSheet = workbook.Worksheets.Cast<Worksheet>().Where(w => w.Name == sheet).FirstOrDefault(); 

                range = inputSheet.Cells;
                range.SetValue("G4", sale.Sale_ID);
                range.SetValue("G5", sale.Sale_Date);

                Range row = inputSheet.get_Range("A22", Missing.Value).EntireRow;

                for (int i = 0; i < saleCollection.Count; i++)
                {
                    row.Insert(XlInsertShiftDirection.xlShiftDown, row);
                    range = inputSheet.Cells;
                    range.SetValue("A" + (22 + i), i + 1);

                    range = inputSheet.Cells;
                    range.SetValue("B" + (22 + i), saleCollection[i].Product.Prod_Code);
                    range = inputSheet.Range["B" + (22 + i) + ":" + "D" + (22 + i)];
                    range.Merge();

                    range = inputSheet.Cells;
                    range.SetValue("E" + (22 + i), saleCollection[i].Product.Prod_Name);
                    range = inputSheet.Range["E" + (22 + i) + ":" + "K" + (22 + i)];
                    range.Merge();
                    range.HorizontalAlignment = 1;

                    range = inputSheet.Cells;
                    range.SetValue("X" + (22 + i), saleCollection[i].SalesDetail.SaleD_Quantity);
                    range = inputSheet.Range["X" + (22 + i) + ":" + "Y" + (22 + i)];                          
                    range.Merge();
                    range.HorizontalAlignment = 1;
                    range.NumberFormat = "0.00";

                    range = inputSheet.Cells;
                    range.SetValue("Z" + (22 + i), saleCollection[i].SalesDetail.SaleD_Rate);
                    range = inputSheet.Range["Z" + (22 + i) + ":" + "AA" + (22 + i)];
                    range.Merge();
                    range.HorizontalAlignment = 1;
                    range.NumberFormat = "0.00";

                    range = inputSheet.Cells;
                    range.SetValue("AB" + (22 + i), saleCollection[i].SalesDetail.SaleD_Amount);
                    range = inputSheet.Range["AB" + (22 + i) + ":" + "AD" + (22 + i)];
                    range.Merge();
                    range.HorizontalAlignment = 1; ;
                    range.NumberFormat = "0.00";
                }
                range = inputSheet.Cells;
                range.SetValue("X" + (22 + saleCollection.Count + 2), saleCollection.Sum(s => s.SalesDetail.SaleD_Quantity));
                range.SetValue("AB" + (22 + saleCollection.Count + 2), sale.Sale_Total);
                fileInfo = new FileInfo("Invoices\\" + sale.Sale_ID + ".xlsx");

                workbook.Application.DisplayAlerts = false;
                workbook.SaveAs(fileInfo.FullName, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges ,Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                releaseObject(row);
                MessageBox.Show("Invoice exported successfully", "Invoice Export", MessageBoxButton.OK, MessageBoxImage.Information);                      
               // Process.Start(fileInfo.FullName);
            }
        }
    }
    catch (Exception)
    {
        MessageBox.Show("Error while generating Excel report");
    }
    finally
    {
        workbook.Close(0);
        application.Quit();
        releaseObject(application);
        releaseObject(workbook);
        releaseObject(inputSheet);
        releaseObject(range);
        GC.Collect();
        Mouse.SetCursor(Cursors.Arrow);
    }
}

I am not able to kill the excel process from the background.I m not sure what I am doing wrong.The code above is the whole stuff I am doing for automating excel. Kindly please help me.

B. Clay Shannon-B. Crow Raven · Accepted Answer · 2016-01-07T21:26:46.667

What I do is use an Initializer and a Deinitializer for the Excel objects, like so:

private void InitializeExcelObjects()
{
    _xlApp = new Excel.Application
    {
        SheetsInNewWorkbook = 2,
        StandardFont = "Tahoma",
        StandardFontSize = 11
    };

    _xlBook = _xlApp.Workbooks.Add(Type.Missing);
    _xlApp.Windows.Application.ActiveWindow.DisplayGridlines = false;

    _xlSheets = _xlBook.Worksheets;
    _xlSheet = (Excel.Worksheet)_xlSheets.Item[1];
    _xlSheetDelPerf = (Excel.Worksheet)_xlSheets.Item[2];
}

private void DeinitializeExcelObjects()
{
    Marshal.ReleaseComObject(_xlSheet);
    Marshal.ReleaseComObject(_xlSheetDelPerf);

    Marshal.ReleaseComObject(_xlSheets);

    _xlBook.Close(false, null, null); 
    Marshal.ReleaseComObject(_xlBook);

    _xlApp.DisplayAlerts = false;
    _xlApp.Quit();
    Marshal.ReleaseComObject(_xlApp);
    _xlApp = null;
}

They are called like this:

public void GenerateDuckbilledPlatypusRpt()
{
    try
    {
        InitializeExcelObjects();
        . . . // all the Excel generation code goes here
    }
    finally
    {
        DeinitializeExcelObjects();
    }
}

IOW, I think you could change your code to something like this:

public void Write(tbl_Sale sale,List<SalesModel> saleCollection)
{  
    Mouse.SetCursor(Cursors.Wait);
    Excel.Application application = null;
    Workbook workbook = null;
    Worksheet inputSheet = null;
    Range range = null;

    try
    {
        . . .
        InitializeExcelObjects();
        . . .
    }
    finally
    {
       DeinitializeExcelObjects();
       Mouse.SetCursor(Cursors.Arrow);
    }
}

private void InitializeExcelObjects()
{    
    application = new Excel.Application();

    workbook = application.Workbooks.Add(Type.Missing);

    sheets = workbook.Worksheets;
    inputSheet = (Excel.Worksheet)sheets.Item[1];
}

private void DeinitializeExcelObjects()
{
    Marshal.ReleaseComObject(inputSheet);

    Marshal.ReleaseComObject(sheets);

    workbook.Close(false, null, null); 
    Marshal.ReleaseComObject(workbook);

    application.DisplayAlerts = false;
    application.Quit();
    Marshal.ReleaseComObject(application);
    application = null;
}

It works for me; YMMV

You're welcome; glad it helped. I'm trying to "pay it forward" for all the help I've gotten here where I can. — B. Clay Shannon-B. Crow Raven, Jan 07 '16 at 21:46

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

1

See this answer: How do I properly clean up Excel interop objects?

You are using releaseObject(application), but that seems to refer to some method you have that is not shown. Not sure what code is doing there.

Try using Marshal.ReleaseComObject(application) in addition to heeding the cautions in the linked question.

edited May 23 '17 at 12:15

Community

1
1

answered Jan 06 '16 at 19:06

Ric Gaudet

898
6
16

That is basically the difference between my code and the OP's, I think (I'm using Marshal.ReleaseComObject() instead of releaseObject(). – B. Clay Shannon-B. Crow Raven Jan 06 '16 at 19:24

C# - how to kill excel process running in the background programmatically

2 Answers2