How can I Export Oracle Reports Data into Excel Sheet?

 Answer 1 By sanjay01
The easiest way to export an Oracle report to Excel is to output it to a delimted (csv) file. This works fine for simple coloumnar reports, but fails for matrix reports

set destype = file
set desformat = delimited

Then run the report and open the file in Excel

 Answer 2 By Srinivas
First make a report then; in the "File" Menu there is an option to "Generate File", in that select "Delimited". A small box appears with the window title "Delimited Output". In that window, specify the date format you gave in the report, if there is one. Specify numbers and cell wrappers also. Then click "OK". If there are no special formats simply press OK.

Then, it will ask you to type the file name for generating the output.

Next go to "Save As". Type in the window "Select All Files". In file name box give the file name as ".xls" and save it.

Compile and run the report. Then the report will be generated.

Later, look where you have saved the file. You will see the file name with the name you had saved with .xls type that will contain data of that particular report.
 Answer 3 By Giselle
If the person creating the Oracle output is the person that want to use the data in Excel, make the report write it to a temporary table, put a button on the report to call Excel with a spreadsheet that already has a pre-setup ODBC link to that temporary table, and then, presto, the data is in Excel (I do this with Access, but don't know how to put a button on the report, so don't ask).

Access can ALSO execute the SQL to view the data, again via ODBC, and THEN the user can export it to a spreadsheet. This solution works best if the data can be collected into a view, preferably on the database (Access can do it too, but sloooowwwsssss down on big record sets). IF a view on the db can ALWAYS keep the requested data together, then Excel can have a permanent ODBC link to that view, but if the data set grows, Access is the better tool: create a table on the view, then create a query on the table with user-changeable sub criteria.

It all depends on how much the user knows, how much s/he is comfortable with manipulating stuff, and how complicated the data collection request is.

Good luck!

There is a third-party software called Oraxcel that is an add-on to MS Excel. This software allows you to access your Oracle database and issue PL/SQL statements in Excel.

 Answer 4 By sem
You may try to output data into XML and open the result file by Excel.
  Answer 5 By Tilal
First generate the file to html file. Then open it in Excel or copy and paste it into Excel.

This is a easy way to get excel. But the html file must in HTML format .
The excel file you get may including many unwelcome blank row .MS-Excel won't remove the blank rows.
You can also refer to another tool call CZ Excel Converter ,But it still won't remove blank rows.
Rep2excel can remove unwelcome blank rows and convert html file in HTMLCSS format !

 Answer 6 By Navneet Singh
You might want to try a workaround:

Suppose you have the paper layout defined in the report (Oracle Reports9i). Go to the web source, and change the contentType as
<%@ page contentType="application/" %>

Also, in the jsp, inside the <rw:dataarea> tag, refer to the "outermost frame" of your paper layout, eg

<rw:dataArea id="yourDataArea">
<!-- Report Wizard inserts the default jsp here -->



Deploy the jsp and access it in the browser. It should show the output in Excel.

Also see another post on this Forum on this topic:

So far there is only the slideshow (viewlet) available.


 Answer 7 By Lion Van

A third-party software called rep2excel can help you to output oracle report in excel format.
The excel file generate by the software may be the one most near to what you need. It can save your time.

Click for detail!


Last Updated on 17th Sep, 2003 , Any suggestion to update this page, please contact me