This discussion is archived
8 Replies Latest reply: Apr 6, 2009 6:19 AM by 588679 RSS

Output report data to excel file format or csv format

3004 Newbie
Currently Being Moderated
Is there any way to save softcopy of report output to excel file format or csv format.
  • 1. Output report data to excel file format or csv format
    3004 Newbie
    Currently Being Moderated
    Yes.

    For 'CSV', use DESFORMAT=DELIMITED. There
    are issues, see another thread about
    DELIMITED_HDR.

    For 'Excel', the Oracle answer is again
    DELIMITED. There are ways to do better, but
    not directly within Reports. We build
    PL/SQL programs to do that, since it gives
    us control over column/row/field formats.

    -- Allan Plumb
  • 2. Re: Output report data to excel file format or csv format
    588679 Newbie
    Currently Being Moderated
    Hi Allan,

    Could you please send me the pl/sql stuff and all the steps to get oracle reports output to be saved as .xls file.

    I am using Reports 10g 1.2.0 ver. I use the desformat=spreadsheet destype=file desname=abc.xls

    It is saving the output as abc.xls. But when i open the output it is giving the warning as "The file you are trying to open is in different format than specified by the extension. Verify the file is not corrupted ....... Do you want to open the file now?" Yes or no dialog

    When I say yes it is opening.

    1) How to create the excel xls output without the warning?

    Is 10g 1.2.0 supports multible worksheets? Oracle site says single worksheet only.

    2) I have requirement where the outputs to be designed for multiple worksheets. Is there any workarounds to create multiple worksheets in oracle reports 10g 1.2.0?

    3) How to save the oracle reports output in another machine database.
    Please give the detailed steps.

    Could you please send me the pl/sql stuff and steps for oracle report to excel .xls file

    Please send it to suthasingaram@gmail.com

    Thanks in advance.
  • 3. Re: Output report data to excel file format or csv format
    Marwim Expert
    Currently Being Moderated
    Hello 585676,

    multiple worksheets are not possible from Oracle Reports. You will have to generate a text file that can be read by excel. At Re: How to save a query result and export it to, say excell? you can find links to different solutions. At least the packages behind second and fourth link support more than one worksheet.

    Regards
    Marcus
  • 4. Re: Output report data to excel file format or csv format
    DC Pro
    Currently Being Moderated
    Using PL/SQL, you can do all your three requirements in After Report trigger. Refer to the other posted links.
  • 5. Re: Output report data to excel file format or csv format
    588679 Newbie
    Currently Being Moderated
    Hello,

    Thanks for your reply.

    My requirement is for each worksheet will have data and graphs.

    Whatever design we do at the main section paper layout that is going to be the output in excel.

    Since at the Report module level in property inspector under Taxonmy title, we can set the worksheet title for single worksheet only.

    How multiple worksheets are possible?

    But using oracle reports 10g, i want to produce complicated multiple excel worksheets as output.

    One worksheet will have data and bar or any type of graph and another sheet will have either of this or combination etc.

    Could you please provide me the examples how to achieve this?

    Any help would be appreciated.

    Thanks in advance.
  • 6. Re: Output report data to excel file format or csv format
    Marwim Expert
    Currently Being Moderated
    Hello 585676,

    it cannot be done in Oracle Reports.

    You can generate a XML file for multiple worksheets, PL/SQL packages for this are linked above. This format cannot contain graphics.

    If you need all of it together I don't know a free solution. Maybe there is some report generator available, but as said it is not Oracle Reports.

    Regards
    Marcus
  • 7. Re: Output report data to excel file format or csv format
    DC Pro
    Currently Being Moderated
    Don't think Oracle Reports can output importing-ready files for Excel with graphs etc. I am not an Excel guy neither. Don't know how Excel makes graph/bar etc. once it read in data.

    See you previous posted three requirements need only comma-delimited text files, which can surely be produced in PL/SQL.
  • 8. Re: Output report data to excel file format or csv format
    588679 Newbie
    Currently Being Moderated
    Hi,

    Regarding csv file format, i have no issues. The file is generating without any issues in using oracle reports without using any PL/SQL code.

    My requirement is to design oracle reports to generate excel (.xls) file with multiple worksheets. Each sheets are having many data and graphs(chart).

    Using oracle reports alone, how to achieve this.

    In oracle reports 10g 1.2.0 version, I tried by creating .rdf file but, it is generating single worksheet only.

    In oracle reports 10g 1.2.0 ver, I tried by creating .jsp file. For this first i am creating excel template about how my ouput column headings all that going to be with one sample hard coded data and save the excel file as web page.
    Eg employee.html.

    Next open the html file in oracle reports builder and double click the websource now, you will see the jsp tags, html and xml tags. Now include the contentType="application/vnd.ms-excel " and charset also.

    Next, include the <rw:foreach id="G_EMPNO_1" src="G_EMPNO">
    here insert the fieldl for each column by removing the hard coded values.
    close the tag
    </rw:foreach>
    Save the file as .jsp and deploy it in oc4 enabled folder (say, devsuite_home/reports/j2ee/reports_ids/web
    Start the oc4J server
    Run it in the browser http://server:port/reports/emp.jsp?useride=uid/pwd@db

    It is invoking the Microsoft excel with 3 sheets default and my emp table output in the first page.
    We can save this output file as .xls file by clicking file -> save as.

    1) The question is, it is working fine with Microsoft excel 97-2003 version. But for excel 2007, i am not able to create single html file like how 2003 save web page option.

    2) I found this in oracle getting started demo
    http://www.oracle.com/technology/products/reports/htdocs/getstart/demonstrations/index.html
    Which is more useful. This is what i am looking for.

    I done that in excel 2003 as per demo. But excel 2007 with reports 10g issues.

    Is there any demo for 10g with excel 2007

    3) For most of excel issues working fine with excel 2003 and 10g. But excel 2007 with 10g reports are issues.

    I want the excel output from oracle reports with multiple worksheet similar to the above demo.

    Thank you.