The "Excel" Component Overview
The Excel Component is designed to read and/or write Excel '97(-2007) files. This component needs to be fed with some property values in order to work correctly. The combo of "Mode" and "Source Type" properties is the key set of properties for this component since it defines how it will work when reading or writing and also which kind of object to use.
There are two values of interest for the "Mode" property: the "Read" value lets you get the content of an Excel file, while the "Write" value lets you write data into an Excel file. The "Source Type" defines if the source Excel file will be provided either as a property at design time using the "Excel File" property, or as an input parameter at runtime using the dynamic URL or as a BLOB file. If the "Mode" is "Write", then it is possible to specify the resulting file format: XLS (default) or XLSX.
The "Cell Info" Sub Component
The Excel Component cannot work without the "Cell Info" sub component. "Cell Info" tells the Excel Component where it must read or write into the Excel spreadsheet. "Cell Info" requires many properties to be configured. The "Name" property represents the name of the "Cell Info" but can also be used to set the name of the Cell if the Excel Component is in "Write" mode and the "Use Name As Header" property is checked. If "Use Name As Header" is checked, that sets the name of the "Cell Info" as a column header. It works only if the Excel Component is working in "Write" mode. "Type" & "Content Type" properties are used to determine the type of the extracted data and to determine the rendition of the cell value. The "Data Format" property defines the format to use to present the cell value to the created Excel file or in the JSP page. Besides these general-purpose properties, there are the Excel-related properties. The "Sheet" property is the 1-based number or the name used to identify the sheet holding the cells to read or write. The default sheet is the first available in the Excel File. "Column" and "Row" properties are used to identify the cell to read or write. "Columns" are identified with letters such as A, B, AC. "Row(s)" is the index, 1-based, to read or write cells. It can be a single row or a range of rows. Examples of specifications are:
- 2 to process only the 2nd row.
- 3:8 to process the rows between the 3rd and the 8th.
- :5 to process the rows between the 1st and the 5th.
- 3: to process the rows between the 3rd and the last one.
- UNSPECIFIED to process all the rows in the column.
The other properties are related to the style of the Excel sheet. This lesson shows how to import and export data using, as the source and target, Excel files.
Importing Data from Existing Excel Files
The Excel File that is going to be used contains two sheets. The first one contains the collection of all the worldwide accepted currencies (code and name) while the second one contains a list of countries with the code of the main currency adopted. Below you can see a snippet of the Excel file for both the sheets. The goal is to import the data stored into the Excel file into two different entities: one for the countries, and the other one for the currencies.
The connection between country and currency is managed with the currency code listed on the country object.
The IFML Model
Since the data related to Countries and Currencies is not supposed to change during the application’s lifetime, the persistency of the two entities will be Volatile relative to Application Scope. This is how the Domain Model looks. The IFML Model provided in the project file already contains the page to show an alphabetical list of currencies and the details to show the adopted currency in each country. It also provides the pages with the Form Component to import the Excel File.
Let’s take a look to the Action Definition required to import the data from the Excel file. The skeleton of the action definition receives a BLOB file from the Form Component as Input Port Parameter and returns a message in case of both success and failure. The core of the Action uses the BLOB file to extract cells values from both the sheets. The extracted data is inserted inside the "Currency" and "Country" entities. Let’s inspect the core of the Action Definition in all its parts, starting from the Excel Component, understanding the structure of the sheets and then defining how data must be retrieved from the Excel file.
The Excel Component will work in "Read" mode since the content of the file will be the source of the information, and the "Source Type" must be set to BLOB since the user will directly pass the reference to the runtime file to be processed. In order for it to work correctly, you must also define the structure of the Excel file, telling the Excel Component where to find data. In this example, four instances of "Cell Info" will be created, one for every column that we want to import from the sheets of the "countries.xlsx" file.
Taking a look at the Excel file, you can see that sheet number one, referring to currencies, has two columns: the currency code (column A) and the currency name (column B), both ranging from column 3 onwards. Two instances of "Cell Info" are created, with the respective settings. The same strategy applies for sheet number two, referring to countries. Here column A refers to country name, while column B refers to the default currency of a country. So other two instances of "Cell Info" are created.
From the Excel Component, an OK Flow leads to the "Create Currencies" create operation, passing all the "Currency Info", while a Data Flow provides the "Country Info" to the "Create Countries" create operation. Both "Create" operations must work in "Bulk" mode since there will be more than one extracted cells.
The whole Action Definition core is put inside an Operation Group since all the operations must be executed transitionally; Countries cannot be created without Currencies.
If the chain of operations ends successfully, an OK Flow leading to the Success OK Port is activated returning a Feedback Message. Otherwise a KO Flow collects all the possible failures, activates the Failure KO Port, and returns an Error Message. Outside the Action Definition context, both an OK and a KO Flow lead to a Message component showing a feedback message.
How to Import Data from an Existing Excel File
Let’s continue working on our "CRM" web project in which we already have the Domain Model containing the "Country", "Currency", and "Excel Report" objects, and the "Home" site view containing the "Home" page and the "Excel" area with its subareas "Import" and "Export". These areas contain the IFML model related to the Excel Data Management. Finally the project contains a Modules Definitions View named "Action Definitions", which includes: "Read & Store [Excel]", "Read [Excel]" and "Write [Excel]".
Let’s see how to model the "Read & Store [Excel]" action definition. From our "CRM" web project, select the "Read & Store [Excel]" action from the "Import" area in the "Home" site view. Move to the Properties View and press the "Go to Action Definition" button, next to the "Action Definition" property. Now we can model the operation chain related to the Action Definition.
Let’s start adding the Excel Component to the Action Definition. Move to the Properties View. As you can see, WebRatio Platform sets by default the "Mode" property to "Read". Click on the dropdown menu next to the "Source Type" property and select the "BLOB" option.
Now, let's define the structure of our Excel file by adding all the necessary "Cell Info". Right-click on the "Read Excel" component, select "Add" and then "Cell Info". Move to the Properties View and write a meaningful name for the "Cell Info", for example "CountryCurrency". Then we will define all the properties we need. As you might notice, WebRatio Platform sets by default the "Type" property to "string". In this case, we do not change this property value. Write the value "2" in the field next to the "Sheet" property, then write the value "b" in the field next to the "Column" property, and finally write the value "2:" in the field next to the "Row(s)" property. This means that we want to read all the data contained in column B of the second Sheet of the Excel file, starting from the second row. Repeat the same steps for adding the other "Cell Info": "CountryName", "CurrencyCode" and "CurrencyName".
To state that the "Excel" component is the first operation of the chain, let’s use an "OK Flow" connecting the Input Port and the "Excel" component. Double-click on the OK Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "BLOB" parameter provided by the "Excel" component with the "BLOB" parameter provided by the Input Port and press the "OK" button to confirm.
To create the Currency items to read from the Excel file, let’s add a "Create" operation based on the "Currency" object. The "Create Currencies" create operation writes many objects at the same time so its "Bulk" property must be enabled.
To state that the "Create" operation must be executed after the "Excel" component, let’s use an "OK Flow" connecting the "Excel" component and the "Create" operation. Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "code" parameter provided by the "Create" operation with the "CurrencyCode" parameter provided by the "Excel" component, and the "name" parameter provided by the "Create" operation with the "CurrencyName" provided by the "Excel" Component. Press the "OK" button.
To create the Country items extracted from the Excel file, let’s add another "Create" operation based on the "Country" object. The "Create Countries" create operation writes many objects at the same time so its "Bulk" property must be enabled.
To state that the "Create Countries" operation must be executed after the "Create Currencies" operation, let’s use an "OK Flow" connecting the "Create Currencies" operation and the "Create Countries" operation.
In order to bind the information between the "Excel" component and the "Create Countries" operation, let's use a "Data Flow". Double-click on the Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "currency code" parameter provided by the Create Operation with the "CountryCurrency" provided by the "Excel" component, and the "name" parameter provided by the "Create" operation with the "CountryName" parameter provided by the "Excel" component. Press the "OK" button.
When the execution of these operations is successful, the Action Definition must exit through the "Success" OK Port. Let’s use the "OK Flow" for this purpose. Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, write a message to bind to the "Feedback Message" parameter provided by the "Success" OK Port, for example: "Operation successfully completed." and press the "OK" button to confirm.
All these operations must be surrounded with an Operation Group since they must be executed as a transaction. Select all the operations in the Action Definition Area, right-click on the selection and select "Refactor" and then "Surround with Operation Group".
Since these Operations are surrounded with an Operation Group, we can use a single "KO Flow" outgoing from the "Store Data" operation group to the "Failure" KO Port. Double-click on the "KO Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, write an error message to bind to the "Error Message" parameter provided by the "Failure" KO Port, for example: "Error in storing the Excel files." and press the "OK" button to confirm.
Exporting Data into Existing Excel Files
The second part of this lesson is about how to write and update an Excel file with a report of customer companies listing the name, country and email. This is an example of the desired output. The Reports will be saved in a dedicated database table, listing the last date modified, the file name and the content.
The IFML Model
The IFML Model provided in the project file already contains: a page with the Master & Details of the Excel Reports giving the possibility to update an existing report and another page to Create a new Excel Report. Between the two pages the IFML model includes also two Actions, one is to read the content of an Existing Excel file associated to an Excel report, while the other writes the new or updated Excel file.
The scenario is the following: the user creates a new Excel file and associates it to an Excel report. Then once the Excel report is created, it is able to add, change or remove content to/from the associated Excel file. Let’s first take a look at the New Report page and have a closer analysis of the "Write [Excel]" action definition. The "New Report" page contains a "Multiple Form" to insert data for the customers, and a "Form" view component based on the Excel report object to manage the instance and hold the report name and report key properties. This page submits to the "Write [Excel]" action all the values related to Customers and the Report properties.
This is the skeleton of the Action Definition. It receives five input parameters on the Input Port and has a Success OK Port that returns the key of the Excel report object that has been managed, and a Failure KO Port that returns an Error Message.
The Core of the Action Definition starts with the "Create Excel File" excel component that works in "Write" mode and has the "Cell Info" describing the desired Excel File structure. It receives on the OK Flow from the Input Port the Customer-related values and the Excel Report Name to rename the file as desired.
Since this operation creates a new Excel file, you need to choose the desired file format: either XLS or XLSX.
The structure of the new file can also be defined in terms of column headers. "Cell Info", if defined with the "Use Name As Header" property enabled, is also able to set the headers for data record set. Here’s an example for the "CustomerName" cell info.
After creating the Excel file, the operation chain activates the "Now" time operation, used to get the current timestamp and set the last-modified date for the Excel report object.
The next step is to check whether an Excel report associated with the Excel file exists or not. For this purpose, an "Is Not Null" operation is activated that uses the "Excel Report Key" as check. The "Excel Report Key" comes through a Data Flow from the Input Port.
After the check on the "Excel Report Key" the operation chain forks, if the Excel Report exists it is updated; otherwise it is created from scratch. The "Create Excel Report" create operation is activated with a "KO Flow" from the "Is Not Null" operation and receives all the required information on the incoming Data Flows.
The "Update Excel Report" update operation is activated with an "OK Flow" that also propagates the "Excel Report Key" used as input value for the "Is Not Null". The other required information arrives through the incoming Data Flows.
From both the Operations, if the execution is successful an "OK Flow" leads to the "Success" OK Port setting the "Excel Report Key" as return parameter.
Outside the Action Definition context, if the execution is successful an "OK Flow" reaches the "Excel Report" page, setting the current element of the Report List. If something fails during the execution of the operation chain, a "KO Flow" leading to the "Failure" KO Port is activated. Outside the Action Definition context, if the execution fails a "KO Flow" reaches the Error Page setting the Feedback Message.
How to Export Data into an Existing Excel file
From our "CRM" web project, select the "Write [Excel]" action from the "Export" area in the "Home" site view.
Move to the Properties View and press the "Go to Action Definition" button, next to the "Action Definition" property. Now we can model the operation chain related to the Action Definition.
Let’s start adding the Excel Component to the Action Definition. Move to the Properties View, click on the dropdown menu next to the "Mode" property and select the "Write" option.
Now, let's define the structure of our Excel file by adding all the necessary "Cell Info". Right-click on the "Excel" Component and select "Add" and then "Cell Info". Write a name for it, for example "CustomerCountry". Then check the "Use Name As Header" property. In this way the Cell Info's name will also become the "Column Header" in the Excel File.
Now let's define all the properties for this Cell Info. As you can notice, WebRatio Platform sets by default "Type" property to "string". In this case we do not change this property value. Write the value "1" in the field next to the "Sheet" property, then write the value "b" in the field next to the "Column" property, and finally write the value "1:" in the field next to the "Row(s)" property. This means that we want to write our data starting from the first row of the column B in the first sheet of the file.
Repeat the same steps for adding the other instances of 'Cell Info': "CustomerEMail", and "CustomerName".
To state that the "Excel" component is the first operation of the chain, let’s use an "OK Flow" connecting the Input Port and the "Excel" component. Double-click on the OK Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "CustomerCountry" parameter provided by the 'Excel' component with the "CustomerCountry" parameter provided by the Input Port, the "CustomerEmail" parameter provided by the "Excel" component with the "CustomerEmail" parameter provided by the Input Port, the "CustomerName" parameter provided by the "Excel" component with the "CustomerName" parameter provided by the Input Port, and finally the "Excel Report Name" parameter provided by the "Excel" component with the "CustomerCountry" parameter provided by the Input Port. Then press the "OK" button.
In order to get the current timestamp and set the last-modified date for the Excel Report object, let's use a "Time" component.
To state that the "Time" operation must be executed after the "Excel" component, let’s use an "OK Flow" connecting the "Exce" component and the "Time" operation.
In order to check whether the Excel File exists or not, let's use an "Is Not Null" operation.
To state that the "Is Not Null" operation must be executed after the "Time" operation, let’s use an "OK Flow".
Let’s use a Data Flow to bind the information between the Input Port and the "Is Not Null" operation. Double-click on the Data Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "Purchase Object" parameter provided by the "Is Not Null" operation with the "Excel Report Key" parameter provided by the Input Port. Press the "OK" button.
To create a new Excel Report, let's use a Create Operation based on the "Excel Report" object.
To state that the "Create" operation must be executed after the "Is Not Null" operation when the Excel Report does not exist, let’s use a "KO Flow".
Let’s use three Data Flows provide the information to "Create" operation. Double-click on the Data Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "file name" parameter provided by the "Create" operation with the "Excel Report Name" parameter provided by the Input Port. Press the "OK" button.
Now add another "Flow" connecting the "Excel" component and the "Create" operation. Double-click on the Data Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "content" parameter provided by the "Create" operation with the "Excel BLOB" parameter provided by the "Excel" component. Then press the "OK" button.
Finally add another "Flow" connecting the "Time" component and the "Create" operation. Double-click on the Data Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "last access" parameter provided by the "Create" operation with the "Current/Input Timestamp" parameter provided by the "Time" component. Press the "OK" button.
When the execution of the "Create" operations is successful, the Action Definition must exit through the "Success" OK Port. Let’s use the "OK Flow" for this purpose. Double-click on the OK Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "Excel Report Key" parameter provided by the "Success" OK Port with the "Excel Report primary key" parameter provided by the "Create" operation and press the "OK" button to confirm.
To update an existing Excel Report, let's use an Update Operation based on the "Excel Report" object.
To state that the "Update" operation must be executed after the "Is Not Null" operation when the Excel Report already exists, let’s use an "OK Flow". Double-click on the OK Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the “Key Condition” parameter provided by the "Update" operation with the "Input Value" parameter provided by the "Is Not Null" operation. Press the "OK" button.
Let’s use two Data Flows to provide the information to "Update" operation.
The first one connects the "Excel" Component and the "Update" operation. Double-click on the Data Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "content" parameter provided by the "Update" operation with the "Excel BLOB" parameter provided by the "Excel" component. Press the "OK" button.
Now add the second "Flow" connecting the "Time" component and the "Update" operation. Double-click on the Data Flow to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "last access" parameter provided by the "Update" operation with the "Current/Input Timestamp" parameter provided by the "Time" component. Press the "OK" button.
When the execution of the "Update" operation is successful, the Action Definition must exit through the "Success" OK Port. Let’s use the "OK Flow" for this purpose. Double-click on the "OK Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, bind the "Excel Report Key" parameter provided by the "Success" OK Port with the "Excel Report primary key" parameter provided by the "Update" operation. Then press the "OK" button.
All these operations must be surrounded with an Operation Group since they must be executed in transaction. Select all the operation in the Action Definition Area, right-click on the selection and select "Refactor" and then "Surround with Operation Group".
Since these Operations are surrounded with an Operation Group, we can use a single KO Flow outgoing from the "Write Excel Report" Operation Group to the “Failure” KO Port. Then double-click on the "KO Flow" to make the binding. In the opening dialog, uncheck the "Enable Default Binding" property, write an error message to bind to the "Error Message" parameter provided by the "Failure" KO Port, for example: "Error in writing Excel file.". Press the "OK" button to confirm.