File Locked after Opening with Excel Interop

Question

I am opening an excel file, refreshing it's data sources, and saving it to PDF with a c# app. I based it on code samples like this. However, the file remains locked even after the calls to final release.

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

// Open the Workbook:
Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Workbooks.Open(
    @"c:\test\test.xlsx",
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

wb.RefreshAll();
Thread.Sleep(4000); //surely a better way to do this

//ws.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, @"c:\test\test.pdf");

// Cleanup:
GC.Collect();
GC.WaitForPendingFinalizers();

Marshal.FinalReleaseComObject(ws);

wb.Close(false, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(wb);

excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);

What additional steps could ensure it is not locked after the process completes?

score 2 · Accepted Answer · answered Feb 21 '15 at 17:33

I think you are doing some work out of order here - ie, collecting before closing your application or workbooks.
I have gotten to the point where I clean up for each part of the Excel object - worksheet, workbook, application, and processID. This may be an abundance of caution, but better to be sure you are cleaning your memory IMO.
I do not know that this is your issue, but I tend to use a fuller statement for exporting PDFs. I would consider it.

xlWorkbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, filepath, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, 1, 1, true, missing);

What I have done for my own work is create two functions to handle the work of killing off ghost processes (which probably is causing your lockup).

My code is packaged inside of a class that I create to handle Excel instances for me called singleExcel, so tweak it as you need, but it should get you on your way.

public Excel._Worksheet xlWorksheet { get; set; }
public Excel.Application xlApp { get; set; }
public Excel.Workbook xlWorkbook { get; set; }
public Process excelProcess { get; set; }

public static void CloseSheet(singleExcel thisExcel)
{
    if (thisExcel.excelProcess != null)
    {
        try
        {
            thisExcel.excelProcess.Kill();
            thisExcel.excelProcess.Dispose();
        }
        catch (Exception ex)
        {
            // depending on your needs   
        }
    }
    else
    {
        thisExcel.xlWorkbook.Close(true);
        thisExcel.xlApp.Quit();
    }
    releaseObject(thisExcel.xlWorksheet);
    releaseObject(thisExcel.xlWorkbook);
    releaseObject(thisExcel.xlApp);
    releaseObject(thisExcel.excelProcess);
    releaseObject(thisExcel);
}

public static void releaseObject(object obj)
{
    try
    {
        Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch
    {
        obj = null;
    }
    finally
    {
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

File Locked after Opening with Excel Interop

1 Answers1