Sunday 10 September 2017

Download line wise(record) csv using Oracle Application express

We are going to discuss about how we can download the CSV file for line wise records in oracle apex.

Let's Create a interactive(IR)/classical report on our page(based on emp table).

Region Query :



SELECT  empno,
ename,
job,
sal,
'<a class="btn btn-large btn-primary logout" href="#"> <span class="pi_barcode fa fa-download"  onclick="download_csv('||empno||','''||empno||''')"  style="align-items: center"></a>' AS "Dwnd_Csv"
FROM emp

What's this ?
<a class="btn btn-large btn-primary logout" href="#"> <span class="pi_barcode fa fa-download"  onclick="download_csv('||empno||','''||empno||''')"  style="align-items: center"></a>


Here we are defing what class we want and what should it look like. Oracle application express have inbuilt classes define so we just need to refernce them to get the desire output.
Onclick: It's an event will fire whenever user clicks on Icon.(Our function download_csv(param1,prama2);

So far so good but where is our actual function !

We need to create a function in Function and Global Variable Declaration and call it using onclick event.

function  download_csv(eno,eno) { // here you can specify you're params for demo purpose i'd pass more params
apex.server.process ("download_csv",{ x01:eno, x02:eno},
                                     {
                                        type: 'GET',
                                        dataType: 'json',
                                        success: function(l_json_str)
{
// first we make sure it's a object or not then deal with it accordingly.
var arrData = typeof l_json_str != 'object' ? JSON.parse(l_json_str) : l_json_str;
var CSV = '';
var row = "";
for (var index in arrData[0]) {
row += index + ',';
}
row = row.slice(0, -1);
CSV += row + '\r\n';

for (var i = 0; i< arrData.length; i++) {
var row = "";
for (var index in arrData[i]) {
var arrValue = arrData[i][index] == null ? "" : arrData[i][index] ;
row += arrValue + ',';
}
row.slice(0, row.length - 1);
CSV += row + '\r\n';
}
var fileName = "EmployeeDetails"; // This will be our CSV file name.
/*if(msieversion()){
var IEwindow = window.open();
IEwindow.document.write('sep=,\r\n' + CSV);
IEwindow.document.close();
IEwindow.document.execCommand('SaveAs', true, fileName"barcode.csv");
IEwindow.close();
} else {*/
var uri = 'data:application/csv;charset=utf-8,' + escape(CSV);
var link = document.createElement("a");
link.href = uri;
link.style = "visibility:hidden";
link.download = fileName + ".csv";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
//}
}
});
}


Javascript function is calling our ajax server process which is...

Create Ajax callback with the name: download_csv

DECLARE
l_json_str VARCHAR2(1000); -- based on data we are fetching we need to change this
L_eno VARCHAR2(50):= apex_application.g_x02;
BEGIN

l_json_str:='[';
for x in (SELECT * FROM emp
WHERE empno = L_eno )
LOOP
l_json_str:=l_json_str||'{"EmpNo":"'||x.empno||'","EmpName":"'||x.ename||'","Job":"'||x.Job||'","Salary":"'||x.sal||'"},';
END LOOP;
l_json_str:=substr(l_json_str,1,length(l_json_str)-1);
l_json_str:=l_json_str||']';
sys.htp.p(l_json_str);              
END;

Assuming we know how ajax server process works with-in oracle application express.


Demo : Download_csv
Twitter : Follow
Linked-In : Connect

No comments:

Post a Comment

Show values in right side of shuttle

While working with select list and shuttle, when we want to display values into right side of shuttle depending upon selection from select ...