APEX stuff in one place

Custom export of APEX Classic reports

APEX 4.2.5 - Custom HTML table export

Export your report table after you have modified it with JQuery

Recently I have had a request to change sum totals in my classic report table and then include these newly created figures in excel export.

Problem was that APEX build in download function was not including these figures in standardised excel report.

After some brain storming these were my thoughts:
1. Is there a way I can refresh report to use standard APEX functionality to retrieve new figures? No or at least not that I know of.
2. Can I use some other technology to do the same? Seemed to me to be fairly common question for all web base apps. Exporting any HTML table in excel this surely must be doable.
3. Is there any APEX plugin that can do the same? Probably but similar to BI Pub reports it will need to generate report from specifically rendered query which is not exactly what I was after.

Just for the challenge itself I decided to explore option 2 as it seemed the most simple way to go with.

Solution can be applied to any HTML table but my code will need to be adjusted as I have specifically used some APEX internal tags in my jQuery selectors. So please be aware of that.

In my example APEX theme is Blue responsive #25. I have been looking on line for similar solution and this is what I have so far.

Code might seem complicated but there is only few important lines of code that are APEX specific.

One note here - there is a difference how IE handles this and how other browsers render this functionality. Reason behind it E doesn't support some of HTML tags and properties used such as URL download property which other browsers seem to support.

Please note I generated this for my project needs but before you decide to use it make sure you go through it and understand how this is achieved.

Let's start by looking at these functions separately.

Firefox and non IE browsers function:

var tableToExcel = (function() {
       //DOWNLOAD FOR browsers not IE
        var uri = 'data:application/vnd.ms-excel;base64,', template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head></head><body><table>{table}</table></body></html>', base64 = function(
                s) {
            return window.btoa(unescape(encodeURIComponent(s)))
        }, format = function(s, c) {
            return s.replace(/{(\w+)}/g, function(m, p) {
                return c[p];
            })
        }
       return function(table, name, filename) {
            if (!table.nodeType)
         //APEX specific class used here
                table = $("#"+ table + " .uReportStandard")[0]; //document.getElementById(table);
            var cln=table.cloneNode(true);
            var paras = cln.getElementsByClassName('ignore');

            while(paras[0]) {
                paras[0].parentNode.removeChild(paras[0]);
            }
            var ctx = {
                worksheet : name || 'Worksheet',
                table : cln.innerHTML
            }

          //THIS renders some random file name
          //window.location.href = uri + base64(format(template, ctx))
         // EXPLANATION for this part to follow
          $(".uReportDownloadLinks").after("<a id='Mydlink' style=\"display:none;\"></a>");
          document.getElementById("Mydlink").href = uri + base64(format(template, ctx));
          document.getElementById("Mydlink").download = filename;
          document.getElementById("Mydlink").click();
        }
    })();

Function receives three parameters which represent TABLE_ID, WORKSHEET name and FILE name. Some parts of function above can be found online but trick that I used is after line commented with //EXPLANATION...... that is specific.

Idea was to replace original download link that you get on APEX classic reports with your own. To be able to do this I changed the original link by adding an ID to it before this function can get to it.

Link that is under my Download is a simple JavaScript call

exportHTML('#report_REGION_ID#','#Report name#');

Of course replacing values with a real ID and file name.

On the other side this is IE specific function:

function exportIEToExcel(tableID, filename, sheetname) {
    //WORKS with IE 11
    // Declare the variables
    var Excel, Book;

    // Create the Excel application object.
    Excel = new ActiveXObject("Excel.Application");

    // Make Excel visible.
    Excel.Visible = true;

    // Create a new work book.
    Book = Excel.Workbooks.Add();

    Book.SaveAs(filename);

    //new sheet
    Sheet = Book.Worksheets.Add();

    //sheet name
    Sheet.Name = sheetname;

    //LOOP TABLE ROWS and fill excell sheet.
    var Mytable = $("#"+ tableID );
    //APEX specific selectors
    Mytable.find('.uReportStandard tr').each(function (i) {
    //for each header
    var $ths = $(this).find('th');
    $ths.each(function (k) {
           Book.ActiveSheet.Cells(i+1,k+1).Value = $(this).html();
        });//end headers

    //for each column
    var $tds = $(this).find('td');
    $tds.each(function (j) {
        //alert('Row ' + (i + 1) + 'Cell ' + (j + 1)+ ' HMTL:' + $(this).html());
        //write into excell cells
        Book.ActiveSheet.Cells(i+1,j+1).Value = $(this).text();
        });//end column
    });            //end rows

        idTmr = window.setInterval("Cleanup();",1000);
} //end exportIEToExcel

function Cleanup() {
window.clearInterval(idTmr);
   //“CollectGarbage” fire JScript's garbage collection to release the reference to Excel
CollectGarbage();
} // end Cleanup()

You will notice it uses completely different logic than our non IE function. Same as the first one function example can be found online but my addition is APEX related selectors and Cleanup function which makes sure that there is no processes left hanging in your Task Manager after user has finished downloading file.

All there is to do before you call these functions is to add this into your page either as Dynamic Action or on page load that will manage already mentioned trick about the report link.

$(".uReportDownloadLinks a").attr("id","my_anchor"); //add ID to old link link
var dLink = 'report_REVENUE';
var filename = 'Revenue';
$( "#my_anchor" ).attr( "href", "javascript:exportHTML('"+dLink+ "','" + filename+ "');");

If you have a look with Firebug end result looks like:

<div class="uReportDownloadLinks">

<a id="my_anchor" href="javascript:exportHTML('report_REVENUE','Revenue' );">Download</a>

</div>

Only known problem is that currently it doesn't support report pagination but I am sure someone will think of the way on how to adjust this code and hopefully let me know about it. :D

Till then all reports without pagination can be downloaded using above approach.

Hope this helps.

Regards,
SLino

APEX stuff in one place

Thursday, 20 November 2014

APEX 4.2.5 - Custom HTML table export

Export your report table after you have modified it with JQuery

No comments:

Post a Comment