Let's follow up this discussion at the Neptune Community

(this forum will be kept in read-only mode)

Read Excel file

Hello,


how can I read an Excel file and put the content in a sap,m table? Some idea? :)


KR

Benjamin Bennicke


Hi Mr. Bennicke,


hope you had a great start into the new year!

The trick is to read and convert your Excel sheet into JSON format. Then you can just use the Neptune functions to set your data into a corresponding sap.m table.


My google research found an interesting library (for web apps) called SheetJS/js-xlsx which seems to have a good reputation on the web. We have not used it so far but it's definitely worth a try.


https://github.com/SheetJS/js-xlsx

http://oss.sheetjs.com/

http://codetheory.in/parse-read-excel-files-xls-xlsx-javascript/


Please let us know if this helps!


Kind regards

Christoph Garms



Hi Mr. Garms,

for sure! I hope you too!


Thanks for your research. That’s what I also found and tried but I get an error. Look at my steps:

1. Download js-xlsx Library and add them to our MIME repository

2. Add jszip.js and xlsx.js to HTML Header


When I'm refreshing the App the console shows the following error:”Mismatched anonymous define() module: function […] http://requirejs.org/docs/errors.html#mismatch"


I'm not sure what that means, but I guess the require command could be the Problem.


An idea how I can solve this problem?

KR
Benjamin Bennicke

Hi Mr. Bennicke,


according to the js-xlsx installation guide (https://github.com/SheetJS/js-xlsx) we only need to include the following tag:

 

<script lang="javascript" src="dist/xlsx.core.min.js"></script>

 

The source path needs to be adjusted to your server destination.


Hope this helps - otherwise we have to look into http://requirejs.org/docs/start.html


Best regards

Christoph Garms

Hi Mr. Bennicke,


the following app setup will work:


  • Download SheetJS from GitHub https://github.com/SheetJS/js-xlsx
  • Provide the JS-XLSX file package in the MIME repository of your SAP system.
  • Add the following src-tag in the HTML5 script of your application (adjust your source-path accordingly) Hint: If you want to include your app in a bundle/launchpad application, the src-tag needs to be added to that bundle/launchpad app as well!

  • Add a oFileUploader element and two ScriptCode elements (here: "Init" and "Functions") to your app hierarchy


  • Add the following code to your Init-script:
sap.ui.getCore().attachInit(function() {
    setTimeout(function() {

        // Add Event listener to oFileUploader Element
        document.getElementById('oFileUploader').addEventListener('change', handleFile, false);

    }, 200);
});

 

  • Add the following code to your Functions-script

  • // https://github.com/SheetJS/js-xlsx
    function process_wb(wb) {
    
        var output = JSON.stringify(to_json(wb), 2, 2);
    
        if (output) {
            processJSON(output);
        }
    }
    
    // https://github.com/SheetJS/js-xlsx
    function to_json(workbook) {
        var result = {};
        workbook.SheetNames.forEach(function(sheetName) {
            var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
            if (roa.length > 0) {
                result[sheetName] = roa;
            }
        });
        return result;
    }
    
    // https://github.com/SheetJS/js-xlsx
    function handleFile(e) {
        var files = e.target.files;
        var i, f;
        for (i = 0, f = files[i]; i != files.length; ++i) {
            var reader = new FileReader();
            var name = f.name;
            reader.onload = function(e) {
                var data = e.target.result;
    
                var workbook = XLSX.read(data, {
                    type: 'binary'
                });
    
                /* DO SOMETHING WITH workbook HERE */
                process_wb(workbook);
            };
            reader.readAsBinaryString(f);
        }
    }
    
    // Process json output in app
    function processJSON(output) {
    
       // log JSON output to console (F12)
        console.log(output);
    
       // set JSON output into textArea element
        txtAreaJSON.setValue(output);
    
        // open dialog to show JSON output
        diaSelection.open();
    }

     

    • To process your JSON data just change function processJSON(output) to your needs.


    Now you should be able to upload Excel Files and retrieve data in JSON format of the table in your first sheet of the Excel file!

    Note: Using UI5 sap.viz library together with js-xlsx in your app will cause javascript errors regarding requirejs.


    Best regards

    Christoph

    Login or Signup to post a comment