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

• Build a Custom Widget in SAP Analytics Cloud, Analytics Application

• Excel File Upload SAPUI5 App with SAP HANA XSA
• github.com/ferrygun/SACXLSXFileUpload