Workbook data context
Download the demo workbook from here
By default, workbook apps will have a
WorkbookDataContext type registered in the project.config file.
This allows scripts and code in the workbook app to easily interact with data from the workbook.
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.
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:
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
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
1 2 3 4 5 6
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
1 2 3
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
libfolder containing the compiled data context type
- register the newly generated type in the
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.