Closing Microsoft Excel sheets in visual studio c#

Question

I am designing a WPF application where I am opening a excel file and then writing some data in it and then closing it. Now I also need to send email to an email address where I need to attach this excel file. But while attaching it throws an exception that the file is used by another process. I looked into task manager and found out that Microsoft excel was running in background.

I looked out for few available solutions and edited the code like below:

        xlWorksheet.SaveAs("C:\\Data.xlsx");
        Marshal.FinalReleaseComObject(xlWorksheet);
        xlWorkbook.Close();
        Marshal.FinalReleaseComObject(xlWorkbook);
        xlApp.Quit();
        Marshal.FinalReleaseComObject(xlApp);

Now this seems to work as it closes the microsoft excel. But it take to much time to close. Sometime it closes excel immediately but sometime it takes 3-5 mins to close excel. As I need to send email just after saving data in excel then how to handle this situation. In my application, user will press the button and following task will happen:

1. Open excel sheet
2. Save data into it.
3. Close the excel sheet
4. Start SMTP
5. Add email address
6. Attach excel sheet
7. Send Email

Now during attachment, it fails because excel is opened in background so it shows error. How to close excel sheet immediately. Is there any alternative to this solution.

Answer 1

You have to be super buttoned up when using Excel interop. I won't repost everything that's already available on SO, but here is the intro comment to my ExcelInterfacer class:

/*
General rules on working with interop COM objects boil down to "Never use 2 dots" (always hold a named
reference to all COM objects so that you can explicitely release them via Marshal.FinalReleaseComObject()): 
https://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects/1307180#1307180
Also, don't force the GC to cleanup in the same method where objects are used (or debugging breaks):
https://stackoverflow.com/questions/17130382/understanding-garbage-collection-in-net/17131389#17131389 
(We initiate our GC calls when disposing.)
*/

How do I properly clean up Excel interop objects?

Understanding garbage collection in .NET

In my program, I only open Excel when it's necessary, and I always wrap the ExcelInterfacer class in a using block. For that, you have to implement IDisposable, but that's easy. Doing this, my Excel processes always close very soon after they're not used anymore, but you're still slave to garbage collection - unless you force that immediately too, I guess.

I've never tried doing anything with the Excel document in the very next code statement after the using() block. You probably want to check whether the hidden ~$ version of the Excel file you want to mail still exists or has been closed. That would be the clear indication that the file is accessible.

My suggestion:

1. Read those posts above and understand them. It's easy to detach the Excel RCW from the C# object that gets created, and then your FinalReleaseComObject() calls won't do much.
2. Create a class for all Excel operations, and make sure that it implements IDisposable correctly.
3. Wrap all of your Excel interactions via that class in a using() block.
4. Check if the ~$ file is gone as an indication if the Excel file is okay to access.
5. Finally: get used to checking your running processes regularly. If your program crashes (or you quit the debug session) while Excel interop is active, you orphan that Excel process. Get used to killing them manually, so that no Excel is running when you start your next debug session.

Go from there :) For reference, the IDisposable code from my Excel interface class:

// Properly clean up Excel on quitting. See:
// https://stackoverflow.com/questions/2260990/com-object-that-has-been-separated-from-its-underlying-rcw-cannot-be-used
public void Dispose()
{
    Dispose(true);
    GC.SuppressFinalize(this);
}

private void Dispose(bool disposing)
{
    if (m_Disposed)
        return;

    // I know this looks like total overkill, but per http://www.xtremevbtalk.com/tutors-corner/160433-automating-office-programs-vb-net-com-interop.html           
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();

    if (disposing)
    {
        // Free managed objects
        CloseWorkbook();

        if (m_Application != null)
        {
            m_Application.Quit();
            Marshal.FinalReleaseComObject(m_Application);
        }

        m_Application = null;
    }

    m_Disposed = true;
}

~ExcelInterfacer()
{
    Dispose(false);
}

Answer 2

I'd like to suggest trying to work with EPPLUS? It'll help you be seperated from the EXCEL Api needing to be installed the machine, as well as a having much clearer (cleaner) API (no marshalling/com etc..) to work with - in other words free you of EXCEL INTEROP and it's COMplicated API (pun intended).

EPPLUS will handle your use case fine, in that it will not open an excel process, and thus when attaching the file to your e-mail, that won't be a problem anymore as the excel process isn't tied to the file. You control the flow with EPPLUS.

So unless you need a very specific API call in excel - but as I see you're only adding data I feel you won't be needing very specific excel functionality - I feel EPPLUS is always a better and cleaner option.

1. Seperates you from needing EXCEL on the machine you're running your code
2. No need to worry about confusing COM / Excel Interop API
3. A lot more memory efficient, partly because it won't start the excel process
4. Support for xlsx and the following: Cell Ranges, Cell styling (Border, Color, Fill, Font, Number, Alignments), Charts, Pivot tables, tables, Pictures, Shapes, Comments, Protection, Encryption, Data validation, Conditional formatting, VBA, Formula calculation, and more...

Check https://www.nuget.org/packages/EPPlus and http://epplus.codeplex.com/documentation Here's some example usages:

using(var package = new ExcelPackage(new FileInfo(@"c:\temp\tmp.xlsx")))
{
   // calculate all formulas in the workbook
   package.Workbook.Calculate();
   // calculate one worksheet
   package.Workbook.Worksheets["my sheet"].Calculate();
   // calculate a range
   package.Workbook.Worksheets["my sheet"].Cells["A1"].Calculate();
   // create a new sheet and write and manipulate cells on it
   var ws = package.Workbook.Worksheets.Add("new sheet");
   ws.Cells["B1"].Value = "some data";
   ws.Cells["C1"].Value = "some more data";
   ws.Cells["B1:E1"].Style.Font.Bold = true;
   // Save your EXCEL sheet, the using statement will close all references afterwards - since it EPPLUS neatly provides the IDisposable pattern around an excel file (aka ExcelPackage).
   package.Save();
}