Dispalying a report's output in an Excel sheet

Posted on 2004-09-20
Medium Priority
Last Modified: 2008-02-20

am working on developer 9i.  When displaying a report, am using the WHEN-BUTTON-PRESSED trigger :


but i dont want to generate the report in a PDF format, but i want it in an excel sheet...how can i do that?

Question by:ausan
  • 4
  • 4
  • 3
  • +2
LVL 22

Expert Comment

by:Helena Marková
ID: 12100997
You can try

Expert Comment

ID: 12101173
if u have ability to use another reporting tool, Crystal Reports can generate reports in pdf, doc, xls, ...

Expert Comment

ID: 12101283
You can create report in Discoverer (easy to convert into excel, but it's much of work aside), use delimited ascii extract (easy to read in excel), or use data query in excel via ODBC. For general usage of excel outputs we usualy use Discoverer.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


Expert Comment

ID: 12104585
You can use desformat=DELIMITEDDATA Henka suggested.  Also add this parameter in your URL:  "mimetype=application/vnd.ms-Excel"

For an Oracle report in DELIMITEDDATA format, you cannot use the same Oracle report file (RDF) that you use for .PDF output.  If you do, the user will see all the column headings on each row on the left hand side of the spreadsheet.  These DELIMITEDDATA reports are very easy to create, as they require no formatting or grouping.  In order to make the Excel output format correctly, here are some tips

1.      Only use one SQL Query.  If you need more complicated stuff that can’t go in a SQL Query, you can use functions, but put the functions in the same group with your other fields.  
2.      Only use one group and don’t put totals or subtotals on the Report.  The users can add subtotals and totals wherever they feel it is necessary
3.      You don’t need column headings in the Layout, but make sure you alias the columns in your SQL statement so that they will make sense to the user as Excel column headings.  If you don’t do this in your very first time you create the SQL statement, you will have to go back and modify the XML tag of each field in your Data Model.
4.      You only really need one field and one group in your Layout.  No need to put everything in there and formatting it.  However, make sure dates are formatted within your SQL statement, as you can’t format them in the Layout.
5.      Make sure the fields are in the correct order in the Data Model, as that is the way they will appear on the Excel Spreadsheet.

Another thing to consider is security.  You are passing your userid/password@database in your URL.  If you need a workround for this:

1.      On the server, Add ‘hide_pass_key:  userid=trans/trans123@omntlp%*’ to the key mapping file: $ORACLE_HOME/server/cgicmd.dat.  

2.  In your URL instead of "&userid=trans/trans123@omntlp", just put in "hide_pass_key"

Author Comment

ID: 12109185
when i tried what u suggested, it gave me the following error,

REP-826: Invalid printer driver 'DELIMITEDDATA' specified by parameter DESFORMAT.

LVL 22

Expert Comment

by:Helena Marková
ID: 12109233
... try with DESFORMAT=delimited ...

Author Comment

ID: 12109384
hi again,

ok...i had the data but the fields are not properly dispalyed,

STATUS      APPROVED      Serial No      Request Date      Empno      Emp Name      From Date      To Date      1046      21/09/2004      53220      MOHD ALI SAID BAIT SHAJEEB      21/09/2004      21/09/2004      

can u help plz...
LVL 22

Accepted Solution

Helena Marková earned 210 total points
ID: 12109671
Maybe this Note:107764.1 from Metalink will help you:

Generating to Delimited Text Output Causes Report Headers to be Repeated

Problem Description

You are using the new feature in Reports 6.0 to generate delimited Text output. You run the report and produce the text output. You have chosen any of the delimiter types to test. You are now pulling the file into Excel and find that the Header fields are not at the top of the data columns as expected, but at the left and repeated for every record.

Solution Description

This is a documented feature of the text delimited feature. In order to produce output and get an excel file that is usable then do the following steps.

The available steps are to:

1. Open the output in notepad or wordpad after the report is complete

2. Highlight the desired Header fields

3. Use Ctrl-C to copy

4. Click at the begining of the first line

5. Use Ctrl-V and Ctrl-V again to copy the headers twice in order to position the headers on top of the data columns

6. Press return to place line 1 on the second line

7. Save the file

8. Then open the file in Excel

9. Highlight the header columns to the left and delete.

You will note that the header is in the right place above the data columns.


This is a documented result of the delimited output feature. You can read up on it by using the Online help, then select FIND, then type in Delimited. In window scroll down to "delimited output." If you look at the examples using the buttons you will see this output.

Expert Comment

ID: 12111712
Did you get it to work using DELIMETED or DELIMITEDDATA?

If you are using DELIMITED, then the reason your fields are not displaying properly, is because they need to be formatted exactly right.  Make sure all the items in the Layout are all the same height and line up perfectly.

Did you put your column headings in the Header section?

Author Comment

ID: 12121073
It worked using the DELIMETED data type,

i checked the column headings,and they were not in the header's section, how can i transfer them to the Header's section?

thank u...

Expert Comment

ID: 12122449
Go to your Layout.  
Select each of the column labels then click on Ctrl-c
Under the View menu, go to Layout Section and click Header Section
Then paste your labels into the header by clicking Ctrl-v

Author Comment

ID: 12130687
i cant copy and paste it

Expert Comment

ID: 12132522
you can always just put in new labels in the header section and delete the old ones from the body.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question