G
Guest
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.ReleaseComObject(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.xlWorkbookNormal,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.ReleaseComObject (xlSheet1);
xlSheet1=null;
System.Runtime.InteropServices.Marshal.ReleaseComObject (wb);
wb=null;
System.Runtime.InteropServices.Marshal.ReleaseComObject (Excl);
Excl = null;
//System.Threading.Thread.Sleep(5000);
GC.Collect(); // force final cleanup!
GC.WaitForPendingFinalizers();
</CODE>
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.ReleaseComObject(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.xlWorkbookNormal,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.ReleaseComObject (xlSheet1);
xlSheet1=null;
System.Runtime.InteropServices.Marshal.ReleaseComObject (wb);
wb=null;
System.Runtime.InteropServices.Marshal.ReleaseComObject (Excl);
Excl = null;
//System.Threading.Thread.Sleep(5000);
GC.Collect(); // force final cleanup!
GC.WaitForPendingFinalizers();
</CODE>