?
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
Medium Priority
?
262 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:Michael Fowler
Michael Fowler earned 500 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 500 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 1000 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

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

752 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