Exporting data from OAF page to Excel programmatically
Now we are going to learn about how to export the data of VO from OAF page to excel sheet.
Consider there is a table region which retrieves a data from a VO. Now we have to export that data to excel sheet.
Create a submit button in the table region.
Write the following code in the Controller ProcessFormRequest
/*
****************************************
* Call the export button
****************************************
*/
if(pageContext.getParameter("Export") != null)
{
/* Specify the hidden attribute column names*/
String ss[] = { null };
/* Call the method to export data */
downloadCsvFile(pageContext, "AOPErrorMsgVO1", null, "MAX", ss);
}
/* End of calling the export button */
/*
*************************************
* Create method to export the data
*************************************
*/
public void downloadCsvFile(OAPageContext pageContext, String view_inst_name, String file_name_without_ext, String max_size, String hidden_attrib_list[])
{
/*
***********************************
* Specify all Column names
**********************************
*/
String voFieldNames[] = {
"Error Message","Item/Level1", "BillToCust/Level2", "ODM/Level3", "OEM/Level4", "MarketSegment/Level5", "Program Name/Level6", "Organization/Level7","Time Period","Fiscal Year", "Total Units", "Total Sales"
};
/* End of column names */ OAViewObject v = (OAViewObject)pageContext.getRootApplicationModule().findViewObject(view_inst_name);
if(v == null)
{
throw new OAException("Could not find View object instance "+view_inst_name+" in root AM.");
}
if(v.getFetchedRowCount() == 0)
{
throw new OAException("There is no data to export.");
}
String file_name = "AOP_Error_Report";
if(file_name_without_ext != null && !"".equals(file_name_without_ext))
{
file_name = file_name_without_ext;
}
HttpServletResponse response = (HttpServletResponse)pageContext.getRenderingContext().getServletResponse();
response.setContentType("application/text");
response.setHeader("Content-Disposition", "attachment; filename="+file_name+".csv");
ServletOutputStream pw = null;
try
{
pw = response.getOutputStream();
int j = 0;
int k = 0;
boolean bb = true;
System.out.println("inside try block");
if(max_size == null || "".equals(max_size))
{
k = Integer.parseInt(pageContext.getProfile("VO_MAX_FETCH_SIZE"));
bb = false;
} else
if("MAX".equals(max_size))
{
bb = true;
} else
{
k = Integer.parseInt(max_size);
bb = false;
}
AttributeDef a[] = v.getAttributeDefs();
StringBuffer cc = new StringBuffer();
ArrayList exist_list = new ArrayList();
for(int l = 0; l < a.length; l++)
{
boolean zx = true;
if(hidden_attrib_list != null)
{
for(int z = 0; z < hidden_attrib_list.length; z++)
{
if(a[l].getName().equals(hidden_attrib_list[z]))
{
zx = false;
exist_list.add(String.valueOf(a[l].getIndex()));
}
}
}
}
for(int l = 0; l < voFieldNames.length; l++)
{
boolean zx = true;
if(zx)
{
cc.append("\""+voFieldNames[l])+"\"");
cc.append(",");
}
}
String header_row = cc.toString();
pw.println(header_row);
OAViewRowImpl row = (OAViewRowImpl)v.first();
do
{
if(row == null)
{
break;
}
j++;
StringBuffer b = new StringBuffer();
for(int i = 0; i < v.getAttributeCount(); i++)
{
boolean cv = true;
for(int u = 0; u < exist_list.size(); u++)
{
if(String.valueOf(i).equals(exist_list.get(u).toString()))
{
cv = false;
}
}
if(cv)
{
Object o = row.getAttribute(i);
if(o != null)
{
if(o.getClass().equals(Class.forName("oracle.jbo.domain.Date")))
{
Date dt = (Date)o;
java.sql.Date ts = dt.dateValue();
SimpleDateFormat displayDateFormat = new SimpleDateFormat("dd-MMM-yyyy");
String convertedDateString = displayDateFormat.format(ts);
} else
{
}
} else
{
b.append("\"\"");
}
b.append(",");
}
}
String final_row = b.toString();
pw.println(final_row);
if(!bb && j == k)
{
break;
}
row = (OAViewRowImpl)v.next();
} while(true);
}
catch(Exception e)
{
e.printStackTrace();
throw new OAException("Unexpected Exception occurred.Exception Details :"+e.toString());
}
pageContext.setDocumentRendered(false);
try
{
pw.flush();
pw.close();
}
catch(IOException ioexception)
{
ioexception.printStackTrace();
throw new OAException("Unexpected Exception occurred.Exception Details :" ioexception.toString());
}
try
{
pw.flush();
pw.close();
}
catch(IOException e)
{
e.printStackTrace();
throw new OAException("Unexpected Exception occurred.Exception Details :"e.toString());
}
}/*End of Export method*/
