Creating an Excel NPOI DataSource

Use the Projects Tree or the Project 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 Projects Tree. The Project, 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.

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..

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)

DataView have standard and custom configurations areas. This section describes how to setup SQL DataView custom area. Refer to the standard section for information about setting up Parameters, Data Pivot or Calculated Columns.

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 required you can Configure Filters to include or exclude rows based on their values, or lack thereof
• If the row above the first row with data contains the headers, check the option My Data Has Headers, optionally specify the offset if the header row is not immediately above.
• Check the Allow User to Add Rows or Delete Rows boxes to allow the creation and deletion of row respectively. You can chose which columns to include in each action from the grid.
• 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.

• 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
• When the creation and/or deletion of records is allowed, you can chose which column to include in the form from each action
• 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

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