Writing readable and concise source code is one of the key objectives of software development as majority of the total costs of ownership of a software solution is usually spent on code maintenance.
Aspect Oriented Programming enables, inter alia, to reduce the amount of source code by unifying the logic for processing of inputs and outputs of class methods (cross-cutting concerns). These code fragments are called boundary aspects.
PostSharp is a leading .NET Aspect Oriented Programming library. Excel-DNA is a leading framework for development of .NET Excel Addons.
This article introduces some unusual applications of PostSharp within the Excel-DNA problem space: (1) a check whether the cell’s formula length is shorter than 255 characters and (2) an automatic resizing of a function result into a range formula.
Without using PostSharp aspects, every single exposed Excel Function would have to repeat the same code implementing this logic resulting in a ballooned code base. With PostSharp, the code is brief, easy to read and easy to maintain.
PostSharp Versions
This article makes use only of the PostSharp’s Method Decorator which is already available in the free version of PostSharp. The full description of various PostSharp versions is available here.
Method Decorator Explanation
Method Decorator (PostSharp Documentation) specified as a method attribute injects into the method code before and/or after the method is run the code of the Decorator.
The before-method decorator, the OnEntry method, is run before the code of the actual method is executed and is ideally suited for checking whether the method can be run and whether the method parameters are sensible. If not, the decorator can halt the execution of the method and return a pre-defined value.
The after-method decorator, OnSuccess method, is run after the method succeeded without raising any exception and thus can be be utilized for transforming of the method return value into the target form.
Excel Background
Excel C API limits the length of the formula it can handle to 255 characters.
By default, the result of an Excel function fills up the size of the range where the formula is inserted. As the user must select the range of the right sizes, this may be bothersome. For that purpose, the ResultResizer does the job and resizes the output range to the right size.
Example
The source code of the example is available on https://github.com/jiripik/PostSharpExcelDNA
To test the code, build the code, and install the produced xll file into Excel. Then, insert a formula =TryMe() and see the magic!
The Aspect defines a before-method decorator, an on-exception decorator and an after-method decorator. The code is largely self-explanatory. In order to use the Aspect, just decorate the function with the attribute [ResultResizerAspect].
namespace PostSharpExcelDNA
{
using System;
using ExcelDna.Integration;
public class ExcelFunctions : IExcelAddIn
{
[ExcelFunction(IsMacroType = true)]
[ResultResizerAspect]
public static object TryMe(object parameter)
{
return ExcelAsyncUtil.Run("TryMe",
new[] { parameter },
()
{
try
{
const int n = 100;
var res = new object[n, n];
for (var i = 0; i < n; ++i)
{
for (var j = 0; j < n; ++j)
{
res[i, j] = string.Format("{0}, {1}", i, j);
}
}
return res;
}
catch (Exception exception)
{
return exception.Message;
}
});
}
public void AutoOpen()
{
}
public void AutoClose()
{
}
}
}
Comments on the Example
- Split up the aspect into two aspects – it may be logically sensible to separate the code for checking of the length of the formula from the code for re-sizing of the result
- The auto-resize code fails for nested asynchronous excel functions
General Comments
- Excellent performance – there is no noticeable penalty for using PostSharp aspects
- The more the aspect is used, the greater the benefits of the aspect
- Do not overuse aspects – aspects, like any other tool, are suitable for certain kind of problems but not very much useful for other ones. Know their limitations
- Other typical uses of aspects include logging, caching, license and security management