Excel Instance won;t die if coping cell content to struct

A

alexia.bee

Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.

here is a snipcode

public System.Collections.Generic.List<frmMain.sDBTest>
LoadTestSet(string TestSetFile,
System.Collections.Generic.List<frmMain.sDBTest> DBviewList)
{
int rowIndex = 2;
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (TestSetFile.ToString() == "")
return null;

Application.DoEvents();

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
return null;
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
return null;
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

progressBar1.Maximum = rowIndex-2;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
progressBar1.Value = rowIndex-2;
frmMain.sDBTest frmmainsDBTestobj = new
frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
// 1.1 frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
// 1.2 frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
//frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
// 1.3 frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
DBviewList.Add(frmmainsDBTestobj);
rowIndex++;
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
//app.Workbooks.Close();
//app.Quit();
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();


return DBviewList;

}
In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.
 
N

Nicholas Paldino [.NET/C# MVP]

Alexia,

You are leaving references open everywhere. Most notably, the object
returned by the Cells property, as well as each Range object returned when
you access the Item property on the Cells object.

You need to keep careful count of these and release all the references
on these in order to make sure the instance dies.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.

here is a snipcode

public System.Collections.Generic.List<frmMain.sDBTest>
LoadTestSet(string TestSetFile,
System.Collections.Generic.List<frmMain.sDBTest> DBviewList)
{
int rowIndex = 2;
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (TestSetFile.ToString() == "")
return null;

Application.DoEvents();

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
return null;
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
return null;
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

progressBar1.Maximum = rowIndex-2;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
progressBar1.Value = rowIndex-2;
frmMain.sDBTest frmmainsDBTestobj = new
frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
// 1.1 frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
// 1.2 frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
//frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
// 1.3 frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
DBviewList.Add(frmmainsDBTestobj);
rowIndex++;
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
//app.Workbooks.Close();
//app.Quit();
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();


return DBviewList;

}
In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.
Click to expand...
 
A

alexia.bee

Nicholas said:
Alexia,

You are leaving references open everywhere. Most notably, the object
returned by the Cells property, as well as each Range object returned when
you access the Item property on the Cells object.

You need to keep careful count of these and release all the references
on these in order to make sure the instance dies.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.

here is a snipcode

public System.Collections.Generic.List<frmMain.sDBTest>
LoadTestSet(string TestSetFile,
System.Collections.Generic.List<frmMain.sDBTest> DBviewList)
{
int rowIndex = 2;
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (TestSetFile.ToString() == "")
return null;

Application.DoEvents();

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
return null;
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
return null;
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

progressBar1.Maximum = rowIndex-2;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
progressBar1.Value = rowIndex-2;
frmMain.sDBTest frmmainsDBTestobj = new
frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
// 1.1 frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
// 1.2 frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
//frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
// 1.3 frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
DBviewList.Add(frmmainsDBTestobj);
rowIndex++;
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
//app.Workbooks.Close();
//app.Quit();
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();


return DBviewList;

}
In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.
Click to expand...
Click to expand...

HI Nicholas and thanks for the reply,

I have no idea where i leave references open. as you see i close all
references. also, in this format the excel instance is dead. but when i
remove the commetns from lines 1.1,1.2 and 1.3 the excel reference
won't be killed.
 
E

Eran.Yasso

Nicholas said:
Alexia,

You are leaving references open everywhere. Most notably, the object
returned by the Cells property, as well as each Range object returned when
you access the Item property on the Cells object.

You need to keep careful count of these and release all the references
on these in order to make sure the instance dies.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.

here is a snipcode

public System.Collections.Generic.List<frmMain.sDBTest>
LoadTestSet(string TestSetFile,
System.Collections.Generic.List<frmMain.sDBTest> DBviewList)
{
int rowIndex = 2;
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (TestSetFile.ToString() == "")
return null;

Application.DoEvents();

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
return null;
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
return null;
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

progressBar1.Maximum = rowIndex-2;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
progressBar1.Value = rowIndex-2;
frmMain.sDBTest frmmainsDBTestobj = new
frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
// 1.1 frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
// 1.2 frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
//frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
// 1.3 frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
DBviewList.Add(frmmainsDBTestobj);
rowIndex++;
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
//app.Workbooks.Close();
//app.Quit();
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();


return DBviewList;

}
In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.
Click to expand...
Click to expand...

Nicholas I don't see where she forgot to release any object. as for the
class, it is being destroied after she calls GC.
 
W

Willy Denoyette [MVP]

Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.

here is a snipcode

public System.Collections.Generic.List<frmMain.sDBTest>
LoadTestSet(string TestSetFile,
System.Collections.Generic.List<frmMain.sDBTest> DBviewList)
{
int rowIndex = 2;
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (TestSetFile.ToString() == "")
return null;

Application.DoEvents();

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
return null;
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
return null;
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

progressBar1.Maximum = rowIndex-2;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
progressBar1.Value = rowIndex-2;
frmMain.sDBTest frmmainsDBTestobj = new
frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
// 1.1 frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
// 1.2 frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
//frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
// 1.3 frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
DBviewList.Add(frmmainsDBTestobj);
rowIndex++;
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
//app.Workbooks.Close();
//app.Quit();
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();


return DBviewList;

}
In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.
Click to expand...



I don't get it, what exactly do you mean with "in this case the excel .... if i remove lines
1.1, .....
Do you mean that it works if you comment the lines (like above) but when you uncomment the
lines it fails ?
Are you running this on the UI thread or what?
The DoEvents() call makes me shiver......
Willy.
 
A

alexia.bee

Willy said:
Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.

here is a snipcode

public System.Collections.Generic.List<frmMain.sDBTest>
LoadTestSet(string TestSetFile,
System.Collections.Generic.List<frmMain.sDBTest> DBviewList)
{
int rowIndex = 2;
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (TestSetFile.ToString() == "")
return null;

Application.DoEvents();

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
return null;
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
return null;
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

progressBar1.Maximum = rowIndex-2;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
progressBar1.Value = rowIndex-2;
frmMain.sDBTest frmmainsDBTestobj = new
frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
// 1.1 frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
// 1.2 frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
//frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
// 1.3 frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
DBviewList.Add(frmmainsDBTestobj);
rowIndex++;
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
//app.Workbooks.Close();
//app.Quit();
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();


return DBviewList;

}
In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.
Click to expand...



I don't get it, what exactly do you mean with "in this case the excel .... if i remove lines
1.1, .....
Do you mean that it works if you comment the lines (like above) but when you uncomment the
lines it fails ?
Are you running this on the UI thread or what?
The DoEvents() call makes me shiver......
Willy.
Click to expand...

Hi Willy,

you are correct. it works if I comment the lines 3 (like above) but
when you uncomment the
lines it doesn't kill the excell instance.

I am not running this on UI thread but in a public function inside a
form.
What's wrong with the DoEvents()? if i don't use the DoEvents() then
Windows doesn't refresh my form.
 
M

Michael C

Hi Willy,

you are correct. it works if I comment the lines 3 (like above) but
when you uncomment the
lines it doesn't kill the excell instance.

I am not running this on UI thread but in a public function inside a
form.
What's wrong with the DoEvents()? if i don't use the DoEvents() then
Windows doesn't refresh my form.
Click to expand...

There is nothing wrong with DoEvents if used properly. If used incorrectly
it is dangerous because any code can become re-entrant. You should make sure
you handle the cases of users interacting with your GUI while a process is
running. It's probably better to use a second thread than doevents but that
introduces a new set of complexities and doesn't solve the issues with
DoEvents anyway (actually it would make all the issues worse:)

With regards to the code you posted, can you post something a little more
compact. If you're paying me I'm happy to wade through excessive amounts of
code but in this case I'm presuming you want an answer for free. :)

Michael
 
A

alexia.bee

Michael said:
There is nothing wrong with DoEvents if used properly. If used incorrectly
it is dangerous because any code can become re-entrant. You should make sure
you handle the cases of users interacting with your GUI while a process is
running. It's probably better to use a second thread than doevents but that
introduces a new set of complexities and doesn't solve the issues with
DoEvents anyway (actually it would make all the issues worse:)

With regards to the code you posted, can you post something a little more
compact. If you're paying me I'm happy to wade through excessive amounts of
code but in this case I'm presuming you want an answer for free. :)

Michael
Click to expand...

No one sees the problem ?
 
M

Michael C

No one sees the problem ?
Click to expand...

I didn't even read the code posted as it was too long. Are you saying there
is a problem with the use of DoEvents in this particular code or in general?
 
A

alexia.bee

Michael said:
I didn't even read the code posted as it was too long. Are you saying there
is a problem with the use of DoEvents in this particular code or in general?
Click to expand...

The problem is not in the DoEvent(). the problem is that if i read 5
columns from excel, then the instance won't die. if i comment 3 lines
that read the clumns(no matter which lines), then the instance is
killed.

I see no reason for this to happen, unless there's bug in MS COM Excel.
 
M

Michael C

The problem is not in the DoEvent(). the problem is that if i read 5
columns from excel, then the instance won't die. if i comment 3 lines
that read the clumns(no matter which lines), then the instance is
killed.

I see no reason for this to happen, unless there's bug in MS COM Excel.
Click to expand...

As I said, the code is too long for my short attention span. Post a smaller
example. If you want an answer for free you need to put in a little effort
yourself. :)

Michael
 
A

alexia.bee

Michael said:
As I said, the code is too long for my short attention span. Post a smaller
example. If you want an answer for free you need to put in a little effort
yourself. :)

Michael
Click to expand...

Hi Michael. Here is a small shorter code..

Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
app = new Excel.ApplicationClass();
oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
frmMain.sDBTest frmmainsDBTestobj = new frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test = ((Excel.Range)oSheet.Cells[rowIndex,
2]).Value2.ToString();
//frmmainsDBTestobj.Teststatus = ((Excel.Range)oSheet.Cells[rowIndex,
3]).Value2.ToString();
//frmmainsDBTestobj.TestComment = ((Excel.Range)oSheet.Cells[rowIndex,
4]).Value2.ToString();
//frmmainsDBTestobj.TST = ((Excel.Range)oSheet.Cells[rowIndex,
5]).Value2.ToString();
//frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());

rowIndex++;
}

app.Workbooks.Close();
app.Application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
 
A

Adrian Gallero

Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.
Click to expand...

In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.
Click to expand...

My Excel Interop knowledge is a little rusty at this moment, but since
you are not getting a solution I will give this a try.

From what I can see on your code, you are not releasing the Range
objects you are creaing.

When you write a line:
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 != null)

you are creating a lot of temporary Excel.Range objects, and never
releasing them.

Anywhere you use a range, you need to ReleaseComObject it.
For example the line:
frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex,3]).Value2.ToString();

should be more like:
Excel.Range Range = (Excel.Range)oSheet;
frmmainsDBTestobj.Teststatus =
Range.Cells[rowIndex,3]).Value2.ToString();
ReleasecomObject(Range);

If you get the code right and ReleaseComObject *every* object you
create, you should not need to call GC.Collect at all. (and neither
GC.WaitForPendingFinalizers()). In fact this GC.Collect call is what
makes your kind of random behavior, since it is not deterministic and
might release or not the pending objects.

I don't remember right now if you also need to call ReleasecomObjects
on the Cell object, but you might try this too. (I do not have a
development enviroment here to test it). Also, if you are reading more
than 3 values, it is a good idea to read entire ranges into arrays and
not cell by cell, since that can be very slow.

As I said before, there have been a lot of years since I last dealed
with this, and I am very happy I do not have to do it anymore :) But I
think this might help.

About myself, because exactly this kind of issues with ole (and many
more that you will find along the way), is that we created a component
to read and write Excel files without Excel. If you want to take a
look, ours is at http://www.tmssoftware.com/go/?flexcelnet, but there
are many more if you google. And they exist for a reason ;) I would
reccomend that you take a look, they might save you a lot of headaches.

Hope this helped.
Kind regards,
Adrian.



--
 
W

Willy Denoyette [MVP]

Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.

here is a snipcode

public System.Collections.Generic.List<frmMain.sDBTest>
LoadTestSet(string TestSetFile,
System.Collections.Generic.List<frmMain.sDBTest> DBviewList)
{
int rowIndex = 2;
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (TestSetFile.ToString() == "")
return null;

Application.DoEvents();

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
return null;
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
return null;
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

progressBar1.Maximum = rowIndex-2;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
progressBar1.Value = rowIndex-2;
frmMain.sDBTest frmmainsDBTestobj = new
frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
// 1.1 frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
// 1.2 frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
//frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
// 1.3 frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
DBviewList.Add(frmmainsDBTestobj);
rowIndex++;
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
//app.Workbooks.Close();
//app.Quit();
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();


return DBviewList;

}
In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.
Click to expand...



I don't get it, what exactly do you mean with "in this case the excel .... if i remove
lines
1.1, .....
Do you mean that it works if you comment the lines (like above) but when you uncomment
the
lines it fails ?
Are you running this on the UI thread or what?
The DoEvents() call makes me shiver......
Willy.
Click to expand...

Hi Willy,

you are correct. it works if I comment the lines 3 (like above) but
when you uncomment the
lines it doesn't kill the excell instance.
OK.

I am not running this on UI thread but in a public function inside a
form.
Click to expand...

Not on the UI thread?, ARE YOU SURE about this?? Are you creating another thread from the UI
thread to run this code in?
If that's true, you are (trying) to update the UI from another thread which is "sin no. 1"
in Windows., you should never update the UI from another thread than the UI thread itself!
Tale a look at Control.Invoke or Control.BeginInvoke if you need to update the UI from
another thread.
What's wrong with the DoEvents()? if i don't use the DoEvents() then
Windows doesn't refresh my form.
Click to expand...
A lot, and it should be avoided if ever possible. But at this moment it's even not relevant,
answer above question first .
Note also, as others said, you should post a complete sample that illustrates the issue,
from the snip you posted it's impossible to know what's wrong with this code


Willy.
 
A

alexia.bee

Willy said:
Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.

here is a snipcode

public System.Collections.Generic.List<frmMain.sDBTest>
LoadTestSet(string TestSetFile,
System.Collections.Generic.List<frmMain.sDBTest> DBviewList)
{
int rowIndex = 2;
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (TestSetFile.ToString() == "")
return null;

Application.DoEvents();

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
return null;
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
return null;
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

progressBar1.Maximum = rowIndex-2;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
progressBar1.Value = rowIndex-2;
frmMain.sDBTest frmmainsDBTestobj = new
frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
// 1.1 frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
// 1.2 frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
//frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
// 1.3 frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
DBviewList.Add(frmmainsDBTestobj);
rowIndex++;
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
//app.Workbooks.Close();
//app.Quit();
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();


return DBviewList;

}
In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.




I don't get it, what exactly do you mean with "in this case the excel .... if i remove
lines
1.1, .....
Do you mean that it works if you comment the lines (like above) but when you uncomment
the
lines it fails ?
Are you running this on the UI thread or what?
The DoEvents() call makes me shiver......
Willy.
Click to expand...

Hi Willy,

you are correct. it works if I comment the lines 3 (like above) but
when you uncomment the
lines it doesn't kill the excell instance.
OK.

I am not running this on UI thread but in a public function inside a
form.
Click to expand...

Not on the UI thread?, ARE YOU SURE about this?? Are you creating another thread from the UI
thread to run this code in?
If that's true, you are (trying) to update the UI from another thread which is "sin no. 1"
in Windows., you should never update the UI from another thread than the UI thread itself!
Tale a look at Control.Invoke or Control.BeginInvoke if you need to update the UI from
another thread.
What's wrong with the DoEvents()? if i don't use the DoEvents() then
Windows doesn't refresh my form.
Click to expand...
A lot, and it should be avoided if ever possible. But at this moment it's even not relevant,
answer above question first .
Note also, as others said, you should post a complete sample that illustrates the issue,
from the snip you posted it's impossible to know what's wrong with this code


Willy.
Click to expand...

Hi Williy,

Here is the complete sample:

public class frmmainsDBTest
{
public int TestNumber;
public string Test;
public string Teststatus;
public string TestComment;
public string TST;
public bool iscomment;
};

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;


namespace WindowsApplication1
{
public partial class Form1 : Form
{

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
int rowIndex = 2;
string TestSetFile = @"D:\per\Eran\er.xls";
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;


while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
frmmainsDBTest frmmainsDBTestobj = new
frmmainsDBTest();
frmmainsDBTestobj.TestNumber =
int.Parse(((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
rowIndex++;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
this.Visible = false;
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

//System.Runtime.InteropServices.Marshal.ReleaseComObject((Excel.Range)oSheet.Cells);
//app.Application.Quit();

//System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
//app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
this.Visible = false;
}


}
}

Hope you are OK with it.
 
W

Willy Denoyette [MVP]

Willy Denoyette [MVP] wrote:
Hi all,

In some weird reason, excel instance won;t die if i remove the comment
from 4 lines of setting values into struct.

here is a snipcode

public System.Collections.Generic.List<frmMain.sDBTest>
LoadTestSet(string TestSetFile,
System.Collections.Generic.List<frmMain.sDBTest> DBviewList)
{
int rowIndex = 2;
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (TestSetFile.ToString() == "")
return null;

Application.DoEvents();

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
return null;
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
return null;
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;

while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

progressBar1.Maximum = rowIndex-2;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
progressBar1.Value = rowIndex-2;
frmMain.sDBTest frmmainsDBTestobj = new
frmMain.sDBTest();
frmmainsDBTestobj.TestNumber =
Int32.Parse(((Excel.Range)oSheet.Cells[rowIndex,
1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
// 1.1 frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
// 1.2 frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
//frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
// 1.3 frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
DBviewList.Add(frmmainsDBTestobj);
rowIndex++;
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message.ToString());
//app.Workbooks.Close();
//app.Quit();
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();


return DBviewList;

}
In this case the excel instance is dying. if i remove lines 1.1,1.2
and1.3, then excel instance won't die.

sDBTest is a public class where all members are string besides
iscomment(bool).

Thanks for the help.




I don't get it, what exactly do you mean with "in this case the excel .... if i remove
lines
1.1, .....
Do you mean that it works if you comment the lines (like above) but when you uncomment
the
lines it fails ?
Are you running this on the UI thread or what?
The DoEvents() call makes me shiver......
Willy.

Hi Willy,

you are correct. it works if I comment the lines 3 (like above) but
when you uncomment the
lines it doesn't kill the excell instance.
OK.

I am not running this on UI thread but in a public function inside a
form.
Click to expand...

Not on the UI thread?, ARE YOU SURE about this?? Are you creating another thread from the
UI
thread to run this code in?
If that's true, you are (trying) to update the UI from another thread which is "sin no.
1"
in Windows., you should never update the UI from another thread than the UI thread
itself!
Tale a look at Control.Invoke or Control.BeginInvoke if you need to update the UI from
another thread.
What's wrong with the DoEvents()? if i don't use the DoEvents() then
Windows doesn't refresh my form.
Click to expand...
A lot, and it should be avoided if ever possible. But at this moment it's even not
relevant,
answer above question first .
Note also, as others said, you should post a complete sample that illustrates the issue,
from the snip you posted it's impossible to know what's wrong with this code


Willy.
Click to expand...

Hi Williy,

Here is the complete sample:

public class frmmainsDBTest
{
public int TestNumber;
public string Test;
public string Teststatus;
public string TestComment;
public string TST;
public bool iscomment;
};

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;


namespace WindowsApplication1
{
public partial class Form1 : Form
{

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
int rowIndex = 2;
string TestSetFile = @"D:\per\Eran\er.xls";
Excel.ApplicationClass app;
Excel._Workbook oWB;
Excel._Worksheet oSheet;

if (!File.Exists(TestSetFile))
{
MessageBox.Show("File " + TestSetFile + " Doesn't
exist...", "File not found!");
}

app = new Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel could not be started.",
"Error");
}

oWB = app.Workbooks.Open(TestSetFile, 0, true, 5, "", "",
true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true, 1, 0);

oSheet = (Excel.Worksheet)oWB.ActiveSheet;


while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2 !=
null)
rowIndex++;

rowIndex = 2;

try
{
while (((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2
!= null)
{
frmmainsDBTest frmmainsDBTestobj = new
frmmainsDBTest();
frmmainsDBTestobj.TestNumber =
int.Parse(((Excel.Range)oSheet.Cells[rowIndex, 1]).Value2.ToString());
frmmainsDBTestobj.Test =
((Excel.Range)oSheet.Cells[rowIndex, 2]).Value2.ToString();
frmmainsDBTestobj.Teststatus =
((Excel.Range)oSheet.Cells[rowIndex, 3]).Value2.ToString();
frmmainsDBTestobj.TestComment =
((Excel.Range)oSheet.Cells[rowIndex, 4]).Value2.ToString();
frmmainsDBTestobj.TST =
((Excel.Range)oSheet.Cells[rowIndex, 5]).Value2.ToString();
frmmainsDBTestobj.iscomment =
bool.Parse(((Excel.Range)oSheet.Cells[rowIndex, 6]).Value2.ToString());
rowIndex++;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
this.Visible = false;
}

app.Workbooks.Close();
app.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

//System.Runtime.InteropServices.Marshal.ReleaseComObject((Excel.Range)oSheet.Cells);
//app.Application.Quit();

//System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
//app.Workbooks.Close();
app = null;
oWB = null;
oSheet = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
this.Visible = false;
}


}
}

Hope you are OK with it.
Click to expand...


No, I'm not, this is not a complete sample, Form1 is a partial class!

Willy.
 
M

Michael C

Hi Michael. Here is a small shorter code..
Click to expand...

That's better but it is of no use because I can't copy/paste it into a new
project and run it. What is frmMain, TestSetFile etc etc? I'm quite happy to
sift through this code and get it working but you will have to be paying me.
If you want an answer for free you have to post something that works staight
off.

Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cannot get Excel process to end 7

Top