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*/
Now run the page and click on export button it will
open the data in excel.
Hi
ReplyDeleteThis is not working when moved to Instance it is not popping up in Excel
may i know where you have to write logic for creation of excel file.
ReplyDeletemay iknow where you have written the logic for creation excel file..
ReplyDelete