Excel process stays alive even after calling Quit

Question

I am creating Excel Sheet using Devexpress Exporter and then saving the file at a particular location.

After the creation of file, I have to open it, to add dropdownlist of items and then save it again in same location.

After all the operations, the file has to be emailed automatically to the email address from database.

Now if I have 1000 email addresses, and to automate this process, it is creating more than 10 instances of Excel.

How can I stop creation of those instance and how can I use excel operations without using more memory.

Code is as below:

protected string CreateExcelFile(string FilterName)
{       
    Random ranNumber = new Random();
    int number = ranNumber.Next(0, 10000000);
    string FileName = "TestDoc"+DateTime.Now.Year.ToString()+number.ToString()+DateTime.Now.Second.ToString()+".xls";
    string path = @"c:\TestDocuments\"+FileName;            
    Directory.CreateDirectory(Path.GetDirectoryName(path));
    FileStream fs = new FileStream(path, FileMode.OpenOrCreate);
    XlsExportOptions options = new XlsExportOptions();
    options.ExportHyperlinks = false;
    ASPxExporter.WriteXls(fs, options);
    fs.Close();     
    AddDropDownToExcel(path);   
    return path;    
}
//Adding The Dropdownlist Of Items TO Generated Excel Sheet
protected void AddDropDownToExcel(string path)
{
         Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();     
    string fileName = path.Replace("\\", "\\\\");
    string RowCount = "F" + (testgrid.VisibleRowCount + 1).ToString();
    // Open Excel and get first worksheet.      
    var workbook = application.Workbooks.Open(fileName);
    var worksheet = workbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
    // Set range for dropdownlist
    var rangeNewStatus = worksheet.get_Range("F2", RowCount);
    rangeNewStatus.ColumnWidth = 20;
    rangeNewStatus.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,
    Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, "Item1,Item2,Item3,Item4");
    // Save.
    workbook.Save();
    workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
    application.Quit();
}

Answer 1

First, I sincerely hope this isn't running on a server.

Then, if your problem is that too many instances of Excel are created, a thought is "don't create an instance every single time". Instead of starting Excel every time AddDropDownToExcel is called, can you reuse the same instance?

The problem you are having shows up regularly in Excel interop scenario; even though you are done and tell Excel to close, it "stays alive". It's usually caused by your app still holding a reference to a COM object that hasn't been disposed, preventing Excel from closing. This StackOverflow answer provides some pointers: https://stackoverflow.com/a/158752/114519

In general, to avoid that problem, you want to follow the "one-dot" rule. For instance, in your code:

var workbook = application.Workbooks.Open(fileName);  

will be a problem, because an "anonymous" wrapper for Workbooks is created, and will likely not be disposed properly. The "one-dot" rule would say "don't use more than one dot when working with Excel interop", in this case:

var workbooks = application.Workbooks;
var workbook = workbooks.Open(fileName);

A totally different thought - instead of using Interop, can't you use OpenXML to generate your Excel file? I have never tried it to create drop downs, but if it supports it, it will be massively faster than Interop, and the type of problems you have won't happen.

Hope this helps.

Answer 2

As I know the grow of number of runnig excel.exe processes is 'normal' situation to excel :)
The dumbest advice is just kill sometimes it's processes. BUT, this way will be absolutely unhelpful if you use excel during your app is working because of you rather don't get which one excel.exe is yours.