Skip to Content
Technical Articles
Author's profile photo Ferry Djaja
Ferry Djaja
September 11, 2020 7 minute read

Populate Master Data in SAC Planning Model from an Excel File Upload with Analytic Application

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 === 8) {
			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

 

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Leoncio Francis Ginel
      Leoncio Francis Ginel

      Hi Ferry,

      Can you share the JSON file for this as well?  As I understand, the code that was posted above is for main js.

      Thanks,

      Francis

      Author's profile photo Saul Buendia Lamela
      Saul Buendia Lamela

      Where did you able to create a json document?

       

       

      Author's profile photo Varun C
      Varun C

      Hi Ferry,

      Could you share the JSON file ?

      Thanks,

      Varun

      Author's profile photo Harish Baire
      Harish Baire

      Hi Ferry,

      I am trying to upload the master data as a flat file, but I am getting the below error

      could please help us in resolving the below error 

      Console Error

      Console%20error

      Network preview

      Thanks

      Harish

       

      Author's profile photo Rijin Baby
      Rijin Baby

      Harish Baire

      Are you able to create the custom widget? If yes, please share the steps.

      Thank You

      Author's profile photo Rijin Baby
      Rijin Baby

      Ferry Djaja,

      Can you provide the JSON file to create the Excel_1 custom widget?