Skip to Content
Technical Articles
Author's profile photo Vaibhav Revankar

uploaded Excel Huge Data convert into JSON format without any performance issue in browser side

Hi Experts,

I have worked in one SAP UI5 Project , and i was having one requirement that User’s will upload the huge Excel file , and that has to be read in browser side (Client side) and file line item has to pass into Odata / PI interface, i have tried many other ways but browser was responding slow due to huge data to read and convert into required Format .

Finally i have found the this JS class and logic which is best way to fullfill the requirement without any weighted to browser.if you want to read uploaded Excel / CSV data from the Portal Application , this is the best way to read DATA from the file and move it into JSON format

So Kindly Find the below steps and code  and required class,

1 step : below files need to add in your project and need to call as shown below

jQuery.sap.require(“*******.util.xlsx”);
jQuery.sap.require(“*******.util.xlsxfullmin”);

 

Step 2:  Main Function to read the data with the help of xlsx class

function ProcessExcel(reader) {

var workbook = XLSX.read(reader, {
type: ‘binary’
});

 

NOTE:  For more logic kindly find the controller file below , w is also contain read data from excel and converting to Json Format and slo table binding 

  1. Below screen with Excel data

 

Upload file screenshot

Converted Data into Json Array

 

response data with mapping into popup table

jQuery.sap.require("sap.ui.core.util.Export");
jQuery.sap.require("sap.ui.core.util.ExportTypeCSV");
jQuery.sap.require("*********.util.xlsx");
jQuery.sap.require("*********.util.jszip");
jQuery.sap.require("*********.util.xlsxfullmin");
jQuery.sap.require("*********.lancer");
sap.ui.controller("**********_po.batch", {

onInit: function(oEvent) {
	
	oControllerBatch = this;
	oControllerBatch.emailid1;
	vndrcode =  parseInt(LoggedInUserData.vendorCode);
	oControllerBatch.compaModel = new sap.ui.model.json.JSONModel();
	var omodel = new sap.ui.model.json.JSONModel();
	
	/////  API to get the all as report 
	omodel.loadData("https://*****URL********"+vndrcode ,null,false);
    var mdlObj = omodel;
	oControllerBatch.compaModel.setData(mdlObj); 
	
	if(oControllerBatch.compaModel.getData().oData.BatchCreationResponseMessage != undefined && 
			oControllerBatch.compaModel.getData().oData.BatchCreationResponseMessage != "")
	{
		   if(oControllerBatch.compaModel.getData().oData.BatchCreationResponseMessage.record.length == undefined)
		    {
			if(oControllerBatch.compaModel.getData().oData.BatchCreationResponseMessage.record.STATUS == "APPROVED")
			{
			oControllerBatch.compaModel.getData().oData.BatchCreationResponseMessage.record.STATUS = "CONFIRMED";
			}
			var oData = {"oData":{"BatchCreationResponseMessage":{"record":[oControllerBatch.compaModel.getData().oData.BatchCreationResponseMessage.record]}}};
			
			oControllerBatch.compaModel.setData(oData);
			oControllerBatch.getView().setModel(oData, "CO");
		   }
		   else
		   {/////////////// to make approve to confirmend
			var statusconfirm = "CONFIRMED";
 			
 			 for(var i = 0; i<oControllerBatch.compaModel.getData().oData.BatchCreationResponseMessage.record.length; i++)
 			
			 oControllerBatch.getView().setModel(oControllerBatch.compaModel, "CO");
		     mdlObj.oData.BatchCreationResponseMessage.record.sort(function(a,b) {return (a.dateCreated < b.dateCreated) ? 1 : ((b.dateCreated < a.dateCreated) ? -1 : 0);} );
		    }

	}
	else if(oControllerBatch.compaModel.getData().oData.BatchCreationResponseMessage == undefined ||
			oControllerBatch.compaModel.getData().oData.BatchCreationResponseMessage == "")
	{
		var oData = {"oData":{"BatchCreationResponseMessage":{"record":[]}}};
		oControllerBatch.compaModel.setData(oData);
		oControllerBatch.getView().setModel(oControllerBatch.compaModel, "CO");
	}
				
	sap.ui.getCore().byId("NavLeftIdR2").setEnabled(false);
	sap.ui.getCore().byId("NavRightIdR2").setEnabled(false);
	this.a;
	this.IPage = 0;
	this.EPage = 10;
	this.fileData;
	
	sap.ui.core.UIComponent.getRouterFor(this).attachRouteMatched(function(oEvent) {
		// when detail navigation occurs, update the binding context
		if ((oEvent.getParameters().name === "batch") && (oEvent.getParameters().arguments.id === "d_display")){
			************.util.borderCss.selectHeaderButton("batchHeaderId");
			oControllerBatch._refreshPage();
			oControllerBatch._pagination(
					oControllerBatch.IPage,
					oControllerBatch.EPage,
					"CO");
		}
		
	});
},

onAfterRendering:function(){

	setTimeout(function() {
		sap.ui.getCore().byId("NavLeftIdR2").setEnabled(false);
		oControllerBatch._pagination(oControllerBatch.IPage,oControllerBatch.EPage,"CO");

						   },100);

},

 _refreshPage: function(){
	 sap.ui.getCore().byId("startDateIdR2").setValue("");
	 sap.ui.getCore().byId("endDateIdR2").setValue("");
},

//On Create Request Button**********************************************************************************
CreateReqButton:function(){
	
	var dialogCreatebatch = new sap.m.Dialog({
		type: 'Message',
		showHeader:false,
		contentWidth:"45%",

		subHeader:[ new sap.m.OverflowToolbar({
			content:[
                       new sap.m.Text({text:"Upload Batch Creation File"}).addStyleClass("TitleBold"),
                       new sap.m.ToolbarSpacer({}),
                       new sap.m.Button("cancelbatch",{text:"Cancel",
                    	   setEnabled:false,
                    	   layoutData: new sap.m.OverflowToolbarLayoutData({
		                   priority:sap.m.OverflowToolbarPriority.NeverOverflow}),
              			   press: function () {
              					dialogCreatebatch.close();
              					dialogCreatebatch.destroyContent();
              				}}).addStyleClass("WhiteBtnClass"),
              				new sap.m.Button("downloadbatch",{text:"Download Template",
                         	   setEnabled:false,
                         	   layoutData: new sap.m.OverflowToolbarLayoutData({
     		                   priority:sap.m.OverflowToolbarPriority.NeverOverflow}),
                   			   press: function () {
                   				window.open("***path****Batch_creation.xlsx");
                   				}}).addStyleClass("WhiteBtnClass"),
              				
              		new sap.m.Button("createFibatchid",{text:"Create",
              			enabled:false,
              			layoutData: new sap.m.OverflowToolbarLayoutData({
                        priority:sap.m.OverflowToolbarPriority.NeverOverflow}),
           			    press: function (fileList) {
                        var fileUpload =  sap.ui.getCore().byId("ledgerUpload");
               			var testData = oControllerBatch.fileData ;
               			var reader = new FileReader();
               	        that = this;
             			var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
             		     if (regex.test(fileUpload.oFileUpload.value.toLowerCase())) {
             	            if (typeof (FileReader) != "undefined") {
             	                var reader = new FileReader();
             	                reader.readAsBinaryString(fileUpload.oFileUpload.files[0]);
             	                var test = reader.result;
             	                //For Browsers other than IE.
             	                if (reader.readAsBinaryString) {
             			          reader.onload = function(test) {
             				       ProcessExcel(test.target.result);
             			             };
              		           }
              	            else {
                                  //For IE Browser.
                                  reader.onload = function (event) {
                                      var data = "";
                                      var bytes = new Uint8Array(e.target.result);
                                      for (var i = 0; i < bytes.byteLength; i++) {
                                      data += String.fromCharCode(bytes[i]);
                                      }
                                      ProcessExcel(data);
                                  };
                                  reader.readAsArrayBuffer(fileUpload.files[0]);
                              }
                          } else {
                              alert("This browser does not support HTML5.");
                          }
                      } else {
                          alert("Please upload a valid Excel file.");
                      };
              	        
                      function ProcessExcel(reader) {
                    	var workbook = XLSX.read(reader, {
               	            type: 'binary'
               	        });
              	 
              	        //Fetch the name of First Sheet.
              	        var firstSheet = workbook.SheetNames[0];
              	 
              	        //Read all rows from First Sheet into an JSON array.
              	        var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet]);
              	        
              	        var brokenMdl = {"SephoraBatch_RequestMessage":{
					  		"Record": []}};
	
          	      for(var i=0;i<excelRows.length;i++){

          	    		var oData = {"SephoraBatch_RequestMessage": {"Record":
          	    			         [{
          						       "MaterialNo": excelRows[i].MaterialNo, 
          						       "EAN": excelRows[i].EAN,
          						       "BatchNo": excelRows[i].BatchNo,
          						       "VendorNo": "",
          						       "ManufacturerDate": excelRows[i].ManufacturerDate,
          						       "ExpiryDate": excelRows[i].ExpiryDate,
          						       "MRPValue": excelRows[i].MRPValue,
          						       "VendorBatch": excelRows[i].VendorBatch
          	    	                 }]	
          				            }};

					
				var serviceUrl =  "https://***************";
          	  	var deletemdlObj = a.vendor.aVendor.lancer.getDataFromRest(serviceUrl, JSON.stringify(oData));
				}
          	     var dialogCreate = new sap.m.Dialog({
         		     contentWidth:"45%",
         		     showHeader:false,
         		     content:[
         		                          
         		             new sap.m.Panel("invoiceDocumentNumber",{width:"auto",
         		             headerToolbar : [new sap.m.OverflowToolbar({content : [
         		             new sap.m.Title ({text : "Batch Created successfully:"}).addStyleClass("TitleBold"),                  
         		             new sap.m.ToolbarSpacer({}),
         		             new sap.m.Button(
         		             {
         		              icon : "sap-icon://decline",
         		              tooltip : "Close",
         		              press : function() {
         		              dialogCreate.close();
         		              dialogCreate.destroyContent();
                              }
         		             }).addStyleClass("WhiteBtnClass")
         		            ]})],
         		                          
         		            content : [
         		               new sap.m.IconTabBar("invIconTabbar",{
         		               items : [
    		                    new sap.m.IconTabFilter({
         		                text : "Details",
         		                content : [
         		                  new sap.m.Table("tableGRN",{
         		                  mode: sap.m.ListMode.None,
         		                  noDataText : "No DATA", 
         		                  columns : [
         							        new sap.m.Column({ hAlign : sap.ui.core.TextAlign.Left,minScreenWidth:"Small", demandPopin:true,width:"15%", 
         									header:[
         											new sap.m.Label({ text:"Batch Number"}).addStyleClass("TableColumnHeader")
         										   ]}),
         									new sap.m.Column({ hAlign : sap.ui.core.TextAlign.Left,minScreenWidth:"Small", demandPopin:true,width:"20%", 
         									header:[
         											new sap.m.Label({ text:"Plant"}).addStyleClass("TableColumnHeader")
         										   ]}),
         									new sap.m.Column({ hAlign : sap.ui.core.TextAlign.Left,minScreenWidth:"Small", demandPopin:true,width:"20%", 
         	         						header:[
         	         								new sap.m.Label({ text:"Storage Location"}).addStyleClass("TableColumnHeader")
         	         							   ]})            
         		                            ],
         		                            items: {
         		                                    path: 'grnModel>/items',          // OData model path
         		                                    template: new sap.m.ColumnListItem({
         				                            cells:[ 
         				                                   new sap.m.Text({text:"{grnModel>BatchNo}"}),
         				                                   new sap.m.Text({text:"{grnModel>Plant}"}),
         				                                   new sap.m.Text({text:"{grnModel>StorageLocation}"}),
         				                                  ]})
        		                                    }
         		                                                           							                   
         		                            })
         		                            ]
         		                            }),
                                		    ]
         		                           })
         		                           ]}).addStyleClass("sapUiLargeMarginBeginEnd sapUiMediumMarginTopBottom PanelSubHeader PanelHeader")
         		                       
         		                     ]
         		              });

         		              dialogCreate.open();
					         var grnTableModel = new sap.ui.model.json.JSONModel();
					         var oData = {items: []} ;
				             oData.items = deletemdlObj.data.SephoraBatchCreationResponseMessage.SephoraBatchMessage.Record;
			                 var grnTableModel = new sap.ui.model.json.JSONModel();
				             grnTableModel.setData(oData);
				             sap.ui.getCore().setModel(grnTableModel, "grnModel");
				             sap.ui.getCore().byId("tableGRN").setModel();

              	      };

 
              			   var vMsg = "Request has been successfully ";
              		       var fdialog = new sap.m.Dialog({title: "", content: new sap.m.Text({	text:vMsg})});
              			   fdialog.addButton(new sap.m.Button({
              	           text: "OK",
              	           press: function(){fdialog.close();}}));
              			   fdialog.open();            			
               			   dialogCreatebatch.destroyContent();
               			   dialogCreatebatch.close();
                     },
	                       layoutData: new sap.m.OverflowToolbarLayoutData({
                           priority:sap.m.OverflowToolbarPriority.NeverOverflow})
              		}).addStyleClass("redButtonClass"),
           		
			         ]}).addStyleClass("diaToolbarClass")],
		
		            content:[
		                   new sap.m.HBox({
							items : []}),
			       
					      new sap.ui.layout.VerticalLayout({
						   content:[
		        	             new sap.m.Label({text:"Batch Creation File",required :true}),
		 				         fileList = null,
		                         new sap.ui.unified.FileUploader("ledgerUpload",{
		                 	     placeholder:"Choose the *.xlsx file",
		                 	     buttonText :"    ",
		                 	    // fileType : ["xls", "xlsx"],
		                 	    icon :"sap-icon://attachment",
		                 	    change: function(oEvent) {
		                		var reader = new FileReader();
		            			var fileList = oEvent.oSource.oFileUpload.files[0];
		            			reader.readAsDataURL(fileList);
		            			reader.onload = function(event) {
                                    var sResult = event.target.result;
		            				var base64 = sResult.substr(sResult.lastIndexOf(','));
		            				oControllerBatch.fileData = base64.split(",")[1]; 
		            				sap.ui.getCore().byId("createFibatchid").setEnabled(true);
		            			};
               					},
               				
		                 	   typeMissmatch : function (){ 
		                	   jQuery.sap.require("sap.m.MessageBox");
		               			  sap.m.MessageBox.show(
               					"Please upload the file in xlsx format only", {
               					
                                	});
               			      } }).addStyleClass("uploaderSpace"),

                          new sap.ui.layout.VerticalLayout({
		     	              content:[
		     							new sap.m.Label({text:"field marked with * are mandatory"})
		     				      ]})
		     					]})
		                   ],
		              beforeOpen: function(){
                      if(sap.ui.Device.system.phone){
                            this.setStretch(true);
                    }else{
                            this.setStretch(false);
                         }
},
		
		
afterOpen: function(){
		this.focus();
},
		
afterClose: function() {
	    var router = sap.ui.core.UIComponent.getRouterFor(oControllerBatch);
		router.navTo("batch", false);
		dialogCreatebatch.destroy();
	}}).addStyleClass("DialogClass");
        dialogCreatebatch.open();		
},

/////////////////////
onChangeDialogDate:function(oEvent){
	var oDP = oEvent.oSource;
	var oEvtParameter = oEvent.getParameters();
	var bDateValid = oEvent.getParameter("valid");
	var sMaxDate = new Date();
    if (oEvtParameter.id === "statPeriod") {
	    var dStartDate = oDP.getDateValue();
		var dEndDate = sap.ui.getCore().byId("endPeriod").getDateValue();
            if (!bDateValid) {
				oDP.setValueState(sap.ui.core.ValueState.Error);
				oDP.setValueStateText("Invalid Date Format");
				oDP.setValue();
		    } else if (dStartDate > sMaxDate) {
				oDP.setValueState(sap.ui.core.ValueState.Error);
				oDP.setValueStateText("From Date cannot accept future dates");
				oDP.setValue();
		    }else if (dEndDate !== null && dStartDate > dEndDate) {
				oDP.setValueState(sap.ui.core.ValueState.Error);
				oDP.setValueStateText("From date should not be greater than To date");
				oDP.setValue();
			}

	}else {
		var dEndDate = oDP.getDateValue();
		var dStartDate = sap.ui.getCore().byId("statPeriod").getDateValue();
			if (!bDateValid) {
				oDP.setValueState(sap.ui.core.ValueState.Error);
				oDP.setValueStateText("Invalid Date Format");
				oDP.setValue();
			} else if (dEndDate > sMaxDate) {
				oDP.setValueState(sap.ui.core.ValueState.Error);
				oDP.setValueStateText("To Date cannot accept future dates");
				oDP.setValue();
			} else if (dEndDate !== null && dEndDate < dStartDate) {
				oDP.setValueState(sap.ui.core.ValueState.Error);
				oDP.setValueStateText("To date must be greater than From date");
				oDP.setValue();
			}
			else{
				oDP.setValueState(sap.ui.core.ValueState.None);
				}
			}
},

_parseDate:function(dateString){
	var dateParts = dateString.split(" ");
	var dateArr = dateParts[0].split("-");
	return new Date(dateArr[0], dateArr[1]-1, dateArr[2]);
 },


 _onFilter: function(){
		var oStartDate = sap.ui.getCore().byId("startDateIdR2").getDateValue();
		var oEndDate = sap.ui.getCore().byId("endDateIdR2").getDateValue();
		var REModel = oControllerBatch.getView().getModel("CO").getData().oData.BatchCreationResponseMessage.record;
		var reModel = oControllerBatch.getView().getModel("CO");
		this.getView().setModel(reModel, "searchModel");
		this._pagination(0, 10, "searchModel");	

		if(oStartDate !== null && oEndDate !== null){
			var searchModel = oControllerBatch.getView().getModel("searchModel").getData().oData.BatchCreationResponseMessage.record;
			var omodel = new sap.ui.model.json.JSONModel();
			var oData = {"oData":{"BatchCreationResponseMessage":{"record":[]}}};
			for (var z=0; z<searchModel.length; z++){
				var createDate = searchModel[z].dateCreated.slice(0,10);
				var parsedate = this._parseDate(createDate);
				if ((parsedate >= oStartDate) && (parsedate <= oEndDate)) {
					oData.oData.BatchCreationResponseMessage.record.push(searchModel[z]); 
					omodel.setData(oData);
		        }
				else{
					oData.oData.BatchCreationResponseMessage.record.push(); 
						omodel.setData(oData);  
				}
			}
			this.getView().setModel(omodel, "searchModel");
			this._pagination(0, 10, "searchModel");			
		}		
	},

//For Date selection******************************
onChange:function(oEvent){
	var oDP = oEvent.oSource;
	var oEvtParameter = oEvent.getParameters();
	var bDateValid = oEvent.getParameter("valid");
	var sMinDate =  new Date(new Date().setMonth(new Date().getMonth()-6));
	sMinDate.setHours(0);
    sMinDate.setMinutes(0);
	sMinDate.setSeconds(0);
	sMinDate.setMilliseconds(0);
	var sMaxDate = new Date();
	sMaxDate.setHours(0);
	sMaxDate.setMinutes(0);
	sMaxDate.setSeconds(0);
	sMaxDate.setMilliseconds(0);
	if(oEvtParameter.id === "startDateIdR2"){
		var dStartDate = oDP.getDateValue();
		var dEndDate = sap.ui.getCore().byId("endDateIdR2").getDateValue();
		 if(!bDateValid){
			oDP.setValueState(sap.ui.core.ValueState.Error);
            oDP.setValueStateText("Invalid Date Format");
            oDP.setValue();
		}
		 else if(dStartDate > sMaxDate){
			 oDP.setValueState(sap.ui.core.ValueState.Error);
             oDP.setValueStateText("From Date cannot accept future dates");
             oDP.setValue();
		}
		else if(dEndDate !== null && dStartDate>dEndDate){
			oDP.setValueState(sap.ui.core.ValueState.Error);
            oDP.setValueStateText("From date should not be greater than To date");
            oDP.setValue();
		}
		else{
			 oDP.setValueState(sap.ui.core.ValueState.None);
			 oControllerBatch._onFilter();
		}
	}
	else{
		var dEndDate = oDP.getDateValue();
		var dStartDate = sap.ui.getCore().byId("startDateIdR2").getDateValue();		
		 if(!bDateValid){
		    oDP.setValueState(sap.ui.core.ValueState.Error);
            oDP.setValueStateText("Invalid Date Format");
            oDP.setValue();
		}
		else if(dEndDate > sMaxDate){
			 oDP.setValueState(sap.ui.core.ValueState.Error);
             oDP.setValueStateText("To Date cannot accept future dates");
             oDP.setValue();
		}
		else if(dEndDate !== null && dEndDate<dStartDate){
			oDP.setValueState(sap.ui.core.ValueState.Error);
            oDP.setValueStateText("To date must be greater than From date");
            oDP.setValue();
		}		
		else{
			oDP.setValueState(sap.ui.core.ValueState.None);
			oControllerBatch._onFilter();
		}
	}        
	
},

//private function for pagination *************************************************************************** 
_pagination: function(pIp,pEp,pModelN){
	var Total = 0;
	var oREModel = new sap.ui.model.json.JSONModel();
	oControllerBatch.IPage = pIp;
	oControllerBatch.EPage = pEp;
	
       if (oControllerBatch.getView().getModel(pModelN).oData == undefined){
         if(oControllerBatch.getView().getModel(pModelN).oData.oData.BatchCreationResponseMessage.record.length !== 0){
           var reId = this.getView().getModel(pModelN).oData.BatchCreationResponseMessage.record;
		   reId.sort(function(a,b) {return (a.dateCreated < b.dateCreated) ? 1 : ((b.dateCreated < a.dateCreated) ? -1 : 0);} );
		   Total = reId.length;
		  oData = {"items":[{
			  "BatchId":[]
		   }]
		   };
		 if(Total<= pEp){
			 	pEp = Total;
			 }
		 for (var z=pIp; z<pEp; z++ ){
			oData.items[0].BatchId.push(reId[z]);
			  oREModel.setData(oData);
			 var m = pIp+1;
			 sap.ui.getCore().byId("textidpageR2").setText("Showing "+m+"-"+pEp+" of "+Total);
		   }	
	     }
      
       }else if (oControllerBatch.getView().getModel(pModelN).oData != undefined){
	      if(oControllerBatch.getView().getModel(pModelN).oData.oData != undefined){
            var reId = this.getView().getModel(pModelN).oData.oData.BatchCreationResponseMessage.record;
		   reId.sort(function(a,b) {return (a.dateCreated < b.dateCreated) ? 1 : ((b.dateCreated < a.dateCreated) ? -1 : 0);} );
		   Total = reId.length;
		  oData = {"items":[{
			  "BatchId":[]
		   }]
		   };
		   if(Total<= pEp){
			 	pEp = Total;
			 } 
		 else if(pEp!=10){pEp = Total-pEp;}
		 for (var z=pIp; z<pEp; z++ ){
			oData.items[0].BatchId.push(reId[z]);
			  oREModel.setData(oData);
			 var m = pIp+1;
			 sap.ui.getCore().byId("textidpageR2").setText("Showing "+m+"-"+pEp+" of "+Total);
		 }	
	 }else if(oControllerBatch.getView().getModel(pModelN).oData.BatchCreationResponseMessage.record.length !== 0)
	  {
		  var reId = this.getView().getModel(pModelN).oData.BatchCreationResponseMessage.record;
		  reId.sort(function(a,b) {return (a.dateCreated < b.dateCreated) ? 1 : ((b.dateCreated < a.dateCreated) ? -1 : 0);} );
			Total = reId.length;
			 oData = {"items":[{
				  "BatchId":[]
			 }]
			 };
			 if(Total<= pEp){
				 	pEp = Total;
				 }
			 for (var z=pIp; z<pEp; z++ ){
				oData.items[0].BatchId.push(reId[z]);
				  oREModel.setData(oData);
				 var m = pIp+1;
				 sap.ui.getCore().byId("textidpageR2").setText("Showing "+m+"-"+pEp+" of "+Total);
			 }	
		  
	  }
    }else{
		sap.ui.getCore().byId("textidpageR2").setText("Showing 0-0 of 0");
	    }
	
	 oControllerBatch.getView().setModel(oREModel, "BAT");
	 if(Total<= pEp){
		 	pEp = Total;
		 	sap.ui.getCore().byId("NavRightIdR2").setEnabled(false);
		 }
		 else{
				  sap.ui.getCore().byId("NavRightIdR2").setEnabled(true);
		 }
		 if(pIp === 0){
		 sap.ui.getCore().byId("NavLeftIdR2").setEnabled(false);	
		 }     
	
},

//navigate to next page of pagination **************************************************************************
nextPage: function(){
	
	if(oControllerBatch.compaModel === undefined){
	var ModelN = "CO";}
	else if(oControllerBatch.getView().getModel("searchModel") !== undefined && oControllerBatch.getView().getModel("searchModel").getData().oData.BatchCreationResponseMessage.record.length !== 0){
	var ModelN = "searchModel";}
	else{
		var ModelN = "CO";}
	sap.ui.getCore().byId("NavLeftIdR2").setEnabled(true);
	sap.ui.getCore().byId("NavRightIdR2").setEnabled(true);
	oControllerBatch.IPage = oControllerBatch.IPage + 10;
	oControllerBatch.EPage = oControllerBatch.EPage + 10;
	var TabLength = this.getView().getModel(ModelN).getData().oData.BatchCreationResponseMessage.record.length;
	if(TabLength<=oControllerBatch.EPage){
		sap.ui.getCore().byId("NavRightIdR2").setEnabled(false);
		this._pagination(oControllerBatch.IPage, TabLength, ModelN);
	}
	else{
		this._pagination(oControllerBatch.IPage, oControllerBatch.EPage, ModelN);	
	}
},

//navigate to previous page of pagination **************************************************************************
prevPage: function(){
	if(this.getView().getModel("searchModel") === undefined){
	var ModelN = "CO";}
	else if(this.getView().getModel("searchModel") !== undefined && this.getView().getModel("searchModel").getData().oData.BatchCreationResponseMessage.record.length !== 0){
	var ModelN = "searchModel";}
	else{
		var ModelN = "CO";}
		sap.ui.getCore().byId("NavLeftIdR2").setEnabled(true);
		sap.ui.getCore().byId("NavRightIdR2").setEnabled(true);
		this.IPage = this.IPage - 10;
		this.EPage = this.EPage - 10;
		if(this.IPage === 0){
			sap.ui.getCore().byId("NavLeftIdR2").setEnabled(false);
			this._pagination(this.IPage, this.EPage, ModelN);
		}
		else{
			this._pagination(this.IPage, this.EPage, ModelN);
		}
			
		},

_findBrowser : function(){
var ua = navigator.userAgent;
var browerEx = ua.match(/(opera|chrome|safari|firefox|msie|trident(?=\/))\/?\s*(\d+)/i) || [];
browerEx = browerEx[2] ? [browerEx[1], browerEx[2]] : [navigator.appName, navigator.appVersion, '-?'];
if ((tem = ua.match(/version\/(\d+)/i)) !=null) {
	browerEx.splice(1,1,tem[1]);
}
return browerEx;
},

///////////////////////////////// download link function start
//-----------------------------------------Download report for BAT----------------------------------------------------------------------------//

onDataExport :  function(oEvent) {	
	    var cobname ="Sephora Batch Report"; 
		var cobdatefrom = sap.ui.getCore().byId("startDateIdR2").getDateValue();
		var cobdateto = sap.ui.getCore().byId("endDateIdR2").getDateValue();
		var monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",
	                      "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
	                      ];
	                          
	    if(cobdatefrom != null && cobdateto != null )
	    {                 		                  
		 var cobdatefm = cobdatefrom.getDate()+''+monthNames[cobdatefrom.getMonth()]+''+(cobdatefrom.getFullYear().toString().substr(2,2));
         var cobdatetoo = cobdateto.getDate()+''+monthNames[cobdateto.getMonth()]+''+(cobdatefrom.getFullYear().toString().substr(2,2)); 
	     var oExport = new sap.ui.core.util.Export({
		    // Type that will be used to generate the content. Own ExportType's can be created to support other formats
			exportType : new sap.ui.core.util.ExportTypeCSV({
				separatorChar : ",",
			    charset: "utf-8"
			}),

			// Pass in the model created above
			models : oControllerBatch.getView().getModel("CO"),

			// binding information for the rows aggregation
			rows : {
				path : "/oData/BatchCreationResponseMessage/record"
			},

			// column definitions with column name and binding info for the content

			columns : [{
				name : "EAN",
				template : {
					content : "{ean}"
				}
			}, 
			{
				name : "SAP Batch Number",
				template : {
					content : "{sapBatchNumber}"
				}
			}, 
			{
				name : "Date Created",
				template : {
					content : "{CREATED_BY}"
				}
			}, 
			{
				name : "Vendor Number",
				template : {
					content : "{vendorCode}"
				}
			}, 
			{
				name : "Vendor Batch",
				template : {
					content : "{vendorBatch}"
				}
			},
			{
				name : "Manufacture Date",
				template : {
					content : "{manufactureDate}"
				}
			}, 
			{
				name : "Expiry Date",
				template : {
					content : "{expiryDate}"
				}
			},
			{
				name : "MRP (₹)",
				template : {
					content : "{mrp}"
				}
			}]
		});
	 if(cobdatefrom != null && cobdateto != null )
     {
	// download exported file
	oExport.saveFile(cobname+"_"+cobdatefm+"to"+cobdatetoo).catch(function(oError) {
		MessageBox.error("Error when downloading data. Browser might not be supported!\n\n" + oError);
	}).then(function() {
		oExport.destroy();
	});
     }
     else
     {
     	oExport.saveFile(cobname).catch(function(oError) {
		MessageBox.error("Error when downloading data. Browser might not be supported!\n\n" + oError);
	}).then(function() {
		oExport.destroy();
	});
     }

}

});

 

 

Conclusion  :   Without any time Consuming we can read a huge data from the File in client side Browser. Reading files and converting into required format into a browser side is normally  it will take much time to get the data if file contains more line items ,  But By using “xlsx.js” “xlsxfullmin.js” files now it’s very easy and in fraction of seconds possible to get the data into our required format even file having huge data line item.

 

Regards,

Vaibhav Revankar.

SAP ABAP + UI5 + Odata .

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Abhiraj Abhiraj
      Abhiraj Abhiraj

      Thanks, this is really working

      Author's profile photo Vaibhav Revankar
      Vaibhav Revankar
      Blog Post Author

      Welcome!

      Author's profile photo Jun Wu
      Jun Wu

      controller code is not in good shape. tons of issue....

       

      for pagination, did it work if you have sort or filtering on?

      Author's profile photo Vaibhav Revankar
      Vaibhav Revankar
      Blog Post Author

      Hi Jun Wu,

      Kindly Find the below more details on sort and filtration. with screenshots and code as below

      Total 17 records as shown below screen

      Sort and Date Range for filtering data as below screen.

      Date created filed filtration based on recent created record in TOP (descending order )

      CODE:  for sorting and descending order for the “dateCreated” field

      reId.sort(function(a,b) {return (a.dateCreated < b.dateCreated) ? 1 : ((b.dateCreated < a.dateCreated) ? -1 : 0);} );

      find the full filtration code below .

      //For Date selection******************************

      onChange:function(oEvent){
      var oDP = oEvent.oSource;
      var oEvtParameter = oEvent.getParameters();
      var bDateValid = oEvent.getParameter(“valid”);
      var sMinDate = new Date(new Date().setMonth(new Date().getMonth()-6));
      sMinDate.setHours(0);
      sMinDate.setMinutes(0);
      sMinDate.setSeconds(0);
      sMinDate.setMilliseconds(0);
      var sMaxDate = new Date();
      sMaxDate.setHours(0);
      sMaxDate.setMinutes(0);
      sMaxDate.setSeconds(0);
      sMaxDate.setMilliseconds(0);
      if(oEvtParameter.id === “startDateIdR2”){
      var dStartDate = oDP.getDateValue();
      var dEndDate = sap.ui.getCore().byId(“endDateIdR2”).getDateValue();
      if(!bDateValid){
      oDP.setValueState(sap.ui.core.ValueState.Error);
      oDP.setValueStateText(“Invalid Date Format”);
      oDP.setValue();
      }
      else if(dStartDate > sMaxDate){
      oDP.setValueState(sap.ui.core.ValueState.Error);
      oDP.setValueStateText(“From Date cannot accept future dates”);
      oDP.setValue();
      }

      else if(dEndDate !== null && dStartDate>dEndDate){
      oDP.setValueState(sap.ui.core.ValueState.Error);
      oDP.setValueStateText(“From date should not be greater than To date”);
      oDP.setValue();
      }
      else{
      oDP.setValueState(sap.ui.core.ValueState.None);
      oControllerBatch._onFilter();
      }
      }
      else{
      var dEndDate = oDP.getDateValue();
      var dStartDate = sap.ui.getCore().byId(“startDateIdR2”).getDateValue();

      if(!bDateValid){
      oDP.setValueState(sap.ui.core.ValueState.Error);
      oDP.setValueStateText(“Invalid Date Format”);
      oDP.setValue();
      }
      else if(dEndDate > sMaxDate){
      oDP.setValueState(sap.ui.core.ValueState.Error);
      oDP.setValueStateText(“To Date cannot accept future dates”);
      oDP.setValue();
      }

      else if(dEndDate !== null && dEndDate<dStartDate){
      oDP.setValueState(sap.ui.core.ValueState.Error);
      oDP.setValueStateText(“To date must be greater than From date”);
      oDP.setValue();
      }

      else{
      oDP.setValueState(sap.ui.core.ValueState.None);
      oControllerBatch._onFilter();
      }
      }

      },

      *****************************************************************

      _onFilter: function(){

      var oStartDate = sap.ui.getCore().byId(“startDateIdR2”).getDateValue();
      var oEndDate = sap.ui.getCore().byId(“endDateIdR2”).getDateValue();
      var REModel = oControllerBatch.getView().getModel(“CO”).getData().oData.BatchCreationResponseMessage.record;
      var reModel = oControllerBatch.getView().getModel(“CO”);
      this.getView().setModel(reModel, “searchModel”);
      this._pagination(0, 10, “searchModel”);
      if(oStartDate !== null && oEndDate !== null){
      var searchModel = oControllerBatch.getView().getModel(“searchModel”).getData().oData.BatchCreationResponseMessage.record;
      var omodel = new sap.ui.model.json.JSONModel();
      var oData = {“oData”:{“BatchCreationResponseMessage”:{“record”:[]}}};
      for (var z=0; z<searchModel.length; z++){
      var createDate = searchModel[z].dateCreated.slice(0,10);
      var parsedate = this._parseDate(createDate);
      if ((parsedate >= oStartDate) && (parsedate <= oEndDate)) {
      oData.oData.BatchCreationResponseMessage.record.push(searchModel[z]);
      omodel.setData(oData);
      }
      else{
      oData.oData.BatchCreationResponseMessage.record.push();
      omodel.setData(oData);
      }
      }
      this.getView().setModel(omodel, “searchModel”);
      this._pagination(0, 10, “searchModel”);

      }

      },

      *********************************************************************

      //private function for pagination ***************************************************************************
      _pagination: function(pIp,pEp,pModelN){

      var Total = 0;
      var oREModel = new sap.ui.model.json.JSONModel();
      oControllerBatch.IPage = pIp;
      oControllerBatch.EPage = pEp;

      if (oControllerBatch.getView().getModel(pModelN).oData == undefined){
      if(oControllerBatch.getView().getModel(pModelN).oData.oData.BatchCreationResponseMessage.record.length !== 0){

      var reId = this.getView().getModel(pModelN).oData.BatchCreationResponseMessage.record;
      reId.sort(function(a,b) {return (a.dateCreated < b.dateCreated) ? 1 : ((b.dateCreated < a.dateCreated) ? -1 : 0);} );
      Total = reId.length;
      oData = {“items”:[{
      “BatchId”:[]
      }]
      };
      if(Total<= pEp){
      pEp = Total;
      }
      for (var z=pIp; z<pEp; z++ ){
      oData.items[0].BatchId.push(reId[z]);
      oREModel.setData(oData);
      var m = pIp+1;
      sap.ui.getCore().byId(“textidpageR2”).setText(“Showing “+m+”-“+pEp+” of “+Total);
      }
      }

      }
      else if (oControllerBatch.getView().getModel(pModelN).oData != undefined){
      if(oControllerBatch.getView().getModel(pModelN).oData.oData != undefined){

      var reId = this.getView().getModel(pModelN).oData.oData.BatchCreationResponseMessage.record;
      reId.sort(function(a,b) {return (a.dateCreated < b.dateCreated) ? 1 : ((b.dateCreated < a.dateCreated) ? -1 : 0);} );
      Total = reId.length;
      oData = {“items”:[{
      “BatchId”:[]
      }]
      };
      if(Total<= pEp){
      pEp = Total;
      }
      for (var z=pIp; z<pEp; z++ ){
      oData.items[0].BatchId.push(reId[z]);
      oREModel.setData(oData);
      var m = pIp+1;
      sap.ui.getCore().byId(“textidpageR2”).setText(“Showing “+m+”-“+pEp+” of “+Total);
      }
      }
      else if(oControllerBatch.getView().getModel(pModelN).oData.COBResponseMessage.record.length !== 0)
      {
      var reId = this.getView().getModel(pModelN).oData.COBResponseMessage.Record;
      reId.sort(function(a,b) {return (a.dateCreated < b.dateCreated) ? 1 : ((b.dateCreated < a.dateCreated) ? -1 : 0);} );
      Total = reId.length;
      oData = {“items”:[{
      “BatchId”:[]
      }]
      };
      if(Total<= pEp){
      pEp = Total;
      }
      for (var z=pIp; z<pEp; z++ ){
      oData.items[0].BatchId.push(reId[z]);
      oREModel.setData(oData);
      var m = pIp+1;
      sap.ui.getCore().byId(“textidpageR2”).setText(“Showing “+m+”-“+pEp+” of “+Total);
      }

      }
      }
      else{
      sap.ui.getCore().byId(“textidpageR2”).setText(“Showing 0-0 of 0”);
      }

      oControllerBatch.getView().setModel(oREModel, “BAT”);
      if(Total<= pEp){
      pEp = Total;
      sap.ui.getCore().byId(“NavRightIdR2”).setEnabled(false);
      }
      else{
      sap.ui.getCore().byId(“NavRightIdR2”).setEnabled(true);
      }
      if(pIp === 0){
      sap.ui.getCore().byId(“NavLeftIdR2”).setEnabled(false);
      }

      },

      Author's profile photo Jun Wu
      Jun Wu

      if you sort the batch number column, will your pagination show correct data?

      Author's profile photo Vaibhav Revankar
      Vaibhav Revankar
      Blog Post Author

      Yes ,  please change change the field name in sort and check,

      you can also use as below code to sort

      .sort(function(a, b){return a - b});

      Author's profile photo Sagar Bansal
      Sagar Bansal

      Hi Vaibhav,

       

      Thank you for the post.

      I used your code to upload around 20k records from excel but the browser got stuck for 5 minutes.

      Is there any other way to reduce the response time?

       

      Thanks

      Sagar Bansal

      Author's profile photo Vaibhav Revankar
      Vaibhav Revankar
      Blog Post Author

      Can you please share you code and the excel  file data

      so i can check and let you know if anything is missing or can add something which may works faster,

      Author's profile photo shruti wardhekar
      shruti wardhekar

      Which tool has been used here to build the application?

      Author's profile photo Vaibhav Revankar
      Vaibhav Revankar
      Blog Post Author

      it's SapNetweaverDeveloperStudio  ,  same as eclipse