Solved

How to ensure I don't keep keep creating additional EXCEL.EXE processes each time I call a routine in C# from a loop?

Posted on 2013-01-11
3
257 Views
Last Modified: 2013-01-14
I am developing a C# Windows application using VS2005.
In the following routine which I call from a loop,
it seems that I create a new EXECEL.EXE process each time this routine is called
according to the Windows Task Manager.
Is there a way to ensure that I drop the EXCEL.EXE before I call this routine the next time?

public static void ExcelFormat(string filename, string footnote)
{
            Excel.Application oXL = new Excel.Application();
            oXL.Visible = false;
            oXL.DisplayAlerts = false;

            Excel._Workbook oWB = (Excel._Workbook)oXL.Workbooks.Open(filename, 0, false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, false, false, Missing.Value, false, false, false);
            Excel._Worksheet oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            oSheet.Activate();

            try
            {

                oSheet.get_Range(oSheet.Cells[2, 1], oSheet.Cells[65535, 1]).EntireColumn.NumberFormat = "#,##0";

            }
            finally
            {                
                oXL.Workbooks.Close();
                oXL.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
                oSheet = null;
                oWB = null;
                oXL = null;
            }
}
0
Comment
Question by:zimmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

Assisted Solution

by:Michael74
Michael74 earned 125 total points
ID: 38769125
Instead of creating the excel object within the method you could create the object in the calling method and then pass this object to the method above. This  should prevent multiple objects being created. You can also call the garbage disposal directly to speed up the release of the memory.

Michael
0
 
LVL 9

Assisted Solution

by:sognoct
sognoct earned 125 total points
ID: 38770340
int pid = 0;
Excel.Application oXL = new Excel.Application();
Process p = Process.GetProcessById(GetWindowThreadProcessId(oXL.Hwnd, out pid));

now that you have the pid of the process you can kill it according to your needs
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 250 total points
ID: 38771170
Michael's remark about creating the Excel object only once is pretty much a golden rule in general. However, in my testing I hardly noticed any delay while creating it over and over so I left this alone for the moment, also because I don't know the structure of the rest of your application, mainly if indeed you are talking about a loop that is calling this function lots of times right after each other or maybe 3 times a day.

His remark about garbage collection I was going to suggest to stay away from however on my own system I couldn't get it to work without it...

Killing a process should be a last resort but in some cases a nice option to have.

What I ended up doing in the 'finally' block is:
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
GC.Collect();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);

Open in new window

One more remark: I haven't used code like this a lot but somewhere I read that you shouldn't use temporary objects like you do with get_Range. This could be causing stray object references which cause the behaviour you're experiencing (not in general but specifically with COM Interop objects). So you would need another object to hold the range, set the NumberFormat on that, and release the range object as well in the finally block. However, for this particular call (since you use .EntireColumn) you should be able to just use:
oSheet.Columns[1].NumberFormat = "#,##0";

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question