Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
ferrygun18
Contributor
In this short tutorial, we will create an SAC Analytic Application (custom widget) Excel File Upload to populate the master data in SAC planning model.

We will create the following modules:

  • Planning Model

  • Excel File Upload Custom Widget

  • Analytic Application



Create a Planning Model

Create a simple model with generic dimension Inventory with the custom properties which will hold the Master Data.





Excel File Upload Custom Widget


Create a custom widget with UI5 component an XMLView with SAPUI5 SimpleForm and FileUploader. Here is the main code in loadthis() function. The output of this function is just simply the records from Excel file in 'stringify' JSON format.
function loadthis(that, changedProperties) {
var that_ = that;

widgetName = changedProperties.widgetName;
if (typeof widgetName === "undefined") {
widgetName = that._export_settings.title.split("|")[0];
}


div = document.createElement('div');
div.slot = "content_" + widgetName;

if (that._firstConnection === 0) {
let div0 = document.createElement('div');
div0.innerHTML = '<?xml version="1.0"?><script id="oView_' + widgetName + '" name="oView_' + widgetName + '" type="sapui5/xmlview"><mvc:View height="100%" xmlns="sap.m" xmlns:u="sap.ui.unified" xmlns:f="sap.ui.layout.form" xmlns:core="sap.ui.core" xmlns:mvc="sap.ui.core.mvc" controllerName="myView.Template"><f:SimpleForm editable="true"><f:content><Label text="Upload"></Label><VBox><u:FileUploader id="idfileUploader" width="100%" useMultipart="false" sendXHR="true" sameFilenameAllowed="true" buttonText="" fileType="XLSM" placeholder="" style="Emphasized" change="onValidate"></u:FileUploader></VBox></f:content></f:SimpleForm></mvc:View></script>';
_shadowRoot.appendChild(div0);

let div1 = document.createElement('div');
div1.innerHTML = '<?xml version="1.0"?><script id="myXMLFragment_' + widgetName + '" type="sapui5/fragment"><core:FragmentDefinition xmlns="sap.m" xmlns:core="sap.ui.core"><SelectDialog title="Partner Number" class="sapUiPopupWithPadding" items="{' + widgetName + '>/}" search="_handleValueHelpSearch" confirm="_handleValueHelpClose" cancel="_handleValueHelpClose" multiSelect="true" showClearButton="true" rememberSelections="true"><StandardListItem icon="{' + widgetName + '>ProductPicUrl}" iconDensityAware="false" iconInset="false" title="{' + widgetName + '>partner}" description="{' + widgetName + '>partner}" /></SelectDialog></core:FragmentDefinition></script>';
_shadowRoot.appendChild(div1);

let div2 = document.createElement('div');
div2.innerHTML = '<div id="ui5_content_' + widgetName + '" name="ui5_content_' + widgetName + '"><slot name="content_' + widgetName + '"></slot></div>';
_shadowRoot.appendChild(div2);

that_.appendChild(div);

var mapcanvas_divstr = _shadowRoot.getElementById('oView_' + widgetName);
var mapcanvas_fragment_divstr = _shadowRoot.getElementById('myXMLFragment_' + widgetName);

Ar.push({
'id': widgetName,
'div': mapcanvas_divstr,
'divf': mapcanvas_fragment_divstr
});
}

that_._renderExportButton();

sap.ui.getCore().attachInit(function() {
"use strict";

//### Controller ###
sap.ui.define([
"jquery.sap.global",
"sap/ui/core/mvc/Controller",
"sap/ui/model/json/JSONModel",
"sap/m/MessageToast",
"sap/ui/core/library",
"sap/ui/core/Core",
'sap/ui/model/Filter',
'sap/m/library',
'sap/m/MessageBox',
'sap/ui/unified/DateRange',
'sap/ui/core/format/DateFormat',
'sap/ui/model/BindingMode',
'sap/ui/core/Fragment',
'sap/m/Token',
'sap/ui/model/FilterOperator',
'sap/ui/model/odata/ODataModel',
'sap/m/BusyDialog'
], function(jQuery, Controller, JSONModel, MessageToast, coreLibrary, Core, Filter, mobileLibrary, MessageBox, DateRange, DateFormat, BindingMode, Fragment, Token, FilterOperator, ODataModel, BusyDialog) {
"use strict";

var busyDialog = (busyDialog) ? busyDialog : new BusyDialog({});

return Controller.extend("myView.Template", {

onInit: function() {
console.log(that._export_settings.title);
console.log("widgetName:" + that.widgetName);

if (that._firstConnection === 0) {
that._firstConnection = 1;
}
},

onValidate: function(e) {

var fU = this.getView().byId("idfileUploader");
var domRef = fU.getFocusDomRef();
var file = domRef.files[0];
var this_ = this;

var oModel = new JSONModel();
oModel.setData({
result_final: null
});

var reader = new FileReader();
reader.onload = async function(e) {
var strCSV = e.target.result;

var workbook = XLSX.read(strCSV, {
type: 'binary'
});

var result_final = [];
var result = [];
var correctsheet = false;

workbook.SheetNames.forEach(function(sheetName) {
if (sheetName === "Sheet1") {
correctsheet = true;
var csv = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);
if (csv.length) {
result.push(csv);
}
result = result.join("[$@~!~@$]")
}
});

if (correctsheet) {
var lengthfield = result.split("[$@~!~@$]")[0].split("[#@~!~@#]").length;
console.log("lengthfield: " + lengthfield);

var total = this_.getView().byId("total");
var rec_count = 0;

var len = 0;
if (lengthfield === 9) {
for (var i = 1; i < result.split("[$@~!~@$]").length; i++) {
if (result.split("[$@~!~@$]")[i].length > 0) {

var rec = result.split("[$@~!~@$]")[i].split("[#@~!~@#]");
if (rec.length > 0) {
len = rec[0].trim().length + rec[1].trim().length + rec[2].trim().length + rec[3].trim().length + rec[4].trim().length + rec[
5].trim().length + rec[6].trim().length + rec[7].trim().length + rec[8].trim().length;
if (len > 0) {
rec_count = rec_count + 1;
result_final.push({
'ID': i,
'DATE': rec[0].trim(),
'COUNTRY_CODE': rec[1].trim(),
'COMPANY_CODE': rec[2].trim(),
'TYPE': rec[3].trim(),
'VALUE_DATE': rec[4].trim(),
'AMOUNT': rec[5].trim().replace(/[,]/g, ""),
'CURRENCY': rec[6].trim(),
'COMMENTS': rec[7].trim().replace(/["'\n\r]/g, ""),
'LOCK_FLAG': rec[8].trim(),
});
}
}
}
}

if (result_final.length === 0) {
fU.setValue("");
MessageToast.show("There is no record to be uploaded");
} else if (result_final.length >= 2001) {
fU.setValue("");
MessageToast.show("Maximum records are 2000.");
} else {
// Bind the data to the Table
oModel = new JSONModel();
oModel.setSizeLimit("5000");
oModel.setData({
result_final: result_final
});

var oModel1 = new sap.ui.model.json.JSONModel();
oModel1.setData({
fname: file.name,
});
console.log(oModel);

_result = JSON.stringify(result_final);

that._firePropertiesChanged();
this.settings = {};
this.settings.result = "";

that.dispatchEvent(new CustomEvent("onStart", {
detail: {
settings: this.settings
}
}));

this_.runNext();
fU.setValue("");
}
} else {
fU.setValue("");
MessageToast.show("Please upload the correct file");
}
} else {
console.log("Error: wrong xlsx template");
MessageToast.show("Please upload the correct file");
}
};

if (typeof file !== 'undefined') {
reader.readAsBinaryString(file);
}
},

wasteTime: function() {
busyDialog.open();
},

runNext: function() {
busyDialog.close();
},

});
});

console.log("widgetName Final:" + widgetName);
var foundIndex = Ar.findIndex(x => x.id == widgetName);
var divfinal = Ar[foundIndex].div;
console.log(divfinal);

//### THE APP: place the XMLView somewhere into DOM ###
var oView = sap.ui.xmlview({
viewContent: jQuery(divfinal).html(),
});

oView.placeAt(div);
if (that_._designMode) {
oView.byId("idfileUploader").setEnabled(false);
}
});
}

SAC Analytic Application


Create a canvas and insert the table Table_1, custom widget Excel_1 and text Text_1.




Create script variable InventoryPlanning with type PlanningModelMember.


Insert the planning model we created earlier.


On the onStart() event of custom widget Excel_1, insert the script to parse the data and call the API PlanningModel.createMembers()  to submit the data to the planning model. Take a note that the dimension type Account is not supported with this API calls.


I am not sure if there is a simple function to parse the JSON data like in JavaScript JSON.parse(). Since I couldn't find any, I wrote this kind of long codes to achieve the same thing. If you have any better ideas, do let me know in the comment.
var Inventory = PlanningModel_1.getMembers("Inventory");
console.log(Inventory.length);

var last_id = 0;
if(Inventory.length > 1) {
last_id = Inventory.length;
}
console.log("last id:");
console.log(last_id);

var str = Excel_1.getUnit();
var str1 = str.split("},{");
var entity = "";
var ID = "";
var DATE = "";
var COUNTRY_CODE = "";
var COMPANY_CODE = "";
var TYPE = "";
var VALUE_DATE = "";
var AMOUNT = "";
var CURRENCY = "";
var COMMENTS = "";
var LOCK_FLAG = "";

var Ar_ID = ArrayUtils.create(Type.string);
var Ar_DATE = ArrayUtils.create(Type.string);
var Ar_COUNTRY_CODE = ArrayUtils.create(Type.string);
var Ar_COMPANY_CODE = ArrayUtils.create(Type.string);
var Ar_TYPE = ArrayUtils.create(Type.string);
var Ar_VALUE_DATE = ArrayUtils.create(Type.string);
var Ar_AMOUNT = ArrayUtils.create(Type.string);
var Ar_CURRENCY = ArrayUtils.create(Type.string);
var Ar_COMMENTS = ArrayUtils.create(Type.string);
var Ar_LOCK_FLAG = ArrayUtils.create(Type.string);

for(var x=0; x<str1.length; x++) {
var str2 = str1[x].split(",");

for(var y = 0; y<str2.length; y++) {
entity = str2[y].replace("[{", "");
entity = str2[y].replace("}]", "");

//ID
if(y === 0) {
ID = entity.split(":")[1];
Ar_ID.push(ID);
}

//DATE
if(y === 1) {
DATE = entity.split(":")[1];
DATE = DATE.replace('"','');
DATE = DATE.replace('"','');
Ar_DATE.push(DATE);
}

//COUNTRY_CODE
if(y === 2) {
COUNTRY_CODE = entity.split(":")[1];
COUNTRY_CODE = COUNTRY_CODE.replace('"','');
COUNTRY_CODE = COUNTRY_CODE.replace('"','');
Ar_COUNTRY_CODE.push(COUNTRY_CODE);
}

//COMPANY_CODE
if(y === 3) {
COMPANY_CODE = entity.split(":")[1];
COMPANY_CODE = COMPANY_CODE.replace('"','');
COMPANY_CODE = COMPANY_CODE.replace('"','');
Ar_COMPANY_CODE.push(COMPANY_CODE);
}

//TYPE
if(y === 4) {
TYPE = entity.split(":")[1];
TYPE = TYPE.replace('"','');
TYPE = TYPE.replace('"','');
Ar_TYPE.push(TYPE);
}

//VALUE_DATE
if(y === 5) {
VALUE_DATE = entity.split(":")[1];
VALUE_DATE = VALUE_DATE.replace('"','');
VALUE_DATE = VALUE_DATE.replace('"','');
Ar_VALUE_DATE.push(VALUE_DATE);
}

//AMOUNT
if(y === 6) {
AMOUNT = entity.split(":")[1];
AMOUNT = AMOUNT.replace('"','');
AMOUNT = AMOUNT.replace('"','');
Ar_AMOUNT.push(AMOUNT);
}

//CURRENCY
if(y === 7) {
CURRENCY = entity.split(":")[1];
CURRENCY = CURRENCY.replace('"','');
CURRENCY = CURRENCY.replace('"','');
Ar_CURRENCY.push(CURRENCY);
}

//COMMENTS
if(y === 😎 {
COMMENTS = entity.split(":")[1];
COMMENTS = COMMENTS.replace('"','');
COMMENTS = COMMENTS.replace('"','');
Ar_COMMENTS.push(COMMENTS);
}

//LOCK_FLAG
if(y === 9) {
LOCK_FLAG = entity.split(":")[1];
LOCK_FLAG = LOCK_FLAG.replace('"','');
LOCK_FLAG = LOCK_FLAG.replace('"','');
Ar_LOCK_FLAG.push(LOCK_FLAG);
}
}
}

console.log(Ar_ID);
console.log(Ar_DATE);
console.log(Ar_COUNTRY_CODE);
console.log(Ar_COMPANY_CODE);
console.log(Ar_TYPE);
console.log(Ar_VALUE_DATE);
console.log(Ar_AMOUNT);
console.log(Ar_CURRENCY);
console.log(Ar_COMMENTS);
console.log(Ar_LOCK_FLAG);

Application.showBusyIndicator();
var idx = last_id;
for(var i=0; i<Ar_ID.length; i++) {
idx = idx + 1;
InventoryPlanning = ({id: idx, description: '', properties: {Amount: Ar_AMOUNT[i], Comment: Ar_COMMENTS[i], Company_Code: Ar_COMPANY_CODE[i], Country_Code: Ar_COUNTRY_CODE[i], Currency_Info: Ar_CURRENCY[i], Date: Ar_DATE[i], Lock_Flag: Ar_LOCK_FLAG[i], Type: Ar_TYPE[i], Value_Date: Ar_VALUE_DATE[i]}});

var result = PlanningModel_1.createMembers("Inventory",InventoryPlanning);
console.log(result);
}
Application.hideBusyIndicator();

Table_1.getDataSource().refreshData();


And that's it.

Demo Video



References



 
6 Comments
Labels in this area