Purpose
Generate an Excel file
Solution
We use an Excel template file that is stored on the server. We transport the current data for the Excel file in invisible S10 fields. The actual generation is then carried out from the template file and the current data with JavaScript. Either we use specialized libraries like "ExelJS" or "SheetJS", or for simple cases we directly replace the placeholders of the Excel template file with the current values.

Here we describe a case of direct replacement of the placeholder variables. For example, the template file "exceltemplate.xlsx" in the SAP MIME repository looks like this:




The placeholders "&[qmnum]", "&[aufnr]",... are arbitrary. In the HTML page, the values are included in an invisible area. They are automatically provided by the S10 framework:

<!-- hidden values -->
<div style="display: none">

  <span class='output' name="qmnum" id="qmnum"></span>
  <span class='output' name="aufnr" id="aufnr"></span>
  <span class='output' name="equnr" id="equnr"></span>
  <span class='output' name="equnr@text" id="equnr@text"></span>

</div>

We implement a JavaScript function that replaces the placeholders in a string with the actual values, for example:

function replace_placeholders(s) {
 s = s.replaceAll('&amp;[qmnum]', 
           document.getElementById('qmnum').innerHTML);
 s = s.replaceAll('&amp;[aufnr]', 
           document.getElementById('aufnr').innerHTML);
 s = s.replaceAll('&amp;[equnr]', 
           document.getElementById('equnr').innerHTML);
 s = s.replaceAll('&amp;[equnr@text]', 
           document.getElementById('equnr@text').innerHTML);
 return s;

};

Here we have replaced the "&" character with the XML value "&amp;" because we will be changing the placeholders in the XML format.

To create the Excel file, we perform the following steps: 

  • Read the template file from the server (SAP MIME Repository)
  • Unzip the Excel file from zip format .xlsx into the individual XML components. Here we use the "jszip" library (open source)
  • Replace the placeholders in the "xl/sharedStrings.xml" file. This file contains the Excel cell values (Open XML document format).
    Instead of modifying this file directly, we could use another JavaScript library, but for our purpose here, that's not necessary. If you want to insert tables with any number of rows into the Excel file, using such a library is useful.
  • Convert the modified Excel file into a data-URL
  • Create an HTML link to this data-URL and call this link

The JavaScript routine is not very long but a bit demanding, because some of the called functions work asynchronously:

<!-- jszip library -->
<script src="https://unpkg.com/jszip@3.7.1/dist/jszip.js"
 </script>

< !--Excel generation -- >
<script>

function generate_excel() {

  const filePath = "exceltemplate.xlsx";

   var xmlhttp = new XMLHttpRequest();
  xmlhttp.open('GET', filePath, true);
  xmlhttp.responseType = "arraybuffer";

  xmlhttp.onload = (event) => {
    var zip = new JSZip();

    zip.loadAsync(xmlhttp.response)
     .then(function (zip) {
       zip.file("xl/sharedStrings.xml").async("String")
       .then(function (data) {
       var newdata = replace_placeholders(data);
       zip.file("xl/sharedStrings.xml", newdata);
       zip.generateAsync({ type: "base64" })
       .then(function (b64) {
       var url = "data:application/vnd.openxmlformats-officedocument
                     .spreadsheetml.sheet;base64," + b64;
       var link = document.createElement('a');
       link.setAttribute('href', url);
       link.setAttribute('target', '_blank');
       link.setAttribute("download", "protocol.xlsx");
       link.click();
       });
     });
   });
  };

   xmlhttp.send();

 }

</script>

The user then receives the following Excel file, for example:

 

Components
 S10 Framework