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-07 21:26:46Z

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.

Community · Accepted Answer · 2017-05-23 12:15:21Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered Jan 6, 2016 at 19:06

Ric Gaudet

9286 silver badges16 bronze badges

1 Comment

B. Clay Shannon-B. Crow Raven Over a year ago

That is basically the difference between my code and the OP's, I think (I'm using Marshal.ReleaseComObject() instead of releaseObject().

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related