Check this site to see a demo: http://www.oracle.com/technology/products/reports/htdocs/getstart/demonstrations/excel/index.html
1) Create an xls template
This template will contains generic information as the title , logo, column header, etc.
And a simple line of data with sample values, this is to ensure that the correct formatting is performed for the different datatypes.
Save the xls spreadsheet as a web page (File -> save as web page (htm or html)).
2) Open the web page in reports builder
Open the web page in reports builder and double click on Web Source to display the html code for the excel spreadsheet. Add a datasource to the report, click on data model icon and click the sql to create a data source based on a sql query:
select cod_provincia, cod_prestacion, descripcion, estado, cantidad
from table
3) Modify the Web Source
Now you need to modify the web source to tell reports builder to display your report in excel.
To force the browser to open MS Excel it is necessary to change the HTTP Content Type to a specific MIME Type: application/vnd.ms-excel
<%@ taglib uri="/WEB-INF/lib/reports_tld.jar" prefix="rw" %>
<%@ page language="java" import="java.io.*" errorPage="/rwerror.jsp" session="false" %>
<%@ page contentType="application/vnd.ms-excel" %>
<rw:report id="reportJsp" >
<rw:objects id="objects">
</rw:objects>
To respect excel format you need to delete the blank lines above the <html> tag:
<%@ taglib uri="/WEB-INF/lib/reports_tld.jar" prefix="rw" %>
<%@ page language="java" import="java.io.*" errorPage="/rwerror.jsp" session="false" %>
<%@ page contentType="application/vnd.ms-excel" %>
<rw:report id="reportJsp" >
<rw:objects id="objects">
</rw:objects>
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
Now you are going to add the data retrieved by your sql query to the report:
Search for the example data that you write on the excel template, and then add a repeating frame:
<rw:foreach id="gCodProvinciaId" src="G_COD_PROVINCIA">
<tr height=18 style='height:13.5pt'>
<td height=18 style='height:13.5pt'></td>
<td class=xl27><rw:field id="codPrestacionId" src="COD_PRESTACION"> &Field </rw:field></td>
<td class=xl27><rw:field id="descripcionId" src="DESCRIPCION"> &Field </rw:field></td>
<td class=xl27><rw:field id="estadoId" src="ESTADO"> &Field </rw:field></td>
<td class=xl26 align=right><rw:field id="cantidadId" src="CANTIDAD"> &Field </rw:field></td>
</tr>
</rw:foreach>
In the opening repeating tag for each add the name of the report group. And then map the cells of the excel spreadsheet to the corresponding field from your data model.
I also add a summary on my report on field cantidad and after </rw:foreach> I show this summary like this:
<tr height=17 style='height:12.75pt'>
<td height=17 style='height:12.75pt'></td>
<td class=xl27> </td>
<td class=xl27> </td>
<td class=xl27> </td>
<td class=xl35><rw:field id="totalId" src="total"> &Field </rw:field></td>
</tr>
Then I save the report as a Report JSP
4) Test the report
You can now test the report using the run web layout icon in the toolbar.
This execution creates a temporary HTML file and launches the browser. To launch excel from the browser you need to test it from Report Server.
First start report server and then copy the jsp report to: ids_home/reports/j2ee/reports_ids/web/test
And on the browser the url: http://hostname:port/reports/test/reporte.jsp?userid=xxx/xxxx@sidxxx
Then I need to call my report from a form that runs on oracle application server 10.1.2 on Suse 10, so I was wondering how to deploy a jsp report on the web and I found the Metalink note 220943.1.
And I chose the simplest way that is to deploy my jsp report in the reports application.
You copy the JSPs under the $Oracle_Home/j2ee/OC4J_BI_Forms/applications/reports/web,
you can create subdirectories. And the code on my form to call my jsp report is : web.show_document('/reports/test.jsp?'||'keyxxx'|| '&p_cod_provincia='||nvl(:b1.cod_provincia,0)||'&p_d_provincia='||:b1.descripcion); 
No comments:
Post a Comment