c# excel does not clean up after creating pdf file

Question

I have successfully converted a Excel file to a pdf file using the code below.
I tried to release the excel object using the following method.
- Quit & Close & null
- Never use two-dots with COM objects
- ReleaseComObject
- GC.Collect

But, still remains "EXCEL.EXE" at Task Manager.
I do not want to terminate "EXCEL.EXE" by calling process list in the task manager

How do I solve this problem?

public bool ExportWorkbookToPdf(string workbookPath, string outputPath)
{
    // If either required string is null or empty, stop and bail out
    if (string.IsNullOrEmpty(workbookPath) || string.IsNullOrEmpty(outputPath))
    {
        return false;
    }

    // Create COM Objects
    Microsoft.Office.Interop.Excel.Application excelApplication;
    Microsoft.Office.Interop.Excel.Workbooks excelWorkbooks;
    Microsoft.Office.Interop.Excel.Workbook excelWorkbook;

    // Create new instance of Excel
    //var excelApplication = new Microsoft.Office.Interop.Excel.Application();
    excelApplication = new Microsoft.Office.Interop.Excel.Application();

    // Make the process invisible to the user
    excelApplication.ScreenUpdating = false;

    // Make the process silent
    excelApplication.DisplayAlerts = false;

    // Open the workbook that you wish to export to PDF
    excelWorkbooks = excelApplication.Workbooks;
    excelWorkbook = excelWorkbooks.Open(workbookPath);

    // If the workbook failed to open, stop, clean up, and bail out
    if (excelWorkbook == null)
    {
        //excelApplication.Application.Quit();
        excelApplication.Quit();

        excelWorkbook = null;
        excelWorkbooks = null;
        excelApplication = null;

        return false;
    }

    var exportSuccessful = true;
    try
    {
        excelWorkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, outputPath);
    }
    catch (System.Exception ex)
    {
        // Mark the export as failed for the return value...
        exportSuccessful = false;

        // Do something with any exceptions here, if you wish...
        // MessageBox.Show...        
    }
    finally
    {
        // Close the workbook, quit the Excel, and clean up regardless of the results...
        excelWorkbook.Close();
        excelWorkbooks.Close();
        excelApplication.Quit();

        excelWorkbook = null;
        excelWorkbooks = null;
        excelApplication = null;

        ReleaseExcelObject(excelWorkbook);
        ReleaseExcelObject(excelWorkbooks);
        ReleaseExcelObject(excelApplication);
    }
    return exportSuccessful;
}
private static void ReleaseExcelObject(object obj)
{
    try
    {
        if (obj != null)
        {
            Marshal.ReleaseComObject(obj);
            obj = null;
        }
    }
    catch (Exception ex)
    {
        obj = null;
        throw ex;
    }
    finally
    {
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

PaulF PaulF · Accepted Answer · 2017-09-04T14:37:11

In your finally statement you set the excelWorkbook, excelWorkbooks & excelApplication to null before using the same reference to pass to the ReleaseExcelObject - so you are effectively calling ReleaseExcelObject(null); which does nothing but call GCCollect. Try swapping the order.

Also, earlier in the code - if you fail to open a workbook you simply quit the application & set the reference to null. You could try putting the open of the workbook inside the try statement, so the error condition will be cleaned up in the finally statement instead of requiring duplicate code.

Something along the lines of :

var exportSuccessful = true;
try
{
  excelWorkbook = excelWorkbooks.Open(workbookPath);

  // If the workbook failed to open, stop, clean up, and bail out
  if (excelWorkbook == null)
    return false;

    excelWorkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, outputPath);
}
catch (System.Exception ex)
{
    // Mark the export as failed for the return value...
    exportSuccessful = false;

    // Do something with any exceptions here, if you wish...
    // MessageBox.Show...        
}
finally
{
    // Close the workbook, quit the Excel, and clean up regardless of the results...
    excelWorkbook?.Close();
    excelWorkbooks?.Close();
    excelApplication?.Quit();

    ReleaseExcelObject(excelWorkbook);
    ReleaseExcelObject(excelWorkbooks);
    ReleaseExcelObject(excelApplication);

    excelWorkbook = null;
    excelWorkbooks = null;
    excelApplication = null;
}

c# excel does not clean up after creating pdf file

1 Answers