This discussion is archived
6 Replies Latest reply: Oct 29, 2011 4:14 PM by Marwim RSS

Regarding export to excel from PLSQL

user7351276 Newbie
Currently Being Moderated
Hello,

I am using a utility to export to excel using OWASYLK utility from asktom suggestion.
Here the file type is loaded as SLK type.

Can anyone help me how to get a final file format as .xls . Just need the file to be loade from plsql as xls type instead of SYLK.

is there any utility as Owasylk for XLS also..

I want to do pivioting in a new file based on the loaded file from pl/sql. But when I choose the SYLK file in external data source, excel throws error.

Help is appreciated...
  • 1. Re: Regarding export to excel from PLSQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    If you need an actual Excel file, you have two basic options.

    Option 1) Assuming your target is a recent version of Excel that supports the XML file format, you can use the PL/SQL XML generation APIs to generate a document using the Excel XML specification.

    Option 2) You can use one of the Java Excel generation APIs in a Java stored procedure.

    I've personally preferred option 2 because I'm more familiar with the Java libraries that generate Excel than I am with the Excel XML file specification. Your mileage may, of course, vary.

    Justin

    Edited by: Justin Cave on Feb 1, 2011 4:06 PM

    On reflection, I imagine there must be an option 3 as well... If you're running Oracle on Windows, there must be .Net libraries that generate Excel files-- you could use those to create a .Net stored procedure that generated the Excel file. But that would be way down my personal preference list.
  • 2. Re: Regarding export to excel from PLSQL
    sybrand_b Guru
    Currently Being Moderated
    XLS is a propietary format, so no, other than CSV or SYLK is not possible.
    You could of course have Excel to connect to Oracle or
    you could use HSODBC (Oracle Hetergeneous Services using ODBC) to connect to an existing XLS file, but that would require abstracting documentation on my end, so as you don't mention a four digit version I'm not going to do that.

    ------------
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: Regarding export to excel from PLSQL
    user7351276 Newbie
    Currently Being Moderated
    thanks for the reply.

    Ok I have a SLK file loaded with data.

    Now I open a new excel file"XLS format" and tryin to create a pivot chart based on data available in the above SLK file..

    is this possible..?
  • 4. Re: Regarding export to excel from PLSQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    That sounds like a question that would be better addressed to a group of Excel experts. I can wrestle Excel into generating charts if given enough time but I'm far from an Excel expert.

    Justin
  • 5. Re: Regarding export to excel from PLSQL
    6363 Guru
    Currently Being Moderated
    user7351276 wrote:

    Ok I have a SLK file loaded with data.

    Now I open a new excel file"XLS format" and tryin to create a pivot chart based on data available in the above SLK file..
    Can't you just open the SYLK file in Excel then Save As.. and choose XLS format, and then pivot?

    Fortunately I don't have Excel at the moment to check.
  • 6. Re: Regarding export to excel from PLSQL
    Marwim Expert
    Currently Being Moderated
    You can try to export your Data using Office 2003-XML format. There are free packages available to do so.
    http://matzberger.de/oracle/spreadsheet-en.html or
    http://radio-weblogs.com/0137094/2006/10/26.html

    The format even supports pivoting, though it is not directly implemented in any of these packages.

    Regards
    Marcus

    Edited by: Marwim on 30.10.2011 01:13
    Removed links to Oracle Samplecode

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points