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;
    }

nogola · Accepted Answer · 2011-03-01 09:29:08Z

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 1, 2011 at 9:29

nogola

2244 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nogola Over a year ago

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.

maephisto Over a year ago

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

Community · Accepted Answer · 2017-05-23 12:19:53Z

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, 2017 at 12:19

CommunityBot

11 silver badge

answered Mar 2, 2011 at 11:36

hearn

1,0177 silver badges8 bronze badges

1 Comment

maephisto Over a year ago

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

Collectives™ on Stack Overflow

Just another Excel COM issue

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related