Skip to Content

In this blog I want to share my experience about how to create a custom #SAPUI5 control extending sap.ui.core.HTML standard control.

My goal is to create a new control to display an ‘Excel-like data grid‘ where the user can edit/merge/change/delete rows and cells with an “Excel-like” look & feel.

Thanks to this tweet Twitter / alavazzi: jQuery plugin Handsontable, … by Alessandro Lavazzi   I discovered some days ago an awesome jQuery plugin called Handsontable (http://handsontable.com/) : but how can I use it in my #SAPUI5 application? How can I use it as a control inside a SAPUI5 view?

How can I bind the data model using the SAPUI5 binding mechanism?

Exploring the official documentation ( https://sapui5.hana.ondemand.com/sdk/#docs/guide/OnTheFlyControlDefinition.html ), we know It is possible to extend existing Controls and to create completely new Controls: so I decided to create my ExcelGrid custom control extending the sap.ui.core.HTML control

excelgrid.js



sap.ui.core.HTML.extend("ExcelGrid",
    {
    metadata:
    {
        properties :
        {
            "options":"object",
            "data":"object",
            "content": {type : "string", defaultValue : "<div></div>"},
            "preferDOM": {type : "boolean", defaultValue : false}
        }
    },
    init: function(){
              this.attachAfterRendering(function(e){
             
                  var constructorOptions = this.getOptions();
                      constructorOptions.data = this.getData();
           
                      this.$().handsontable(constructorOptions);
                   });
   
            },
       
    getInstance: function(){
        return this.$().handsontable('getInstance');
    },
           
    renderer: "sap.ui.core.HTMLRenderer"
    });



Properties Definition

The first step is the properties definition for the new control, the documentation tell us :

  • a property is defined by at least its name and its type
  • setter and getter methods are created behind the scenes , including data binding and type validation (this is very important , we’ll be able to bind values of properties using #SAPUI5 binding concepts)

I defined 2 properties of type [object]: ‘options‘ and ‘data‘ .

The handsontable constructor options has a lot of properties (https://github.com/warpech/jquery-handsontable/wiki/Options#constructor-options) , I don’t want to manually define all properties so we’ll use the generic “options” object to set the properties.

In “options” object I can set  width , height , headers , contextMenu (used to create new rows/columns) and so on.

The “data” property is very important because we’ll bind the property to the model to display our array of arrays to the Excel Grid

Methods

from the official doc:

“You can add any method to a new Control by providing the implementation, without adding it to the metadata. By convention, all methods are public”

“There are some method names you may use but which have a special meaning:

  • on...: Methods starting with “on” are event handlers that are automatically bound to browser events
  • init: Is the name of the initialization function called right after Control instantiation
  • renderer: Is a special name that holds either
    • the function that creates the Control’s HTML or
    • a complete structure that contains this function and more”

I redefine the “init” method because I need to handling the “afterRendering” event : here the effective grid rendering is done , I have the jquery instance of my empty rendered div (“<div></div>” as default value of the “content ” property ) and executing  “handsontable” method the div is filled with the magic provided by the jQuery plugin.  You may have noticed I used the get Methods to retrieve  my “options” and “data” control properties  , I didn’t implement them because as we said setter and getter methods are created behind the scenes.

(Note: I tried to implement the onAfterRendering Method instead of attachAfterRendering in “init” method but with no luck..)

The getInstance method is an helper method , it returns the jquery instance of the grid : we’ll be useful to call specific plugin methods.

The Renderer method: from the offical doc

This method is responsible for creating the HTML structure that makes up the Control

I want to use the sap.ui.core.HTML standard renderer so my renderer is “sap.ui.core.HTMLRenderer”.

Controller – main.controller.js


sap.ui.controller("excelgrid.main", {
/**
* Called when a controller is instantiated and its View controls (if available) are already created.
* Can be used to modify the View before it is displayed, to bind event handlers and do other one-time initialization.
* @memberOf excelgrid.main
*/
    onInit: function() {
         var data = {"data":[["HANA", "HCP", "SAPUI5", "ABAP", "Mobile", "OData"],
                             ["SQL Views", "JDBC", "open source!", "Gateway", "SMP", "REST"],
                             ["Attributes Views", "Document Service", "sap.m", "Bsp", "Phonegap", "Entity"],
                             ["Analytic Views", "CMIS", "FIORI", "", "Afaria", "$filter"],
                             ["Calculation Views", "XS", "MVC", "", "Html5", "$skip"]]};
     
         var oModel = new sap.ui.model.json.JSONModel();
        // set the data for the model
        oModel.setData(data);
    
        // set the model to the core
        //sap.ui.getCore().setModel(oModel);
    
        //Control-Specific Model
        //var oExcel = sap.ui.getCore().byId("excelgrid");
        //oExcel.setModel(oModel);
    
        this.getView().setModel(oModel);
    },
});


I create a JSON model with dummy values and set the model for the current view.

View – main.view.js


sap.ui.jsview("excelgrid.main", {
    /** Specifies the Controller belonging to this View.
    * In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
    * @memberOf excelgrid.main
    */
    getControllerName : function() {
        return "excelgrid.main";
    },
    /** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
    * Since the Controller is given to this method, its event handlers can be attached right away.
    * @memberOf excelgrid.main
    */
    createContent : function(oController) {
    
        //create my Excel Grid control
        var oExcelGrid = new ExcelGrid("grid", {
            data : "{/data}",  //binding the model
            options: {minSpareRows: 1,colHeaders: true,contextMenu: true,manualColumnResize:true}
        });
    
        //create the ApplicationHeader control
        var oAppHeader = new sap.ui.commons.ApplicationHeader("appHeader");
        //configure the branding area
        oAppHeader.setLogoSrc("http://www.sap.com/global/images/SAPLogo.gif");
        oAppHeader.setLogoText("SAPUI5 'Excel-like data grid' Control ");
        //configure the welcome area
        oAppHeader.setDisplayWelcome(false);
        //configure the log off area
        oAppHeader.setDisplayLogoff(false);
    
        var oLayout = new sap.ui.commons.layout.MatrixLayout({
            id : "matrix1",
            layoutFixed : false,
            width: "100%"
            });
    
        var oButtonModel = new sap.ui.commons.Button({
            text : "alert( oExcelGrid.getModel().getJSON())",
            tooltip : "Alert grid model",
            press : function() {alert( oExcelGrid.getModel().getJSON());}
        });
        var oButtonMethod = new sap.ui.commons.Button({
            text : "Call method countRows()",
            tooltip : "Test Call Method",
            press : function() {alert( oExcelGrid.getInstance().countRows() );}
        });
        oLayout.createRow(oAppHeader);
        oLayout.createRow(oExcelGrid);
        oLayout.createRow(oButtonModel);
        oLayout.createRow(oButtonMethod);        
        return oLayout;
    }
});


With  var oExcelGrid = new ExcelGrid I create my custom instance control defining the data binding for my model view and some properties to define the look and feel of the grid (options property).

Check the official documentation of the plugin to discovery all the options  Options · warpech/jquery-handsontable Wiki · GitHub

The “oButtonModel” writes the model using the javascript alert and the oButtonMethod writes the result of the method “countRows”.

To call the method we use the getInstance() method of the ExcelGrid control , it returns the handsontable instance that we can use it to call the countRows() ; check all the available methods here! Methods · warpech/jquery-handsontable Wiki · GitHub

and finally….the index.html


<!DOCTYPE HTML>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <script src="https://openui5.hana.ondemand.com/resources/sap-ui-core.js"
                id="sap-ui-bootstrap"
                data-sap-ui-libs="sap.ui.commons"
                data-sap-ui-theme="sap_bluecrystal">
        </script>
        <!-- add sap.ui.table,sap.ui.ux3 and/or other libraries to 'data-sap-ui-libs' if required -->
       <script src="http://handsontable.com/dist/jquery.handsontable.full.js"></script>
        <script src="js/excelgrid.js"></script>
        <link href="http://handsontable.com/dist/jquery.handsontable.full.css" media="screen" rel="stylesheet">
        <script>
                sap.ui.localResources("excelgrid");
                
                var view = sap.ui.view({id:"idmain1", viewName:"excelgrid.main", type:sap.ui.core.mvc.ViewType.JS});
                view.placeAt("content");
        </script>
    </head>
    <body class="sapUiBody" role="application">
        <div id="content"></div>
    </body>
</html>

Now we are ready to execute the web application!ExcelGrid1.JPG

We can edit a cell

ExcelGrid2.JPG

ExcelGrid3.JPG and writes the Model

ExcelGrid4.JPG

Insert a new row using the contextual menu

ExcelGrid5.JPG

ExcelGrid6.JPGand call a method to check the count

ExcelGrid7.JPG

If you want to try it how it works I uploaded the project here! goo.gl/dxMlqI

In the next blog I’ll try to describe how to handle grid events

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Alessandro Lavazzi

    Hi Alessandro,

    It’s great to see how a tweet can be transformed in a valuable content like this by you.

    Thank you Alessandro for sharing, It’s a very good job!

    Cheers,

    Alessandro

    (0) 
  2. flavio ciotola

    Thank you Alessandro, really great blog, very informative!

    Tried out the sample project, awesome!

    Just looking forward to reading the next blog…. ๐Ÿ™‚

    Thanks again for sharing

    Ciao,

    Flavio

    (0) 
  3. Alessandro Spadoni Post author

    Thank you for your feedbacks Custodio and Flavio!

    and of course Alessandro thank you to tweeted about it !I’m waiting for your next plugin discovery ๐Ÿ˜† ๐Ÿ˜‰

    (0) 
  4. Tobias LeiรŸner

    That’s a great tutorial and is very helpful. I can’t wait to see the next chapter.

    However, I don’t know how the data binding works. As far as I can see the data inside the ExcelGrid the data is a plain object, which gets assigned to the data property.

    Later, on init function, you create a local JSONModel and assign it to the view.

    The view creates the ExcelGrid without providing real data, but providing the binding path.

    How does SAPUI5 know, what JSONModel to use, and how to put the data from the binding path into the ExcelGrid? There is no connection between the ExcelGrid and the JSONModel at all.

    Maybe you can explain the behaviour of the JSONModel, the data property and the binding path.

    Thanks again for this great blog post.

    Best regards,

    Tobias

    (0) 
    1. Alessandro Spadoni Post author

      Hi Tobias,

      how you can see on main.controller.js you can set the model in different ways

      // set the model to the core 

      //sap.ui.getCore().setModel(oModel); 

           

      //Control-Specific Model 

      //var oExcel = sap.ui.getCore().byId(“excelgrid”); 

      //oExcel.setModel(oModel); 

      this.getView().setModel(oModel); 

      Probably the “Control-Specific Model” is more clear to check how the binding works;

      with 

      constructorOptions.data = this.getData();

      I get the value of the object property called “data” of the ExcelGrid control bound to the model when I create the control with

      var oExcelGrid = new ExcelGrid(“grid”, { 

                  data : “{/data}”,  //binding the model 

                  options: {minSpareRows: 1,colHeaders: true,contextMenu: true,manualColumnResize:true

              });

      {jsonModel:data}->setModel (Core/View/Control)->binding to ExcelGrid:{data}->onAfterRendering->getData()

      Of course the SAPUI5 framework is something new for me too , so every suggestion or different ways / mechanism to get the same result are welcome

      (0) 
      1. Tobias LeiรŸner

        I’m missing the connection between the property “data” and the data of the JSONModel.

        The property value of “data” is the binding path, so this.getData() will get you {/data}, right?

        Is there some magical connection between the JSONModel and the data binding?

        “data” property of ExcelGrid –> {/data}

        this.getData() of ExcelGrid –> {/data}

        jsonmodel.getData() –> Array data

        How does the control know, what model to use, and that the binding path is a binding path? Is “data” a special property used for data binding?

        Another question:

        Will this control automatically be rerendered when the data of the JSONModel will be changed? Will the init() function get called, or what function will be called when the data of the JSONModel changes?

        Thanks again for your help. It’s a complex topic.

        (0) 
  5. joerg arndt

    Hi Alessandro,

    great, but one Question.

    Is it possible to make it droppable.

    I tried a lot of things but nothing works.

    Have you an idea how to do it.

    Thaks Joerg

    (0) 
  6. Iosif Diana Cristina

    Hello Alessandro,    

    Thank you for the example!

    I want to build an application after the new structure of sapui5 applications using the XML views.

    I tried to use “SAPUI5 Diagnostics tool” to convert your JS View, but when I add the view to the application it provides me an error and the application is not running.

    I think it comes from the inappropriate conversion that defines the new custom control “ExcelGrid”.

    Can you help me regarding this topic? Do you think it is appropriate to start building the view from scratch?

    Thank you,

    Diana

    (0) 

Leave a Reply