Technical Articles

Ferry Djaja

September 5, 2020 7 minute read

Excel File Upload From SAP Analytics Cloud Analytic Application

There was a requirement to upload an Excel file directly from the SAC Analytic Application. I would like to share here how I did it.

We will create a custom widget in SAC with library from SheetJS with a little modification. We will also create table, view and stored procedure in HANA. I am using HANA Classic for this purpose.

In a nutshell, we need to create the following modules:

HANA Table and Calculation View
HANA Stored Procedure
HANA XSJS
SAC Custom Widget
SAC Analytic Application

HANA Table and View

Create HANA table SACXLSX.hdbtable. This table will store the data from Excel file.

// To define an HDB table with main sql type columns, you can use the following code.
// In the example below a schema should exist.   

table.schemaName = "COMMON";
table.tableType = COLUMNSTORE; // ROWSTORE is an alternative value

table.columns = [
    {name = "DATE"; sqlType = DATE;  comment = "date - YYYYMMDD";},
    {name = "COUNTRY_CODE"; sqlType = NVARCHAR; length = 2; comment = "Country Code";},
    {name = "COMPANY_CODE"; sqlType = NVARCHAR; length = 10; comment = "Company Code";},
    {name = "TYPE"; sqlType = NVARCHAR; length = 30; comment = "Type of Data";},
    {name = "VALUE_DATE"; sqlType = DATE; comment = "Value Date - YYYYMMDD";},
    {name = "AMOUNT"; sqlType = DOUBLE; comment = "Amount";},
    {name = "CURRENCY"; sqlType = NVARCHAR; length = 3; comment = "Currency";},
    {name = "COMMENTS"; sqlType = NVARCHAR; length = 100; comment = "Comments";},  
    {name = "LOCK_FLAG"; sqlType = NVARCHAR; length = 10; comment = "Lock Flag";}
];

Create HANA Calculation View TC_SACXLSX.calculationview. We will later create the model in SAC Analytic Application with this view.

HANA Stored Procedure

Create stored procedure insertData.hdbprocedure to insert data to table SACXLSX.

PROCEDURE "insertData" ( 
	in DATE DATE,
	in COUNTRY_CODE NVARCHAR(2),
	in COMPANY_CODE NVARCHAR(10),
	in TYPE NVARCHAR(30),
	in VALUE_DATE DATE,
	in AMOUNT DOUBLE,
	in CURRENCY NVARCHAR(3),
	in COMMENTS NVARCHAR(100),
	in LOCK_FLAG NVARCHAR(1)
) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	--DEFAULT SCHEMA <default_schema_name>
	--READS SQL DATA AS
	AS -- "READS SQL DATA " removed  
BEGIN
/***************************** 
	Write your procedure logic 
 *****************************/
insert into
	"SACXLSX"
	values 
		(
			DATE,
			COUNTRY_CODE,
			COMPANY_CODE,
			TYPE,
			VALUE_DATE,
			AMOUNT,
			CURRENCY,
			COMMENTS,
			LOCK_FLAG
		);
END;

HANA XSJS

Create XSJS processData.xsjs. This server-side scripting will process the data from the HTTP Post request from the SAC custom widget and calls the store procedure InsertData to insert the records to table.

try {
	var content = $.request.body.asString();	
	var data = JSON.parse(content);	
	var result = ""; 
	var DATE;
	var COUNTRY_CODE;
	var COMPANY_CODE;
	var TYPE;
	var VALUE_DATE;
	var AMOUNT;
	var CURRENCY;
	var COMMENTS;
	var LOCK_FLAG;	 
	
	var conn = $.hdb.getConnection({ "xssqlcc": "anonuser"}); 
	var procedureCall = conn.loadProcedure("insertData");
	
	for(var i = 0; i <data.length; i++) {
		DATE = data[i].DATE;
		COUNTRY_CODE = data[i].COUNTRY_CODE;
		COMPANY_CODE = data[i].COMPANY_CODE;
		TYPE = data[i].TYPE;
		VALUE_DATE = data[i].VALUE_DATE;
		AMOUNT = data[i].AMOUNT;
		CURRENCY = data[i].CURRENCY;
		COMMENTS = data[i].COMMENTS;
		LOCK_FLAG = data[i].LOCK_FLAG;
		
		procedureCall(DATE, COUNTRY_CODE, COMPANY_CODE, TYPE, VALUE_DATE, AMOUNT, CURRENCY, COMMENTS, LOCK_FLAG);		
	}
	
	conn.commit();
	conn.close();
	
	$.response.headers.set("Access-Control-Allow-Origin", "*");
	$.response.contentType = "application/json";
	$.response.setBody(JSON.stringify(data));
	$.response.returnCode = 200;	
} catch (err) {
	$.response.headers.set("Access-Control-Allow-Origin", "*");
	$.response.contentType = "application/json";
        $.response.setBody(JSON.stringify(err.message));
        $.response.returnCode = 200;
}

SAC Custom Widget

In the custom widget, we will load the external library with the modified code from SheetJS, xslx.js.

onCustomWidgetAfterUpdate(changedProperties) {
  var that = this;

  let xlsxjs = "http://localhost/SAC/sacexcel/xlsx.js";
  async function LoadLibs() {
    try {
      await loadScript(xlsxjs, _shadowRoot);
    } catch (e) {
      console.log(e);
    } finally {
      loadthis(that, changedProperties);
    }
  }
  LoadLibs();
}

We’ll create an XMLView with SAPUI5 SimpleForm and FileUploader.

Using SheetJS library, we parse the data from the uploaded Excel file and perform the validation to make sure the data is valid.

Once the data is validated, we will send it to HANA processData.xsjs with POST method.

We’ll wait until the data is completely processed and once is done, we’ll refresh the SAC model.

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);

                  var oHeaders = {
                    "Authorization": "Basic XXXXXXX",
                    "Content-Type": "application/x-www-form-urlencoded"
                  }

                  var oModel = new JSONModel();

                  console.log(result_final);
                  oModel.loadData("processData.xsjs", JSON.stringify(result_final), true, 'POST', false, true, oHeaders);

                  oModel.attachRequestCompleted(function() {
                    var result = oModel.getData();
                    console.log(result);

                    _result = result;

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

                    that.dispatchEvent(new CustomEvent("onStart", {
                      detail: {
                        settings: this.settings
                      }
                    }));
                  });
                  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

Insert the custom widget Excel_1 and table Table_1 with the model from TC_SACXLSX.calculationview.

In Excel_1 widget onStart() event, add the following code to refresh the model:

Table_1.getDataSource().refreshData();

Demo Video

References

9 Comments

You must be Logged on to comment or reply to a post.

Jef Baeyens

September 7, 2020 at 7:32 am

Hi Ferry,

Great Post. Do you think the same requirement is in any way possible for a SAC planning (acquired) model?

Kr,
Jef

Ferry Djaja

Blog Post Author

September 8, 2020 at 1:15 am

Hi Jef Baeyens,

I haven't try with the SAC planning. But I guess it will work as well.

Regards,

Ferry

Jef Baeyens

September 8, 2020 at 12:27 pm

If you think it will work, please let us know how, because you can't use hana stored procedure on planning models.

Ferry Djaja

Blog Post Author

September 9, 2020 at 6:27 am

Not using HANA Stored Procedure.

After getting the result from Excel, parse it and write the data to Planning model with createMembers API, for example.

var id = 1;
for (var i=0; i<2; i++) {
	materialMember = ({id: id, description: 'Phone ' + ConvertUtils.numberToString(id), properties: {Material: 'D' + ConvertUtils.numberToString(id), MaterialColor: 'White', MaterialType: 'Phone', Quantity: '1'}});	
        var result = PlanningModel_1.createMembers("Material",materialMember);
	id = id + 1;
}

Ferry Djaja

Blog Post Author

September 10, 2020 at 5:32 am

I created a simple demo to upload an Excel File records to Planning Model.

https://youtu.be/22OYEFJZwr4

Jef Baeyens

September 10, 2020 at 11:49 am

Great stuff, but we need it to update transaction data, not only master data. Do you think that's possible?

Ferry Djaja

Blog Post Author

September 11, 2020 at 6:53 am

I guess is not possible at this moment.

Jef Baeyens

September 9, 2020 at 10:58 am

Yes, but we also need it for transaction data, a high nr of records need to be validated & uploaded. Hope that is possible somehow...

Tuan Hoang Minh

January 18, 2021 at 4:27 pm

Hi Ferry,

that was a great work. I would like to ask if you have worked for downloading files from a widget.

For example, I have a widget (SAC analytics cloud Application) including a chart and a table. Then I created Dropdown or Listbox with options to download images or table (as .cvs file). Could you help me out with that thing?

The .cvs files are the actual filtered data from the table in the widget. Then different widgets/tables can be downloaded different .cvs data

Thank you very much!

Technical Articles

Excel File Upload From SAP Analytics Cloud Analytic Application

HANA Table and View

HANA Stored Procedure

HANA XSJS

SAC Custom Widget

SAC Analytic Application

Demo Video

References

Assigned Tags