Flask Blueprint for Microsoft Azure AAD Authentication

This blog post takes you through adding authentication to a Flask application, using a blueprint, connecting to Microsoft Azure AAD using MSAL Python library.

The hope is, you can grab the blueprint, and drop it in to your flask application, authentication done bish-bash-bosh, and then spend your time doing the more interesting stuff. You won’t have to code up any authentication endpoints!

As always, go to the “I just want the code” section if you want to simply drop the blueprint into your app as quickly as possible without the detail.

  • Part 1 of this blog will go through how to use the blueprint in your own flask app.
  • Part 2 will talk a bit about what is going on under the covers of the blueprint code.
  • Part 3 is a ‘bonus feature’ of this application: As I was originally trying to figure out how to authenticate a plotly dash web app with AAD, I will show you how to do this with the same blueprint.

A future improvement might be to add this to flask-login, but, I didn’t. *shrug*

Prerequisites

  • You have a Microsoft Azure account, or you can create a free one.
  • You have the necessary permissions to create an app registration in the account
    • true if using the free account, or if you own your account
    • true if you own or can create a new/test ‘azure active directory’ within the account
    • not always true if you work for an organization with a DevOps / InfoSec function – but in this case you can ask them to add on your behalf
  • You have heard of Flask
  • Have install of python 3.x

I just want the code

Complete example project: https://bitbucket.org/andysprague44/flask.aad 

Authentication blueprint you can lift and drop into to your existing flask app: https://bitbucket.org/andysprague44/flask.aad/src/master/blueprints/auth/

Part 1: How to use the flask authentication blueprint

Given an existing Flask app, how can we secure it?

1a. Add an Azure App Registration

For the Flask App to accept authentication requests from it’s users it needs to be ‘trusted’ in the AAD domain. This is achieved by creating an ‘App Registration’, which represents the application in Azure-AAD-land.

1. Navigate to ‘Azure Active Directory’ in the left hand menu, then App registrations. Click ‘New registration’

NewAppRegistration_AzurePortal

2. Create the App Registration. Give it a useful name, choose the account types according to your needs, and add an initial redirect URL:

RegisterAnApplication_AzurePortal

3. Add ‘App Roles’ (for more info, see https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-add-app-roles-in-azure-ad-apps). Go to ‘Manifest’ tab in your app registration, and replace the line 

“appRoles”: [],

with the following (change ‘DemoApp’ to the name you chose for the app reg in step 2):

"appRoles": [
 {
 "allowedMemberTypes": ["User"],
 "description": "Read Access",
 "displayName": "DemoApp Read",
 "id": "a8161423-2e8e-46c4-9997-f984faccb625",
 "isEnabled": true,
 "value": "DemoApp.Read"
 },
 {
 "allowedMemberTypes": ["User"],
 "description": "Write Access",
 "displayName": "DemoApp Write",
 "id": "b8161423-2e8e-46c4-9997-f984faccb625",
 "isEnabled": true,
 "value": "DemoApp.Write"
 },
 {
 "allowedMemberTypes": ["User"],
 "description": "Admin Access",
 "displayName": "DemoApp Admin",
 "id": "f2ec0750-6aee-4640-8f44-e050b8e35326",
 "isEnabled": true,
 "value": "DemoApp.Admin"
 }
 ],

4. Give your user the required app role(s), see https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-add-app-roles-in-azure-ad-apps.

  • Note that, in the blueprint, if a user has Admin they are assumed to have Write & Read, and if the user has Write they are assumed to have Read.

1b. Adding the blueprint

My project assumes you are using the Flask Application Factory Pattern, if you are not, well, you should be, so change your project structure then come back to this tutorial. If you can’t be bothered, then fine (I guess), but you are somewhat on your own in hooking up this blueprint!

Good to carry on? OK then…

First, drop in the entire ‘auth’ blueprint folder into your app. I’ve assumed this is added to the path “blueprints/auth” relative to the project root.

Then, add the following to your requirements.txt file:

msal==1.6.0
flask-session==0.3.2

Then, register the blueprint in your app.py file, and use the decorator function login_required to secure your flask routes:

from flask import Flask
from werkzeug.middleware.proxy_fix import ProxyFix
from flask_session import Session
from . import appsettings as config
from blueprints.auth.decorators import login_required

def create_app():
    """Construct core Flask application with embedded Dash app."""
    app = Flask(__name__)
    app.config.from_object('application.appsettings.FlaskConfig')
    Session(app)
    
    with app.app_context():
        # Register Flask routes
        @app.route("/")
        @login_required #**This decorator authenticates the flask route**
        def index():
            return render_template('index.html', user=session["user"], version=msal.__version__)
        
        # Register blueprint for auth
        from blueprints import auth
        app.register_blueprint(
            auth.construct_blueprint(config.AuthenticationConfig),
            url_prefix='/auth')

        # Fix "flask.url_for" when deployed to an azure container web app
        # See https://github.com/Azure-Samples/ms-identity-python-webapp/issues/18#issuecomment-604744997
        app.wsgi_app = ProxyFix(app.wsgi_app, x_proto=1, x_host=1)
    
    return app

Note that the example project splits out the flask routes to a routes.py file, but the approach is the same.

When registering the blueprint, you’ll note the object config.AuthenticationConfig being passed in. This is a dict with the following keys, to add to a appsettings.py file (or perhaps a config.py in your own flask app):

# Config required by the authentication flask blueprint
AuthenticationConfig = {
    "TENANT": tenant,
    "CLIENT_ID": client_id,
    "CLIENT_SECRET": client_secret,
    "HTTPS_SCHEME": https_scheme
}
  • tenant: from your App Registration overview page this is the guid which at the time of writing is called “Directory (tenant) ID”
  • client_id: from your App Registration overview page this is the guid which at the time of writing is called “Application (client) ID”
  • client_secret: from your App Registration, go to ‘Certificates & secrets’ page, and add a new client secret. Copy the value. Do not check this one into source control!
  • https_scheme: this is either ‘http’ or ‘https’. While running locally this can be ‘http’ but should always be ‘https’ when deployed to production.

*Important*: Your flask session needs to use server-side sessions, to avoid the error “The “b’session'” cookie is too large”. Add SESSION_TYPE = 'filesystem' to your flask app configuration:

app.config.update(SESSION_TYPE = 'filesystem')

1c. Configuring your application

My approach to configuration is pretty ‘dotnet’-like (with appsettings.json files), so you may prefer something like python-dotenv; fine by me. However, if you are using the demo application, including it’s configuration approach (rather than grabbing the blueprint alone) you’ll need to do the following:

  1. Copy file appsettings.json and rename the copy to appsettings.Development.json
  2. Leave appsettings.json alone (do not add your secrets here!)
  3. Add your application config, including secrets, to appsettings.Development.json, noting that this file is ignored by git
  4. If you add/remove keys, make sure to also update appsettings.py where the settings are materialized.

You can then configure the application authentication blueprint differently per environment, by having different application registration entries and adding the config to an env specific appsettings.{env}.json file.

If you are using your own configuration approach, you will need to construct the dict somewhere, and pass it into the blueprint.

# Config required by the authentication flask blueprint
AuthenticationConfig = {
    "TENANT": tenant,
    "CLIENT_ID": client_id,
    "CLIENT_SECRET": client_secret,
    "HTTPS_SCHEME": https_scheme
}

1d. Wrap up

With any luck, you should now be able to run your application locally, and have your flask app up and running with Azure AAD without coding up any authentication endpoints yourself!

Part 2: What is the blueprint doing?

The ‘auth’ blueprint uses the python MSAL library to mediate authentication.

  1. The python web application uses the Microsoft Authentication Library (MSAL) to obtain a JWT access token from the Microsoft identity platform (formerly Azure AD v2.0):
  2. The token contains the app roles of the authenticated user; accordingly, the blueprint code checks that the uses has at a minimum ‘Read’ access to the application, or authentications fails.
  3. The access token is saved into the flask session, for use later as a bearer token to authenticate the user in requests (e.g. calling the Microsoft Graph).
Overview

More details around the workflow can be found in the MSAL documentation, start here: https://github.com/Azure-Samples/ms-identity-python-webapp

Part 3: Applying this to a plotly dash application

Dash is a library for creating responsive web apps written declaratively in python (no javascript required!).

Dash is powered by flask, so we are able to apply this same approach to securing dash applications. I followed the excellent walk-through at https://hackersandslackers.com/plotly-dash-with-flask/ for the initial “add flask to dash” approach.

The trick is to start up Dash with a flask server that we control. To do this, we can add the dash app initialization as a step in the flask ‘create_app’ method (again, this assumes flask application factory pattern is utilized, in this case it’s probably mandatory).

Your ‘create_app’ method can add these 2 lines, right after the flask routes and the authentication blueprint are registered:

# Register an embedded dash app
from .dashapp import create_dashapp
app = create_dashapp(app)

Then add the file ‘dashapp.py’ containing a factory method ‘create_dashapp’ that takes the flask app as a parameter (called server’ to avoid confusion with the dash ‘app’). Now, we can start the dash app up, using our existing flask app!

def create_dashapp(server):
    """
    Init our dashapp, to be embedded into flask
    """
    app = dash.Dash(
        __name__,
        server=server,
        url_base_pathname='/dash/')
    app.config['suppress_callback_exceptions'] = True
    app.title = 'My Dash App'
    
    #... add dash callbacks & layout code here
    
    # End of create_dashapp method, return the flask app aka server (not the dash app)
    return app.server

The last step is how we fold in the authentication piece. We can protect the dash views, so that if a user navigates directly to ‘/dash’ will be redirected to authenticate as we expect. Add the following method to dashapp.py:

from blueprints.auth.decorators import login_required
def protect_dashviews(dash_app):
    for view_func in dash_app.server.view_functions:
        if view_func.startswith(dash_app.config.url_base_pathname):
            dash_app.server.view_functions[view_func] = login_required(dash_app.server.view_functions[view_func])

And add an additional line to the ‘create_dashapp’ factory method:

def create_dashapp(server):
    """
    Init our dashapp, to be embedded into flask
    """
    app = dash.Dash(
        __name__,
        server=server,
        url_base_pathname='/dash/')
    app.config['suppress_callback_exceptions'] = True
    app.title = 'My Dash App'
    protect_dashviews(app) #***We just added this line***

Your dash app is now authenticated!

One last thing to note in the dash app, is that I found it quite tricky to get the name of the authenticated user inside of the dash app components, especially immediately after the user authenticates. To achieve this, add the following component to the dash app layout:

dcc.Location(id='url', refresh=False), # represents the URL bar, doesn't render anything

Then add a callback to grab the name, which is fired when the redirection from the authentication workflow takes the user back to the dash app:

# Add callback for writing name of user to navbar
    @app.callback(Output('navbar-navigation', 'label'),
                  Input('url', 'pathname'))
    def user_name_to_navbar(pathname):
        if 'user' in flask.session:
            user = flask.session['user'].get('name', 'unknown')
        else:
            user = 'unknown'
        return user

Obviously, the above relies on the existence of the element ‘navbar-navigation’, so change the output to wherever you need to write the username to.

Summary

I hope you found this useful. Chat in the comments with your suggestions on how to make this better, or raise a Pull Request.

Happy flask’ing!

NetOffice.Excel – Add Validation to a Cell

My most recent blogs have been focused on Excel-DNA, and the pretty cool ability it brings to easily add your own custom ribbons.  This blog is looking at the other component of writing a C# app that can talk to Excel – namely NetOffice.Excel.

NetOffice.Excel provides the ability to interact with Excel COM elements, in a (mostly) painless way.  Think workbooks, worksheets, ranges, cells, read, write, format, copy… and pretty much all else you are able to do through Excel.

This blog will focus on the ability to create validation within a cell.  From excel, you can find that option here:

image

 

Code

As always, feel free to dive straight into the code and take snippets at will.  you can find the full project at:

https://bitbucket.org/andysprague44/netoffice.excel.extensions

And the code for this blog:

https://bitbucket.org/andysprague44/netoffice.excel.extensions/src/91d59b64a54fc0342064f5a529bf6f65685466da/NetOffice.Excel.Extensions/Extensions/CellValidationExtensions.cs?at=master&fileviewer=file-view-default

 

Getting set-up

In order to test out our application, I’d suggest getting immediately familiar with Excel-DNA, as this gives us a way to launch Excel from Visual Studio direct and gives us a way to see interactively what is going on.  Otherwise we’d be updating a closed excel worksheet, and you’d have to launch it after manually to see the impact.  And hey, nobody likes anything manual.  Follow this tutorial to get set-up with a Hello World application that we can use to test.

https://andysprague.com/2017/02/03/my-first-custom-excel-ribbon-using-excel-dna/

If you already have an application that uses NetOffice.Excel but not Excel-DNA, then the code should still be relevant, you’ll just be on your own testing it out.

Adding validation with a hard-coded list

If you are on this blog and know this one, but you’re getting errors with long lists, don’t panic, skip to the next section.

So essentially we are replicating:

image

Add this extension method to your project:

using System;
using System.Collections.Generic;
using System.Linq;
using NetOffice.ExcelApi;
using NetOffice.ExcelApi.Enums;

namespace NetOffice.Excel.Extensions.Extensions
{
    public static class CellValidationExtensions
    {
        public static void AddCellListValidation(this Range cell, IList<string> allowedValues, string initialValue = null)
        {
            var flatList = allowedValues.Aggregate((x, y) => "{x},{y}");
            if (flatList.Length > 255)
            {
                throw new ArgumentException("Combined number of chars in the list of allowedValues can't exceed 255 characters");
            }
            cell.AddCellListValidation(flatList, initialValue);
        }

        private static void AddCellListValidation(this Range cell, string formula, string initialValue = null)
        {
            cell.Validation.Delete();
            cell.Validation.Add(
                XlDVType.xlValidateList,
                XlDVAlertStyle.xlValidAlertInformation,
                XlFormatConditionOperator.xlBetween,
                formula,
                Type.Missing);
            cell.Validation.IgnoreBlank = true;
            cell.Validation.InCellDropdown = true;
            if (initialValue != null)
            {
                cell.Value = initialValue;
            }
        }
    }
}

Hopefully this should be easy enough to follow.  First, we aggregate the list to a comma separated string (which is the ‘Source’ or ‘formula’), then we call the cell.Validation method with some ugly excel settings rearing up.  More description of these in the documentation at

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.validation.add(v=office.15).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1

Then to test it:

1. Add a button in the custom ribbon to add cell validation (COMING SOON – right clicking on the cell can also provide this functionality)

<group id="Group1" label="My Group">
    <button id="Add Validation" label="Validate!" imageMso="FastForwardShort" size="large" onAction="OnAddValidation">
 </group>

2. Add this code to customRibbon.cs:

using static MyExcelAddin.CellValidationExtensions;
...

public void OnAddValidation(IRibbonControl control)
{
    var allowedValues = new List<string> { "England", "30", "6", "Australia", "Equals", "Thrashing" };
    ((Worksheet)_excel.ActiveSheet).Range("A1").AddCellListValidation(allowedValues);
}

Launch excel and Click the new button, and you should now see the cell validation in cell A1:

image

To change the error message displayed when invalid data is entered use:

cell.Validation.ErrorMessage = "No Chance";

So we then get:

image

Cell Validation for long list

This method breaks down for long lists, are there is a limit of (I think) 255 chars for this length of the comma seperated list.  In this case all we can do is save the values to a range, and reference that to provide our cell validation.

Add this method to your CellValidationExtensions class:

public static void AddCellListValidation(this Range cell, Range allowedValuesRange, string initialValue = null)
        {
            var fullAddress = $"='{allowedValuesRange.Worksheet.Name}'!{allowedValuesRange.Address}";
            cell.AddCellListValidation(fullAddress, initialValue);
        }

The difference here is that the formula/source is now referencing a range, rather than a hard-coded string.  Simples!

To test, change the method in CustomRibbon.cs to the following:

public void OnAddValidation(IRibbonControl control)
{
var allowedValues = new List<string> { "England", "30", "6", "Australia", "Equals", "Thrashing" };
var activeSheet = ((Worksheet)_excel.ActiveSheet);
var range = activeSheet.Range("A1:F1");
range.Value = allowedValues.ToArray();
activeSheet.Range("A2").AddCellListValidation(range);
}

First, save the list to a range, then use this range to populate the validation.   In a real application you would probably want to create a hidden sheet, and save the allowed value list there, it works the same.

The result is this:

image

 

Now you know how to use NetOffice.Excel to add cell validation!   I’m always open to feedback, please comment what you think below.

Until next time.

Custom Icons in Excel-DNA Custom Ribbon

When adding options to your menu, you’ll need icons.  There are about 9,000 already available icons (yikes!), so you’ll probably find one that is suitable and is consistent with the UX of Excel.  You can also add custom icons.  As always code is available on my bit bucket account: https://bitbucket.org/andysprague44/excel-dna-examples/…CustomImage

Built-in icons

You can add a built-in icon to your custom ribbon using the imageMso argument.  For example:

<button id="refreshButton" imageMso="RefreshMenu" size="large" onAction="onRefreshMenu" />

How can you see the available icons?  The best way is a great excel add-in that allows you to browse the full icon gallery.  Install from https://imagemso.codeplex.com/releases/view/116271  – instructions on how to access are in the documentation tab of the same website.  The result?

imageMsoGallery

Alternatively there is an online gallery at http://www.spreadsheet1.com/office-excel-ribbon-imagemso-icons-gallery-page-01.html – however, this seems like a subset of the available icons only, and there is no search function.

Custom Icons

You can also add custom icons using the getImage argument.  For this you need to

    1. Add an image to your resources
    2. Add a getImage argument to the element in the customRibbon.xml file
    3. Add an action handler to serve up your image.

The action is invoked when you first load up the ribbon and the image will appear.

Note that any image is allowed but excel displays small PNG icons style images best – I use http://www.flaticon.com/ to get free icons.  A favourite summer holiday snap is not going to render very well.

Add an image to resources

Start by adding the image to a folder called Resources in the project.  Then to add the image to the strongly typed resources of the project itself, go to project properties (right click on project in the project Explorer -> Properties), then choose Resources.  Add your resource from the top menu using ‘Add Existing File’.

AddResource

Then, right click on the Resources.resx file and choose ‘Run Custom Tool’ to generate a strongly typed reference Properties.Resources.rugbyimage that we can call in our action handler later on.

image

 

Add custom xml element

Add to CustomRibbon.xml:

<button id="RugbyImageButton" label="Rugby" getImage="GetImage" size="large" onAction="OnPressMe"/>

Note that the option is ‘getImage’ as opposed to using ‘imageMso’ when using built in images.

Add action handler

Then you need to add an action handler to serve up your image:

CustomRibbon.cs:

public Bitmap GetImage(IRibbonControl control)
        {
            switch (control.Id)
            {
                case "RugbyImageButton": return new Bitmap(CustomImage.Properties.Resources.RugbyImage);
                default: return null;
            }
        }

Using the switch on the ID of the element allows us to easily add multiple images in the future using the same action handler.

This is the final result!

image

Show Message Boxes with Excel-DNA

Often a simple way to get user feedback is to show a pop-up message box.  Read on for a tutorial on how to do this.  As an example, this is the result we will get from this blog:

SimpleMessageBox

 

The second section of this blog deals with how to handle message boxes during asynchronous operations, as this is a little more involved.  All code examples can be found at: https://bitbucket.org/andysprague44/…/MessageBox

The below assumes a simple Excel-DNA project has been created as per my tutorial at: https://andysprague.com/2017/02/03/my-first-custom-excel-ribbon-using-excel-dna/

Simple Message Box

To achieve this in your Excel-DNA project there are several steps.

1. Add a class that displays the message box:

public interface IExcelWinFormsUtil
    {
        DialogResult MessageBox(string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon);
    }

public class ExcelWinFormsUtil : IExcelWinFormsUtil
    {
        public DialogResult MessageBox(string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon)
        {
            return ShowModal(parentWindow => MsgBox.Show(parentWindow, text, caption, buttons, icon));
        }

        private static DialogResult ShowModal(Func<IWin32Window, DialogResult> dialogFunc)
        {
            var parentWindow = new NativeWindow();
            parentWindow.AssignHandle(ExcelDna.Integration.ExcelDnaUtil.WindowHandle);

            try
            {
                return dialogFunc(parentWindow);
            }
            finally
            {
                parentWindow.ReleaseHandle();
            }
        }
    }

The critical line is “parentWindow.AssignHandle(ExcelDna.Integration.ExcelDnaUtil.WindowHandle);”.  Here we use Excel-DNA to get a reference to the Excel window, and then use this to display a message box within the context of excel (as a child window).

2. Next is to create an instance of this class when the add-in loads.  In the CustomRibbon.cs class:

public override string GetCustomUI(string ribbonId)
        {
            _excel = new Application(null, ExcelDna.Integration.ExcelDnaUtil.Application);
            _excelWinFormsUtil = new ExcelWinFormsUtil();

            string ribbonXml = GetCustomRibbonXML();
            return ribbonXml;
        }

 

And to make the form visible to the controller any action that requires message boxes can be run like this:

public void OnPressMe(IRibbonControl control)
        {
            using (var controller = new ExcelController(_excel, _thisRibbon, _excelWinFormsUtil))
            {
                controller.PressMe();
            }
        }

3. The last thing to do is to call it!  The below is called from the ExcelController.cs class but could be anywhere in your code-base:

public void PressMe()
        {
            var dialogResult = _excelWinFormsUtil.MessageBox(
                "This is a message box asking for your input - write something?",
                "Choose Option",
                MessageBoxButtons.YesNoCancel,
                MessageBoxIcon.Question);

            switch (dialogResult)
            {
                case DialogResult.Yes:
                    _excel.Range("A1").Value = "Yes chosen";
                    break;
                case DialogResult.Cancel:
                    _excel.Range("A1").Value = "Canceled";
                    break;
                case DialogResult.No:
                    _excel.Range("A1").Value = null;
                    break;
            }
        }

MessageBoxButtons contains the standard options, YesNo, OkCancel etc.

There are some changes to how excel itself handles windows from versions 2013 onwards but I have tested before and after so should work in most Excel versions (though no guarantees).

Asynchronous Message Boxes

When calling message boxes from background threads things get a little tricky.  Excel is at heart a single threaded application, so any Excel COM interaction has to be passed back to the main thread.  You’ll know when you’ve hit this problem if the focus returns to the wrong workbook after a message box is displayed, or there may be just a cryptic COM exception.

Excel-DNA provides help with this, which is to use the function ExcelAsyncUtil.QueueAsMacro.  This waits for the main thread to be free then runs the code that interacts with Excel, i.e if the user is editing a cell at the time the function is called, it will wait until editing is finished, and then and only then call the code.

However, if we want to show a message box in this workflow we often want to wait for user feedback i.e. YesNo, OkCancel.  the default method does not block execution so any code that uses a returned DialogResult will always use DialogResult.None.  This means we need to do some more work with the provided function.

I’ve implemented as an extension method that does this, add the following class to your application:

public static class ExcelDnaExtensions
    {
        /// <summary>
        /// Run a function using ExcelAsyncUtil.QueueAsMacro and allow waiting for the result.
        /// Waits until excel resources are free, runs the func, then waits for the func to complete.
        /// </summary>
        /// <example>
        /// var dialogResult = await excel.QueueAsMacroAsync(e =>
        ///     _excelWinFormsUtil.MessageBox("Message", "Caption", MessageBoxButtons.YesNo, MessageBoxIcon.Question) );
        /// </example>
        public static async Task<T> QueueAsMacroAsync<T>(this Application excel, Func<Application, T> func)
        {
            try
            {
                var tcs = new TaskCompletionSource<T>();
                ExcelAsyncUtil.QueueAsMacro((x) =>
                {
                    var tcsState = (TaskCompletionSource<T>)((object[])x)[0];
                    var f = (Func<Application, T>)((object[])x)[1];
                    var xl = (Application)((object[])x)[2];
                    try
                    {
                        var result = f(xl);
                        tcsState.SetResult(result);
                    }
                    catch (Exception ex)
                    {
                        tcsState.SetException(ex);
                    }
                }, new object[] { tcs, func, excel });
                var t = await tcs.Task;
                return t;
            }
            catch (AggregateException aex)
            {
                var flattened = aex.Flatten();
                throw new Exception(flattened.Message, flattened);
            }
        }
    }

Then to call, include the extensions class in your imports:

using static MessageBoxAddin.Extensions.ExcelDnaExtensions;

and call as in this example:

public void OnPressMeBackgroundThread(int delay)
        {
            Task.Factory.StartNew(
                () => RunBackgroundThread(delay),
                CancellationToken.None,
                TaskCreationOptions.LongRunning,
                TaskScheduler.Current
            );
        }

public async Task RunBackgroundThread(int delay)
        {
            Thread.Sleep(delay*1000);

            //get user input as part of a background thread
            var dialogResult = await _excel.QueueAsMacroAsync(xl =>
                _excelWinFormsUtil.MessageBox(
                    "Message box called from background thread",
                    "Long Running Thread",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Information)
            );

            //do stuff depending on dialog result in the background

            //finally, call back to excel to write some result
            ExcelAsyncUtil.QueueAsMacro(() =>
            {
                _excel.Range("A1").Value = dialogResult.ToString();
            });
        }

 

So what are we doing here?  By using a TaskCompletionSource we can force completion of the function before continuing using ‘await tcs.Task’.   This is turn means we can await the QueueAsMacroAsync function and do something with the MessageBox result.

AsyncMessageBox

Excel-DNA Custom Ribbon Cheat Sheet

In this blog I’ll give some helpful snippets of code to work with Custom Ribbons in Excel-DNA.

I will assume you have already got a project that creates a custom ribbon, and you now want my amazing insight on how to add something that is more elaborate than, say, a button that writes a hard-coded string to cell A1 of the current worksheet .  If this isn’t true head over to my previous blog: My First Custom Excel Ribbon using Excel-DNA.

This is a companion to the main documentation which you can find here.  This goes into a bit more detail on some of the below snippets.

A quick side-note:

If you are looking for a cheat-sheet for how to interact with Excel itself i.e. read and write to cells, you actual want information on NetOffice.Excel.  This blog is focused on the Excel-DNA features only.  I’ll be writing a part 2 blog on NetOffice.Excel specific stuff in future but for now, well, search Google.  I mention this as the distinction confused me when I first started working in this space!

Now on with the blog…

Auto-Completion of the customui xml

The first one is a biggy:  auto-completion of xml elements in the ribbon definition.  Once you have this you can see all available options in the current scope as a drop-down.  Neato!

XmlAutoComplete

In theory it should be as simple as adding a customUI element to the xml definition that loads the schema, but that didn’t work for me (comment if you have worked it out, would be really appreciated and I’ll update).  By all means try yourself though:

<?xml version="1.0" encoding="utf-8" ?/>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnLoad"/>
<!--Your ribbon definiton--/>
</customUI>

Otherwise the following steps will do the trick:

  1. Download the customUi.xsd schema, contained in the ‘Office Fluent User Interface XML Schema’ download package, and follow the installation instructions (remember where you save it)
    • For Office 2010 go here
    • For Office 2007 go here
    • For newer excel versions using either of the above is probably good enough
    • For older versions, you are on your own dude.
  2. Open the xml file in Visual Studios, right click anywhere in the window, and choose Properties from the menu
  3. In the ‘schemas’ field, click the elipsis (…) on the right, and add the schema ‘customUi.xsd’ from the save location of step 1

AddCustomUISchema

Now it’s just up to you to get experimenting.

Write Back To Excel From a Background Thread

As soon as you release control from your application and have some background thread doing long running tasks so that the user can interact with Excel in the meantime, the below is vital.

It’s not a helpful error message but you may get something like the below when editing a cell at the same time as the completion of your background task:

‘An exception of type ‘System.Runtime.InteropServices.COMException’ occurred in NetOffice.dll but was not handled in user code’

With Excel-DNA this is luckily a super simple fix.

Task.Factory.StartNew( () => DoSomeWork() ) 
    .ContinueWith(t => WriteSomethingToExcel());

becomes

Task.Factory.StartNew( () => DoSomeWork() ) 
    .ContinueWith(t => 
        ExcelAsyncUtil.QueueAsMacro(() => WriteSomethingToExcel()));

As soon as the user frees up Excel (i.e. stops editing a cell) then and only then is ‘WriteSomethingToExcel’ performed.

ImageMso Gallery

When adding options to your menu, you’ll need icons.  There are about 9,000 already available icons (yikes!), so you’ll probably find one that is suitable and is consistent with the UX of Excel.  you can also add custom icons.

Built-in icons

You can add a built-in icon to your custom ribbon using the imageMso argument.  For example:

<button id="refreshButton" imageMso="RefreshMenu" size="large" onAction="onRefreshMenu" />

How can you see the available icons?  The best way is a great excel add-in that allows you to browse the full icon gallery.  Install from https://imagemso.codeplex.com/releases/view/116271 – instructions on how to access are in the documentation tab of the same website.  The result?

imageMsoGallery

Alternatively there is an online gallery at http://www.spreadsheet1.com/office-excel-ribbon-imagemso-icons-gallery-page-01.html – however, this seems like a subset of the available icons only, and there is no search function.

Custom Icons

You can also add custom icons using the getImage argument in the CustomRibbon.xml definition.  A walkthrough of this is at https://andysprague.com/2017/07/03/custom-icons-in-excel-dna-custom-ribbon/

CustomRibbon.xml:

<button id="CustomImageButton" label="Custom Image" getImage="GetImage" size="large" onAction="OnPressMe"/>

 

CustomRibbon.cs:

public Bitmap GetImage(IRibbonControl control)
 {
 switch (control.Id)
   {
     case "CustomImage": return new Bitmap(Properties.Resources.CustomImage);
     default: return null;
   }
 }

 

 

Message Boxes

Show a pop-up message box and get user inputs.

</pre>
public DialogResult MessageBox(string text, string caption, MessageBoxButtons buttons, MessageBoxIcon icon)
{
return ShowModal(parentWindow => MsgBox.Show(parentWindow, text, caption, buttons, icon));
}

private static DialogResult ShowModal(Func<IWin32Window, DialogResult> dialogFunc)
{
var parentWindow = new NativeWindow();
parentWindow.AssignHandle(ExcelDna.Integration.ExcelDnaUtil.WindowHandle);

try
{
return dialogFunc(parentWindow);
}
finally
{
parentWindow.ReleaseHandle();
}
}
<pre>

More details, including how to handle this in background threads, at https://andysprague.com/2017/07/03/show-message-boxes-with-excel-dna/

Dynamic Menus

See http://stackoverflow.com/a/34033472, at some point I’ll do a blog with more details on this one but I’ll basically be copying that answer anyhow.  Credit to the original poster Caio will obviously be given 😉

My First Custom Excel Ribbon using Excel-DNA

  • What if we could write plugins for Excel using the power of C# instead of VBA?
  • What if we could target and maintain a plugin that is compatible with multiple excel versions, at the same time?
  • What if we could provide our users with their familiar Excel workflow but add custom options, template generation, database access, and dynamic menus?
  • What if I could create user defined worksheet functions (UDFs) that run fast and asynchronously?

… Enter Excel-DNA.

In this blog I give a tutorial on getting started with Excel-DNA, a project to build excel add-ins using .NET.  By the end you will have a new custom tab in your excel ribbon that contains a button.  I will also show you how to debug this excel plugin by running the project directly from Visual Studio.

All code can be found at https://bitbucket.org/andysprague44/excel-dna-examples/HelloWorld.

What is Excel-DNA?

Simply put, Excel-DNA is an independent open-source project to integrate .NET into Excel.  If you were wondering, the ‘DNA’ stands for DotNet for Applications, as opposed to VBA standing for Visual Basic for Applications.  The full introduction can be found at the home page on https://exceldna.codeplex.com/.   Some getting started instructions can be found at https://exceldna.codeplex.com/wikipage?title=Getting%20Started.

If the above has stoked your curiosity, but you are already tied to a VBA add-in, this blog is not for you.  Instead you need to switch, and for this you should visit the excellent blog at https://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/

Getting Set-Up

Install Visual Studios, and start a new Class Library application called MyExcelAddin.

Using the package manager console (Tools –> NuGet Package Manager –> Package Manager console) run the following:

  • Install-Package Excel-DNA
  • Install-Package NetOffice.Excel

Building the ribbon

To add a custom ribbon we need 2 files – an XML description of the new custom ribbon element, and a class that implements ExcelDna.Integration.CustomUI.ExcelRibbon.

Add these both to the root folder of the project and call them ‘CustomRibbon.xml’ and ‘CustomRibbon.cs’

CustomRibbon.xml:

&lt;?xml version="1.0" encoding="utf-8" ?&gt;
&lt;customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnLoad"&gt;
&lt;ribbon&gt;
&lt;tabs&gt;
&lt;tab id="MyFirstExcelRibbonTab" label="My Custom Tab"&gt;
&lt;group id="HelpGroup" label="My Group"&gt;
&lt;button id="PressMeButton" label="Press Me!" imageMso="FastForwardShort" size="large" onAction="OnPressMe" /&gt;
&lt;/group&gt;
&lt;/tab&gt;
&lt;/tabs&gt;
&lt;/ribbon&gt;
&lt;/customUI&gt;

We also have to add the XML file as an embedded resource.  To do this go to the file in Solution Explorer, right click and go to properties.  Then set the Build Action to ‘Embedded Resource’.

CustomRibbon.cs:

using System;
using System.IO;
using System.Resources;
using System.Runtime.InteropServices;
using Application = NetOffice.ExcelApi.Application;
using ExcelDna.Integration.CustomUI;

namespace MyExcelAddin
{
    [ComVisible(true)]
    public class CustomRibbon : ExcelRibbon
    {
        private Application _excel;
        private IRibbonUI _thisRibbon;

        public override string GetCustomUI(string ribbonId)
        {
            _excel = new Application(null, ExcelDna.Integration.ExcelDnaUtil.Application);
            string ribbonXml = GetCustomRibbonXML();
            return ribbonXml;
        }

        private string GetCustomRibbonXML()
        {
            string ribbonXml;
            var thisAssembly = typeof(CustomRibbon).Assembly;
            var resourceName = typeof(CustomRibbon).Namespace + ".CustomRibbon.xml";

            using (Stream stream = thisAssembly.GetManifestResourceStream(resourceName))
            using (StreamReader reader = new StreamReader(stream))
            {
                ribbonXml = reader.ReadToEnd();
            }

            if (ribbonXml == null)
            {
                throw new MissingManifestResourceException(resourceName);
            }
            return ribbonXml;
        }

        public void OnLoad(IRibbonUI ribbon)
        {
            if (ribbon == null)
            {
                throw new ArgumentNullException(nameof(ribbon));
            }

            _thisRibbon = ribbon;

            _excel.WorkbookActivateEvent += OnInvalidateRibbon;
            _excel.WorkbookDeactivateEvent += OnInvalidateRibbon;
            _excel.SheetActivateEvent += OnInvalidateRibbon;
            _excel.SheetDeactivateEvent += OnInvalidateRibbon;

            if (_excel.ActiveWorkbook == null)
            {
                _excel.Workbooks.Add();
            }
        }

        private void OnInvalidateRibbon(object obj)
        {
            _thisRibbon.Invalidate();
        }
    }
}

This file has a bit of magic in it, but the method descriptions should be pretty self-explanatory.  You are unlikely to need to change the above methods initially.  What we will do is to add new methods to handle events raised from COM elements.  You might have noticed there is an action on the button defined in the XML file called “OnPressMe” that currently does nothing.  So lets make it do something!

First though, let’s take a short side-track and get our new excel add-in working in Excel.

Debugging the add-in from Visual Studio

To view the ribbon in Excel, we could set this up manually.  First build the project, then open Excel and go to File –> Options –> Add-ins –> Manage: Go… –> Browse, and add the dll at MyExcelAddin/bin/Debug/MyExcelAddin-AddIn64.xll.

However, during development this loop is pretty slow.  So let’s set up Visual Studio so that Excel is launched with the add-in automatically attached when clicking Start.  You might have been lucky enough that this was automatic (it was for me).  But if not go the properties of the project (Alt + Enter from Solution Explorer) and choose ‘Start external program’ from the Debug settings and point to your excel location.  Also add ‘MyExcelAddin-AddIn.xll’ to the Command line arguments.

debugsettings

You may also need to change the Build events to do the following tasks in the Post-build event command line (but, you probably do not, especially if you are using .NET framework >= 4.7.x):

xcopy "$(SolutionDir)\packages\ExcelDna.AddIn.0.33.9\tools\ExcelDna.xll" "$(TargetDir)MyExcelAddin-AddIn.xll*" /C /Y
xcopy "$(TargetDir)MyExcelAddin-AddIn.dna*" "$(TargetDir)MyExcelAddin-AddIn64.dna*" /C /Y
xcopy "$(SolutionDir)\packages\ExcelDna.AddIn.0.33.9\tools\ExcelDna64.xll" "$(TargetDir)MyExcelAddin-AddIn64.xll*" /C /Y
"$(SolutionDir)\packages\ExcelDna.AddIn.0.33.9\tools\ExcelDnaPack.exe" "$(TargetDir)MyExcelAddin-AddIn.dna" /Y
"$(SolutionDir)\packages\ExcelDna.AddIn.0.33.9\tools\ExcelDnaPack.exe" "$(TargetDir)MyExcelAddin-AddIn64.dna" /Y

Now click ‘Start’.  Excel should load up and have the plugin attached.  You will need to click ‘Enable Plugin’ on launch.

You should now see your new ribbon element in Excel!

helloworldexample1

Doing something useful

The last step is to hook up the button to perform an action.  In the spirit of all beginner tutorials the world over lets get it to write “Hello, World!” to cell A1.

To do this, we need to add an event handler for the action to CustomRibbon.cs.  Add the following snippet to the end of the class:

public void OnPressMe(IRibbonControl control)
        {
            using (var controller = new ExcelController(_excel, _thisRibbon))
            {
                controller.PressMe();
            }
        }

Now, let’s add our controller.  Add a new class to the project called ExcelController, and copy in the following code:

using System;
using Application = NetOffice.ExcelApi.Application;
using ExcelDna.Integration.CustomUI;
using NetOffice.ExcelApi;

namespace MyExcelAddin
{
    class ExcelController : IDisposable
    {
        private readonly IRibbonUI _modelingRibbon;
        protected readonly Application _excel;

        public ExcelController(Application excel, IRibbonUI modelingRibbon)
        {
            _modelingRibbon = modelingRibbon;
            _excel = excel;
        }

        public void PressMe()
        {
            var activeSheet = _excel.ActiveSheet as Worksheet;
            activeSheet.Range("A1").Value = "Hello, World!";
        }

        public void Dispose()
        {
        }
    }
}

Now, restart your application and click the button, voila!

helloworldexample2