Oracle to Excel - PL/SQL export procedure

Question

Hi,

I’m writing pl/sql procedure that exports data from Oracle to Excel. I need data formatting so I can’t use CSV. I’ve already tried with XML but it generates too large files when I want to export e.g. 70000 rows with 50 columns (almost 300 MB!!!).

That’s why I decided to use HTML tags to generate XLS file – it is smaller than XML and I must directly define format of only some special columns (strings, numbers and dates are formatted automatically by Excel). It’s very simple and convenient but I can’t define more than one worksheet.

Do you know how to add/define more than one worksheet in excel file written using HTML? I’ve tried to use VBScript formula like <% ActiveWorkbook.Worksheet.Add %>, but it doesn’t work.

Best regards,

Przemek

Answer 1

Instead of creating Excel or HTML files on Oracle server, you can fetch Oracle data to existing Excel document via ODBC or OLEDB. The shortcoming is, that you should be careful with user permissions.

Answer 2

You could save a (small) Excel sheet as html in Excel, and then reproduce that format.

Answer 3

You can get Tom Kyte's OWA-SYLK utility, which supports a subset of .xls format features.

Answer 4

There is a product called SQL*XL which allows you to run sql queries from within Excel, and the results appear within the worksheet (it can also update).

It is commercial, not free, but is only about €50, so not expensive. I use it quite a lot

Answer 5

I have developed a package in pl/sql for generating excel file with formatting and multiple worksheets, it might be useful for you.

http://sanjeev-oracle-world.blogspot.com/2007/06/create-excel-workbook-by-plsql-code.html

Regards Sanjeev

Answer 6

The ExcelDocumentType is a great solution. It allows you to generate fully functional multi-sheet Excel documents with PL/SQL. You can find it here:

http://radio.weblogs.com/0137094/2006/10/26.html

http://radio.weblogs.com/0137094/2009/01/02.html

(Jason Bennett's Developer Corner)

Answer 7

I've had similar issues and eventually made a spreadsheet with some VBA code that queried and populated the spreadsheet for me. My task was to export a series of tables, each one on a different sheet, but any flag could be used to switch to a new sheet. Anyhow, let me know if you would like to see the code. Here is a chunk that might help you out. Just change the TableSQL string to whatever your select should be. Each record returned will be inserted as a row in the sheet. Then, based on whatever flag you decide, you can create and move to the next sheet. Please let me know if you need more information (as this particular example isn't EXACTLY what you are doing)

Private Sub getMyRows(inSchema As String, InTable As String)
    Dim RS As Object
    Dim TableSQL As String
    Dim DataType As String
    Dim DataLength As String
    Dim DataPrecision As String
    Dim DataScale As String
    Dim ColCount As Integer
    Dim WS As Worksheet
' create a sheet with the current table as name
    Worksheets.Add().Name = InTable
    Set RS = CreateObject("ADODB.recordset")
    TableSQL = "Select * from " & inSchema & "." & InTable
' grab the data
    RS.Open TableSQL, conn, adOpenStatic
    For ColCount = 0 To RS.Fields.Count - 1
' set column headings to match table
       ActiveSheet.Cells(1, ColCount + 1).Value = RS.Fields(ColCount).Name
    Next

' copy table data to sheet
     With Worksheets(InTable).Range("A2")
        .CopyFromRecordset RS
    End With
    RS.Close

  End Sub

Answer 8

Hi,

We use to OOXML methods. We were first writing our own method to do it in PL/SQL but a coworker found this product called Excellant. It it is you pass in a xml spec with column mappings and styles/formulas (almost any excel formula works), the query and it gives you a clob back. So you can then run gzip on the clob if you want to make it smaller. The product is pretty cheep my manager bought it with a pcard.

The web site is www.peak42solutions.com. We couldn't use ODBC since the network folks don't allow access directly to the database. And we are now emailing invoicing in excel to customers.

Thanks,

Bill

Answer 9

Hi you can refer the article that explains how to craete a custom package for exporting data into excel. You can use it as a base model to generate your own package

http://www.protalk.in/oracle/plsql-tips-package-for-exporting-data-to-excel/

Thanks Nitin