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

[Praveen K]

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>

[bruce barker]

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>

[Praveen K]

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>