TransposeBy - Extending Excel With C# And Excel-DNA

Intro

At some point, I think all of us have received an Excel workbook filled with badly formatted data that we somehow have to transform into a neatly formatted report. Several times, I've gotten a column of data that contains records comprising multiple fields, like this.

When what I really need is this,

Excel does contain an inbuilt function called Transpose but it’s quite limited in that it just flips a row to a column and vice versa. As I don’t want to have to reformat the data by hand, preferring an automated solution, I have a couple of options open to me. I can create a VBA based macro or an Office Add-In, but I don’t really want to go that route. Macros can be difficult to add to every workbook I use, and the Add-In will require me to add a button to the tool ribbon which I want to avoid. The last option is to create a custom User Defined Function that will be available to me whenever I use Excel, which as it turns out, is very easy to do in a C# project thanks to an open source framework called Excel-DNA.

Excel-DNA makes the creation of a .xll, the Excel add-in format (It’s similar to a .dll but specific to Excel) extremely simple. There are commercial solutions available, but you’ll probably only ever need this framework.

Set-up

The function I ended up creating is called TransposeBy and I’ll be using the project code to illustrate this article. That source code can be found on GitHub in this repository. Creation of the project is pretty easy so I’m going to assume you can handle the following steps:

1. Create a C# class library project in Visual Studio.
2. Using NuGet, install the following libraries:

• ExcelDNA.AddIn
• ExcelDNA.Integration
• ExcelDNA.Intellisense (Optional)

And now, you’re ready to go.

The Solution

So what does this function need to do to be able to accomplish the outcome I need"? It’ll have to:

• Accept an Excel Range, either a column or a row, specifying the source data I wish to transpose.
• Optionally, accept a Boolean flag to switch the transposition from being by column to being by row.
• Exit if the function was not called as an Array Formula in Excel (If you’re unfamiliar with Array Formula, you can find a primer on them here)
• Perform validation on the source data.
• Initialize and fill an output object array.

The Code

In my sample project, you’ll see a file called TransposeByUDF.cs. This is just a standard class file which inherits the XlCall class in ExcelDNA.Integration and references the Namespace ExcelDna.Integration. I’ve also referenced the Namespace ExcelDna.Intellisense so that users can get some additional information on the function in Excel but it’s not necessary for a simple function.

The next step is to create a static function that returns an object and to attach the following directive to the function. The ExcelFunction directive contains a number of options but I’ve only used the description option to give Excel users some information on the function. I’m returning an object as my function will be filling in a cell range but you can return strings, numbers etc. as appropriate. In the function declaration, I’ve also added a directive to the two parameters to give readable parameter names and descriptions to the Excel users.

Now, that the function is all set up, you can use the many objects that Excel-DNA exposes to interact with the parent Excel application. Documentation in the Excel-DNA project isn’t perfect but there are extensive example projects and an archive of the Google Groups forum that can be searched. For my function, I first needed to get the range of cells that was selected when the function was called and this was made available to me thru the XlCall.Excel object. This object accepts an Enum as a parameter that allows you to define what type of information you want to retrieve. In my case, I’ve used the value xlfCaller to reference the destination cell range.