Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

SAP Screen Personas


 

Scripting: Uploading an Excel Spreadsheet


SAP SCREEN PERSONAS KNOWLEDGE BASE - by Pradeep B , Regina Sheynblat , Pooja Mittal , Kranthi Kumar Muppala

Purpose

This article shows how to use a Javascript library from https://github.com/SheetJS/js-xlsx to upload an excel file from Personas client, pass the contents to the backend and render it as an ABAP List.

DISCLAIMER: The JavaScript library is an open source library that is not an IP of SAP. Refer to the license file and use it accordingly.

Overview

In order to consume the javascript library, the CDN link https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.1/xlsx.full.min.js can be used. The following functionality of the library is used in this tutorial
XLSX.read – Read the sheet contents
XLSX.utils.sheet_to_csv – Convert the sheet contents to CSV format

Upload the Excel Spreadsheet

In order to upload the spreadsheet, a Screen Personas HTML viewer is used to add a HTML Input control with type = “file”. This input control will accept the excel file path from the user.

var htmlContent = "<html><body>";
htmlContent += "<input type='file' id='xlf' />";
htmlContent += "<script src='https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.1/xlsx.full.min.js'></script>";
htmlContent += "<script>";
// Domain relaxation
var relaxedDomain = window.document.location.hostname.substring(window.document.location.hostname.indexOf(".")+1); // cuts off the first token off the hostname
window.document.domain = relaxedDomain;  // relax main window domain
htmlContent += ' document.domain = "'+relaxedDomain+'";'; // relax html viewer domain
htmlContent += "(function() {\
    debugger;\
 var xlf = document.getElementById('xlf');\
 if(!xlf.addEventListener) return;\
 function handleFile(e) { do_file(e.target.files); }\
 xlf.addEventListener('change', handleFile, false);\
})();";
htmlContent+= "var do_file = (function() {\
 return function do_file(files) {\
     debugger;\
  var f = files[0];\
  var reader = new FileReader();\
  reader.onload = function(e) {\
   var data = e.target.result;\
   process_wb(XLSX.read(data, {type: 'array'}));\
  };\
  reader.readAsArrayBuffer(f);\
 };\
})();";

 

Parse Contents of the Excel Spreadsheet

Once the file contents are read, we make use of the XLSX.utils.sheet_to_csv method to convert the data into CSV format. This data is then sent to another Personas script for forming the parameter of an RFC to transfer data to backend. 

htmlContent += "var process_wb = (function() {\
 var to_csv = function to_csv(workbook) {\
  var result = [];\
  workbook.SheetNames.forEach(function(sheetName) {\
   var csv = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetName]);\
   if(csv.length){\
    result.push(csv);\
   }\
  });\
  return result.join('\\n');\
 };\
 return function process_wb(wb) {\
  global_wb = wb;\
  var output = to_csv(wb);\
  parent.sap.personas.scripting.executeScriptAsync('wnd[0]/scrptPersonas_005056AC47781EE89ED642D19D58A69B', {content:output});\
  alert(output);\
 };\
})();";
htmlContent+= "</script></body></html>";
session.findById("wnd[0]/usr/htmlViewerPersonas_152999439565714").content = htmlContent;

 

Invoke RFC Function Module to Transfer Contents to the Backend

In the second script, we receive the comma separated data, create a well formed string of contents and send it to backend as an RFC parameter. 

var output = data.content; 
var strings = output.split("\n");
var parameters = "[";
var valueExists = false;
for(var index = 0; index < strings.length; index++){
var re_value = /(?!\s*$)\s*(?:'([^'\\]*(?:\\[\S\s][^'\\]*)*)'|"([^"\\]*(?:\\[\S\s][^"\\]*)*)"|([^,'"\s\\]*(?:\s+[^,'"\s\\]+)*))\s*(?:,|$)/g;
// Initialize array to receive values. 
var a = [];
// "Walk" the string using replace with callback.
strings[index].replace(re_value,
function(m0, m1, m2, m3) {
// Remove backslash from \' in single quoted values.
if (m1 !== undefined) a.push(m1.replace(/\\'/g, "'"));
// Remove backslash from \" in double quoted values.
else if (m2 !== undefined) a.push(m2.replace(/\\"/g, '"'));
else if (m3 !== undefined) a.push(m3);
return ''; // Return empty string.
});

if (a[0]){
valueExists = true;
parameters += '{"F1":"' + a[0] + '","F2":"' + a[1] + '","F3":"' + a[2] + '","F4":"' + a[3] + '","F5":"' + a[4]+ '"},';
}
}

// Removing the additional comma.
if(valueExists){
parameters = parameters.substring(0, parameters.length-1);
}
// Adding the end closing bracket.
parameters+= "]";
var oRFC = session.createRFC("ZTEST_CSV_TO_LIST");
oRFC.setParameter("DATA_TABLE", parameters);
oRFC.requestResults(["DATA_TABLE"]);
oRFC.send();

 

Render contents in ABAP List

 The function module ZTEST_CSV_TO_LIST receives DATA_TABLE parameter and exports it to ‘MEMORY’. 

FUNCTION ztest_csv_to_list.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  TABLES
*"      DATA_TABLE STRUCTURE  ZTEST_XL_SO OPTIONAL
*"----------------------------------------------------------------------

  EXPORT data_table TO MEMORY ID 'MEMORY'.
  SUBMIT ztest_xl_to_list.

ENDFUNCTION.

Then a report program ‘ztest_xl_to_list’  is invoked to loop through the data_table contents and write them as an ABAP List. 

REPORT ztest_xl_to_list.
DATA: data_table TYPE TABLE OF ztest_xl_so,
      ls_table   TYPE ztest_xl_so.
START-OF-SELECTION.
  IMPORT data_table FROM MEMORY ID 'MEMORY'.
  LOOP AT data_table INTO ls_table.
    WRITE:/10 ls_table-f1 ,
           40 ls_table-f2,
           80 ls_table-f3,
           110 ls_table-f4,
           150 ls_table-f5.
  ENDLOOP.

 

Related Search Terms:

SAP Screen Personas, Microsoft Excel to ABAP List, Excel upload in SAP Screen Personas for SAP GUI for HTML