Show HN: I’ve built a C# IDE, Runtime, and AppStore inside Excel

ORM for Excel Tables

Notice that column names show up in auto-complete, meaning that somewhere there is a strongly typed class that represents the rows of the table.

Where does this class come from? The answer is – it’s generated automatically by QueryStorm (using Reflection.Emit).

The generated class lives inside a dll which the script automatically references. Each time the user changes a table, QueryStorm will examine the change and, if needed, update the generated types.

This functions as an ORM layer on top of Excel tables. For a proper ORM, though, we’d need to be able to specify column data types and table relationships.

By default, QueryStorm will guess the data type for each table column based on the data it contains, and it will not assume any relationships between tables.

However, defining a custom data context allows us to specify column types and table relationships. Adding relationships between tables will cause extra navigation properties to be added to the generated types.

For example, let’s assume we have a “cities” table and a “countries” table. We could modify the column types and add table relationships using the following data context class:

public class WorkbookDataContext1 : WorkbookDataContext 
{
	public WorkbookDataContext1(IWorkbookAccessor workbookAccessor, ISyncRunner syncRunner)
		: base(workbookAccessor, syncRunner, false)
	{
	}

	// Set up column data types
	protected override void Configure(ITablesConfiguration config)
	{
		config.ConfigureTable("countries", tableView => {
			tableView
				.ConfigureColumn("Name")
				.ConfigureColumn("Population (2020)");
		});

		config.ConfigureTable("cities", tableView => {
			tableView
				.ConfigureColumn("city")
				.ConfigureColumn("population");
		});
	}

	// Set up relationships
	protected override IEnumerable CreateRelationships()
	{
		// city.Country relationship (each city to 1 country)
		yield return new RelationInfo("cities", "countryName", To.One, "countries", "Name", "Country");
	
		// country.Cities relationship (each country to N cities)
		yield return new RelationInfo("countries", "Name", To.Many, "cities", "countryName", "Cities");
	}
}

Once the data context is updated, we can see the new navigation properties in our C# scripts.

For example, let’s try to find how much of a country’s population each city accounts for. Since rows in the “cities” table now have a navigation property that allows us to fetch the corresponding country, this is an easy task:

As we specified in the data context definition, the population properties are now of type “int?” and cities now have a navigation property “Country” that makes it easy to navigate to the corresponding row in the countries table.

Updating Excel tables

We aren’t limited to just querying the data in Excel tables. We can also perform updates on Excel tables.

For example, let’s add a new column in the cities table for the share of the country’s population that the city accounts for. First, we must add the column manually in Excel, and then update it through the C# script.

cities.ForEach(c =>
{
	int? cityPopulation = c.population;
	int? countryPopulation = c.Country?.Population__2020_;

	var shareOfCountrysPopulation = 
		1.0 * cityPopulation/ countryPopulation;
	
	c.share_of_country_s_popupation = 
        	$"{100.0 * shareOfCountrysPopulation:0.00}%";
})

Formatting table rows

Lastly, we can update row formatting using the Format() extension method available on IEnumerable objects.

For example, let’s highlight all cities that account for more than 20% of the population of their country.

cities.Where(c => c.share_of_country_s_popupation > 0.2)
	.Format(r => r.Interior.ColorIndex = 35);

Performance

The data from Excel tables is internally cached, so reading performance is about the same as working with lists of objects in memory, i.e., very fast.

Reading data from large tables, even ones with hundreds of thousands of rows takes just a few milliseconds (except for the first read that populates the cache and can take a few seconds for huge tables).

Formatting, though slower than reading, is also highly optimized and has a processing rate of about 1k rows per second.

Extending Excel functionality with C#

Scripts are mainly used for running one-off queries against data in tables. But we can also use C# to add permanent functionality to Excel itself! We can even publish our extensions to allow other people to use them.

We can extend Excel with the following kinds of elements:

• Custom Excel functions
• New context menus
• New ribbon tabs and controls
• Keyboard shortcuts

Custom Excel functions with C#

In VBA, we can easily define custom functions for use in Excel formulas. In QueryStorm, it’s just as easy (but more powerful).

To define one or more custom Excel functions, we need to create a project in QueryStorm, write the C# function, decorate it with the [ExcelFunction] attribute, and build the project. At that point, the function will be available for use in Excel.

For example, let’s create a function called “IsMatch” which will tell us if an input string matches a regular expression pattern:

using System;
using System.ComponentModel;
using System.Text.RegularExpressions;
using QueryStorm.Apps;
 
namespace DemoProject;

public class ExcelFunctions1
{
	[ExcelFunction(Description="check if input matches rx pattern")]
	public static bool IsMatch(string inputText, string regexPattern)
		=> Regex.IsMatch(inputText, regexPattern);
}

Here’s what the new function looks like in Excel:

The .NET base class library contains all sorts of functionality which might be useful in Excel (like regular expressions), and this is an easy way to make it available in Excel.

And, as we’ll see later on, we can also use NuGet packages in our extensions.

C# functions exposed as Excel functions can:

• be synchronous or asynchronous
• return one value or an array of values that spills across a range of cells
• return a single value or a stream of values that change over time (e.g. stock prices)

See the documentation for more details on defining Excel functions with C#.

Adding custom commands to Excel

Aside from custom functions, we can also extend Excel with custom ribbon commands, context menus, and keyboard shortcuts.

For example, suppose we want to add a custom context menu command for converting the texts in selected cells to upper case.

Here’s what the code for that command would look like:

using Microsoft.Office.Interop.Excel;
using System;
using QueryStorm.Apps;
using QueryStorm.Apps.Contract;
using QueryStorm.Tools.Excel;
using QueryStorm.Tools;

namespace Project;

public class ContextCommand1 : ContextMenuCommand
{
    private readonly IWorkbookAccessor workbookAccessor;

    public ContextCommand1(IWorkbookAccessor workbookAccessor)
        : base(
            caption: "To UPPPER case",
            faceId: 100,
            allowedLocations: new[] { KnownContextMenuLocations.Cell, KnownContextMenuLocations.Table })
    {
        this.workbookAccessor = workbookAccessor;
    }

    public override void Execute()
    {
        workbookAccessor.Run(wb => 
        {
        	var range = wb.Application.Selection as Range;
        	object [,] data = range.GetData();
        	for (int i = data.GetLowerBound(0); i <= data.GetUpperBound(0); i++)
        	{
        		for (int j = data.GetLowerBound(1); j <= data.GetUpperBound(1); j++)
        		{
        			var val = data[i,j];
        			if(val is string s)
        				data[i, j] = s.ToUpper();
        		}
        	}
        	range.Value = data;
        });
    }
}

And here’s what the new context menu command looks like in Excel:

If you look at the context menu command’s constructor, you might notice that it uses dependency injection to access the current workbook. In fact, QueryStorm apps use dependency injection extensively. More on that in this section of the documentation.

Publishing Extension packages

Once you’ve built the customizations you want, you might want to share them with colleagues or clients. You can do so by publishing your extension package to a repository.

Once you publish an extension to a repository, users who have the QueryStorm Runtime and the URL of your repository can download and use it. More on this in the “Runtime” section of this post.

Automating workbooks

Another thing you can do in Excel with VBA is react to events in the workbook. We can easily do this with C# and QueryStorm too.

To add behavior (in the form of C# code) to a workbook, create a project inside the workbook and add a component class:

For example, the following component pops up the name of the oldest person in an Excel table whenever the user clicks a specific button in the workbook.

using System;
using System.Collections.Generic;
using System.Linq;
using QueryStorm.Apps;
using QueryStorm.Apps.Contract;
using QueryStorm.Data;
using static QueryStorm.Tools.DebugHelpers;

namespace Project;

public class Component1 : ComponentBase
{
	private double _number;
		
	// data-bind to "People" table
	[BindTable]
	public PeopleTable People { get; set; }
		
	// handle click of button "Sheet1!CommandButton1"
	[EventHandler("Sheet1!CommandButton1")]
	public void Test()
	{
		var oldestPersonsName = People
			.OrderByDescending(x => x.Age)
			.First()
			.Name;
			
		System.Windows.Forms.MessageBox.Show(
			$"Oldest person is {oldestPersonsName}");
	}
}

The component uses data binding to access data in an Excel table and defines a method for handling the click event of a button.

Upon building the project, the compiled workbook app is embedded inside the workbook as one or more dll files. The QueryStorm Runtime then detects the workbook app and starts it.

It’s worth noting that workbook apps can also include their own custom Excel functions. The functions defined inside of a workbook are only available in that particular workbook. The same goes for any context menus, ribbon customizations, and keyboard shortcuts that the workbook app defines.

The project system

QueryStorm has its own project system similar to the one in Visual Studio.

Projects that contain Excel extensions are defined at the machine level, while a project that automates a workbook is stored inside the workbook itself.

The context menu contains commands for generating various kinds of files with some basic code scaffolding included.

VB.NET support

One thing worth mentioning is that C# is not the only language that’s supported – VB.NET is as well. When creating a project you can choose which language to use.

This choice is stored inside the project.config file.

Support for VB.NET might appeal to users with a background in VBA. Since Roslyn includes support for VB.NET, surfacing it in QueryStorm made sense.

Note that we can use VB.NET to build extension apps and workbook apps, but there is no scripting flavor of VB.NET in Roslyn, so there are no VB.NET scripts, only C# scripts.

NuGet packages

One of the main advantages of C# and .NET is the ability to use existing libraries and packages from the .NET ecosystem, so supporting NuGet was a must for QueryStorm.

Here’s what adding a package to a project looks like:

The QueryStorm Runtime

Ok, so how does someone use a workbook or Excel extension that you’ve built using QueryStorm?

The main thing they need is the QueryStorm runtime. The runtime allows running workbooks with compiled C# code as well as downloading and running Excel extensions created by QueryStorm.

The runtime is a free 4MB add-in that end users install into Excel. Users can download it from the QueryStorm website.

The installer performs a per-user installation that does not require admin privileges.

Workbook apps and extension apps are very similar but differ in how they are distributed.

Running and distributing workbook apps

For workbook apps, distribution is simple. The compiled code is embedded inside the workbook itself. The workbook can be distributed via email, network share, cloud storage, etc…

Note that sharing the workbook as an email attachment might not always work, since the workbook contains an embedded dll which some email filters will detect and block for security reasons. Sharing through OneDrive, Dropbox, or Google Drive will work just fine, though.

When an end user opens a workbook that has compiled code inside it, the QueryStorm runtime will prompt the user to allow the code in the workbook to run.

If the user allows the workbook to run, they will not be asked again for that particular workbook, unless the compiled code is changed in any way.

Check out the docs for more information on workbook app security.

Running and distributing extension apps

Excel extensions, on the other hand, are distributed through the QueryStorm app store. The author publishes their extension package to a (NuGet) server.

End users can pull the extension from there, but they must first register the URL of the repository using the “Extensions” dialog in the “QueryStorm Runtime” tab in the ribbon:

Once they do, they will be able to browse and install all extensions on that repository.

The QueryStorm runtime comes with one predefined repository where we (mostly I) publish general-purpose extensions.

For example, I’ve built and published an extension that adds several functions for working with phone numbers.

Here’s how to install and use it:

Currently, authors can’t publish their extensions to this built-in repository (contact me if you would like to). However, you can easily create your own repository and use it to share your extensions with colleagues and clients.

If you would like to share extensions with other users on the same network, a simple shared folder will work nicely. Simply registering the path of the network share is sufficient both for package authors and end users.

If you would like to share your extensions with other people outside of your network, then using a cloud repository like Azure Artifacts would be best. Azure Artifacts has a free tier that offers 2GB of storage which is enough for hundreds of extensions. Setting up an Azure Artifacts repository takes just a few minutes, see this video for instructions.

For more information on setting up repositories, check out this section of the documentation.

Debugging C# code in Excel

If you’re writing more than a trivial amount of C#, you’ll need to be able to debug. There are several ways to debug C# code in QueryStorm, the easiest one being the built-in .NET debugger.

To start debugging a project, press F5.

All of the debugger commands are available in the ribbon, but you can also use keyboard shortcuts which you can configure in the settings dialog:

One limitation of the debugger is that you can only use it to debug regular C# code. Debugging C# scripts is not (yet) supported.

To debug C# scripts, you can either fall back on log statements (using the Log() method) or attach Visual Studio to the Excel process and use the Debug() method to act as a breakpoint.

Final thoughts

Excel has a lot of functionality. With the ability to process data and add logic using C#, however, it can become an extremely powerful tool in the hands of a skilled user.

This post only scratched the surface of the possible ways to use C# inside Excel.

If you’re a C# developer, VBA developer, data analyst, or data scientist, I hope that QueryStorm finds its place in your toolbox as your favorite tool.

If you have any thoughts or comments, please leave them below. I’d very much welcome your feedback.