Technical Articles
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
- 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 .
Thanks, this is really working
Welcome!
controller code is not in good shape. tons of issue....
for pagination, did it work if you have sort or filtering on?
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);
}
},
if you sort the batch number column, will your pagination show correct data?
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});
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
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,
Which tool has been used here to build the application?
it's SapNetweaverDeveloperStudio , same as eclipse