C# Discussion Boards - CodeProject

7-Aug-09 1:07

Hello there,

I wrote a tool for importing and exporting data into ExcelSheets (better said: workbooks).

Some relevant parts of my code are:
using Excel = Microsoft.Office.Interop.Excel;
Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
excelApp.ScreenUpdating = false;
excelApp.DisplayAlerts = false;

then I open some workbooks, open some sheets, write some stuff into them and close all of them. I also dereference them if they are no more needed (mysheet = null).
But after finally shutting down the ExcelApp doing so:
excelApp.Quit();
excelApp = null;

... I have to wait quite a long time (about half a minute) until I see the EXCEL-thread diappear in my ProcessExplorer.
The problem is, that I can not open the just written xls-files if the application did not end, because it still has got its hands on the files.
So I have to know when this happens.

My questions are:
Why does it take so long?
When does the Excel-thread really end?
How do I end it correctly?
How can I provoke the thread to be ended?
Or is it just a matter of garbage collection? (a field I did not yet explore)

Thanks in advance,
Harry

no plan,
no signature

Moreno Airoldi7-Aug-09 2:03

7-Aug-09 2:03

Put simply, there is no way to completely shut down Excel (close the task so that you don't see it in the task list anymore) when you want to. It's a COM<->gargabe collector issue.

In earlier versions of the framework, it could be done with a direct call. GC.Collect() after quitting and nulling, but starting from 2.0 or 3.0 (can't remember cleary) it's no use.

The bright side is if you open more than one Excel app, only one will stay in memory for a long time, while all others will close immediately.

2+2=5 for very large amounts of 2
(always loved that one hehe!)

Harry6667-Aug-09 5:06

7-Aug-09 5:06

Thanks a lot! Thumbs Up | :thumbsup:

But I've still got one question left. Is there any event I can handle when the Excel Application shut down completely. I mean: I can wait some seconds Wink | ;)

but is there something I can respond to, because I want to know when I can open the xls-file?

Greets
Harry

no plan,
no signature

Moreno Airoldi7-Aug-09 15:12

7-Aug-09 15:12

Nope, no event you can handle. You might get a list of the running processes and check when "excel" isn't there anymore, but nothing will let you handle properly the case where the user opens other instances of Excel.

As for opening the xls file, you will be able to do that safely once you call Close on it (on the corresponding Workbook object), so that won't be a problem. Smile | :)

2+2=5 for very large amounts of 2
(always loved that one hehe!)

Harry66610-Aug-09 23:29

10-Aug-09 23:29

Moreno Airoldi wrote:
As for opening the xls file, you will be able to do that safely once you call Close on it (on the corresponding Workbook object), so that won't be a problem. Smile

Unfortunately not!
I tried opening the xls file I just wrote after myWorkbook.Close(), after myWorkbook = null, and even after myFileInfo = null. But doing so, Excel AND my little application will end up in "not responding". Maybe I can collect some garbage in order to get rid of the unreferenced workbook. But - Hey! - I do this at the end, when I wrote all the excelfiles I have to and finally end the application.
And so I still think, the best point to let anyone open the new files is when I closed and cleaned all up.

Greets
Harry

no plan,
no signature

Moreno Airoldi11-Aug-09 0:10

11-Aug-09 0:10

Then there must be something else in your code which prevents the workbook to be released.
Consider the following example:

Microsoft.Office.Interop.Excel.Application myApp = null;
Microsoft.Office.Interop.Excel.Workbook myWorkbook = null;
try
{
    myApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
    myApp.AskToUpdateLinks = false;
    myApp.DisplayAlerts = false;
    myWorkbook = myApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
    myWorkbook.SaveAs("D:\\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,
        System.Type.Missing, System.Type.Missing, false, false,
        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Type.Missing,
        System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);

}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
    if (myWorkbook != null)
    {
        myWorkbook.Close(false, "", null);
        myWorkbook = null;
    }
    if (myApp != null)
    {
        myApp.Quit();
        myApp = null;
    }
}

System.Diagnostics.Process.Start("D:\\test.xls");

The created workbook is successfully opened right after it's Saved and Closed (please note that Process.Start will leave you with yet another open Excel process in the task list).

I've been using this kind of interop a few times, and I assure you it works fine. I'll be more than glad to try and help you more if you want. Smile | :)

2+2=5 for very large amounts of 2
(always loved that one hehe!)

Harry66611-Aug-09 3:20

11-Aug-09 3:20

Okay, I thought about some things you said.

First thing:
My algorithm looks a little different...
I copy the master.xls to temp.xls
fiMyMaster.CopyTo(Path.Combine(destDir, "temp.xls"),true);
Then I don't call myWorkbook = myApp.Workbooks.Add(...)
I call myWorkbook = myApp.Workbooks.Open(...)

After I modified a lot of cells and ranges in all the sheets of this workbook
I simply do myWorkbook.Save(); // this is the temp.xls
and fiTemp.CopyTo(destfile);
where fiTemp obviously is the FileInfo to the temp.xls and destfile the designated full filename

Second thing:
Sure, I can call Process.Start after all that - works well! But I tried Process.Start(destfile) after the myWorkbook.Close part, before the myApp.Quit() and this doesn't work well. Frown | :(

Third:
I have to close the workbook and app, because it runs invisible. When I run Process.Start the user will see the opened ExcelApp Which he can stop by himself. So this is okay. I just have to check whether the designated file already is opened...

Greets
Harry

P.S.: the tool is paid Smile | :)

All I do now (e.g. opening the xls files out of the tool) is my leisure time (perhaps this will also be paid Cool | :cool:

)

no plan,
no signature

Moreno Airoldi12-Aug-09 1:24

12-Aug-09 1:24

Yes you are right! Calling Process.Start() before Application.Quit() won't work properly. I made some tests and I confirm it.

The problem is in how Excel optimizes opening documents. Consider the following example (derived from my previous):

Microsoft.Office.Interop.Excel.Application myApp = null;
Microsoft.Office.Interop.Excel.Workbook myWorkbook = null;
try
{
    try
    {
        myApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        myApp.Visible = true;
        myApp.AskToUpdateLinks = false;
        myApp.DisplayAlerts = false;
        myWorkbook = myApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
        myWorkbook.SaveAs("D:\\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,
            System.Type.Missing, System.Type.Missing, false, false,
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Type.Missing,
            System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);

    }
    finally
    {
        if (myWorkbook != null)
        {
            myWorkbook.Close(false, "", null);
            myWorkbook = null;
        }
    }

    System.Threading.Thread.Sleep(3000);
    System.Diagnostics.Process.Start("D:\\test.xls");
    System.Threading.Thread.Sleep(3000);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
    if (myApp != null)
    {
        myApp.Quit();
        myApp = null;
    }
}

You will clearly see that since one Excel task is still active (the one we instanced for opening and saving the workbook), the call to Process.Start() will cause it to be the container for the re-opening of the document. Since we call Application.Quit() right after that, we close the workbook! This "task optimization" is an Excel internal, so I doubt there's any way to bypass it.

All the other differences between your code and my example don't seem to count: it's all basically the same.

I think you'll just have to call Application.Quit() before opening the file. There's probably no way to avoid that (unless you use another application - not Excel - to show the file...).

If you are working on multiple documents, and you want to open each saved file while processing the next, you may consider opening a different Excel.Application for each single one. There will sure be an overhead, but you can make some tests and see if it's acceptable (it probably is).

This (brutal) example works fine:

for (int i = 1; i < 6; i++)
{
    Microsoft.Office.Interop.Excel.Application myApp = null;
    Microsoft.Office.Interop.Excel.Workbook myWorkbook = null;
    try
    {
        try
        {
            myApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            myApp.Visible = true;
            myApp.AskToUpdateLinks = false;
            myApp.DisplayAlerts = false;
            myWorkbook = myApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            myWorkbook.SaveAs("D:\\test" + i.ToString() + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,
                System.Type.Missing, System.Type.Missing, false, false,
                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Type.Missing,
                System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);

        }
        finally
        {
            if (myWorkbook != null)
            {
                myWorkbook.Close(false, "", null);
                myWorkbook = null;
            }
            if (myApp != null)
            {
                myApp.Quit();
                myApp = null;
            }
        }

        System.Diagnostics.Process.Start("D:\\test" + i.ToString() + ".xls");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Good luck, let me know how it goes! Smile | :)

2+2=5 for very large amounts of 2
(always loved that one hehe!)

Harry66612-Aug-09 2:09

12-Aug-09 2:09

Thanks a lot!

I read your hints, thought about them, and came to the conclusion: tomorrow is when my holidays start, and today is the day to prepare for this. Cool | :cool:

So for the next 5 days I don't think about programming. Wink | ;)

I'll pick it up again next week. Sigh | :sigh:

Cheers
Harry

no plan,
no signature

Moreno Airoldi12-Aug-09 2:15

12-Aug-09 2:15

Have a nice one, man! Cool | :cool:

2+2=5 for very large amounts of 2
(always loved that one hehe!)

Luc Pattyn7-Aug-09 5:31

Luc Pattyn

7-Aug-09 5:31

Hi,

I haven't done any of this, but it does not look OK you would have to wait for Excel to quit. Here are two suggestions:
1) look for a way to close your excel document, without closing excel
2) what happens if you tell your excelApp to save the current document and start a new document, doesn't that relinquish the old one?

BTW: if you modified the excel document, chances are background processes such as Office Indexers and Antivirus Software are reading your document and preventing you from opening it with write access for a while.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Harry6668-Aug-09 1:38

8-Aug-09 1:38

Hello Luc,
as I supposed it was just a matter of garbage collection.
I inserted the line
GC.Collect() and now it works fine.

Answering your suggestions:
1)I tell the workbook to SaveAs(...) then I Quit() the application. I want Excel to Quit when I finished writing the xls-documents, because someone (e.g. myself) might want to open the documents. And this is not possible as long as another task locks the documents. And Excel locks the documents as long as it is not shut down.

2)I'm starting one single Excel Application, with this I'm writing several workbooks (xls-files). When I wrote the file, I save and close it. Then I create the next file.
Actually, I copy an original to temp.xls (using only IO), then I write several stuff into this temp.xls, at the end I copy temp.xls to the final thisIsHowIWantToCallIt.xls (using IO)
An Excel Application may have more than one workbooks. the App.'s got a collection called workbooks. So opening a new would probably not abandon the old one.

3)There is no problem with the rights afaik. I create all the files by myself. They are just copies from the master. I'm using some makros in this Excel-sheets. If they were not allowed my programm would tell you to stop working and try playing solitaire Big Grin | :-D

(just kidding)

Greets
Harry

no plan,
no signature

Luc Pattyn8-Aug-09 1:47

Luc Pattyn

8-Aug-09 1:47

Hi Harry,

Harry666 wrote:
I inserted the line GC.Collect() and now it works fin

That cannot possibly be the right solution. GC.Collect() should not be needed at any time, calling it most often even is a bad idea.

It does tell me some object is no longer in use (e.g. its reference got set to null) but was still keeping resources because it wasn't closed or disposed properly. I'm not fimiliar with Office Interop, my guess would be saving a workbook is not enough, it needs to be closed as well (preventing more edits and saves, and discarding data and resources).

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Harry6668-Aug-09 2:22

8-Aug-09 2:22

Hello Luc,

it's understood that I do not only save the workbook, I also close it and finally set the reference to null. (Sorry, if I forgot to tell)
As I can see, the task is loitering around and I have to wait for its shutdown.
When I call GC.Collect() I do NOT have to wait.
The last thing I do, before the user gets back the control, is shutting down Excel. So this has to be finished before the user gets the control. I can not wait at this point. Or I have to know when Excel finished (some event to handle would be nice).

Do you know alternatives to GC.Collect() ?

Harry

no plan,
no signature

Moreno Airoldi11-Aug-09 0:11