Tutorial 5 : Federating Excel Documents

1. Purpose of this tutorial

This tutorial gives an overview of the Excel technology adapter and presents how to use it .

2. Introduction

Before starting the tutorial it is important to present Excel concepts that can be used within Openflexo. The main concept is an ExcelWorkbook, it is generally stored as an Excel file with the extension .xls and .xlsx, notice that Openflexo supports these two. An ExcelWorkbook contains a set of ExcelSheets, which has a name and a position within the ExcelWorkbook, and is made of ExcelRows. An ExcelRow has an index in the ExcelSheet and owns a set of ExcelCells. An ExcelCell is associated to an ExcelRow and a column index. It has a value which can be a String, an Integer, a Formula, or a Boolean. Both ExcelRow and ExcelSheet have some Graphical styles(background styles, foreground styles, Font etc...).

The tutorial is composed will present the following scenario:

• Part I: Setup the project
  • Download Openflexo
  • Load Excel resources within Openflexo
  • Create a Project and a Viewpoint
• Part II: Working with Excel
  • Working with Excel concepts
    • Create a Virtual model that relies on an Excel sheet listing information about customers
    • Create the Flexo concept of Customer that relies on an Excel row in this listing
  • Working with Excel behaviors
    • Create action to retrieve and create customers wihtin Openflexo from rows in the sheet.
    • Create action to add new customers
• Part III: Federating Excel workbooks
  • Create Company Sheet
  • Create Company concept
  • Federate sheets
• Part IV: Advanced Features
  • Working with Styles
    • Style of a row
    • Style of a cell
  • Working with formulas

The main elements are illustrated below, at the end of the tutorial you will obtain two separated Excel sheets one with customers information and the other with companies information. Of course these information are described as cells, openflexo provides to you a support to re-interpret as bussiness concepts, Customers and Companies. As a result you can manipulate together these clear defined concepts through different actions like coloring rows representing particular Customers, or adding new Customers and therefore adding new rows in Excel sheet etc...

3. Part I : Setup the project

3.1 Download Openflexo

As a preliminary phase, you should get a recent Openflexo distribution (Openflexo Maintainer 1.8.x)

Download it on:

• https://developers.openflexo.org/developers/download.html
• Alternatively take the current 1.8.x snapshot (https://downloads.openflexo.org/openflexo/1.8.0SNAPSHOT/)

3.2 Load excel resources within Openflexo

Launch Openflexo, and choose the Information Space. Select the Excel technology adapter perspective.

Then add a new resource center that will be a container for Excel resources. Click on Tool->Manage Resource Center, Add the folder "ExcelFiles" from the zip ExcelFiles.zip attached to the tutorial. Save and quit, normally these files are now visible in the left browser panel, click twice on the file customer.xlsx to open it, you should have something like this:

Excel files cannot be edited directly here but it gives a short overview of what the content of the file is. The customers sheet is made of 5 colums, a name, an occupation, a company, an address and a salary, and each row correspond to a customer. Well done you can access to you excel resource, now let's go for federation!

3.3 Create a Project/Viewpoint

To begin choose the ViewPointModeller, click on New project, choose a name, here TutoExcel.prj, create a new Viewpoint, give a name, an uri and a description.

Create a Virtual model, right click on the Viewpoint > New > Create Basic Virtual Model. Double click on the Virtual model created to open its view.

Now create a ModelSlot for this Virtual model that will later reference a customers Excel file. To do that right click on the Structural folder in the Virtual model view and select New > Propety > Create Model Slot.

Here we can choose between two kind of Excel ModelSlots. As a remainder an Excel ModelSlot represents a connection point for a real Excel resource (generally it is an Excel file). Each kind of ModelSlot provides its own interpretation for an Excel resource. In the one hand a Basic Excel ModelSlot interpret Excel file in its most simple form, as a set of sheets containing cells organized in rows and columns. In the other hand Excel files can be interpreted with a business structure, for instance rows can be clients or bills... In that case one must provide this structure. This tutorial focuses only on the Basic ModelSlot. Select this kind of ModelSlot that represent a workbook and set its name and a description.

A workbook is made of sheets, a sheet has a name and a position within this Workbook. Thererefore the first thing we have to do is to retrieve the customers sheet from the workbook. To simplify the future VirtualModel design, define a property to return this sheet, to do that right click on the Structure folder, then new > Property >Get/Set property. When the Get operation of this property will be invoked, a graph of actions will be executed and a value will be returned(here we expect an excel sheet). Give a name like "customer_sheet".

Program the GET behavior of this property, to do that right click on the + and add action in Get control Graph. Then select Expression Action, check return (to return the Sheet) and set the expression customers_workbook.getExcelSheetByName("CustomersSheet") , which means you select the first Excel sheet named "CustomerSheet" in the customers_workook. This expression is available in the binding selector if you select the extended expression button. An alternative is to select a sheet using its position(represented as an integer value) in the workbook, for instance using the expression: customers_workbook.getExcelSheetAtPosition(0), be careful 0 is the index of the first sheet in the list.

Now whenever you need to access the Excel sheet related to customers use the property customers_sheet .

4 Part II: Working with Excel

In this part we will learn how to create a new flexo concept that relies on an excel row. Then we will see how to give it some behaviors.

4.1 Working with Excel concepts

4.1.2 New Customer Concept

Create a new Flexo Concept (either right click on the virtualmodel > new >flexo concept, or select (+) in the inner concept folder within virtual model view). Give a name Customer , and a description.

4.1.2 New Customer Roles

Once it is created open the Flexo concept . Now we have to associate it to a row within the Excel customers sheet. To do that right click on the structure folder , new >property >create_technology_role .

In the wizard panel set the name of the role customer_row , set the technology adapter Excel , the type a row and a description.

4.2 Working with Excel behaviors

A Customer is a row in the Excel sheet. Now let's see how to create a new instance of Customer Flexo Concept. There may be different scenarios, either instanciate a new Customer from an existing row and therefore link the Customer to the existing row, or instanciate a new Customer and add a new row.

4.2.1 New concept instance from a row

Let's create an action that instanciate a new Customer from an existing row. For that we must use a Creation Scheme which role consists in a new Customer instantiation. Normally a default Creation Scheme is created when you declare the Customer Flexo concept, so just modify it. Select this scheme. A Scheme has a set of parameters, and execute a set of sequential actions. Our creation action requires only one parameter, an existing row. Then the sequence of actions consists to assign this row parameter to the row role. To do so create the parameter by right clicking on the action new > create_behavior_parameter .

Select the type of parameter, in our case this is an Excel row , and set its name for instance row_parameter .

Now assign the parameter value to the row role, to do that select the Creation Scheme and (+) > Expression action. Fill the expression parameters.row_parameter (the value to be assigned), and assign it to the customer_row role.

4.2.2 Add new customers

The second action instanciates a new Customer and creates a new row in the sheet. Compared to the last Creation Scheme, there is one main difference, the row doesn't exist yet. Therefore the user have to provide some information ie. the name, the occupation, the company, the address and the salary. To do that create a new Creation Sheme , right click on Behavioral folder > New > flexo_behavior . Then select Creation scheme and give a name for instance addNewCustomer .

This action requires 5 parameters. First create the name parameter, its type is a String and the widget is a Text field. The widget is a graphical UI the user will use to set the value. Do the same for the occupation and the company parameters. The address parameter widget can be a Text area that because there may be differents lines. Finnaly the salary parameter is an Integer and can be assigned through an Integer widget.

4.2.3 Call these action from the virual model

4.2.3.1 Add new customer

Define a call for these schemes directly in the virtual model, for that open the Virtual Model and in the Behavioral folder create a new ActionScheme .

Create 5 parameters, name_parameter, occupation_parameter, company_parameter, address_parameter of type String and salary_parameter of type Integer. Select the same widgets as before and set the required checkox for each ones.

Then when this Scheme will be invoked ones would like a new Customer to be instantiated with a new corresponding row. For that create an AddFlexoConceptInstance action within the ActionScheme which purpose is to call a CreationScheme anf thus instanciate a Concept. Select the addCustomer CreationScheme and set the parameters like illustrated above.

4.2.3.2 Retrieve existing customers

When you will load an excel sheet that already contains customers you will expect new Customers to be created for each relevant row, this can done by invoking the first Customer CreationScheme with a row parameter. For a Virtual Model , Synchonization Schemes are kind of Schemes that are automatically executed at Virtual model creation or loading . To create a Synchonization scheme right click on Behavioral Folder > new >Flexo behaviour , and select Synchonization Scheme .

Then browse over each row by using an __Iteration action > Fetchrequest > Select Excel row __ . For this action you have to choose the ModelSlot here customers_workbook and Excel sheet, here _sheet customers_sheet . Set also the name of the iterator for instance row . Do not forget to put a condition select.rowIndex!=0 to not include the first row that is not a customer, but the name of the column.

Once this is created, add an internal action to the Iterator action in order to instanciate a Customer for each row found. Of course we want to avoid duplicated Customers, and therefore we can use the MatchFlexoConceptInstance action that role is to instanciate a Flexo Concept based on matching criteria. To do that select the Iteration action and click on the (+) and add action in Iteration. Set the Virtual Model Instance , the concept, and the CreationScheme ("Creation"). In the matching citeria select the row, which means that if an existing Customer is alrady assigned to this row then it will not be created again. Assigne the row to the parameter value.

At this point of the tutorial, you dispose of a tool to load and retrieve Customers from an excel sheet, and to create new Customers. Let's try it!

5 Test customer management tool

In order to test the viewpoint, switch to view editor, select a project(create one if required) and right click > New > Create View .

Give a name and select its viewpoint(here TutoExcel) and set the configure all model slot manually , then Finish .

Once created instanciate a virtual model in it, to do so right click on the view > instanciate new virtual model instance .

Giva a name and select the ExcelVirtualModel virtual model as a reference. Like before choose configure all model slots manually and Next .

Now configure the excel model slot with a real excel resource, either an xls or xlsx file. Select the resource named customers.xlsx and finish.

Once Virtual model instance is created the Synchonization Scheme is executed. As you remember we programmed this scheme in order to browse all rows of CustomersSheet excepted the first one, and instanciate a new Customer for each row. If you unfold the Virtual model instance you will see several Customer instances.

Now try to add a new Customer, to do that right click on the Virtual model instance and AddNewCustomer.

Fill the different informations, and finish.

A new Customer instance is created, save Excel files(CTRL+S) and normally a new row appeared in customers.xlsx. Check it by either switching to the information space and open the file, or open it in from your file system. If you see the new customer then congratulation you finish the Part I!

In this part you had a first overview of Excel concepts and built a basic tool to manage an excel sheet. You should have a minimal but usefull knowledge to start using the Excel technology adapter. However how to federate different Excel workbooks, how to create new workbooks, or how to manage cells instead of rows? These are the purposes of Part II.

6 Part III: Federating Excel workbooks

In the Part II we have tooled an excel sheet containing a customers listing. Customers are described with many information, a name, an occupation, a company, an address and a salary. In the third part of this tutorial we will learn how to create a new Excel Workbook with a sheet, federate two workbooks and manipulate cells instead of rows. If you have not done the Part I, you can download the viewpoint of Part II from here : Tuto_Excel_RC2.prj.zip

6.1 Create Company Sheet

The second workbook will list the companies in which customers are employed. In the Virtual Model create a new BasicExcelModelSlot for the new companies workbook.

In the same way as Part II, create a property that references a sheet related to Companies named "companies_sheet" (see 3.3 for more details). In the screenshot below we used companies_workbook.getExcelSheetAtPosition(0) expression assuming that the sheet of interest is the first one.

6.2 Create Company concept

Then similarily to Part II, 4.1.1, create the Company Flexo concept. While for Customer we defined a reference to a row, here we will only define a reference to an Excel cell . Thus create a role relying on an Excel cell .

Create a list of customers which are employed within this company. This list is a set of flexo concept instance of Customer flexo concept type. In order to create a list set the cardinality to 0.*.

When finish, select it and change the type of Flexo Concept to Customer.

Create a reference to the name of the company, using for instance an Expression. Right click on Structural > New > Property > create expression property.

Set the expression : name_cell.cellValueAsString and a name like company_name .

6.2.1 Company Creation Scheme

Now define a creation scheme(you can reuse the default create scheme already present) in order to instanciate a new Company from a name. Therefore, define a new String parameter called name_parameter for this Creation Scheme (see 4.2.1).

Create an AddExcelCell action. Its role is to instanciate a new cell in a specified workbook.

6.3 Update companies workbook

Now open the Virtual Model_ and create a new Action Scheme named updateCompanies. Its purpose is to browse all Customers, instanciate a new Company or retrieve it if already present, and add for the Company instance the Customer.

Inside create a new Iteration action to browser over all current Customers.

Select the Iteration Action and select (+) > Add action inside iteration.

Create a new MatchFlexoConceptInstance action in order to either retrieve or instanciate a Company. Select the company_name matching criteria to customer.customer_row.getCellAt(2).cellValueAsString. The criteria indiquates that a new Company is instanciated only if there are no existing Companies with a company_name equals to company name of the Customer. If a Company already exists with that name, the action just retrieve it. Set a variable name like company and set the parameter value for name as customer.customer_row.getCellAt(2).cellValueAsString. Note that cellValueAsString is not mandatory in our case because we are manipulating strings. Click on Finish to create the action.

Finnaly add the Customer to the Company's customers list, thus create an Assignation Action and set the checkbox add to list. The list is company.customers and the object to add(expression) is customer.

Now the action is ready and can be invoked from the Virtual Model Instance, however to call it during the synchonization, select the Synchonization Scheme created in Part II and add a new Expression Action.

Now let's try it.

6.4 Test company viewpoint

In the same way as in the section 5, switch to View editor and create a new View from this Viewpoint. Then create a new Virtual Model Instance, and for the first Excel Model slot select again the customers file. However now you also have to fill the second Excel Model slot related to companies. It is not created yet, so select create new resource. Give a name and a path for this new Excel file and select Finish.

If everything had gone smouthly you should have now a set of Customers and a set of Companies Flexo concept instances. If you select a Company then you may see it is related to a set of Customers (bottom right panel).

Now save the companies Excel file:

And open it from the Infomation Space or from you file system:

7 Part IV: Advanced features

You can download the viewpoint of Part III from here : TutoExcel_RC2_PartIII.prj.zip. In this Part we will learn to use styles, and formulas.

7.1: Working with Styles

7.1.1: Style of a row

Create an action that fills a row with a color. For now all the row style is affected, it is not possible yet to set e begin/end cell. Open the virtual model, and in structural part, create a new Action Scheme name changeRowStyle.

Give a name and provide a parameter which is a Customer (Flexo Concept).

Give a name to the parameter like for instance customer.

Then you have to create two CellStyleAction , the first one sets the style to a Solid Foreground:

Set the subject (the row you want to be modified, here it is the given parameter), set the Cell Style as Pattern, and set the Pattern Style to SOLID_FOREGROUND.

Set also the Assign value which is the same as the Subject (this should change in futher versions).

Click on Finish and create a new CellStyle Action with these information:

Click on Finish.

To check if it runs, switch to the View Editor, if you have not created a View and a Virtual Model Instance then you need to create them, see Part II or III. Otherwize right click on the Virtual Model Instance and select the action ChangeRowStyle. Select a Customer to be colored and Validate.

Then save the customers.xlsx file, and open it in the file system.

7.1.2: Style of a cell

Styles can be also applied on particular cells. Let's try to change the style of a cell. Create a new action within the Virtual Model called changeCellStyle.

Add a parameter for this action name cell_name of type String. It will be formatted like this: "ROW_ID" + "COL_ID". For instance "B15", "AA10" etc... Don't forget to set the checkbox "required".

Create a new Expression Action as illustrated below to retrieve the corresponding cell, using the expession getCellFromName(String param) where parame is in the format explained previously.

Create 2 CellStyleAction on the cell to modify its style:

Finnaly try it in the View Editor. On the VirtualModelInstance right click > ChangeCellStyle. Change the style of cell B15 for instance, click Validate, and save the Excel file.

7.2: Working with Formulas

Create a new action in the Virtual Model named computeAverageSalary. Inside this action create an AddCellAction in which we will compute the average salary. Fill the information as below:

Test the action in the View Editor, and check you see the average salary in you Excel sheet.

The final viewpoint is available here: TutoExcel_RC2_PartIV.prj.zip.