Skip to Content
Author's profile photo Alessandro Spadoni

Excel -like custom grid control in SAPUI5 #OpenUI5

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 ( : 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 ( ), 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


        properties :
            "content": {type : "string", defaultValue : "<div></div>"},
            "preferDOM": {type : "boolean", defaultValue : false}
    init: function(){
                  var constructorOptions = this.getOptions();
             = this.getData();
    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 ( , 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


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
        // set the model to the core
        //Control-Specific Model
        //var oExcel = sap.ui.getCore().byId("excelgrid");

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.setLogoText("SAPUI5 'Excel-like data grid' Control ");
        //configure the welcome area
        //configure the log off area
        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() );}
        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

        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <script src=""
        <!-- add sap.ui.table,sap.ui.ux3 and/or other libraries to 'data-sap-ui-libs' if required -->
       <script src=""></script>
        <script src="js/excelgrid.js"></script>
        <link href="" media="screen" rel="stylesheet">
                var view = sap.ui.view({id:"idmain1", viewName:"excelgrid.main", type:sap.ui.core.mvc.ViewType.JS});
    <body class="sapUiBody" role="application">
        <div id="content"></div>

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

We can edit a cell


ExcelGrid3.JPG and writes the Model


Insert a new row using the contextual menu


ExcelGrid6.JPGand call a method to check the count


If you want to try it how it works I uploaded the project here!

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

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Custodio de Oliveira
      Custodio de Oliveira

      Hi Alessandro,

      Nice stuff, thanks for sharing. Keep'em coming!



      Author's profile photo Former Member
      Former Member

      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!



      Author's profile photo flavio ciotola
      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



      Author's profile photo Alessandro Spadoni
      Alessandro Spadoni
      Blog 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 😆 😉

      Author's profile photo Fabio Di Micco
      Fabio Di Micco

      Thanks Alessandro for sharing!

      A very very good job, now I'm waiting to see your custom ExcelGrid in a great project!


      Author's profile photo Alessandro Spadoni
      Alessandro Spadoni
      Blog Post Author

      Thank you Fabio!

      with the 2nd part I'll try to put the project on github 😉

      Author's profile photo Sanket Chimalwar
      Sanket Chimalwar

      Thanks for sharing

      Author's profile photo Former Member
      Former Member

      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,


      Author's profile photo Alessandro Spadoni
      Alessandro Spadoni
      Blog 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 



      //Control-Specific Model 

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



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

      with = 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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo CD Raju
      CD Raju

      Nice one, thanks for sharing..

      Author's profile photo Oliver Keim
      Oliver Keim

      Great stuff. How can I open the context menu by using keyboard?

      Author's profile photo Ranjitha Balaraman
      Ranjitha Balaraman

      Nice article. Thanks for sharing!

      Author's profile photo Rauf Shaikh
      Rauf Shaikh

      Alessandro Spadoni - SCN Member of the Month January 2014. Thanks a lot for sharing this 🙂



      Author's profile photo joerg arndt
      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

      Author's profile photo Former Member
      Former Member

      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,


      Author's profile photo Former Member
      Former Member

      Hi Diana,

      By Seeing the above model  I implemented in XML views I got the Result.

      Thank you,


      Author's profile photo Maruthi Revankar
      Maruthi Revankar

      Thanks for the above example. It works absolutely fine in IE. But displays only column headers in Chrome. Has anything changed over time?

      Author's profile photo Bilen Cekic
      Bilen Cekic

      i have the same problem, did you solve it ? If you writeclick to the control and click add row or column suddenly all rows are appearing.

      Author's profile photo Nico Dominguez
      Nico Dominguez

      And for putting styles (in excel)

      Author's profile photo Adithya S N
      Adithya S N

      Where will u get the Updated values of row or column  i tried afterChange function but it is not working for me , Anyone Suggest how to work on it

      Thanks in Advance

      Author's profile photo GIRIPRASATH BALAJI

      i knew i am very late to this topic - but still can u please share the code - old link is not working - Thanks

      Author's profile photo Alessandro Spadoni
      Alessandro Spadoni
      Blog Post Author

      yes I think dropbox doesn't support html pages anymore,

      this is the direct link but probably you have to refactor it



      Author's profile photo GIRIPRASATH BALAJI

      Thanks for sharing – i did review my code comparing with yours. But looks like i am struck with some issue – i end up with error stating excelgrid is not defined .


      I doubt if the handsontable.js gets included or not ?



      please guide