Just another Excel COM issue

Question

I have an app that imports data from an excel file. It creates an excel COM object and reads data from it. After that i release all object and release all excel objects. It does all this on a windows server, using excel installed on this machine. The import files are stored on user's machines.

If i try to import data from an file that is also open in Excel on the user's machine, then the app can't release the Excel COM objects.

Any ideea how can i fix this (close that instance anyway) ?

Thanks!

I've added my code :

public DataTable DoImportToDataTable(BackgroundWorker worker, string strPath, int columnCount, bool bIgnoreFirstLine = true)
    {
        bool importOk = false;
        DataTable datatable = new System.Data.DataTable("ExcelContent");

        Excel.Application excelApp = null; // the excel application instance
        Excel.Workbook importFile = null; // the export workbook
        Excel.Worksheet sheet = null; // the worksheet
        Excel.Range range = null;
        Excel.Sheets sheets = null;
        try
        {
            excelApp = new Excel.Application();
            excelApp.DisplayAlerts = false;
            // try to open the file
            importFile = excelApp.Workbooks.Open(strPath, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            sheets = importFile.Worksheets;
            sheet = (Excel.Worksheet)sheets.get_Item(1);

            range = sheet.UsedRange; 


            int usedColumnsCount = range.Cells.Columns.Count;
            int usedRowsCount = range.Cells.Rows.Count;

            if (usedColumnsCount < columnCount)
            {
                throw new ImportException("Wrong file structure! Please check and correct the import file to match the requirements.");
            }

            Object[,] values = (Object[,])range.Value2;

            data.Clear();

            int row = 1;
            // read data from used range
            while (row <= usedRowsCount)
            {
                if (row == 1 && bIgnoreFirstLine)
                {
                    row++;
                    continue;
                }

                if (worker.CancellationPending)
                {
                    throw new Exception("Operation cancelled");
                }

                ArrayList line = new ArrayList();
                bool bIsLineEmpty = true;
                for (int i = 0; i < columnCount; i++)
                {
                    if (values[row, i + 1] == null)
                        line.Add("");
                    else
                    {
                        line.Add((String)values[row, i + 1].ToString());
                        bIsLineEmpty = false;
                    }
                }

                if (bIsLineEmpty)
                    // return after first empty line in range
                    break;


                datatable.Rows.Add(line.ToArray());
                data.Add(line);
                row++;
            }
            // cleanup
            excelApp.DisplayAlerts = false;

            GC.Collect();
            GC.WaitForPendingFinalizers();

            GC.Collect();
            GC.WaitForPendingFinalizers();

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

            if (sheet != null) {
                Marshal.FinalReleaseComObject(sheet);
                sheet = null;
            }
            if (sheets != null)
            {
                Marshal.FinalReleaseComObject(sheets);
                sheets = null;
            }
            if (importFile != null)
            {
                importFile.Close(Type.Missing, Type.Missing, Type.Missing);
                Marshal.FinalReleaseComObject(importFile);
                importFile = null;
            }
            if (excelApp != null)
            {
                excelApp.Quit();
                Marshal.FinalReleaseComObject(excelApp);
                excelApp = null;
            }


            importOk = true;
        }
        catch (COMException e)
        {
            message = e.Message;
        }
        catch (ImportException e)
        {
            message = e.ImportMessage;
        }
        catch (Exception e)
        {
            message = e.Message;
        }
        finally
        {
            if (!importOk)
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();

                GC.Collect();
                GC.WaitForPendingFinalizers();

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

                if (sheet != null)
                {
                    Marshal.FinalReleaseComObject(sheet);
                    sheet = null;
                }
                if (sheets != null)
                {
                    Marshal.FinalReleaseComObject(sheets);
                    sheets = null;
                }
                if (importFile != null)
                {
                    importFile.Close(Type.Missing, Type.Missing, Type.Missing);
                    Marshal.FinalReleaseComObject(importFile);
                    importFile = null;
                }
                if (excelApp != null)
                {
                    excelApp.Quit();
                    Marshal.FinalReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
        }
        return datatable;
    }

score 0 · Answer 1 · answered Mar 01 '11 at 09:29

0

I've just tried what you described, like this:

        _Application app = new Application();
        Workbook wb = app.Workbooks.Open(@"C:\Users\josip.INCENDO\Desktop\Payment (uplate) - primjer.xls");
        wb.Close();
        wb = null;
        app.Quit();
        app = null;

And it works perfectly fine, even if the user has opened document. Can you post the code maybe?

answered Mar 01 '11 at 09:29

nogola

214
4
12

I've tried your code, just commented out the stuff where you're doing smth with the file, and it's working fine. I open the file with Excel => in Task Manager there is 1 Excel process. I start the programm, it opens the file. => in Task Manager there are 2 Excel processes. The program ends, closes the app. => in Task Manager there is 1 Excel process. I close the Excel appliaction => no Excel processes in the Task Manager. – nogola Mar 01 '11 at 09:51
Thanks for the quick reponse. I get the same behaviour locally. The problem is when the app runs on a windows server and the file is stored and opened on another machine – maephisto Mar 01 '11 at 10:09

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

Have a look here: Excel 2007 Hangs When Closing via .NET

Always assign your excel objects to local variables, never going 'two dots down', like this:

//fail
Workbook wkBook = xlApp.Workbooks.Open(@"C:\mybook.xls");

//win
Worksheets sheets = xlApp.Worksheets;
Worksheet sheet = sheets.Open(@"C:\mybook.xls");
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

.NET creates a wrapper for the COM object that is invisible to you and is not released until the GC weaves its magic.

edited May 23 '17 at 12:19

Community

1
1

answered Mar 02 '11 at 11:36

hearn

1,007
7
8

Thanks! I know this issues, and have applied them on my code. If the file is not open locally, the instance is closed with no problem. But if the file is open, this instance just can't be closed – maephisto Mar 02 '11 at 12:06

Just another Excel COM issue

2 Answers2