Excel and WPF

I do a lot of programming.  It’s what I do for a living, and it’s also what I do to relax.

I know, for many of you, that probably sounds really weird; but I discovered a few years ago that I was dreading waking up in the morning.  That was also the time that I realized that what I wanted to do with my life was, simply put, create.

So, I quit my full-time job, which I hated, took a part time job to pay the bills, and then started my own freelance programming business.

A lot of what I’ve been doing has involved writing programs which are integrated with Excel.  That was all fine and good while I was writing in VBA (which was slower than molasses on Pluto), or using Windows Forms (which was fine, but tended to be very restrictive in what I could do), but, when I made the leap to WPF and XAML, I ran into a very annoying problem:

If you create a window with a textbox (or textarea) in it, display the window and then try to type something in the textbox, Excel snatches the focus away from the window and you discover that you’re typing in a cell on the spreadsheet again.

Very annoying.

So, I did some research, and I came up with a way to solve this little problem; and I thought I’d share it with you.

I like to use the MVVM approach, which places most, if not all, of the UI processing within a ViewModel, and leaves the back-end code for the View object extremely simple.  I’m not going to get into the MVVM issues here, except to say that the code you’re going to see follows the MVVM model.

You will also see that I manually create and link the ViewModel to the View from the main code.  That’s by design.  I prefer to retain control over that from the program, because then I know how to directly access the ViewModel, and then I can also display, hide, and close the View when I need to.  I’m sure that some of you may cringe when you see that.

Oh well.  If you’ve got a better idea, feel free to make a comment.

So here we go:

Let’s make a very basic assumption that you’ve got a program on the go, running nicely within an object you’ve created to handle the main operations.  Here’s we’re just going to wrap everything inside an arbitrary class, which, because I happen to like Disney’s Tron, we can call the Master Control Processor, or MCP.

What we’re going to do is put the view on a different thread, wrapped nicely within a generic class which then can be re-used. This should save you some coding time down the road…

using System;
using UserInterface;

// etc.
namespace MasterControlProcessor {
    public partial class MasterControl {
    
        MainViewModel mvm;                 // The Main View Model
        MainView mv;                       // The Main View
        
        UserInterfaceThread uit  // The thread object that holds the user interface.
    }
}

There are many different approaches at this point that you could take, as to where and when to initialize the user interface thread and how many of them you will need. It all depends on what you’re doing of course. For my purposes, I’ll set the UIT up inside the constructor for this object.

    // Constructor for MasterControl defined above.

    public MasterControl() {

        mvm = new MainViewModel();
        
        uit = new UserInterfaceThread(mvm);

    }

As you can see, I’m putting passing the MainViewModel object that I just instantiated into the User Interface Thread.

So, let’s pause here and have a look at how I’ve designed UserInterfaceThread object, which is a wrapper that holds the thread itself, and holds all the various methods for managing aspects of the thread.

using System;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows;
using UserInterface.View;

namespace UserInterface {

    class UserInterfaceThread where T : Window, new() {

        public Thread uiThread;
        private object dataContext;

        private T win;

        public UserInterfaceThread(object _datacontext) {

            dataContext = _datacontext;
        }

I discovered fairly quickly that I needed to hold onto the viewModel object. This is because the main part of the program interfaces back and forth with the viewmodel, which, if the user closes the window at some point in time, could potentially cause strange things to happen, or crash the program. If I hold onto the ViewModel like this, then we can work with it even while the window is closed. This will come together better in a few lines.

What we want to do now is create a method for building the UI. It will need to sit inside a single-threaded apartment, and we will configure it to be a background thread:

        private void buildUI() {
            
            uiThread = new Thread(WindowWorker);
            uiThread.SetApartmentState(ApartmentState.STA);
            uiThread.IsBackground = true;
            
        }

Now, we can start putting things together and create the WindowWorker method, which, when called, will create a new T object known as win, set up its datacontext, and show it, while running within this new thread:

        private void WindowWorker() {

            win = new T();
            (win as View.View).closingWindow += new EventHandler(windowCloseEvent);
            win.DataContext = dataContext;
            win.Show();
            System.Windows.Threading.Dispatcher.Run();
        }

Let’s also set up the handler for the windowCloseEvent. This will make sure that the thread handles the situation where the user closes the window, by nullifying the win object (and letting the garbage collection system clean it up in due time) and shutting down the actual thread. This leaves the UserInterfaceThread object sitting there with a placeholder object pointing at nothing until it needs a UI object (and corresponding thread) again.

        private void windowCloseEvent(object sender, EventArgs e) {

            win = null;
            uiThread = null;
        }

Finally, let’s create some helper methods to start and stop the thread when called upon to do so…

        public void startThread() {

            if (uiThread == null) {

                buildUI();
            }

            uiThread.Start();
        }

        public void stopThread() {

            if (uiThread != null) {

                win.Close();
            }
        }
    }
}

Now, the beauty of this approach is whenever you need to open up the window, all you need to do is this:

    uit.StartThread();

and, when you are finished with it, if the user closes it themselves, then the thread shuts itself down, while if, for whatever reason you need the main program to handle something sent as an event from the window (such as the user has pressed a button marked “Cancel” which gets tossed to the main program from the ViewModel as an event (and tossed to the ViewModel as an ICommand), all you need to do is:

    uit.stopThread();

Over in the XAML, ViewModel and View side of things, I’ve also put together a re-usable structure that is portable as well:

First, let’s create a View wrapper object which inherits from Window, and set up an event handler to deal with the Window_Closing event. This will throw an event of its own, if it exists, so the main program, can deal with anything it needs to deal with.

using System;
using System.Windows;

namespace UserInterface.View {
    public class View : Window {

        public event EventHandler closingWindow;

        private void Window_Closing(object sender, System.ComponentModel.CancelEventArgs e) {

            if (closingWindow != null) {

                closingWindow(null, EventArgs.Empty);
            }
        }
    }
}

In the same way, let’s also do a ViewModel wrapper object to hold the PropertyChanged event handlers. This way, we can create whatever other more specific viewModels we like, and have them inherit from this class, meaning we don’t have to constantly rewrite the same code. (More experienced programmers, I know you already get this, so stay with me…)

using System.ComponentModel;

namespace UserInterface.ViewModel {
    public class ViewModel : INotifyPropertyChanged {

        #region INotifyPropertyChanged

        public event PropertyChangedEventHandler PropertyChanged;

        protected void OnPropertyChanged(string propertyName) {

            PropertyChangedEventHandler handler = PropertyChanged;

            if (handler != null) {

                handler(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        protected void OnPropertyChanged(object sender, PropertyChangedEventArgs e) {

            OnPropertyChanged(e.PropertyName);
        }

        #endregion INotifyPropertyChanged
  
    }
}

Don’t forget your RelayCommand object (which I’ll let you figure out yourselves).

Now, when we create the actual window in XAML, all we need to do is define the UserControl as a View rather than a window, like this:


    <!-- XAML stuff goes here -->


With this approach, you can implement XAML and WPF on a VSTO Add-In, and deal with the annoying problem of textboxes losing focus, and it will save you a bunch of time in coding it, since all the repetitive threading stuff is coded once in a generic class for you.

About Steven Britton

Steve is a freelance programmer, partial billionaire, dad, Recovering Atheist, Conservative, and occasionally prolific blogger.