An important part of using the native Excel connector is to read information from the Excel file. To read a range of cells and save their values in an collection of your Bizagi Model use this action.
If you want to read an individual cell from your Excel use the Read action.
The Read range actions lets you read a range of cells and store it as a collection in your Data Model. You can identify this action by its orange border on the third panel.
Give this action a meaningful name and select the sheet where the action will take place.
With a Read Range Action you can read multiple columns in a single range. You have to define an output for each column of the range. Each column on the range is associated with an attribute of your collection. To set up this action you must configure the range and the column definition.
In the Range definition panel you can configure your range either by using coordinates or using a named range.
To identify a range with its coordinates select the option Coordinate range and provide the coordinate of the top left cell of the range and of the bottom right cell.
To identify a range with its name select the option Range name from the dropdown list and select the name of your cell from the available options. The name must already exist in your Excel file to be shown in the options.
The panel automatically calculates the number of columns you must configure in both cases.
Afterward configure the columns in the Column definition panel. You must have a definition for each column in your range. You can add a new column definition by clicking the button.
If you want to automatically create the expected number of columns click Autofill columns.
For each column definition you must provide the following parameters:
•Name: Name for the column. This name will let you identify this particular input in the mapping
•Type: The data type of the column. Its possibles values are: String, Boolean, Byte, Date, Decimal, Double and Integer
If your range has empty rows the connector creates empty records in your collection in Bizagi.
When you map the inputs of the connector using the advance view you can map the following properties for the Read range action:
•Sheetname: With this field you can select your sheet where the action is executed dynamically. Use the name of the Sheet as it is shown in your Excel file.
•Range: With this field, you can select the range from where you want to read the information. If the cell is configured to receive a coordinate, you must provide a coordinate. In the same way, if you configure it to receive a name, you must provide an available cell name.
In this example we will use the following Excel file:
In this simple Excel file we have a list of names in the range from cell A2 to cell A12 two empty ranges (B2:B12 and C2:12) and a column in which each cell calculates the percentage difference of the two previous columns. Finally the range B2:B12 is named SystemList and the range A2:A12 is named ClientList.
On this example, the connector will Read information from the ranges A2:A12 and D2:D12 when executed. Since the ranges are not adjacent to each other we have to configure two different actions.
For the first action in the Range definition select Range name and from the dropdown select ClientList(A2:A11).
In the Column definition add one single column and name it as Clients and make its type Decimal.
For the second action in the Range definition select Coordinate name and write D2:D12.
In the Column definition add one single column and name it as CalculatedDiff and make its type Decimal.