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
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
