473,236 Members | 1,808 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,236 software developers and data experts.

ASP.Net and Excel Process is not killed. Unmanged code problems

I have a problem in communicating between the C# and the Excel Interop
objects. The problem is something as described below.

I use Microsoft Office-XP PIA dll’s as these dll’s were been recommended by
many for web applications. I create the instances of Excel, Workbook and the
worksheet. And later on Release the references by
“System.Runtime.InteropServices.Marshal.ReleaseC omObject(Object)” and making
the object as null finally. In between the creation of instances and release
finally some manipulation on the spreadsheet is happening. I am facing two
problems here. I have solved both the problems but I need a better solution
for it. Please provide any comments on them if you and your team can.

Problem 1: the process of the Excel is created with the user IUSR_<<Name>>
which do not have the permission to kill the process hence even though you
have released the excel instances still the process keeps on running in the
task manager.

Solution: Change the processModel in the machine.config file set the
UserName=”SYSTEM” instead of machine. Before going to this solution I tried
to give privileges to the ASPNET, IUSR_<<Name>> user’s and get it work, but
it didn’t helped me out. So finally I need to change the machine.config to
get it working, I wrote the processModel in the web.config but still it
didn’t helped me as the web.config processModel section was not overriding
the machine.config. It took an entire day to find out the solution.

Problem 2: When I call the COM Object in a sequence for more than 5 to 6
times the excel process is not getting killed from the task manager. The
issue is something like below: I create the instance of Excel Application
Class, then the workbook and then worksheet instance lets consider the
instance name of the worksheet is xSheet1. Now if you call the xSheet1 for 5
times and release the Excel all objects, excel process from the task manager
is killed. Where as if I hit the same with more than 5 times and release
Excel objects the process is left behind and is not killed.

I haven’t got a proper solution for this. I installed the DotNetfx service
pack1.1 for .net, but still the problem exists. What I feel is the problem
lies in communication between the managed and unmanaged code. I think the
there are few bugs in the following dll’s

mscorwks.dll
mscorsvr.dll
mscorjit.dll
mscorlib.dll

Microsoft claims that they have upgraded this dll’s in the service pack 1.1,
but after installing the service pack also the same issue exists.

Your earliest response will be helpful. As our entire work has been stuck in
between and we need to get rid of this problem, We have already spent good
amount of time doing R&D on this.

Below is the Code sample of the work..
<CODE>

// Sample Code of the problem

Excel.Application Excl = new Excel.ApplicationClass();
Excel.Workbook wb = Excl.Workbooks.Open(pathnew,Type.Missing,false,
Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing,
Type.Missing,true, Type.Missing, Type.Missing, Type.Missing,Type.Missing,
Type.Missing);
Excel.Worksheet xlSheet1 = (Excel.Worksheet)wb.ActiveSheet;
string strfranchiseekey ="3083"; string strmodetype = "2";
SqlDataReader sqldreader;
//GetUnitDetails(strfranchiseekey, strmodetype,out sqldreader) ;
GetUnitDetails("3083","2",out sqldreader) ;
int irow=3;
if (sqldreader.HasRows)
{
while (sqldreader.Read())
{
if (sqldreader.GetSqlValue(2)!= "")
{
irow +=1;
xlSheet1.Cells[irow,2] = sqldreader.GetSqlValue(10).ToString().Trim();
xlSheet1.Cells[irow,3] = sqldreader.GetSqlValue(0).ToString().Trim();
xlSheet1.Cells[irow,4] = sqldreader.GetSqlValue(3).ToString().Trim();
xlSheet1.Cells[irow,5] = sqldreader.GetSqlValue(5).ToString().Trim();
xlSheet1.Cells[irow,6] = strmode;
// If the below lines are commented the process is killed after execution of
this code, where as if i uncomment the below lines the process remains in the
task manager
/*xlSheet1.Cells[irow,9] = strmode;
xlSheet1.Cells[irow,10] = strmode;
xlSheet1.Cells[irow,11] = strmode;
xlSheet1.Cells[irow,12] = strmode; */
}
}
}
Response.Write( pathnew);
sqldreader.Close();
sqldreader=null;
downloadpath= pathnew;
//wb.SaveAs(pathnew,Excel.XlFileFormat.xlWorkbookNor mal,null,null,false,false,Excel.XlSaveAsAccessMode .xlShared,false,false,null,null,null);
wb.Save();
wb.Close(null,null,null);

//wb.Close(null,null,null);
Excl.Workbooks.Close();
Excl.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlSheet1);
xlSheet1=null;
System.Runtime.InteropServices.Marshal.ReleaseComO bject (wb);
wb=null;
System.Runtime.InteropServices.Marshal.ReleaseComO bject (Excl);
Excl = null;
//System.Threading.Thread.Sleep(5000);
GC.Collect(); // force final cleanup!
GC.WaitForPendingFinalizers();
</CODE>
Nov 18 '05 #1
2 5404
microsoft does not recommend this approach:

http://support.microsoft.com/default...b;EN-US;257757

here is the workaround to do the exit

http://support.microsoft.com/default...b;EN-US;317109
-- bruce (sqlwork.com)
"Praveen K" <Pr******@discussions.microsoft.com> wrote in message
news:CE**********************************@microsof t.com...
I have a problem in communicating between the C# and the Excel Interop
objects. The problem is something as described below.

I use Microsoft Office-XP PIA dll's as these dll's were been recommended by many for web applications. I create the instances of Excel, Workbook and the worksheet. And later on Release the references by
"System.Runtime.InteropServices.Marshal.ReleaseCom Object(Object)" and making the object as null finally. In between the creation of instances and release finally some manipulation on the spreadsheet is happening. I am facing two
problems here. I have solved both the problems but I need a better solution for it. Please provide any comments on them if you and your team can.

Problem 1: the process of the Excel is created with the user IUSR_<<Name>>
which do not have the permission to kill the process hence even though you
have released the excel instances still the process keeps on running in the task manager.

Solution: Change the processModel in the machine.config file set the
UserName="SYSTEM" instead of machine. Before going to this solution I tried to give privileges to the ASPNET, IUSR_<<Name>> user's and get it work, but it didn't helped me out. So finally I need to change the machine.config to
get it working, I wrote the processModel in the web.config but still it
didn't helped me as the web.config processModel section was not overriding
the machine.config. It took an entire day to find out the solution.

Problem 2: When I call the COM Object in a sequence for more than 5 to 6
times the excel process is not getting killed from the task manager. The
issue is something like below: I create the instance of Excel Application
Class, then the workbook and then worksheet instance lets consider the
instance name of the worksheet is xSheet1. Now if you call the xSheet1 for 5 times and release the Excel all objects, excel process from the task manager is killed. Where as if I hit the same with more than 5 times and release
Excel objects the process is left behind and is not killed.

I haven't got a proper solution for this. I installed the DotNetfx service
pack1.1 for .net, but still the problem exists. What I feel is the problem
lies in communication between the managed and unmanaged code. I think the
there are few bugs in the following dll's

mscorwks.dll
mscorsvr.dll
mscorjit.dll
mscorlib.dll

Microsoft claims that they have upgraded this dll's in the service pack 1.1, but after installing the service pack also the same issue exists.

Your earliest response will be helpful. As our entire work has been stuck in between and we need to get rid of this problem, We have already spent good
amount of time doing R&D on this.

Below is the Code sample of the work..
<CODE>

// Sample Code of the problem

Excel.Application Excl = new Excel.ApplicationClass();
Excel.Workbook wb = Excl.Workbooks.Open(pathnew,Type.Missing,false,
Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing,
Type.Missing,true, Type.Missing, Type.Missing, Type.Missing,Type.Missing,
Type.Missing);
Excel.Worksheet xlSheet1 = (Excel.Worksheet)wb.ActiveSheet;
string strfranchiseekey ="3083"; string strmodetype = "2";
SqlDataReader sqldreader;
//GetUnitDetails(strfranchiseekey, strmodetype,out sqldreader) ;
GetUnitDetails("3083","2",out sqldreader) ;
int irow=3;
if (sqldreader.HasRows)
{
while (sqldreader.Read())
{
if (sqldreader.GetSqlValue(2)!= "")
{
irow +=1;
xlSheet1.Cells[irow,2] = sqldreader.GetSqlValue(10).ToString().Trim();
xlSheet1.Cells[irow,3] = sqldreader.GetSqlValue(0).ToString().Trim();
xlSheet1.Cells[irow,4] = sqldreader.GetSqlValue(3).ToString().Trim();
xlSheet1.Cells[irow,5] = sqldreader.GetSqlValue(5).ToString().Trim();
xlSheet1.Cells[irow,6] = strmode;
// If the below lines are commented the process is killed after execution of this code, where as if i uncomment the below lines the process remains in the task manager
/*xlSheet1.Cells[irow,9] = strmode;
xlSheet1.Cells[irow,10] = strmode;
xlSheet1.Cells[irow,11] = strmode;
xlSheet1.Cells[irow,12] = strmode; */
}
}
}
Response.Write( pathnew);
sqldreader.Close();
sqldreader=null;
downloadpath= pathnew;
//wb.SaveAs(pathnew,Excel.XlFileFormat.xlWorkbookNor mal,null,null,false,fals
e,Excel.XlSaveAsAccessMode.xlShared,false,false,nu ll,null,null); wb.Save();
wb.Close(null,null,null);

//wb.Close(null,null,null);
Excl.Workbooks.Close();
Excl.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlSheet1);
xlSheet1=null;
System.Runtime.InteropServices.Marshal.ReleaseComO bject (wb);
wb=null;
System.Runtime.InteropServices.Marshal.ReleaseComO bject (Excl);
Excl = null;
//System.Threading.Thread.Sleep(5000);
GC.Collect(); // force final cleanup!
GC.WaitForPendingFinalizers();
</CODE>

Nov 18 '05 #2
I have tried all the workaround but still the problem exists. The problem is
the automation has random cells reference problem. yes i know that microsoft
is not recommending this but still i do not have any other options for my
requirement. i have already tried the ADO, XMl and all other alternatives. I
read that the Dotnetfx Servicepack 1.1 resolves this problem but why i am not
able to find it out .. some ask Microsoft why they do maintain such Dll's
which do not works. This is a bad remark for We professionals who supports
Microsoft. Thats the reason the Delphi is growing stronger to MS.. our
company has solved this issue using Delphi components.. i need a solution in
C# not in Delphi...

"bruce barker" wrote:
microsoft does not recommend this approach:

http://support.microsoft.com/default...b;EN-US;257757

here is the workaround to do the exit

http://support.microsoft.com/default...b;EN-US;317109
-- bruce (sqlwork.com)
"Praveen K" <Pr******@discussions.microsoft.com> wrote in message
news:CE**********************************@microsof t.com...
I have a problem in communicating between the C# and the Excel Interop
objects. The problem is something as described below.

I use Microsoft Office-XP PIA dll's as these dll's were been recommended

by
many for web applications. I create the instances of Excel, Workbook and

the
worksheet. And later on Release the references by
"System.Runtime.InteropServices.Marshal.ReleaseCom Object(Object)" and

making
the object as null finally. In between the creation of instances and

release
finally some manipulation on the spreadsheet is happening. I am facing two
problems here. I have solved both the problems but I need a better

solution
for it. Please provide any comments on them if you and your team can.

Problem 1: the process of the Excel is created with the user IUSR_<<Name>>
which do not have the permission to kill the process hence even though you
have released the excel instances still the process keeps on running in

the
task manager.

Solution: Change the processModel in the machine.config file set the
UserName="SYSTEM" instead of machine. Before going to this solution I

tried
to give privileges to the ASPNET, IUSR_<<Name>> user's and get it work,

but
it didn't helped me out. So finally I need to change the machine.config to
get it working, I wrote the processModel in the web.config but still it
didn't helped me as the web.config processModel section was not overriding
the machine.config. It took an entire day to find out the solution.

Problem 2: When I call the COM Object in a sequence for more than 5 to 6
times the excel process is not getting killed from the task manager. The
issue is something like below: I create the instance of Excel Application
Class, then the workbook and then worksheet instance lets consider the
instance name of the worksheet is xSheet1. Now if you call the xSheet1 for

5
times and release the Excel all objects, excel process from the task

manager
is killed. Where as if I hit the same with more than 5 times and release
Excel objects the process is left behind and is not killed.

I haven't got a proper solution for this. I installed the DotNetfx service
pack1.1 for .net, but still the problem exists. What I feel is the problem
lies in communication between the managed and unmanaged code. I think the
there are few bugs in the following dll's

mscorwks.dll
mscorsvr.dll
mscorjit.dll
mscorlib.dll

Microsoft claims that they have upgraded this dll's in the service pack

1.1,
but after installing the service pack also the same issue exists.

Your earliest response will be helpful. As our entire work has been stuck

in
between and we need to get rid of this problem, We have already spent good
amount of time doing R&D on this.

Below is the Code sample of the work..
<CODE>

// Sample Code of the problem

Excel.Application Excl = new Excel.ApplicationClass();
Excel.Workbook wb = Excl.Workbooks.Open(pathnew,Type.Missing,false,
Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing,
Type.Missing,true, Type.Missing, Type.Missing, Type.Missing,Type.Missing,
Type.Missing);
Excel.Worksheet xlSheet1 = (Excel.Worksheet)wb.ActiveSheet;
string strfranchiseekey ="3083"; string strmodetype = "2";
SqlDataReader sqldreader;
//GetUnitDetails(strfranchiseekey, strmodetype,out sqldreader) ;
GetUnitDetails("3083","2",out sqldreader) ;
int irow=3;
if (sqldreader.HasRows)
{
while (sqldreader.Read())
{
if (sqldreader.GetSqlValue(2)!= "")
{
irow +=1;
xlSheet1.Cells[irow,2] = sqldreader.GetSqlValue(10).ToString().Trim();
xlSheet1.Cells[irow,3] = sqldreader.GetSqlValue(0).ToString().Trim();
xlSheet1.Cells[irow,4] = sqldreader.GetSqlValue(3).ToString().Trim();
xlSheet1.Cells[irow,5] = sqldreader.GetSqlValue(5).ToString().Trim();
xlSheet1.Cells[irow,6] = strmode;
// If the below lines are commented the process is killed after execution

of
this code, where as if i uncomment the below lines the process remains in

the
task manager
/*xlSheet1.Cells[irow,9] = strmode;
xlSheet1.Cells[irow,10] = strmode;
xlSheet1.Cells[irow,11] = strmode;
xlSheet1.Cells[irow,12] = strmode; */
}
}
}
Response.Write( pathnew);
sqldreader.Close();
sqldreader=null;
downloadpath= pathnew;

//wb.SaveAs(pathnew,Excel.XlFileFormat.xlWorkbookNor mal,null,null,false,fals
e,Excel.XlSaveAsAccessMode.xlShared,false,false,nu ll,null,null);
wb.Save();
wb.Close(null,null,null);

//wb.Close(null,null,null);
Excl.Workbooks.Close();
Excl.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlSheet1);
xlSheet1=null;
System.Runtime.InteropServices.Marshal.ReleaseComO bject (wb);
wb=null;
System.Runtime.InteropServices.Marshal.ReleaseComO bject (Excl);
Excl = null;
//System.Threading.Thread.Sleep(5000);
GC.Collect(); // force final cleanup!
GC.WaitForPendingFinalizers();
</CODE>


Nov 18 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Tim Marsden | last post by:
Hi, I have a routine which is call from a ASP.NET web form. This routine creates an excel application, opens a workbook , runs some code to update the workbook, saves it as HTML on the sever and...
2
by: Powerguy | last post by:
Hi all, I am looking for a way to get the Process id (or a handle) of an EXCEL process created from within my code. For example when the following code is executed: Dim EXL As...
18
by: lgbjr | last post by:
Hi All, I have a VB.NET app that, among other things, writes data to Excel. I am having trouble getting the Excel process to terminate after I quit Excel. I found an article related to this...
16
by: LP | last post by:
Hello, I am trying to use .NET with Excel. I installed Office 2003 and selected ..NET programming suport option, so it installed all those PIA, as MS sugests. But I can not find a way to destroy...
6
by: B. | last post by:
my small project has two files, umg.cpp (unmanaged c++) and mged.cpp (MC++), and unmanged code will call managed code. However, I cannot debug from unmanged code into managed code. Can anyone help...
13
by: chuckie_9497 | last post by:
hello all you gurus. I am struggling with releasing com objects. I have isolated the problem to the code below. Objects are released and the process ends until I use "int k = sheet.Count;" Then...
4
by: pavi14 | last post by:
Hi, I have a method in C# which checks if any Excel process is running on my system. If it finds any process is there a way to get the directory path of the file running in that excel process...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.