SAP Development: Mastering SAP UI5, BTP, Fiori, CAPM, RAP, ABAP; and More!: crud

crud csv file reader HANA import sapui5 sapui5 csv file upload import upload

How to import Excel CSV file to SAP UI5 Application.

Sanjo Thomas•06:59:00

In this blog, we will see how can we import a csv file in SAP UI5 application.

This would be a bit different from importing excel file just as I explained inthe previous blog, since in this technique we wont be rquired to use a third party library.

Just like in my previous blog, lets keep the use case simple. We will have a simple view with a FileUploader to browse a csv file and a table to display the data.

<core:View xmlns:core="sap.ui.core" xmlns:mvc="sap.ui.core.mvc"
	xmlns="sap.m" xmlns:u="sap.ui.unified" xmlns:l="sap.ui.layout.form"
	controllerName="com.fileuploader.FileUpload" xmlns:html="http://www.w3.org/1999/xhtml">
	<Page title="Demo on CSV File Upload">
	<content>
		<l:SimpleForm editable="true">
		 <l:content>
		  <Label text="File Name">
		  </Label>
		<VBox>
		 <u:FileUploader id="idfileUploader" width="50%"
		    sameFilenameAllowed="false" buttonText="" fileType="CSV"
		    placeholder="Choose a CSV file" style="Emphasized">
		</u:FileUploader>
		<Button text="Upload" press="onUpload"></Button>
		</VBox>
		</l:content>
		</l:SimpleForm>

		<Table id="idTable" items="{/}">
		<items>
		    <ColumnListItem>
			<cells>
			<Text text="{VBELN}"></Text>
			<Text text="{ERDAT}"></Text>
			<Text text="{VBTYP}"></Text>
			<Text text="{TRVOG}"></Text>
			<Text text="{AUART}"></Text>
			</cells>
		   </ColumnListItem>
                </items>
	        <columns>
			<Column>
			<Text text="Sales Document"></Text>
			</Column>
			<Column>
			<Text text="Date"></Text>
			</Column>
			<Column>
			<Text text="Type"></Text>
			</Column>
		        <Column>
			<Text text="Sales Org"></Text>
			</Column>
			<Column>
			<Text text="Category"></Text>
			</Column>
		</columns>
		</Table>
		</content>
	</Page>
</core:View>

Now update the code in the onUpload function in the respective controller.

onUpload : function(e) {
	
	var fU = this.getView().byId("idfileUploader");
	var domRef = fU.getFocusDomRef();
	var file = domRef.files[0];
	
	
	// Create a File Reader object
	var reader = new FileReader();
	var t = this;
	
	reader.onload = function(e) {
	    var strCSV = e.target.result;
	    var arrCSV = strCSV.match(/[\w .]+(?=,?)/g);
	    var noOfCols = 5;

	    // To ignore the first row which is header
	    var hdrRow = arrCSV.splice(0, noOfCols);

	    var data = [];
	    while (arrCSV.length > 0) {
		var obj = {};
		// extract remaining rows one by one
		var row = arrCSV.splice(0, noOfCols)
		for (var i = 0; i < row.length; i++) {
		    obj[hdrRow[i]] = row[i].trim()
		}
		// push row to an array
		data.push(obj)
	    }
	    
	    // Bind the data to the Table
	    var oModel = new sap.ui.model.json.JSONModel();
	    oModel.setData(data);
	    var oTable = t.byId("idTable");
	    oTable.setModel(oModel);
	};
	reader.readAsBinaryString(file);
    }

The above code in onUpload function will read the data in CSV file and load it to json model and bind it to the table.

Hereby, we have successfully uploaded the CSV to UI5 application and shown it on the table. Now, as per requirement user can select a particular record and perform CRUD operations.

This way we have seen both ways of importing Excel and CSV files to UI5 application. We can also add some additional validations on excel imported files by simply comparing the desired template with the imported file template. This we can see in the next blog.

crud odata sapui5

How to Build an application using Create, Update, Delete Operations in oData Service

Sanjo Thomas•21:41:00

This blog explains how to create a CRUD application using SAPUI5 Using Odata Model.

Blog Content :

Creating the User Interface

Creating the project
Setting up the SAPUI5 bootstrap and required libraries
Creating the view components
Implementing the Controller’s methods

Read, Create, Update, Delete

Scenario:

We will be creating a table, bind the data using oData model and then we will add a new row(CREATE), Update the existing Row(UPDATE), Delete a particular row(DELETE).

Steps to be followed :-

1. Create the Project : Create a SAPUI5 MVC project in eclipse

It shows like below mentioned in the picture :

Setting up the SAPUI5 bootstrap and required libraries in index.html file

Add the required libraries in the "data-sap-ui-libs" tag

2. Creating the view components and fetching the Bank data :

Add following code in to the bank.view.js

sap.ui.jsview("bankcrud.bank", {
/** 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 bankcrud.bank
*/
getControllerName : function() {
return "bankcrud.bank";
},
/** 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 bankcrud.bank
*/
createContent : function(oController) {
var layout = new sap.ui.commons.layout.MatrixLayout({
id : 'matrix4',
layoutFixed : false,
});
var appHeader = new sap.ui.commons.ApplicationHeader('appHeader',   {
logoText : "Bank Application",
displayLogoff : false,
displayWelcome : true,
userName : "Welcome"
});
layout.createRow(appHeader);
var rPannel = new sap.ui.commons.Panel('rPannel', {
text : "BankCollection List",
});
var oTable = new sap.ui.table.DataTable({
id : "bankTableID",
title: "Bank CRUD Application",
width : "100%",
visibleRowCount: 10,
selectionMode : sap.ui.table.SelectionMode.Single,
//setEditable : false,
rowSelectionChange : function(oEvent) {},
toolbar: new sap.ui.commons.Toolbar({
                 items: [
                      new sap.ui.commons.Button({
                                text: "Create",
                                press: function() {
                                     oController.Create();
                                }
                      }),
                      new sap.ui.commons.Button({
                             text: "Update",
                                press: function() {
                                     oController.Update();
                                }
                      }),
           new sap.ui.commons.Button({
                   text: "Delete",
                    press: function() {
                   oController.Delete();
                                }
                      }),
                     ]
}
});
oTable.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({
text : "Bank ID"
}),
template : new sap.ui.commons.TextField().bindProperty("value",
"bankID"),
sortProperty : "bankID"
}));
oTable.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({
text : "Bank Country"
}),
template : new sap.ui.commons.TextField().bindProperty("value",
"bankCountry"),
sortProperty : "bankCountry"
}));
oTable.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({
text : "Bank Name"
}),
template : new sap.ui.commons.TextField().bindProperty("value",
"bankName"),
sortProperty : "bankName"
}));
oTable.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({
text : "Region"
}),
template : new sap.ui.commons.TextField().bindProperty("value",
"region"),
sortProperty : "region"
}));
oTable.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({
text : "Street"
}),
template : new sap.ui.commons.TextField().bindProperty("value",
"street"),
sortProperty : "street"
}));
oTable.addColumn(new sap.ui.table.Column({
label : new sap.ui.commons.Label({
text : "City"
}),
template : new sap.ui.commons.TextField().bindProperty("value",
"city"),
sortProperty : "city"
}));
// Add table to the Panel
rPannel.addContent(oTable);
// Add panel to the Layout
layout.createRow(rPannel);
// Display Layout
this.addContent(layout);
}
});

Then bind the bank table in controller file. Add the following code in to the "onInit" function

onInit: function() {
//Initialize the Model
var oModel = new sap.ui.model.odata.ODataModel( "http://domain_name/sap/opu/odata/sap/Z_TM_BANK_SRV",false, "Username", "Password");
//Set the Model to the Table
var oTable = sap.ui.getCore().byId("bankTableID");
oTable.setModel(oModel);
// Filter the DATA
var FilterOperator = sap.ui.model.FilterOperator;
var filter = new sap.ui.model.Filter("bankCountry",FilterOperator.EQ, "AR");
//Bind the Data to the Table
oTable.bindRows("/BankCollection", null, null,[ filter ]);
},

Output :

After binding the table :

Create Operation :

1. Add the button in view file (already created-in view file)
2. Create the function for create operation in controller file

Add the following code mentioned below :

Create: function() {
// Create a dialog to get the information of the bank to be created
var oDialog = new sap.ui.commons.Dialog("Dialog", {
    modal: true,
closed: function(oControlEvent){
  sap.ui.getCore().getElementById('Dialog').destroy();
     }
  });
  oDialog.setTitle("New Bank Collection");
  // Create a layout to place the controls in the dialog
  var oLayout = new sap.ui.commons.layout.MatrixLayout({
columns: 2,
width: "100%"
  });
  // Create text field for the bankCountry
  var oTF = new sap.ui.commons.TextField("tbankCountry", {
          tooltip: 'Bank Country',
          editable: true,
          width: '200px',
        required: true
  });
var oLabel = new sap.ui.commons.Label("lbankCountry", {
    text: 'Bank Country',
   labelFor: oTF
  });
// Create the first row
  oLayout.createRow(oLabel, oTF);
  // Create text field for the bankID
  oTF = new sap.ui.commons.TextField("tbankID", {
tooltip: 'Bank ID',
   editable: true,
required: true,
width: '200px'
  });
oLabel = new sap.ui.commons.Label("lbankID", {
    text: 'Bank ID',
labelFor: oTF
});
  oLayout.createRow(oLabel, oTF);

  oTF = new sap.ui.commons.TextField("tbankName", { 
          tooltip: 'Bank Name',
             editable: true,
          required: true,
          width: '200px'
  });
  oLabel = new sap.ui.commons.Label("lbankName", {
   text: 'Bank Name',
  labelFor: oTF
  });
  oLayout.createRow(oLabel, oTF);
  oTF = new sap.ui.commons.TextField("tregion", {
      tooltip: 'Region Name',
          maxLength:3,
          editable: true,
               width: '200px
  });
  // Label for the last name field
  oLabel = new sap.ui.commons.Label("lregion", {
          text: 'Region Name',
          labelFor: oTF
  });
  // Create the 4th row
  oLayout.createRow(oLabel, oTF);
  oTF = new sap.ui.commons.TextField("tstreet", {
tooltip: 'Street Name',
editable: true,
width: '200px'
});
  oLabel = new sap.ui.commons.Label("lstreet", {
       text: 'Street Name',
     labelFor: oTF
  });
  oLayout.createRow(oLabel, oTF);
  oTF = new sap.ui.commons.TextField("tcity", {
     tooltip: 'City Name',
     editable: true,
       width: '200px'
});
  oLabel = new sap.ui.commons.Label("lcity", {
        text: 'City Name',
        labelFor: oTF
  });
  oLayout.createRow(oLabel, oTF);
// Add the layout to the dialog
  oDialog.addContent(oLayout);
// Add a button to post the bank's data to the odata interface
  oDialog.addButton(new sap.ui.commons.Button({text: "Save", press:function(){
  // Add a button to post the bank's data to the odata interface

    var bankCountry_var    = sap.ui.getCore().getControl("tbankCountry").getValue(); 
         var bankID_var      = sap.ui.getCore().getControl("tbankID").getValue(); 
         var bankName_var  = sap.ui.getCore().getControl("tbankName").getValue(); 
         var region_var   = sap.ui.getCore().getControl("tregion").getValue(); 
         var street_var   = sap.ui.getCore().getControl("tstreet").getValue(); 
         var city_var    = sap.ui.getCore().getControl("tcity").getValue(); 
         OData.request 
         ({  
              requestUri:      "http://server_name/sap/opu/odata/sap/Z_TM_BANK_SRV/BankCollection/?$filter=bankCountry eq'AR'",  
                    method: "GET",  
                    headers:  
                        {       
    "X-Requested-With": "XMLHttpRequest", 
   "Content-Type": "application/atom+xml", 
"DataServiceVersion": "2.0",          
"X-CSRF-Token":"Fetch"                                 }                    
                 },  
                 function (data, response) 
                 { 
     header_xcsrf_token = response.headers['x-csrf-token']; 
                  OData.request 
                  ({  
                       requestUri: 
                        "http://server_name/sap/opu/odata/sap/Z_TM_BANK_SRV/BankCollection",  
                             method: "POST",  
    headers: {   "X-Requested-With": "XMLHttpRequest",                        
"Content-Type": "application/atom+xml", 
     "DataServiceVersion": "2.0",  
"Accept": "application/atom+xml,application/atomsvc+xml,application/xml", 
"X-CSRF-Token": header_xcsrf_token    },  
                             data:  
                                 {  
                              bankCountry: bankCountry_var,  
                              bankID:bankID_var, 
                              bankName:bankName_var, 
                              region: region_var, 
                              street: street_var, 
                              city: city_var, 
                      }  
                          },
                          function (data, response) 
                            {  
                           document.location.reload(true);
                                             $("<div>Returned data " + window.JSON.stringify(data) + "</div>").appendTo($("#MessageDiv")); 
                            },  
                                   function (err)  
                                   { 
                                        $("<div>Returned error " + window.JSON.stringify(err.response) + "</div>").appendTo($("#MessageDiv")); 
                                   } 
                  ); 
        },  
        function (err)  
                       { 
                            var request = err.request; // the request that was sent. 
                            var response = err.response; // the response that was received. 
                            alert("Error in Get -- Request "+request+" Response "+response); 
                       } 
        );                      
  oDialog.close();
}}));
  oDialog.open();
},

Output :

Before creation :

After Creation :

Update Operation :

1. Add the button in view file (already created-in view file)
2. Create the function for update operation in controller file

Add the following code mentioned below :

Update : function() {
var oTable = sap.ui.getCore().getElementById('bankTableID');
       var i = oTable.getSelectedIndex();
       var ServiceURL = "http://server_name/sap/opu/odata/sap/Z_TM_BANK_SRV";
       if (i == -1) {
               alert("Please Select a row to Update");
               return;
             }else if(i>=0){

  


var selectedRow = oTable.getRows()[i];
var selectedId = selectedRow.getCells()[0].getValue();
        var selectedCount = selectedRow.getCells()[1].getValue();
        OData.read(ServiceURL + "/BankCollection(bankCountry='"
+ selectedCount + "',bankID='"
+ selectedId + "')",
        function(response) {
      var oDialogu = new sap.ui.commons.Dialog("Dialogu", {
                  modal: true,
           closed: function(oControlEvent){
          sap.ui.getCore().getElementById('Dialogu').destroy();
             }
  });
      oDialogu.setTitle("Update Bank Collection");
var oLayout = new sap.ui.commons.layout.MatrixLayout({
            columns: 2,
        width: "100%"
  });
  var oTF = new sap.ui.commons.TextField("tbankCountry", {
               tooltip: 'Bank Country',
    editable: false,
             value:response.bankCountry,
          width: '200px',
        required: true
  });
var oLabel = new sap.ui.commons.Label("lbankCountry", {
        text: 'Bank Country',
      labelFor: oTF
  });
  oLayout.createRow(oLabel, oTF);
   oTF = new sap.ui.commons.TextField("tbankID", {
          tooltip: 'Bank ID',
         editable: false,
          required: true,
          width: '200px',
      value:response.bankID
  });
  oLabel = new sap.ui.commons.Label("lbankID", {
          text: 'Bank ID',
          labelFor: oTF
  });
         oLayout.createRow(oLabel, oTF);
      oTF = new sap.ui.commons.TextField("tbankName", {
          tooltip: 'Bank Name',
          editable: true,
          required: true,
          width: '200px',
       value:response.bankName
  });
oLabel = new sap.ui.commons.Label("lbankName", {
          text: 'Bank Name',
          labelFor: oTF
  });
  oLayout.createRow(oLabel, oTF);
  oTF = new sap.ui.commons.TextField("tregion", {
          tooltip: 'Region Name',
          maxLength:3,
          editable: true,
          value:response.region ,
          width: '200px'
  });
  oLabel = new sap.ui.commons.Label("lregion", {
        text: 'Region Name',
         labelFor: oTF
  });

oLayout.createRow(oLabel, oTF);
  oTF = new sap.ui.commons.TextField("tstreet", {
tooltip: 'Street Name',
editable: true,
width: '200px',
  value:response.street
  });
  oLabel = new sap.ui.commons.Label("lstreet", {
text: 'Street Name',
    labelFor: oTF
  });
  oLayout.createRow(oLabel, oTF);
  oTF = new sap.ui.commons.TextField("tcity", {
          tooltip: 'City Name',
          editable: true,
          value:response.city,
          width: '200px'
  });
  oLabel = new sap.ui.commons.Label("lcity", {
text: 'City Name',
labelFor: oTF
  });
  oLayout.createRow(oLabel, oTF);
  oDialogu.addContent(oLayout);
  oDialogu.addButton(new sap.ui.commons.Button({text: "Update", press:function(){
  var bankCountry_var    = sap.ui.getCore().getControl("tbankCountry").getValue(); 
         var bankID_var      = sap.ui.getCore().getControl("tbankID").getValue(); 
         var bankName_var  = sap.ui.getCore().getControl("tbankName").getValue(); 
         var region_var   = sap.ui.getCore().getControl("tregion").getValue(); 
         var street_var   = sap.ui.getCore().getControl("tstreet").getValue(); 
         var city_var    = sap.ui.getCore().getControl("tcity").getValue(); 
  OData.request 
         ({  
  requestUri: 
               "http://server_name/sap/opu/odata/sap/Z_TM_BANK_SRV/BankCollection/?$filter=bankCountry eq'AR'",  
                    method: "GET",  
                    headers:  
                        {       
"X-Requested-With": "XMLHttpRequest", 
"Content-Type": "application/atom+xml", 
"DataServiceVersion": "2.0",          
"X-CSRF-Token":"Fetch"  
                          }                    
                 },  
                 function (data, response) 
                 { 
           header_xcsrf_token = response.headers['x-csrf-token']; 
                  OData.request 
                  ({  
                       requestUri: "http://server_name/sap/opu/odata/sap/Z_TM_BANK_SRV/BankCollection(bankCountry='"+ selectedCount + "',bankID='"+ selectedId+ "')",  
                             method: "PUT",  
                             headers: {  
     "X-Requested-With": "XMLHttpRequest",                        
"Content-Type": "application/atom+xml", 
"DataServiceVersion": "2.0",  
  "Accept": "application/atom+xml,application/atomsvc+xml,application/xml", 
  "X-CSRF-Token": header_xcsrf_token  
  },   data:  
    {    bankCountry: bankCountry_var,  
      bankID:bankID_var, 
       bankName:bankName_var, 
       region: region_var, 
        street: street_var, 
       city: city_var, 
}  
                          },
   function (data, response) 
                            {
var oSubDialog = new sap.ui.commons.Dialog( {title: "Updated",
                           content : [new sap.ui.commons.Label({
                           text : "Data Updated Successfully"
                           })]});
                           oSubDialog.open();
                           oSubDialog.addButton(new sap.ui.commons.Button({text: "OK", press:function(){oSubDialog.close();}}));                       
                     
                                             $("<div>Returned data " + window.JSON.stringify(data) + "</div>").appendTo($("#MessageDiv")); 
                                       //      document.location.reload(true);
                            },  
                                   function (err)  
                                   { 
                                        $("<div>Returned error " + window.JSON.stringify(err.response) + "</div>").appendTo($("#MessageDiv")); 
                                   } 
                  ); 
        },  
   function (err)  
{ 
   var request = err.request; // the request that was sent. 
var response = err.response; // the response that was received. 
alert("Error in Get -- Request "+request+" Response "+response); 
                       } 
        );
  oDialogu.close();
     }}));
  oDialogu.open();    
        });
       }
    },

Output :

Before Update :

After Update :

Delete Operation

1. Add the button in view file (already created-in view file)
2. Create the function for create operation in controller file

Add the following code mentioned below :

Delete : function(oEvent) {
         var oTable = sap.ui.getCore().getElementById('bankTableID');
  // Retrieve the selected index, i.e., the index of the selected row
      var i = oTable.getSelectedIndex();
var ServiceURL = "http://server_name/sap/opu/odata/sap/Z_TM_BANK_SRV";
        if (i == -1) {
               alert("Please Select a row to Delete");
               return;
             }
             else if(i>=0){
        var selectedRow = oTable.getRows()[i];
        var selectedId = selectedRow.getCells()[0].getValue();
        var selectedCount = selectedRow.getCells()[1].getValue();
       }
     OData.request 
        ({  requestUri: "http://server_name/sap/opu/odata/sap/Z_TM_BANK_SRV/BankCollection/?$filter=bankCountry eq'AR'",  
                   method: "GET",  
                   headers:  
                       {       
  "X-Requested-With": "XMLHttpRequest", 
   "Content-Type": "application/atom+xml", 
  "DataServiceVersion": "2.0",          
  "X-CSRF-Token":"Fetch"  
                         }                    
                },
                function (data, response) 
                {  
                       header_xcsrf_token = response.headers['x-csrf-token']; 
           OData.request 
           ({ 
            requestUri: "http://server_name/sap/opu/odata/sap/Z_TM_BANK_SRV/BankCollection(bankCountry='"+ selectedCount + "',bankID='"+ selectedId+ "')",
  method: "DELETE",  
  headers: {  
                    "X-Requested-With": "XMLHttpRequest",                        
                    "Content-Type": "application/atom+xml", 
                    "DataServiceVersion": "2.0",  
                    "X-CSRF-Token": header_xcsrf_token  
                         } 
                   },  
                     function (data, request) 
                     { 
                    document.location.reload(true);
                                 $("<div>Returned data in Delete " + window.JSON.stringify(data) + "</div>").appendTo($("#MessageDiv")); 
                      },  
                    function (err)  
                    { 
                                 $("<div>Returned error in Delete " + window.JSON.stringify(err.response) + "</div>").appendTo($("#MessageDiv"));                              
                     } 
           ); 
       }, 
         function (err)  
             { 
                      var request     = err.request;  
                      var response = err.response;  
                      alert("Error in Get -- Request "+request+" Response "+response); 
             } 
       );       
    }
});

Output:

Before Delete :

Before delete four records are there in the table.

After Delete :

After delete three records are there in the table.

In this way we can create the desktop application in sapui5 with MVC structure and for the backend data we have to consume the Nerweaver Gateway Service.In this application Odta Model is used.