Oracle Reports: Generate output into Spreadsheet Excel Format

 Most end-users use Excel as a very generic tool, because of their familiarity and the control in analysing data using the power of MS Excel.

Customers request converting lot of reports to excel.
As you can imagine, converting all reports to excel is a mammoth task using BI (XML) Publisher.

There is a trick to capture Oracle Reports output (text output) into a excel sheet.

This trick uses the power of XML and MS Excel to format the data. This is trick is a simple and effective work around.


Basic Steps :
1) Set the output format for the report to XML
2) Run the report to generate output.
3) Save the output file locally as a XML file.
4) Open the file using MS Excel.
5) To make it more beautiful, you may use a MS Excel Template.

The Excel Templates can be used to do complex data analysis and formatting. Displayed example below is a simple excel template, to make the point.

Example URL:

http://127.0.0.1/reports/rwservlet?server=rep_APPSER_FRHome1&report=C:\MYREPORT.rdf&desformat=xml&destype=cache&userid=%77%61%68%2F%77%61%68%40%73%65%72%31%38&paramform=No&maximize=Yes

By mentioning destination, you can directly save the xml output into .xls excel file.

Example URL:

http://127.0.0.1/reports/rwservlet?server=rep_APP-SER_FRHome1&report=C:\MYREPORT.rdf&desname=c:\myfile1.xls&desformat=xml&destype=file&userid=%77%61%68%2F%77%61%&paramform=No&maximize=Yes

Another Technique is:

1. The SELECT statement should produce a string of data separated by commas.
For example:

select deptno||','||dname||','||loc the_string
from dept;

2. The report must be created as a Character Mode report, and the output
filename must have a .csv extension. Set the following System Parameters
under the Data Model node in the Object Navigator. Make column size large
enough to hold the data.

System Parameter                    Name Initial Value
---------------------------------- ------------------------------

MODE                                           Character
DESTYPE                                     File
DESNAME                                   x.csv
DESFORMAT                              dflt

3. Now, run the report; it will create the output file x.csv.

The file x.csv can be imported into MS Excel. There will be three columns
of data.

NOTE: This information is valid up to, but not including Reports 6.0,
where desformat=delimited, is all that is required.