Getting started with the Excel Unit

From Wiki

Introduction

The Excel Unit is both a Content and Operation unit which is available from the WebRatio 5.1 version. This unit can be placed in either a Site View or Service View or Module View. It is based on the Apache POI project and it is able to read/write Excel '97(-2007) files. In order to specify on the unit where it must read or write into the Excel spreadsheet, it's necessary to add some Cell Info elements to the unit itself.

The Excel unit has the following specific properties:

• 
  Mode. It specifies the execution mode of the unit. The property possible values are:
  • Read (default value)
  • Write
  • Update/Evaluate
• Source Type. It specifies if the source Excel file will be provided either as a property at design type (Static URL), as an input parameter at run-time (Dynamic URL), or as a BLOB file (BLOB). The property possible values are:
  • Static URL
  • Dynamic URL
  • BLOB
• Static URL. It specifies the URL of the Excel file being read; available only if the URL Type property is set to static. Local Excel files can be referenced too; for instance the file C:\WRExamples\products.xls is referenced by the URL file:///C:/WRExamples/products.xls.
• Use Header. If checked, the extracted columns are identified by matching the name of each CellInfo with the value contained into the first row of each column. It works only in Read mode and if the first row contains the header.
• Cell Info Order. The order of Cell Info elements. In the image on the right the unit has two Cell Info elements, "Product name" and "Quantity".

In order to add a Cell Info element to the Excel Unit you have to right click on the unit and choose the Add Cell Info command. These are the Cell Info properties you can set:

• Name. A name for the Cell Info element.
• Type. The type of the cell.
• Content Type. The content type of a cell of type string, text, BLOB or URL. The selected content type determines the rendition of the cell value.
• Data Format. The data format used to present the cell value into the created Excel or into the JSP page. The format must be expressed in a Java pattern (e.g. #.0## or dd/MM/yy). You can find other information about date patterns here and about numeric patterns here.
• Sheet. The number (1-based) or the name of the sheet holding the cell(s) to read or write. The default one is the first available into the Excel.
• Column. The column code holding the cell(s) to read or write, for example: A, B, AC
• Row(s). The row index (1-based) to read or write, it can be a single row or a range of rows. In the case of range of rows both the indexes are considered.
  • 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

How to read an Excel file

Suppose you want to read an Excel file and show its content on a Web page. First of all, add in the data model the volatile entities to store the Excel file content. You must know the Excel file structure in order to import the data correctly. In this example suppose to have an Excel storing the information about a store and its warehouse (Excel Sample File). In the following figure there is an example of the Data Model.

Now, you have to create an hypertext that allows the user to choose the file to import and to see the imported data. The following image is just an example.

In this page there are:

• an Entry unit with a BLOB field that allows to choose the Excel file to import.
• a Data unit which allows to see the Store details imported from the Excel file.
• a MultiData unit which shows the warehouse data connected to the imported store. These data will be also collected from the Excel file.

Let's consider now the operation chain.

1. Add an Excel unit outside the page and name it "Read Excel File".
2. In the Properties View choose the "Read" option for the Mode property and "BLOB" for the Source Type property.
3. Add the Cell Info elements to the Excel unit. To add a Cell Info element, right click on the unit and choose the Add Cell Info command. You have to add four Cell Info elements, in order to retrieve all the information from the Excel (check the images on the right to better understand). This is the list of the Cell Info elements:
   1. StoreAddress. Type:=string, Sheet:=1, Column:=C, Row(s):=3
   2. StoreEmail. Type:=string, Sheet:=1, Column:=C, Row(s):=4
   3. ProductName. Type:=string, Sheet:=2, Column:=A, Row(s):=3:
   4. ProductQuantity. Type:=string, Sheet:=2, Column:=B, Row(s):=3:
4. Connect the Excel Unit and the Entry unit with a normal link named "Import". Double click on the link to open the Coupling dialog and couple the BLOB field of the Entry unit with the BLOB input parameter of the Excel unit.
5. Add a Create unit and name it "Create Store".
6. Connect the "Create Store" Create Unit with the Excel Unit with an OK Link. Double click on the link to open the Coupling dialog and couple the StoreAddress and StoreEmail output parameters of the Excel unit with the correspondent two attributes of the Create Unit.
7. Add a Create unit and name it "Create Warehouse". Mark this unit as "bulk" in the Properties View.
8. Connect the "Create Warehouse" Create Unit with the Excel Unit with a transport link. Double click on the link to open the Coupling dialog and couple the ProductName and ProductQuantity output parameters of the Excel unit with the correspondent two attributes of the Create Unit.
9. Add an OK link connecting the "Create Warehouse" Create Unit and the page.

How to write to an Excel File

Suppose that you want to use the data imported in the previous section to update the warehouse data of the store. You have to add a new page in the Web Model in order to allow the user to manage the warehouse. The model can be like the following:

The "Warehouse Management" page has the following content:

• a MultiEntry unit for managing the warehouse. This unit must have two preloaded fields, the product and the quantity field.
• an Entry unit with a BLOB field to choose the Excel file to update.
• a normal link named "Manage Warehouse" that connect the MultiEntry unit with the "Warehouse" MultiData unit of the other page.

The "Operation Result" page contains:

• a MultiMessage unit showing the result of the operation.

Let's see now the Excel unit operation.

1. Add the "Update Excel File" Excel unit.
2. In the Properties View choose the "Update/Evaluate" option for the Mode property and "BLOB" for the Source Type property.
3. Add the Cell Info elements to the Excel unit. To add a Cell Info element, right click on the unit and choose the Add Cell Info command. You have to add two Cell Info elements, in order to write the new warehouse information in the Excel file. This is the list of the Cell Info elements:
   1. ProductName. Type:=string, Sheet:=2, Column:=A, Row(s):=3:
   2. ProductQuantity. Type:=string, Sheet:=2, Column:=B, Row(s):=3:
4. Connect the "Update Excel File" Excel Unit and the Entry unit with a normal link named "Save". Double click on the link to open the Coupling dialog and couple the BLOB field of the Entry unit with the BLOB input parameter of the Excel unit.
5. Connect the "Update Excel File" Excel Unit and the MultiEntry unit with a transport link. Double click on the link to open the Coupling dialog and couple the two fields of the MultiEntry unit with the correspondent input parameters of the Excel Unit.
6. Connect the "Update Excel File" Excel Unit and the MultiMessage unit with two different links, an OK link and a KO link. Double click on each link to open the Coupling dialog and write a custom message for each workflow.

Related articles: Category Difficulty Refers Context Parameters Web Model Beginner Get Unit Set Unit Reset Unit Create a "Hello world" application Web Model Beginner Site View WebML WebRatio Create a "Simple data centric" web application Web Model Beginner Site View WebML WebRatio Custom Locations and Master pages Web Model Beginner Master Page Custom Location Getting started with Groovy Technical Reference Beginner Script Unit Template … further results