Components

Components are mostly used in workbook apps to automate the workbook. A workbook app can define any number of components, each controlling its own (arbitrarily defined) part of the workbook.

Components have the following characteristics:

• They can accept dependencies via dependency injection.
• They are data bound to a data context (usually a WorkbookDataContext).
• Public properties of a component can be data-bound to cells in Excel via the [Bind] attribute as well as to tables via the [BindTable] attribute.
• The methods of the component react to events coming in from Excel (e.g. click of a button). This is configured using the [EventHandler(eventName)] attribute.

Example

For example, the following component reads a searchText parameter from a cell in Excel. Each time this cell's value changes, the component updates a table called People (by putting a star next to each name that contains the searchText), and writes a message into a cell named messages:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using QueryStorm.Core;
using static QueryStorm.Core.DebugHelpers;

namespace Project
{
    public class Component1 : ComponentBase
    {
        [Bind("searchText")]
        public string SearchText { get; set; }

        [BindTable]
        public PeopleTable People{ get; set; }

        private string _Message;
        [Bind("messages")]
        public string Message
        {
            get => _Message;
            set { _Message = value; OnPropertyChanged(nameof(Message)); }
        }

        [EventHandler("searchText")]
        public void Test()
        {
            Message = $"Searched for '{SearchText}' at {DateTime.Now.ToShortTimeString()}";

            People.ForEach(t =>
            {
                // note that we have strongly-typed access to data in tables
                string nameWithoutStar = t.FirstName.TrimEnd('*');
                if(t.FirstName.IndexOf(SearchText, StringComparison.OrdinalIgnoreCase) >= 0)
                    t.FirstName = nameWithoutStar + "*";
                else
                    t.FirstName = nameWithoutStar;
            });

            People.SaveChanges();
        }
    }
}

Here is the resulting behavior:

The important thing to note is that no part of the code interacts with Excel directly. The component only accesses its own properties, and the binding infrastructure takes care of communicating with Excel.

Bindings

Bindings allow you to read and write values from Excel without having to access Excel objects directly or subscribe to their events to listen for changes.

Bindings and the data context

It's important to note that components aren't bound to Excel directly; they don't actually know anything about Excel. Instead, they are bound to a data context. For workbook applications, the default data context happens to be a WorkbookDataContext which exposes data and events from the workbook. For more information about the data context, customizing it and replacing it, click here.

Binding to cells

Component properties can be bound to cells in Excel by using the [Bind(nameOfCell)] attribute.

[Bind("searchText")]
public string SearchText { get; set; }

By default, bindings are bi-directional. When the user changes the value of a cell, any property that's bound to the cell also gets updated to the new value.

On the other hand, when a component changes the value of one of its properties, any cells that are bound to that property also get updated to the new value. For the binding infrastructure to detect the change, however, the component must raise the PropertyChanged event by calling the OnPropertyChanged method. This is typically done in the setter of the property, for example:

string searchText;

[Bind("searchText")]
public string SearchText 
{
    get => searchText; 
    set 
    {
        searchText = value;
        OnPropertyChanged(nameof(SearchText))
    } 
}

Having to call OnPropertyChanged in the setter unfortunately means we can't use auto-implemented properties, but we can make typing this boiler-plate code easier in two ways:

1. by using the propn snippet which scaffolds a property with the OnPropertyChanged call in the setter
2. by using the "convert to full property" code fixer (Ctrl+.) on the auto-implemented property, in which case we only need to add the OnPropertyChanged call

Also, if changes only need to flow from Excel into the component, and not in both directions, having an auto-implemented property is sufficient.

Binding to tables

A component's property can also bind to an Excel table by applying the [BindTable(tableName)] attribute.

[BindTable("People")]
public PeopleTable People{ get; set; }

Once we define the property, we can use it to read and write data in the table.

[EventHandler("searchText")]
public void Test()
{
    // modify table rows
    People.ForEach(t =>
    {
        string nameWithoutStar = t.FirstName.TrimEnd('*');
        if(t.FirstName.IndexOf(SearchText, StringComparison.OrdinalIgnoreCase) >= 0)
            t.FirstName = nameWithoutStar + "*";
        else
            t.FirstName = nameWithoutStar;
    });
    // don't forget to call SaveChanges()
    People.SaveChanges();
}

It's important to note that any changes made to the table need to be explicitly saved by calling SaveChanges(). In C# scripts, SaveChanges() is called automatically after each run, but in components this call needs to be explicit.

Strongly-typed table access

At this point, you might be wondering where the PeopleTable class in our example came from. The answer is: it was generated automatically. Each time the structure of the tables in the workbook is changed, the project dynamically generates a dll with types that offer strongly typed access to the tables. This dll is then added to the lib folder of the project.

The data-binding mechanism then uses the types in this dll to wrap the Excel tables and set the data bound properties. For more information about the data context and strongly typed access to tables, see the data context section.

The IBindable interface

The data-binding mechanism was designed specifically with components in mind, since components are designed to add behavior to a data context. However, you can make use of data-binding in any object that is created by the IOC container. For example, you could define a ribbon class that updates a workbook table. To allow the ribbon class to use data-binding, simply implement the IBindable interface. This is a marker interface with no members. Its only purpose is to instruct the app to enable data-binding for the class that implements it. Once the ribbon implements this interface, it can use data binding just like components can:

public class MyRibbon1: RibbonBase, IBindable
{
    [BindTable]
    public DepartmentsTable Departments { get; set; }    
}

Handling events

Components can react to events coming in from the workbook by defining public void methods marked with the EventHandler attribute.

[EventHandler("searchText")]
public void Test()
{
    // ... handle the "searchText" event
}

Multiple EventHandler attributes can be applied to a method to allow it to handle multiple events.

Currently, workbooks can emit the following events:

• ActiveX button was clicked
• Range value has changed
• An event was sent from VBA

Technically, the WorkbookDataContext emits the events, not the workbook.

Handling button click events

When a method needs to handle the click of an ActiveX button, the following syntax should be used for the event name: {sheetName}!{buttonName}.

For example, to handle the click of an ActiveX button named MyButton located on a sheet named Sheet1, the method should be decorated as follows:

[EventHandler("Sheet1!MyButton")]
public void MyEventHandlerMethod()
{
    // ...
}

ActiveX buttons have several issues (occasionally require double-clicking, visual artifacts when using multiple screens), so it is advisable to use regular Excel buttons and VBA instead (as described below).

Handling range value changes

When a method needs to be called every time a range changes, the name of the range should be used as the event name:

[EventHandler("nameOfTheCell")]
public void MyEventHandlerMethod()
{
    // ...
}

Sending and handling events from VBA

Events can be sent from VBA code into the workbook app. One particular reason this might be useful is that it allows using regular buttons to send events, instead of ActiveX buttons which have known issues in Excel.

To send an event from VBA, use the QueryStorm.Runtime.API class, like so:

Sub Button1_Click()
    Call CreateObject("QueryStorm.Runtime.API").SendEvent("myEvent")
End Sub

Instances of the QueryStorm.Runtime.API class are lightweight objects that forward messages to the QueryStorm Runtime. They carry no state and do not need to be cached.

Handling the event is simple:

[EventHandler("myEvent")]
public void MyEventHandlerMethod()
{
    // ...
}