Tuesday, January 5, 2010

Implementing Export Button Functionality Programatically

Hi All,
There is often a requirement to export a VO data or a UI table data into a CSV file. OA Framework provides export button bean for the this functionality, which works fine in almost 99% of the cases. But sometimes, you might face a scenario when ur table u have complex UIs like switcher/hide show columns etc, where the standard export functionality doesn't work or you want some columns not to come in export, or you wanna change some data on export.

In such cases, you can implement the export functionality programatically by yourself.Copy paste the following method in the page CO and pass appropriate parameters to get exported data.You can change this method, as per your requirement, to get data/format data or change data.

By default this method will bring all columns of fetch rows of VO instance,although you can use param hidden_attrib_list to pass attributes which you don't want to be included in csv file.You can call this method in the submit button event that you have made in process form request like :

//see api parameter details in the method below.
//array of Vo attr names which need not be written in csv file
String ss[]={xID,xName};
downloadCsvFile(pageContext, "XxAdatVisSearchVO",null, "MAX",ss);

/**
* @param pageContext
* @param view_inst_name is the view object instance name like VO1 etc in root AM.
* Make sure the VO instance name you have passed should be same as in Root AM.
* @param file_name_without_ext - pass for eg for abc.csv , u should pass "abc".If no
* name is passed then by default it will pick "Export.csv".
* @param max_size -pass "MAX", if u want all rows, pass null to get fetch row count
* else pass integer number like 10,20 etc , the number of rows you want to fetch.
* @param hidden_attrib_list -Array of VO attribute names which doesn't need to be shown/written in
* csv file.
*/
public void downloadCsvFile(OAPageContext pageContext,
String view_inst_name,
String file_name_without_ext,
String max_size, String[] hidden_attrib_list)
{
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 = "Export";
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");
PrintWriter pw = null;

try
{
pw = response.getWriter();
int j = 0;
int k = 0;
boolean bb = true;
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;
}

//Making header
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()));
}
}
}
if (zx)
{
cc.append("\"" + a[l].getName() + "\"");
cc.append(",");
}
}
String header_row = cc.toString() + "\n";
pw.write(header_row);

for (OAViewRowImpl row = (OAViewRowImpl) v.first(); row != null;
row = (OAViewRowImpl) v.next())
{
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")))
{
//formatting of date
oracle.jbo.domain.Date dt = (oracle.jbo.domain.Date) o;
java.sql.Date ts = (java.sql.Date) dt.dateValue();
java.text.SimpleDateFormat displayDateFormat =
new java.text.SimpleDateFormat("dd-MMM-yyyy");
String convertedDateString = displayDateFormat.format(ts);
b.append("\"" + convertedDateString + "\"");
}
else
{
b.append("\"" + o.toString() + "\"");
}
}
else
{
b.append("\"\"");
}
b.append(",");
}
}
String final_row = b.toString() + "\n";
pw.write(final_row);
if (!bb)
{
if (j == k)
{
break;
}
}
}
}
catch (Exception e)
{
// TODO
e.printStackTrace();
throw new OAException("Unexpected Exception occured.Exception Details :" +
e.toString());
}
finally
{
pw.flush();
pw.close();
}
}

Happy coding!

30 comments:

Unknown said...

Hi Mukul,

running into following issue with your code:
java.lang.IllegalStateException: OutputStream already retrieved

at com.evermind.server.http.EvermindHttpServletResponse.getWriter(EvermindHttpServletResponse.java:975)

at xxtn.oracle.apps.per.personnel.webui.ControllerUtil.downloadCsvFile(ControllerUtil.java:566)


any ideas?

Thx,

Mark.

Mukul Gupta said...

Hi MARK,
You must have called getWriter() earlier also in ur code apart from this api. You are allowed to do that only once.

Read the Javadoc for getWriter().

If you need to write multiple times keep a refference to the Writer in a local variable and use that variable to invoke write().

Unknown said...

Hmmm, not by my hand though... must have been some OAF code presumably.

Unknown said...

Mukul,

I face the same issue as Mark: as soon as I call this method in my processFormRequest, the line "out = response.getWriter();" goes into error "OutputStream already retrieved"... You do anything special to make this work?

Unknown said...

I have change "getWriter" into "getOutputStream" and the I don't get an error anymore. However it still doesn't work completely because now I have the date in my csv-file, but also a lot of HTML text under my data. Any idea how this is possible?

Mukul Gupta said...

JB/Tim,
I am not facing this error , I am testing on r12 , jdev 10g. Can you let me know where exactly you guys are facing this error .. apps version?
Also can try definig printwriter as global variable and take an instance of it from response only if its null.
--Mukul

Bill Zheng said...

Mukul,

I got the same error as Tim and Mark, I changed from getWriter to getOutputStream as Tim did, I get a lot of weird information at the last line like these:

Bill Zheng said...

I figured it out. Put pageContext.setDocumentRendered(false); before pm.flush().

Thank you Mukul for your great article.
-Bill

Unknown said...
This comment has been removed by the author.
Ajay Sharma said...

Hi All

If you are experiencing "java.lang.IllegalStateException: OutputStream already retrieved" error with above code. Please go through below link

http://forums.oracle.com/forums/thread.jspa?threadID=2136568&stqc=true

Thanks
AJ

rbojja said...

Hi Mukul,
I need to session out OAF page after 20 mins. How to programatically time out session in OAF pages.

Thanks,
Raghav

Raja said...
This comment has been removed by the author.
Raja said...
This comment has been removed by the author.
Raja said...
This comment has been removed by the author.
Unknown said...

HI Mukul,

I am basically a java web developer with experience in struts, hibernate. How can i start learning OAF? Shall i start with oracle apps or shall i start with OAF itself?

~JOni~ said...

Hi Mukul,

Encounter the following error when i used the code. The error code is display in the excel (csv) file.

META name="fwk-error-detail" content="oracle.apps.fnd.framework.OAException: java.lang.NullPointerException
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1408)
at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2662)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1665)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:502)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:423)
at _OA._jspService(OA.jsp:40)

any idea? Thanks
Joni

Unknown said...

Hi Mukul,
I have a custom page which had been working fine till I added few new columns to that page. Now when I run the page it gives me the data correct, but when I click the Export button it is giving me error
oracle.apps.fnd.framework.OAException: oracle.jbo.RowNotFoundException: JBO-25060: Unexpected error encountered while trying to retrieve the next row from JDBC ResultSet for collection OAExportHelper_SecondRowSet
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1408)
at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2680)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1683)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:509)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:430)
at _OA._jspService(OA.jsp:33)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
at java.lang.Thread.run(Thread.java:534)
## Detail 0 ##
java.sql.SQLException: ORA-01722: invalid number


I have no clue why. I have deleted the old button and created a new one but still the same issue. Please please help me as this is in production.
Thanks.

BRANTIX said...

It was really helpful 4 me!

Thaaank you so much!

BRANTIX said...

It was really helpful 4 me!

Thaaank you so much!

ronny said...

Works like a charm. Keep up this good work.
Ronny

ronny said...
This comment has been removed by the author.
Tani said...

Hi Mukul,

Encounter the following error when I used the code. The error code is display in the excel (csv) file.

META name="fwk-error-detail" content="oracle.apps.fnd.framework.OAException: java.lang.NullPointerException
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(OAPageErrorHandler.java:1408)
at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2662)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1665)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:502)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:423)
at _OA._jspService(OA.jsp:40)

Please let me know your suggestions.Thanks.

♥ ♥ ♥♫ Prince Anurag ♫ ♥ ♥ ♥ said...

Hi Admin,


clicked on the export button in OA.jsp for downloading table as CSV.
But in the generated CSV along with table data, i'm getting JSP HTML code also. how to resolve this issue?

BTW , used the same code which u have posted here.

Manjula said...

Hi Mukul,

We have a requirement to hide few columns in the excel after export the table results. i have made export view attribute as false. In this case the data is not coming in excel but column headers are still appearing on the excel. How can we hide the columns ..it would be great if you can help me in achieving this.

Thanks,
Manjula

Unknown said...

Adu Jago

Hitendra Singh said...

Hi Mukul,

After Exporting data into Excel File. I am getting HTML tags as well..How to remove these tags.Please help.

Unknown said...

bandar adu ayam online s128 paling aman

Eva.William said...
This comment has been removed by the author.
dewisari said...

Yuk di add pin WA: +628122222995
Sabung ayam online dan semua jenis permainan judi online ..
Semua bonus menarik kami berikan setiap hari nya ... :)
www,bolavita, ltd ayamlaga bangkok

bakee said...

Hi Mukul,

After Exporting data into CSV File. I am getting HTML tags as well..How to remove these tags.Please help.