Skip to content

Workbook data context

Download the demo workbook from here

By default, workbook apps will have a WorkbookDataContext type registered in the project.config file.

Workbook data context

This allows scripts and code in the workbook app to easily interact with data from the workbook.

The default WorkbookDataContext is good enough in many situations, but does have some limitations. Since Excel does not have a mechanism for specifying the data type of a column, the context guesses the type based on the column contents and this guess might not be what you want (e.g. you might want an int? instead of a double?). Additionally, since Excel does not have a good mechanism for defining table relations, the built-in context will not be able to detect any relations between the tables, so the strongly typed accessor classes will not have any navigation properties.

Custom WorkbookDataContext

In order to solve the above limitations, we can create our own data context type that derives from the WorkbookDataContext class and specifies column types and table relations.

To do so, add a workbook data context script from the context menu of the project:

Add workbook data context command

This will create a new data context definition script and scaffold the code for the new data context class.

Adding this script will also immediately remove any existing WorkbookDataContext entry from project.config.

Workbook data context script

The Configure method should be used to describe the schema of the data context by specifying column types and table relationships. The scaffolding will populate the Configure method with code that specifies column types based on the best guess of the default data context. It is up to the user to change this code as they see fit.

Customizing column types

To customize column data types, use the schema argument in the Configure method.

1
2
3
4
5
6
// get the TableSchema for the Departments table
schema.ConfigureTable("Departments")
    // set the type of the Id field to int
    .ConfigureColumn<int>("Id")
    // set the type of the Name field to string
    .ConfigureColumn<string>("Name")

The ConfigureTable method returns the TableSchema for the specified table. The ConfigureColumn<T> method is then used to configure the data type for a particular column. This method returns the TableSchema object again to allow further chained calls to ConfigureColumn<T> for other columns.

Customizing table relations

Specifying table relations allows the project to generate navigation properties when generating strongly typed wrappers for the data context.

To register a relationship from one table to another use the AddRelation method.

1
2
3
schema.ConfigureTable("Departments")
    // each department can have many employees
    .AddRelation("Id", To.Many, "Employees", "DptId", "Members");

As with the ConfigureColumn<T> method, this method also returns the TableSchema object to allow further chained calls.

Running the data context script

When this script is executed it will automatically:

  • generate a new dll in the lib folder containing the compiled data context type
  • register the newly generated type in the project.config file.

The containing project will then detect the new data context and react by updating the dll with the strongly typed accessors. At this point, the project is using the new data context which can be seen by C# scripts and app code.

Workbook data context demo