Contact : oafqueries@gmail.com (OAF/ADF Trainings and Customizations)

Saturday, June 18

How to export the multiple VO data to excel programatically

Hi,

A lot of times we have the requirement to export table data in excel. We have a standard OAF item called Export Button to do the same but, one export button is attached to one View Object Instance only and it only exports attributes which are shown on to the table region not the hidden attributes.

So, how should we export data from multiple VO's at single button click??

Well, here is the code :

1) Create a submit button on page say : "download"
2) We have multiple VO's for exporting the data
xxcnspDetailsVO1
xxcnRepositoryVO1
xxcntrCallVO1
3) Handle the download button click on PFR of CO as :

if(pageContext.getParameter("download")!=null)
{
String dept_name = null;
String emp_name = null;
String site_name = null;


OAViewObject vo = (OAViewObject)am.findViewObject("xxcnspDetailsVO1");
if(vo!=null)
{
dept_name = (String)vo.first().getAttribute("DeptName");
emp_name = (String)vo.first().getAttribute("Name");
}
OAViewObject repvo = (OAViewObject)am.findViewObject("xxcnRepositoryVO1");
if(vo!=null)
{
site_name = (String)repvo.first().getAttribute("Name");
}
OAViewObject trCallVO = (OAViewObject)am.findViewObject("xxcntrCallVO1");

try
{

HttpServletResponse response = (HttpServletResponse) pageContext.getRenderingContext().getServletResponse();
response.setContentType("application/text");
response.setHeader("Content-Disposition","attachment; filename=" + "Employee Trouble Call Report" + ".csv");
PrintWriter pw = null;
pw = response.getWriter();


pw.write("Department :");
pw.write(",");
if(dept_name !=null) // for null handling in the code
{
pw.write(dept_name);
}
else
{
pw.write(" ");
}

pw.write("\n"); // for going to next line

pw.write("Team :");
pw.write(","); // for going to next cell in excel
pw.write(site_name);
pw.write("\n");

emp_name = emp_name.replaceAll(",",".");
pw.write("Employee :");
pw.write(",");
pw.write(emp_name);
pw.write("\n");
pw.write("\n");
pw.write("\n");

//Writing the headers
pw.write("Customer Number");
pw.write(",");
pw.write("Account");
pw.write(",");
pw.write("Install Repeat Date");
pw.write(",");
pw.write("Work Order Number");
pw.write(",");
pw.write("Work Order Type");
pw.write(",");
pw.write("Problem Code");
pw.write(",");
pw.write("Finding Code");
pw.write("\n");
// getting table data
if(trCallVO!=null)
{
for(xxcntrCallVORowImpl row = (xxcntrCallVORowImpl)trCallVO.first();row!=null;row =(xxcntrCallVORowImpl)trCallVO.next())
{
if(row.getCustNumber()!=null)
{
pw.write(row.getCustNumber());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getAcct()!=null)
{
pw.write(row.getAcct());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getInstRpDate()!=null)
{
pw.write(row.getInstRpDate());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getWoNum()!=null)
{
pw.write(row.getWoNum().intValue());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getWoNum()!=null)
{
pw.write(row.getWoNum().intValue());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getOrdType()!=null)
{
pw.write(row.getOrdType());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getProbCode()!=null)
{
pw.write(row.getProbCode());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getFindingCode()!=null)
{
pw.write(row.getFindingCode());
}
else
{
pw.write(" ");
}
pw.write("\n");
pw.write(" ");
pw.write(",");
pw.write(" ");
pw.write(",");
if(row.getInvDate()!=null)
{
pw.write(row.getInvDate());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getOrderNumber()!=null)
{
pw.write(row.getOrderNumber().intValue());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getAttribute74()!=null)
{
pw.write(row.getAttribute74());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getAttr28()!=null)
{
pw.write(row.getAttr28());
}
else
{
pw.write(" ");
}
pw.write(",");
if(row.getAttr27()!=null)
{
pw.write(row.getAttr27());
}
else
{
pw.write(" ");
}
pw.write("\n");
}
}
pw.write(" ");
pageContext.setDocumentRendered(false); //mandatory
pw.flush(); //exporting data
pw.close();

}
catch(Exception e)
{
e.printStackTrace();
}

}

This way you can include as many VO's as you want.

Thanks,
Gaurav

3 comments:

  1. Thank you ... helped me a lot.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Thank you Gaurav, I have a question on Export. In our case the export is based on a complex view which takes around 15-20 min to execute so the whole download process takes around 15-20 min.

    Can you please suggest if there are alternative ways to improve the performance.

    ReplyDelete