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.
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:
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 fromproject.config
.
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 |
|
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 |
|
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.