Automatic testing of DNA functions

[Ron]

Hi,

I created some useful functions with DNA and now I want to make a
strong test procedure. I love huge automatic tests so I built a N-Unit
project with a procedure that opens a worksheet, make it call some DNA
functions and check the results.

Here is my problem : if my test opens a workbook and my xll file, then
the Excel process won't disappear from tasks list after the end of the
tests.

It is not really a DNA matter, but I am pretty sure that some DNA
users tried to do the same in the past !

Here are some examples :

1) With the following test, the Excel process will disappear at the
end.

[TestFixture]
public class UnitTest
{
[Test]
public void Run()
{
Application excelApp = new Application();

try
{
string filePath = Directory.GetCurrentDirectory() +
@"\MyFile.xls";
Workbook book = excelApp.Workbooks.Open(filePath, 0,
true, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0,
true, false, false);
}
finally
{
foreach (Workbook book in excelApp.Workbooks)
{
book.Close(false, Missing.Value, Missing.Value);
}

excelApp.Quit();
}
}
}

2) With this one, the Excel process also will disappear at the end.

[TestFixture]
public class UnitTest
{
[Test]
public void Run()
{
Application excelApp = new Application();

try
{
string xllPath = Directory.GetCurrentDirectory() +
@"\MyAddin.xll";
excelApp.RegisterXLL(xllPath);
}
finally
{
foreach (Workbook book in excelApp.Workbooks)
{
book.Close(false, Missing.Value, Missing.Value);
}

excelApp.Quit();
}
}
}

3) Now I try to open my xll file and my workbook. The Excel process
will never disappear.

[TestFixture]
public class UnitTest
{
[Test]
public void Run()
{
Application excelApp = new Application();

try
{
string xllPath = Directory.GetCurrentDirectory() +
@"\MyAddin.xll";
excelApp.RegisterXLL(xllPath);

string filePath = Directory.GetCurrentDirectory() +
@"\MyFile.xls";
Workbook book = excelApp.Workbooks.Open(filePath, 0,
true, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0,
true, false, false);
}
finally
{
foreach (Workbook book in excelApp.Workbooks)
{
book.Close(false, Missing.Value, Missing.Value);
}

excelApp.Quit();
}
}
}


So now, I use an awful workaround : manually kill the process ! But I
want to find something better.

[TestFixture]
public class UnitTest
{
[DllImport("user32.dll")]
private static extern IntPtr GetWindowThreadProcessId(IntPtr
hWnd, out IntPtr ProcessId);


[Test]
public void Run()
{
Application excelApp = new Application();

try
{
string xllPath = Directory.GetCurrentDirectory() +
@"\MyAddin.xll";
excelApp.RegisterXLL(xllPath);

string filePath = Directory.GetCurrentDirectory() +
@"\MyFile.xls";
Workbook book = excelApp.Workbooks.Open(filePath, 0,
true, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0,
true, false, false);
}
finally
{
foreach (Workbook book in excelApp.Workbooks)
{
book.Close(false, Missing.Value, Missing.Value);
}

excelApp.Quit();

IntPtr id;
GetWindowThreadProcessId(new IntPtr(excelApp.Hwnd),
out id);
Process proc = Process.GetProcessById(id.ToInt32());
proc.Kill();
}
}
}

Has anyone some good idea ?

Thanks
Ron

[Govert van Drimmelen]

Hi Ron,

I'm very interested in how you set up the unit testing. If Excel-DNA
had a set of unit tests like these to check the basic functionality it
would help me a lot.

I suspect the problem you see is due to a reference to an Excel object
created in your add-in. Are you perhaps assigning
ExcelDnaUtil.Application to some static variable somewhere in your
code?
For a while Excel-DNA tried to get and store such a reference on
startup, so that subsequent access to the Application object would be
fast. But a consequence was exactly the problem you describe.

Do you see this even with a simple add-in, say the ExcelDna.xll and
ExcelDna.dna files in the Excel-DNA distribution?
If so, I can try to have a look.

Regards,
Govert

[Ron]

Hi Govert,

Thanks for your answer.

I tried to create a simple add-in with only a "Hello world" function
and I added a unit test with the same kind of code as earlier.
Unfortunately for me, Excel disappears normally, so it shows that
something is instantiated in my code and not destroyed.
I have some ideas and I will check them before giving any explanation,
if you are interested in.

For my unit tests, it is not complicated : as you can see in my
previous examples, I use VSTO in order to open a workbook (I could
also create a new one) and then I load my xll file helps to the
"RegisterXLL" function in Excel.Application object.
Then I can write some of my custom formulas in any cell and check the
written results.
In my case, some functions create new worksheets. That's why I want to
automatically verify what is displayed in the new sheet.

Obviously, functions that just return a parameter without creating any
excel object can be called inside the unit tests without using Excel.
But I think that it is also important to check that the call of those
functions through Excel works as well.

The only matter in that way is that Excel runs in another process than
my unit tests so I cannot handle the objects instantiated by my add-in
while the functions have been called through Excel. All I can do is
see the visible results in Excel, as a user would do.

If you have some questions about my tests, I would be happy to give
you more answers.

Ron

[Ron]

Finally, I may have found the problem : my add-in has a contextual
menu with events (ex: Worksheet.BeforeRightClick). Some events are
register in the "AutoOpen" function of my IExcelAddIn class. So I
unregister those events in "AutoClose".
Unfortunately, "AutoClose" is not always called during Excel closing.
So I am pretty sure that those events prevent my add-in IExcelAddIn
class to be garbage-collected.
I will have to find another way :)

Ron

[Govert van Drimmelen]

Hi Ron,

You can do context menus with the CommandBars stuff in Excel-Dna.

Maybe your .dna will look something like:

<DnaLibrary>
<ExternalLibrary Path='...' />
<CustomUI>
<commandBars xmlns='http://schemas.excel-dna.net/office/2003/01/
commandbars' >
<commandBar name='Cell'>
<button caption='Ron test item' enabled='true'
onAction="ShowHelloMessage"/>
</commandBar>
</commandBars>
</CustomUI>
</DnaLibrary>

Where ShowHelloMessage is something like:

public static void ShowHelloMessage()
{
....
}

-Govert

[Ron]

Hi Govert,

I want my contextual menu to change according to the cell location or
content, that's why I chose to make my own class.
Does DNA manage such behavior for contextual menus ?

I saw during my investigations that my problem comes from the
subscription of my menu to ExcelDnaUtil.Application's
"WorkbookActivate" event. I think that, even if my menu removes the
subscriptions, Excel still waits for its destruction to end the
process, as if the unsubscription doesn't work properly. On the other
hand, it is the add-in which creates it, so it is destroyed only when
Excel closes.

Have you met such issue in the past ? May be I am missing something
with event subscription.

Ron