Excel is trusted by many companies worldwide to build calculating tools or to reflect a certain business logic. It is also used by thousands of employees to perform basic or complex calculations that would otherwise take a lot of time and effort. Bizagi acknowledges this reality and features its the out of the box Excel Connector to support this tool natively.
The Excel Connector is a way to integrate those tools that companies already rely on for their operations with Bizagi. Using this connector, you can upload an Excel file to Bizagi. This file contains all the operations and formulas already pre-configured on it, and the connector provides a set of operations to read and write on the file. That is, with the Excel Connector, you can write information in an Excel file directly from Bizagi, let Excel handle the calculations and then read the results to store them in your Bizagi's data model.
Another useful scenario to use the Excel Connector in Bizagi is to replace the usage of large (or very simple) Parameter Entities. You can configure an Excel Connector with a file containing all the possible values of an entity. With this, you no longer have to manage your Parametric Entities from your development or production environment, delegating some of your business logic to Excel and making the Deployment process easier.
In this article, you can find the description of all the operations that the Excel Connector can perform, a step by step guide on how to configure it, all the restrictions and important notes around it.
Creating an Excel Connector
Bizagi Studio supports the Excel Connector natively. To create a specific connector that suits your business case, head to the Expert view and locate the External systems option.
In the options that are shown, locate Excel under the Connectors branch.
Then, right click on Excel and click Add Excel Connector. Alternatively, you can first click Excel and then click Add Excel Connector on the ribbon.
The connector creation wizard appears.
On the image shown above, you can see the four different panels that appear in this wizard:
1.In this panel, you must select the Excel file you want to upload. When you click the Choose an Excel file button, a file explorer appears. Select the file that contains the Business Logic and calculations that you want to automate in Bizagi with this connector.
2.On the second panel, you are asked to provide a Name and a Description for your connector. This is useful to differentiate between Excel Connectors when you want to use a specific one on a process and you need to identify it from the list of available Excel Connectors.
3.The third panel only gets active when you have already selected an Excel file. In this panel you can start adding the sequence of actions that the Excel Connector will perform over the specified file each time it is called.
4.The fourth panel is where you configure the details of each of the actions of the connector. On this panel you can specify from which cell or range of cells you want to read, or in which cell you want to write. This is the main panel of the wizard.
In this example, let's create an Excel Connector that performs a simple calculation based on information it receives from a Bizagi process.
The first step is launching the Wizard an then clicking the Choose an Excel File button. Select the file you want to perform operations on. In this case we are going to use a simple Excel file which takes two values and calculates their relative difference:
The uploaded Excel file, for this specific example, contains three input cells and one calculated cell:
•The A2 cell receives a Client name.
•The B2 cell receives the month closure amount reflected on the company's system.
•The C2 cell receives the month closure amount as per informed by the sales team.
•The D2 cell calculates the relative difference between the amounts for a month closure analyst to be able to approve or reject the month closure of the client. The calculation performed by the cell is: ((Sales amount - System amount) / Sales amount)
You should only keep on the Excel file the necessary fields for it to operate. The previous image is an example on how the spreadsheet works, but the uploaded file to Studio for the connector configuration has no values on the A2, B2 and C2 cells. The D2 cell contains the formula shown on the image.
Configuring the connector's actions
The next step is to define the sequence of actions that the connector will follow each time it is executed. To add a step to the sequence , click the Add action button on the third panel on the wizard.
As you can see, a new write action is added. This is the default action and it can be changed from the main panel. On the third panel of the wizard you can see the sequence of actions that have been configured for the connector. On the main panel of the wizard you can see the configuration options for the current action.This actions are executed in the sequence order shown in the third panel of the wizard. If you want to change the order of execution of the connector's actions, just drag and drop an action into its corresponding position.
Bizagi's Excel Connector can perform three different types of actions, and you can select the type of action you want to configure in the Action type control, located at the main panel of the wizard. You can also select the corresponding Sheet of the file in which you want the action to take place.
The Write action allows you to input information from your Data Model into a specific cell of the spreadsheet. You can identify this action by its green border on the third panel.
With one single Write Action you can configure multiple writing operations. For each cell that you wish to write to, you need to provide a name, a data type and a cell coordinate. You can add an input by clicking the Add input button.
On our example, the connector will Write information into the A2, B2 and C2 cells when executed.
The Cell name and Type are taken into account when you are configuring the connector execution, where you are asked to map an attribute from your Data Model to each input.
The Read cell actions allows you to read the content of a specific cell of the file and store it in one of your Data Model's attribute. You can identify this action by its light-blue border on the third panel.
With a Read cell action you can only read the contents of one cell, thus if you want to read multiple cells you would have to configure as many of these actions as cells you want to read. Alternatively, if the cells you want to read are clustered in a range of cells on the file, you can use the Read range action instead.
In the Cell definition panel, you need to configure the cell coordinates and the output data type. This information is used to store the data of your Excel file in the Data Model.
In our example set the cell coordinate to D2 and the corresponding data type to decimal.
Once again this is used when mapping the attributes of the connector for its execution.
The Read range actions lets you read a range of cells as a collection and store it in your Data Model. You can identify this action by its orange border on the third panel.
To configure this action, provide the starting (top left cell) coordinate and the ending (bottom right cell) coordinate of the range. Provide this information in the Range definition section.
Since this action performs a reading operation, you need to configure the results in the Cell definition section of the configuration. Here you give a name and a type to each column in your range. To add a new column definition click the Add output button.
You have to define an output for each column of the range. This means that if you want to read four columns (as it is configured on the image above) you need to define four outputs.
This outputs are returned as a collection in which each column is an attribute (with the name and type defined in Cell Definition) that can then be mapped the corresponding attributes of your Data Model.
In our example we will not use Read Range actions.
Using your configured connector on a process
Up until this point we have defined the actions that our connector will perform whenever it is executed, and the logical order of execution of those actions. However, we haven't used the connector in our project.
There are two different ways to start the execution of a configured connector in a process: as a Form action or as an Activity action.
Configuring the execution of a connector from a form
To configure the execution of an Excel Connector from a form, go to the Actions and validations option, where you can define the set of conditions that have to be meet for your connector to be executed. Define such conditions in the When section of an action, and in the Then section select the option to execute Excel connector.
As soon as you click the Argument button, a Wizard to configure the integration with the connector pops up.
Configuring the execution of a connector from an Activity Action
The second option to configure the execution of an Excel Connector on a process is from an Activity Action. Go to the fourth step of the Process Wizard and then select the activity on which you want to trigger the execution of your connector. After you click the activity, the Activity Actions window of the task appears. Click on the plus icon of any of the Activity types (On Enter, On Save or On Exit) and then click Excel connector to launch a Wizard to configure the integration between the connector and your process.
To help you configure your connector's execution, a Wizard similar to the one used with Connectors appears.
In this window, select the specific connector you wish to use (you can have multiple Excel Connectors configured for a project) and then click Next.
The Action inputs mapping window appears. Map the corresponding attribute from your Data Model to each of the connector's inputs.
If you are working in an environment with localization active, you must be very carefull with what you expect to send to your Excel. When this option is active parametric attributes store different values in each language. This connector will always send the value in the default language regardless of the localization configuration.
When you are done configuring the inputs for the connector's execution, click Next.
A new mapping window appears. This time map the output values of the execution of the Excel Connector to its corresponding attribute on your Data Model.
When you are done mapping the outputs of the connector to your Data Model, click Finish.
At this point you have an Excel Connector configured and ready to be executed! Whenever you get to the point you configured the connector to be executed at, it will perform the sequence of tasks you programmed it to do and retrieve the results. This is evidenced in the image below.
In the Month closure request task, the user inputs the Client, Sales amount and System amount values; upon clicking the Save button, the Excel Connector is invoked and inputs the three fields into the spreadsheet, which calculates the result that is finally read by the connector and saved into the data model in the % Diff attribute.
There are some important aspects that need to be taken into account when dealing with Excel Connectors:
•An Excel Connector may have an impact on the performance of a case. This depends on the size of the Excel file that is used and the capacities of the server where Bizagi is running. We recommend using files smaller than 14 MB.
•When writing values to an Excel file, the data type and format configured on the specific cell on Excel prevails over the data type configured in Bizagi.
•The allowed file extensions to be used with this feature are: XLS, XLSX and XLSB.
•Excel Macros cannot be used with the Excel Connector. This means that the Excel Connector does not support reading results calculated by a Macro.
•The Excel Connector is built on top of the Aspose Cells library (version 18.11), this means that all restrictions of the library also apply to the Excel Connector. Take into account that Aspose Cells counts with many features from which a reduced set of them were considered to develop the connector; this means that a feature available in Aspose Cells might not be available to use in the Excel Connector. In this website you can find a list of what Aspose Cells does support.
•We do not recommend using this connector to load large volumes of data.