Author Archives: purpleblob

Using ManagedXLL to supply functions to Excel

In the last post I went through the steps for creating a class library for use with ManagedXLL to create an Excel add-in. We very briefly encountered the ManagedXLL way of marking methods for use in Excel.

Now I’m going to delve a little deeper into what we can do with ManagedXLL. This will not be comprehensive, but will highlight some of the key features of ManagedXLL.

ExcelAddInEvent

We can mark a method with this attribute to carry out a task when the add-in is loaded (AddInEvent.Open), allowing us to carry out specific functionality, such as maybe getting an SSO token for the user or the likes. We can also use mark a method to be called when an add-in is being closed, i.e. when a workbook is closed, for example to allow us to save data to the cloud using AddInEvent.Close. We can also use AddInEvent.Add and AddInEvent.Remove to carry out a task when the add-in is added to the add-in manager or removed from it.

[ExcelAddInEvent(AddInEvent.Open)]
public static void OnLoaded()
{
}

A couple of subclasses exist to handle Open and Close, named ExcelAutoOpen and ExcelAutoClose.

Excel Object

Before moving onto more attributes, let’s quickly look at the ManagedXLL Excel object. It includes methods to interact with Excel from our code, such as displaying a message box in Excel or getting at the sheets collection and so on, for example

Excel.MessageBox("You are not authorised to connect to the selected service", Excel.MessageBoxType.ErrorOK, "MyExcelAddin");

WorksheetFunction

The WorksheetFunction attribute is used to tell Excel, not only the name of the function (as seen by Excel users), but also how Excel should use the method in the sense of how it should display exceptions and whether to detect the user aborting of the method.

[WorksheetFunction("CalculateFee", DetectUserAbort = true, Exceptions = Echo.Verbatim)]
public static double CalculateFee()
{
}

ExcelDescription

We can mark methods, arguments and return values with the ExcelDescription attribute, allowing us to document what the various arguments and/or return values of a method are as well as what the method itself does.

[ExcelDescription("Gets a string based upon the supplied index")]
[return: ExcelDescription("Returns the selected string")]
public static string DoSomething([ExcelDescription("The Index")] int index)
{
}

ExcelHidden

We can mark methods and hidden to hide them from the function wizard using the ExcelHidden attribute, this doesn’t stop us using the method, it just doesn’t appear in the function wizard

[WorksheetFunction("MyName", DetectUserAbort = true, Exceptions = Echo.Verbatim)]
[ExcelDescription("Returns a string with the name of the add-in.")]
[return: ExcelDescription("A string representing the name of the add-in")]
[ExcelHidden]
public static string Name()
{
}

ExcelCategory

The ExcelCategory is used on a class and allows us to create a category for groups of functions – this aids the function wizard in grouping the methods. You can create multiple classes and give them the same category or group together specific functionality into their own categories.

[ExcelCategory("My Date & Time Functions")]
public static class DateTimeFunctions
{
}

ExcelAbbreviation

We can mark individual arguments of a method using this attribute. In essence we’re defining an alternate name for the parameter as viewed by Excel’s function wizard.

public static string Name([ExcelAbbreviation("Name")] String enterYourName)

ExcelCommand

The ExcelCommand attribute allows us to define methods that are registered as Excel macro functions which can be run from the command’s listed in the Managed.XLL.config

[ExcelCommand(".Run")]
public static void Run()
{
}

<command title="My Run Command" macroName=".Run"
    description="Runs a command" edition="Professional"/>

This will add a command to the Excel menu and when clicked will run our macro/

Creating an XLL add-in using ManagedXLL

Excel allows us to extend it’s programming/functional model by adding in new components known as Excel addin’s (or XLL files). There are several ways of creating these in .NET. For this post I’m going to concentrate on a commercial product known as ManagedXLL.

Let’s just straight into creating a skeleton project for an XLL add-in.

Let’s start by creating a new class library, I’m going to call mine (the nondescript name) MyExcelPlugin.
Now add a reference to ManagedXLL.dll
Next we need to copy and rename the Managed.XLL.config which comes with ManagedXLL. Copy it to your project’s folder (you can ofcourse place it where you like) and name it projectname.XLL.config (obviously replacing projectname with your project’s name) then add it to the project and set it’s “Copy to Output Directory” property in Visual Studio to “Copy if newer”
Next we need to copy the Managed.XLL file to our project. Copy it and rename it projectname.XLL, where project name is your project’s name. Add it to the project and set the “Copy to Output Directory” property in Visual Studio to “Copy if newer”.
Note: Both the projectname.XLL and projectname.XLL.config files must be available in the same locations.
If you’ve not done so already, change the name of the Class1 file which was created with the project to MyExcelFunctions (or whatever you prefer)

Okay so to recap, we’ve created a class library, reference ManagedXLL.dll and copied (and renamed) Managed.XLL and Managed.XLL.config. Great but what have we really done ?

So the ManagedXLL.dll supplies us with attributes which will will use in a later section to mark out code for use in Excel. It also supplies functions for interacting with Excel. The Managed.XLL file is the add-in we use in Excel. We rename it so that obviously there’s (hopefully) no conflicts with other add-ins and the same goes with renaming the Managed.XLL.config. The Managed.XLL file is the runtime that Excel communicates with and it in turn will communicate with our DLL. So it acts as the proxy to our DLL. As is probably obvious at this point, the Managed.XLL.config will supply information to Managed.XLL about our DLL, such as whether we want menu’s displayed, the name of the add-in and so on.

Let’s make some changes to the Managed.XLL.config (now obviously renamed for our project).

Note: We can also use the XLL.config for any configuration required by our add-in, for example if there’s WCF config required or just plain old appSettings.

The first thing we’ll want to do is change the display name to reflect our add-in. So open the XLL.config file and scroll down to to the managedxll/settings. Here you will see the displayName so change the value attribute to the project name, for example MyExcelPlugin or whatever name you want to have Excel display
We now need to tell ManagedXLL the name of the assembly that it’s acting as a proxy to, so find the assemblies section in the config file and change the location to the name of your assembly. You’ll notice in the config comments for this section that there’s various ways of declaring your assembly’s location.
The last thing we’re going to add, for this section at least, is the license information. Locate the licenses section in the config file and supply the relevant information in the data and hash attributes

At this point we’re created the bare minimum of a DLL that we can load into Excel. Ofcourse it does nothing, but if you want to load up Excel and select Tools | Add-ins, then browse to the bin\debug or bin\release (whichever you’re building) of your project and load the .XLL you named after your project. Then press OK in the Add-ins dialog in Excel to accept it, this will add a menu entry named ManagedXLL (we can change this easily enough) and if all goes well no errors are displayed, i.e. the ManagedXLL debug console does not get displayed with any error messages.

Note: if you now make changes to the project and compile you will get “Unable to copy xxx because it is being used by another process” errors. This is because, you’ve probably got Excel running with the Add-in loaded. Either close Excel or go back to the Add-ins dialog in Excel and remove the Add-in by unchecking it.

Time to add something useful, some code

Open the class file, mine’s called MyExcelFunctions.cs and change the class to a static and add the ExcelCategory attribute to the class, as follows
```
[ExcelCategory("My Excel Functions")]
public static class MyExcelFunctions
{
}
```
This will add a category title for display in the worksheet functions dialog etc.
Now let’s add an actual Excel callable function. This will not be anything special, but will simply return a string. However it also demonstrates how easy it is to now create functions for Excel.
Add the following code to our class
```
[WorksheetFunction("MyName", DetectUserAbort = true, Exceptions = Echo.Verbatim)]
[ExcelDescription("Returns a string with the name of the add-in.")]
[return: ExcelDescription("A string representing the name of the add-in")]
public static string Name()
{
   return "My Excel Functions";
}
```
If you now build this and add the add-in to Excel (it may prompt you to say do you want to overwrite the existing plugin (or words to that effect), say yes to this.

Once the add-in is loaded, select a cell then go to Insert | Function option in Excel and if you select the “Or select a category” combo box you can scroll down and find the text we supplied for the ExcelCategory, for example “My Excel Functions”. From here we can select our function. As there are no arguments for this function you’ll see very little of interest but if you click on “Help on this function” you’ll see the return description we set up in our class.

Anyway, click OK on the function dialog and it’ll enter

=MyName()

into Excel and display the string our class returns. Ofcourse you do not need to go through the Functions dialog everytime, this was just to show the category and return description are available.

Note: Notices that I wrote =MyName() and not =Name() as per our method name. As you might have spotted in the WorksheetFunction we declare the name of the function that Excel see’s/exposes. Thus allowing us to alter the name of the method for Excel.

So at this point we should have a basic, but working, Excel add-in.

Minimal steps to create a TIBCO RV message and send it

The previous post was on creating a minimal TIBCO RV listener. So naturally it’s time to look at creating a minimal TIBCO RV “sender”. In other words, creating a message and sending it.

Note: The initial code was take from the C# TIB RV samples, but I wanted to distil these to a quick and simple step by step guide.

For this sample we’ll create a Console application in C# and we’ll assume you are getting the server, network etc. values from some key/value mechanism, whether this is a command line parser or config is down to your specific implementation.

So create a Windows Console project then follow the steps below (the full code will be listed at the end of this rambling)

In case you’ve come to this post without first reading the post on creating a minimal listener, I will repeat a fair amount of code/steps from that post here.

Add references to TIBCO.Rendezvous and ensure that TIBCO.Rendezvous.netmodule resides in the same folder as TIBCO.Rendezvous (just in case you’re not referencing the TIB RV installation itself

For this simple example we’ll hard code a few variables

string server = arguments["server"];
string network = arguments["network"];
string daemon = arguments["daemon"];

Next we need to open the Rendezvous environment using
```
TIBCO.Rendezvous.Environment.Open();
```
The above code should be enclosed in a try..catch block, either enclosing all of the following code or if you want the granularity, then each line in essence. For now we’ll just assume it’s all in a try..catch block

We now need to create the transport

Transport transport = new NetTransport(service, network, daemon);

Now it’s time to create the message. The subject is set to “myapp.mysubject” and we add some data to a new field we’ve created named “Data”
```
Message message = new Message { SendSubject = "myapp.mysubject" };
message.AddField("Data", "Some Data");
```
To send the message is simply a case of
```
transport.Send(message);
```
Finally when we’ve finished with RV we need to close the environment with
```
TIBCO.Rendezvous.Environment.Close();
```

Before we look at the full code, here’s what the listener shows us if we listen to “myapp.>”

2013-11-21 15:58:49 (2013-11-21 15:58:49.281000000Z): subject=myapp.mysubject, message={Data=”Some Data”}

And as promised, the full code is

public void Process(CommandLineArguments arguments)
{
   string service = arguments["server"];
   string network = arguments["network"];
   string daemon = arguments["daemon"];

   try
   {
      TIBCO.Rendezvous.Environment.Open();

      Transport transport = new NetTransport(service, network, daemon);

      Message message = new Message { SendSubject = "myapp.mysubject" };
      message.AddField("Data", "Some Data");
      transport.Send(message);
   }
   catch (Exception e)
   {
      Console.WriteLine(e.Message + "\n\n" + e.StackTrace);
   }
   finally
   {
      TIBCO.Rendezvous.Environment.Close();
   }
}

Minimal steps to create a TIBCO RV listener

Following is a list of the steps (and code) for creating a minimal TIB RV listener which will simply output messages to the console (therefore implementing the same sort of functionality as the tibrvlisten application.

Note: The initial code was take from the C# TIB RV samples, but I wanted to distil these to a quick and simple step by step guide.

So create a Windows Console project then follow the steps below (the full code will be listed at the end of this rambling)

Add references to TIBCO.Rendezvous and ensure that TIBCO.Rendezvous.netmodule resides in the same folder as TIBCO.Rendezvous (just in case you’re not referencing the TIB RV installation itself

For this simple example we’ll hard code a few variables

string server = arguments["server"];
string network = arguments["network"];
string daemon = arguments["daemon"];
string topics[] = arguments["topics"].Split(',');

Next we need to open the Rendezvous environment using
```
TIBCO.Rendezvous.Environment.Open();
```
The above code should be enclosed in a try..catch block, either enclosing all of the following code or if you want the granularity, then each line in essence. For now we’ll just assume it’s all in a try..catch block

We now need to create the transport

Transport transport = new NetTransport(service, network, daemon);

Now create the listeners and attach a MessageReceived handler to each listener.

TIBCO.Rendezvous.Listener[] listeners = new TIBCO.Rendezvous.Listener[topics.Length];
for (int i = 0; i < listeners.Length; i++)
{
   listeners[i] = new TIBCO.Rendezvous.Listener(Queue.Default, 
                     transport, topics[i], null);
   listeners[i].MessageReceived += OnMessageReceived;
}

The code for the OnMessageReceived handler looks like

static void OnMessageReceived(object listener, 
              MessageReceivedEventArgs messageReceivedEventArgs)
{
   Message message = messageReceivedEventArgs.Message;

   Console.Out.WriteLine("{0}: subject={1}, reply={2}, message={3}",
	DateTime.Now, message.SendSubject, message.ReplySubject, message);

   Console.Out.Flush();
}

Now for this example we’re going to go into a look and just keep calling the Queue.Default.Dispatch method to keep messages dispatching or block whilst no messages exist

while (true)
{
   try
   {
      Queue.Default.Dispatch();
   }
   catch (RendezvousException exception)
   {
      Console.Error.WriteLine("Exception dispatching default queue:");
      Console.Error.WriteLine(exception.StackTrace);
      break;
   }
}

Finally when we’ve finished with RV we need to close the environment with
```
TIBCO.Rendezvous.Environment.Close();
```

Full code

public void Run(CommandLineArguments arguments)
{
   string service = arguments["server"];
   string network = arguments["network"];
   string daemon = arguments["daemon"];

   string[] topics = arguments["topics"].Split(',');
			
   try
   {
      TIBCO.Rendezvous.Environment.Open();

      Transport transport = new NetTransport(service, network, daemon);

      TIBCO.Rendezvous.Listener[] listeners = new TIBCO.Rendezvous.Listener[topics.Length];
      for (int i = 0; i < listeners.Length; i++)
      {
         listeners[i] = new TIBCO.Rendezvous.Listener(Queue.Default, 
                               transport, topics[i], null);
         listeners[i].MessageReceived += OnMessageReceived;
      }

      while (true)
      {
         try
         {
            Queue.Default.Dispatch();
         }
         catch (RendezvousException exception)
         {
             Console.Error.WriteLine("Exception dispatching default queue:");
             Console.Error.WriteLine(exception.StackTrace);
             break;
         }
      }
   }
   catch (Exception e)
   {
      Console.WriteLine(e.Message + "\n\n" + e.StackTrace);
   }
   finally
   {
      TIBCO.Rendezvous.Environment.Close();
   }
}

static void OnMessageReceived(object listener, 
         MessageReceivedEventArgs messageReceivedEventArgs)
{
   Message message = messageReceivedEventArgs.Message;

   Console.Out.WriteLine("{0}: subject={1}, reply={2}, message={3}",
		DateTime.Now, message.SendSubject, message.ReplySubject, message);

   Console.Out.Flush();
}

How to multiple a cell range (in Excel) by a value

I was working on an application which reads data from supplied spreadsheets. In one column it expected a %. It expected it to be written as 30 (for example for 30%). However some spreadsheets arrived with the % written as 0.30.

So to change these value within the spreadsheet (for any selected range) I wanted to simply multiple the selected cells by 100.

To achieve this is very simple. Just follow these steps

In an empty cell type 100
Now select the altered cell and copy it
Next, select the range of cells you wish to multiply by the copied value
Right mouse click on the cells and select Paste Special
Select the Operation Multiple and press OK
Finally delete the cell we added in step 1

Separating out configuration files in .NET

Occasionally we might wish to de-clutter our App.config by moving parts of the configuration into separate files. Ofcourse this process is also useful where we might wish to simply reuse existing config parts.

This capability is built into .NET, so for example we can have

<appSettings configSource="MyAppSettings.config" />

We can also use the appSettings attribute file, for example

<appSettings file="MyAppSettings.config" />

The MyAppSettings.config file might look something like

<?xml version="1.0" encoding="utf-8" ?>
<appSettings>
  <add key="configuration" value="..\..\..\..\Samples\SpaceMonitor.xml"/>
  <add key="artifacts" value="..\..\..\..\..\Samples\artifacts"/>
</appSettings>

Note: the appSettings section starts the file (after the xml declaration), i.e. we do not have a configuration section within this file.

So this is cool, appSettings shows the configSource attribute in intellisense in Visual Studio, but we can in fact use the configSource on any section. Maybe we’ve created a section called CustomSection, then it implicitly has a configSource attribute built in.

It’s important though to note that sectionGroups do not have an implicit configSource. Say we have something like

<configSections>
  <sectionGroup name="orchestrator">
    <section name="aliases" type="Orchestrator.Configuration.AliasesSectionHandler,   
                                  Orchestrator.Configuration"/>
    <section name="plugins" type="Orchestrator.Configuration.PluginsSectionHandler, 
                                  Orchestrator.Configuration"/>
  </sectionGroup>
</configSections>

Now you might think you can write <orchestrator configSource=”Orchestrator.config”/> but I’m afraid not.

Only sections can use the configSource

Instead what we’d need to do is something like this

<orchestrator>
  <aliases configSource="Alias.config" />
  <plugins configSource="Plugins.config"/>
</orchestrator>

and the actual files would look something like

<?xml version="1.0" encoding="utf-8" ?>
<aliases>
  <alias key="cron" value="StandardTriggers.CronTrigger, StandardTriggers"/>
</aliases>

and

<?xml version="1.0" encoding="utf-8" ?>
<plugins>
  <plugin type="StandardPlugins.WCFMonitorPlugin, StandardPlugins"/>
</plugins>

So with the above information you can see that if you wanted to separate out a system.serviceModel, for example, you would only be able to separate the behaviors, services etc. as the system.serviceModel is a sectionGroup.

.NET Installer cannot access your App.config

I’ve been caught by this once before and so it’s time to ensure it’s documented.

I’ve created an Installer for a Windows Service in .NET, it’s a fairly generic service (or more succinctly it’s an application that allows me to create services with minimal code) so I want it to be easy to give it a service name that properly represents it’s functionality (not the generic container’s name). So I added an appSettings section to the App.config and tried to install the service using InstallUtil and ofcourse it failed to get the info. from the App.Config.

Whilst the code for the Installer is within the EXE assembly, it’s run via InstallUtil so cannot pick up the correct App.config automatically. It’s simple to implement this functionality, see the code below

string configFile = Assembly.GetExecutingAssembly().Location + ".config";

ExeConfigurationFileMap map = new ExeConfigurationFileMap();
map.ExeConfigFilename = configFile;

Configuration config = ConfigurationManager.OpenMappedExeConfiguration(map,   
                              ConfigurationUserLevel.None);
	
string serviceName = config.AppSettings.Settings["ServiceName"].Value;
serviceInstaller.ServiceName = serviceName ?? DEFAULT_SERVICE_NAME;

In the sample above I have a fallback DEFAULT_SERVICE_NAME in case we forget to set the config as follows

<appSettings>
  <add key="ServiceName" value="My Application"/>
</appSettings>

NInject ChildKernel

I’ve been working on an application which is basically a glorified scheduler. Within it I have the concept of projects which are made up triggers, tasks etc. So we have a parent child relationship going on between the project and the child elements, the triggers etc.

One problem I came across was that the application holds a StandardKernel (as one might expect) and injects dependencies etc. into the projects, triggers and tasks but I wanted to use the same mechanism to allow me to inject the current instance of a project into the children, i.e. the triggers and tasks could (if they wanted) get access to the parent project instance.

Obviously if I add the project to the application wide kernel, all triggers and tasks would get a project object, but not necessarily the project which acts as their parent (actually it’d probably fail to allow me to resolve to multiple instances of a project anyway). Of course I could pass the instance of the project to each trigger or task at construction or explicitly calling the relevant method/property, but I wanted to reuse the NInject mechanisms so as to keep a standard way of injecting objects into this application.

Enter the ChildKernel. A Ninject extension class available via GitHub.

Using the ChildKernel we can create a new kernel, which (if you like) inherits the bindings from a parent kernel and then we add further bindings specific to it’s use, as per

ChildKernel child = new ChildKernel(kernel);
child.Bind<IProject>().ToMethod(_ => currentProject);

kernel would be our StandardKernel acting as the parent kernel.

Now we simply Get or Inject using this child kernel and if a dependency is not resolved using the child, it is passed to the parent kernel to resolve.

MemoryCache, caching in .NET 4

Having used the Enterprise Application Blocks in the past I’ve been well aware of the capabilities of the caching block, but recently was looking for some caching for a small application I have and stumbled across the MemoryCache which looks like it’s the replacement for the Caching Block.

It supports caching policy’s such as absolute expiration and sliding expiration.

You can create multiple instances of a MemoryCache or use a singleton in the form of MemoryCache.Default in the typical scenarios where only a single instance of the cache is required.

A simple example is shown below

MemoryCache cache = MemoryCache.Default;

cache.Add("Key", "Value", new DateTimeOffset(DateTime.Now.AddSeconds(10));
object o = cache.Get("Key");

In the above (fairly useless) example we get the default instance of the cache and then add a key and value to the cache with an absolutely expiry in 10 seconds time. We then get the item from the cache and ten seconds later it will have been removed from the cache.

When I get around to it I’ll amend with some better examples.

What version of Oracle am I connecting to ?

We recently moved from using Oracle 10g to Oracle 11g

Note: I have access to the DB in this scenario but only via Oracle SQL Developer

I wanted to verify that the version I was connecting to was indeed 11g, so found this

SELECT * FROM PRODUCT_COMPONENT_VERSION;

Which returns the following

[table “” not found /]

My Memory

Outsourcing my memory and thoughts (and other ramblings) to the web