Tutorials

Hello,

I often had the problem, that I had to process query results and provide them as excel files. Writing them on Unix systems. Usualy I wrote comma separated csv-files. But unfortunately when opening them excel begins to "guess" what datatype a specific value might be. So sometimes a number value 4.09 became a date 4-SEP etc.

Reading AskTom one day I found that you could set MARKUP in SQL*PLUS and excel accepts this as a valid format. Playing around a bit I found some useful features and wrote a package with HTML format as output.

Downside: the file can not be read by OpenOffice Calc. A friend gave me a hint about Microsoft XML format for spreadsheeets so I changed the output a little bit. Not every feature is available ib OpenOffice, but most are.

There is a bug in OpenOffice 2.3.1, it cannot open any XML file!

xml_spreadsheet

Features

New: Instead of directly writing into a file you now can choose to write into a LOB

  • Can be read by Open Office and MS Office XP/2002. To import into MS Office 2000 see http://support.microsoft.com/kb/923505
    • Limitation: after version 2.0 I dropped the support for OpenOffice, because despite trying to persuade it, it does not say why opening a file fails.
  • Easy to use (sample script in package header)
  • Column headers
  • Top captions spanning one or more column headers
  • Fix header and/or colums when scrolling
  • Predefined output formatting for
    • date with or without time
    • number as percent, 2 digits, integer, unformated, currency, accounting
    • string with markup for bold/underline
  • any self defined format including colors for text and background, font size and borders
  • Output from a given query or cursor. Begins new worksheet automatically after a give number of rows
  • Comments
  • Autofilter (does not work with OpenOffice)
  • Conditional formatting (does not work with OpenOffice). Set individual text color, background etc.
  • More than one worksheet
  • Print setup including margins and titles
  • ...

Tutorial

Look at submenue at right hand side

Download

Documentation

Disclaimer

The software is not intended for use in productive environments. If you do so be sure to test thoroughly because I will not give support in any case. I don't claim that this software is free of errors (it certainly is not).

Other resources

Perhaps owa_sylk from Tom Kyte will suit you better.

Jason Bennett wrote a similar program. The difference is mainly the ways to retrieve the document:

  • as a CLOB
  • through mod_plsql to a web browser
  • in a PL/SQL table via user defined type called ExcelDocumentLine

Anton Scheffer wrote as_xlsx. With this program you can generate real XLSX files.

oracle@matzberger.de For contacts of any kind including error reports. Impressum