CODEWITHSUNDEEP
htmloracle-databaseexcelvbscriptplsql
Przemek
Przemek

Reputation:

Oracle to Excel - PL/SQL export procedure

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.

Upvotes: 11

Views: 64713

Answers (11)

Migs
Migs

Reputation: 1480

Your problem seems similar to a thread here in SO (Writing in ExcelSheet using UTL_FILE package in Oracle.) and I was able to find a simple solution to this problem recently. i used a package called as_xlsx created by Anton Scheffer, (Create an Excel-file with PL/SQL) and did a few modifications so it creates multiple sheets inside a single Excel Workbook by putting it through a Loop. It also supports some basic formatting such as Cell Color, Font Style, Font Size, Borders, etc. Its pretty nifty. (Create an Excel File (.xlsx) using PL/SQL).

Hope this helps!

Upvotes: 0

user38123
user38123

Reputation: 669

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.

https://www.youtube.com/watch?v=Adz0zZFePf8

Upvotes: 4

vbence
vbence

Reputation: 20333

From: MSDN. If you want your worksheet to be portable and not to rely on System DSNs you can connect using a connection string like:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

cnn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=<PASSWORD>;Persist Security Info=True;User ID=<USER ID>;Data Source=<DATABASE NAME>"
cnn.Open

... then use Moleboy's solution.

Upvotes: 1

gWay
gWay

Reputation: 1245

I found this solution on the web (not invented by me), using SQL plus:

set feed off markup html on spool on
spool c:\table1.xls
select * from table1;
spool off
set markup html off spool off

Upvotes: 1

ora_excel
ora_excel

Reputation: 112

There is another project ORA_EXCEL (www.oraexcel.com) that can produce Excel documents from Oracle database using only PL/SQL.

Upvotes: 1

Bill Perry
Bill Perry

Reputation:

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

Upvotes: 0

moleboy
moleboy

Reputation: 864

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

Upvotes: 2

Jason Bennett
Jason Bennett

Reputation:

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)

Upvotes: 1

JonS
JonS

Reputation:

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

Upvotes: 0

Andrew G
Andrew G

Reputation: 2496

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

Upvotes: 2

Matthew Farwell
Matthew Farwell

Reputation: 61705

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

Upvotes: 0

Related Questions