Re: spool excel file with sql*plus

Hi,

You can use SQL*XL (Oraxcel is the old name) to automate things on the server. It fully supports VBA coding and macro recording. For example you can get it to a level where you load a spreadsheet into Excel which collects all the data and saves it in another spreadsheet. Easy to do.
The only thing left to tackle is the kick off a spreadsheet from within your PL/SQL. I believe there are some ways now to start external programs from within PL/SQL.

If you like to do it all from within Oracle you could have a look at the following:

If you wanted to create the .xls from within Oracle you may want to go the OLE route. Schematically your code would look something like this:

oExcel = CreateObject("Excel.Application") oWorkbook = oExcel.Workbooks.Add
oWorkSheet = oWorkbook.Worksheets(1)

With the Worksheet use the following technique to populate the data oWorkSheet.Range("A1").Value = 'abc'
or
oWorkSheet.Cells(1,1).Value = 'abc'

To save the file use

oWorkbook.Save

Finally for closing Excel you could use: oExcel.Exit

Have not seen anyone doing this yet but in theory I think it should be possible to do.

Kind regards, Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com Received on Wed May 22 2002 - 12:53:42 CEST