Skip to Content

Trigger for the blog

What started from a tweet actually end up learning a bit more about browser. Yesterday our dear community member Phil Cooley made a very genuine feature request on SAP cloud platform to have the ability to download the HTML5 applications table data, which was missing. So the search to find a quick and dirty solution started from here!

Quick and Dirty trick

The first step was to check if we have any API being called in the background, we found one but sadly the API link is not accessible if called directly as can be seen below, DJ Adams any solution you found for this? Since API approach did not work for me at least, time was to move to the quick and dirty solution.

I still remembered the great video and blog by DJ Adams about using Greasemonkey techniques to modify the frontend of website without actually changing anything at source code level. So the first task for me was to get the table id for the HTML5 applications pages which was simple to get.

var tab = document.getElementById("__xmlview1--Html5AppList_AppTable-listUl");
tab.rows

Next task to was to run javascript code in the chrome console to read this table id data and download to excel. No need to write anything a simple search provided a sample code to download data of table from table id and bingo the basic solution was ready. All i need to do is create and call this function in debugger

function downloadexcel() {
    var tab_text = "<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange;
    var j = 0;

    tables = document.getElementById('__xmlview0--Html5AppList_AppTable-listUl');
    for (j = 0; j < tab.rows.length; j++) {
        tab_text = tab_text + tab.rows[j].innerHTML + "</tr>";
    }
    tab_text = tab_text + "</table>";
    tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, "");
    tab_text = tab_text.replace(/<img[^>]*>/gi, "");
    tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, "");
    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE ");
    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
        txtArea1.document.open("txt/html", "replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus();
    } else
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));

    return (sa);
}

Although the issue was resolved for a particular page but we might have multiple tables and different id. So i thought investigate further and make it a generic one.

 

Extending the concept to generic one

So definitely we don’t want to hard code the table id, so was thinking a way to get all the tables on the page a simple search led me to searching elements by id and i have list of all tables on the page.

tables = document.getElementsByTagName("table");

A small modification from the previous code which is another loop for each table was added and i had a generic excel file download code ready. But that was not the end, i tried it on some pages i could see this concept failing, the reason being in some cases i can see SAP has added two tables – one for header and another for data, why i am still wondering. That is where i extended my code to handle two different type of tables one simple one with list combined header and another for crazy tables with one for header and another for data. So finally i have a working code.

function downloadexcel() {

    tables = document.getElementsByTagName("table");
    console.log(tables);
    for (i = 0; i < tables.length; i++) {
        tab = document.getElementById(tables[i].id);
        // logic for tables with name of header and data or listUl. 
        //  You can add your own table id if further somenew id's are found.
        substringTwoTable = ["-header"];
        substringOneTable = ["-listUl"];
        foundOneTable = substringOneTable.some(function(v) {
            return tables[i].id.indexOf(v) >= 0;
        });
        if (foundOneTable) {
            var tab_text = "<table border='2px'><tr bgcolor='#87AFC6'>";
            var textRange;
            var j = 0;
            for (j = 0; j < tab.rows.length; j++) {
                tab_text = tab_text + tab.rows[j].innerHTML + "</tr>";
            }
            tab_text = tab_text + "</table>";
            tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, "");
            tab_text = tab_text.replace(/<img[^>]*>/gi, "");
            tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, "");

            var ua = window.navigator.userAgent;
            var msie = ua.indexOf("MSIE ");

            if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
                txtArea1.document.open("txt/html", "replace");
                txtArea1.document.write(tab_text);
                txtArea1.document.close();
                txtArea1.focus();
                sa = txtArea1.document.execCommand("SaveAs", true, "Say Thanks to Sumit.xls");
            } else
                sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));
        } else {
            found = substringTwoTable.some(function(v) {
                return tables[i].id.indexOf(v) >= 0;
            });
            if (found) {
                var tab_text = "<table border='2px'><tr bgcolor='#87AFC6'>";
                var textRange;
                var j = 0;
                for (j = 0; j < tab.rows.length; j++) {
                    tab_text = tab_text + tab.rows[j].innerHTML + "</tr>";
                }
            } else {
                for (j = 0; j < tab.rows.length; j++) {
                    tab_text = tab_text + tab.rows[j].innerHTML + "</tr>";
                }
                tab_text = tab_text + "</table>";
                tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, "");
                tab_text = tab_text.replace(/<img[^>]*>/gi, "");
                tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, "");
                var ua = window.navigator.userAgent;
                var msie = ua.indexOf("MSIE ");
                if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
                    txtArea1.document.open("txt/html", "replace");
                    txtArea1.document.write(tab_text);
                    txtArea1.document.close();
                    txtArea1.focus();
                } else
                    sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));
            }
        }
    }
}

Now each time we don’t want the debugger to be opened and function to be execute that is where DJ Adams DJ Adams helped me in next section.

Discovering Bookmarklet

Most of us have heard about bookmarks but how many of you know #Bookmarklets(at least i did not know). I only came to know it first as a Greasemonkey and then while trying to get it working today got to know they are called #Bookmarklets. We have some cool Bookmarklets available do a google search and try . Bookmarklet is nothing but some javascript code which you want to run on a page when clicked .So next step was pretty straight forward which was to add the bookmarklet for the same. Now all you need to do is a simple refresh and click on bookmark on any page it will download the data

javascript:(function(){tables = document.getElementsByTagName("table");console.log(tables); for(i = 0 ; i < tables.length ; i++) { tab = document.getElementById(tables[i].id);alert(tables[i].id); substringTwoTable = ["-header"]; substringOneTable = ["-listUl"]; foundOneTable = substringOneTable.some(function(v) { return tables[i].id.indexOf(v) >= 0; }); console.log("found"+foundOneTable); if(foundOneTable){ var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";var textRange; var j=0; for(j = 0 ; j < tab.rows.length ; j++) {tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";} tab_text=tab_text+"</table>"; tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, ""); tab_text= tab_text.replace(/<img[^>]*>/gi,""); tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); var ua = window.navigator.userAgent; var msie = ua.indexOf("MSIE "); if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) { txtArea1.document.open("txt/html","replace"); txtArea1.document.write(tab_text); txtArea1.document.close(); txtArea1.focus(); sa=txtArea1.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls"); } else sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text)); }else{ found = substringTwoTable.some(function(v) { return tables[i].id.indexOf(v) >= 0; }); if(found){ var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";var textRange; var j=0; for(j = 0 ; j < tab.rows.length ; j++) {tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";} }else{ for(j = 0 ; j < tab.rows.length ; j++) {tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";} tab_text=tab_text+"</table>"; tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, ""); tab_text= tab_text.replace(/<img[^>]*>/gi,""); tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); var ua = window.navigator.userAgent; var msie = ua.indexOf("MSIE "); if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) { txtArea1.document.open("txt/html","replace"); txtArea1.document.write(tab_text); txtArea1.document.close(); txtArea1.focus();  } else sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text)); }} }})();

 Is our SAP Cloud platform actually Open?

It might not be a great solution but still i believe it works and has taught me something new.  I was wondering it would have been a straight forward solution had SAP allowed authenticated user to access the API. I am not allowed to access it, is it because of trial account or something else don’t know. I for sure was expecting this to be just consuming the API call and getting your work done.  We have been talking a lot about open source, extensibility etc. and all but is our platform actually that open to our fellow developers so that they can build or extend as per the need.

Wrapping up, hopefully you have also learnt something from this blog. Feel free to provide your feedback or a better solution or any other crazy solution, would love to know.

 

To report this post you need to login first.

6 Comments

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

  1. Phil Cooley

    Wow Nabheet Madan – seriously cool and I had not heard of this Bookmarklet before so thanks for the knowledge. Will try this and see how we go. I also found the tip by DJ Adams around a hidden API so have tried that as well. Will report back how I go.

    Thanks and well done on posting the blog about it, glad I could help out 🙂

    (1) 

Leave a Reply