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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

801 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