Creating an Excel NPOI DataSource

Use the Sources Manager Tree or the DataNode Configuration dialogue to Create or Edit a new Excel NPOI DataSource.

NPOI DataSource can also be configured by dragging and dropping the target file directly onto the Sources Manager Tree. The DataNode, DataSource and DataView will be automatically configured to point at the first sheet of the workbook, with all columns read only.

Configuring the Excel NPOI DataSource

To setup a NPOI Excel DataSource simply provide the workbook full path, with extension. It can be .xls or .xslx.

TIPS
	If you browse to a file located on a network drive the workbook path will be resolved as a UNC path.

When using the DataSource to write data back to the Excel file, you can choose to create a Backup File. The NPOI external library used by BimSens is in development and using it to write Excel file can lead to corrupted workbooks. To ensure you do not lose any data keep the Backup option activated. The backup option will create 2 files:

One file with extension .bak0, created the first time an edit is made onto the DataView, and never modified.
One file with extension .bak, created the first time an edit is made in a BimSens session. This file is overridden every time you start a new session and edit at least one record.

Uncheck the Evaluate Formulas option if your workbook contains formulas that are not supported by the Excel NPOI DataSource. Support for Excel syntax is progressively added to the NPOI library and the Excel NPOI DataSource is updated accordingly, but there are still functions that are not supported..

warning
	When a non-supported function is detected by BimSens or when the option Evaluate Formulas is unchecked, the DataView is forced to read only

Get Change Notifications

NPOI Excel DataSource support change notification: the DataView can be automatically refreshed to show modification to the workbook that have been made outside of BimSens or by another user. To enable notifications check the corresponding option and choose a Waiting Timer.

Waiting Timer are used by BimSens to wait for the file to be available again: when the file is open by a programme requiring exclusive locks on the file such as Excel, BimSens cannot refresh the DataView until the file is released (closed in Excel). BimSens will wait the specified amount of time for the user to close Excel after having saved its changes before trying to refresh. If the file is still open after that time, BimSens will raise an error as it cannot access the file.

For BimSens to refresh the data when notification are received, you must activate the automatic refresh option from the Option dialogue, Refresh page.

Configuring the Excel NPOI DataView(s)

You can load several DataViews from the same workbook each looking at different sheets or subset of the data of the same sheet. To create a new DataView, from the DataSource Configuration dialogue click the New DataView button. Refer to the section Configuring a DataView for more ways to create and edit DataViews.

An Excel NPOI DataView can be configured to look at any sheet of the workbook:

Specify a Name for the DataView
Specify whether to use the First Sheet of the workbook or a Sheet with a specific name
Specify the index of the First Row with Data. The first row of the sheet is the row 0. You can click the button Pick From Sheet to open a preview of the selected sheet and select the row directly from there.
If the row above the first row with data contains the headers, check the option My Data Has Headers
Click the button Load Columns to load the columns of the sheet. This will populate the grid with the column found under the specified configuration.
From the Grid you can:

exclude columns from being loaded in the DataView by unchecking the box in the column Load.

TIPS

The number of columns being loaded has a direct impact on the performance of the refresh operation, especially columns calculated by a Formula; try only loading the necessary columns.

For instance when using several columns to calculate a result in multiple steps, only select the column of the final result.

specify the type of data contained in each column. See the section About Data Types below for more information.
specify whether the data in that column can be edited or not. See the section Enabling Editing of the File below for more information
specify values that the user is allowed to use when editing the column. See the section Defining Allowed Values for more information

Click the OK button to finish the configuration.

About Data Types

Unlike Excel, BimSens only accepts a single data type per column and it will try to use the best type to display the data for each column. If no compatible type is found it will default back to String (Text) which can display all values.

You can force the data type to use by choosing a type from the column Display As:

Auto: BimSens uses the Suggested Type
Text: supports String type which is the most generic
Integer: supports integer numbers, without decimals
Money: supports decimal numbers with exact values
Double: supports all numbers with floating point precision. This is the default storage type of Excel for number and dates
Date: supports most date formats
TrueFalse: supports a boolean value like TRUE / FALSE

TIPS
	By default date are stored as double in Excel and BimSens will only suggest the date type if the cells are formatted for dates. In other situations you will have to force the Date type.

warning
	When you Force the type of a column, all values from that column that are not of the correct type will be ignored and shown as blank when the data is loaded.

Editing of the data from the DataView

You can set a column to be editable via BimSens simply by checking the Editable box.

Consider having the Backup File option of the DataSource active when using the NPOI DataView in Write mode.

Defining Allowed Values

By default, when editing a column, the user can enter any value of the proper data type. By defining Allowed Values for a column, you restrict the user input and the editing form will display a drop down with the values you defined only.

To define Allowed Values for a column:

Click the <None> link in the Values column
in the Allowed Values dialogue, select whether you want to explicitly define values, or use the sheet Data Validation
In Explicit mode:

Enter the required values in grid. You can specify a value that needs to be of the proper type in the Value column
Optionally you can add a different display name in the Display Value column. The user will see the Display Value, but the actual Value will be written in the sheet

In Data Validation mode, the values will be dynamically loaded from the sheet. The Data Validation of the first cell of the sheet column is used for the entire column in BimSens, regardless of other Data Validation conditions in the sheet. The first cell is defined as the cell of the column that is in the First Row with Data as specified above. The supported types of Data Validation are 'List of comma delimited values' and 'List referencing a range of the same sheet'.

Select a frequency to reload the Data Validation:

Once: the values are loaded once, the first time the sheet is refreshed, and kept in cache for the duration of the BimSens session
OnViewRefreshed: the values are cleared and reloaded every time the view is refreshed
Always: the values are not cached, and reloaded from the sheet every time they are needed