ExcelGen - An Oracle PL/SQL Generator for MS Excel Files

ExcelGen is a PL/SQL utility to create Excel files (.xlsx, .xlsb) out of SQL data sources (query strings or cursors), with automatic pagination over multiple sheets.
It supports encryption, cell merging, various formatting options through a built-in API or CSS, table layout, formulas and defined names, data validation, conditional formatting and images.

What's New in...

Version 4.4 : image support
Version 4.0 : defined names and formulas support
Version 3.0 : cell API, CSS styling support, multitable sheet, merged cells
Version 2.0 : support for XLSB format output
Version 1.0 : added encryption features
Version 0.1b : Beta version

(Change Log)

Bug tracker

Found a bug, have a question, or an enhancement request?
Please create an issue here.

Installation

Getting source code

Clone this repository or download it as a zip archive.

Note : ExcelCommons and MSUtilities dependencies are provided as submodules, so use the clone command with recurse-submodules option :
git clone --recurse-submodules https://github.com/mbleron/ExcelGen.git
or download them separately as zip archives and extract the content of root folders into ExcelCommons and MSUtilities folders respectively.

Database requirement

ExcelGen requires Oracle Database 11.2.0.1 and onwards.

PL/SQL

Using SQL*Plus, connect to the target database schema, then :

Install ExcelGen and dependencies using script install.sql.
If your schema does not have a grant to DBMS_CRYPTO, you can use script install_nocrypto.sql, but will not be able to call the setEncryption procedure.

Quick Start

Basic Excel export from a SQL query :

declare
  ctxId   ExcelGen.ctxHandle;
  sheet1  ExcelGen.sheetHandle;
begin
  ctxId := ExcelGen.createContext();  
  sheet1 := ExcelGen.addSheetFromQuery(ctxId, 'sheet1', 'select * from my_table');
  ExcelGen.setHeader(ctxId, sheet1, p_frozen => true);
  ExcelGen.createFile(ctxId, 'TEST_DIR', 'my_file.xlsx');
  ExcelGen.closeContext(ctxId);
end;
/

See the following sections for more examples and detailed description of ExcelGen features.

Operational Notes

ExcelGen Model

<img src="./resources/model.png"/> The workbook structure is maintained in a context object referenced through its handle during the generation process.

A workbook contains at least one sheet, and a stylesheet.
A sheet may contain:

zero or more tables
individual cells

A table is a contiguous rectangular set of cells (range) arranged in rows and columns, with an optional header row, and whose data comes from a SQL source (cursor or query string).

Provided there is only one table declared in a sheet, it is possible to partition the underlying data source across multiple sheets past the first one.
Similarly, a sheet containing a single table will benefit from a streaming generation model, resulting in a low memory footprint. Otherwise, if the sheet contains multiple tables, or a mix of tables and individual cells, all data will first be built up in memory, then written out to the sheet.

Various styling options are available at sheet, table or cell level, using the built-in API or CSS. Cell styles are defined globally in the context so that they can be referenced multiple times.

For simple requirements such as a single-table sheet, shortcut procedures and functions addSheetFromQuery, addSheetFromCursor and related table-agnostic subprograms are available to bypass table management.

ExcelGen Subprograms and Usage

Context and file management
Sheet management
Table management
Cell management
Formulas and Names
Data validation
- addDataValidationRule
- setTableColumnValidationRule
Conditional Formatting
Style management
Image management
- putImageCell
- addImage

createContext function

This function creates and returns a new generator handle.

function createContext (
  p_type  in pls_integer default FILE_XLSX 
)
return ctxHandle;

Parameter|Description|Mandatory ---|---|--- p_type|Output file format. One of FILE_XLSX, FILE_XLSB. Default is FILE_XLSX.|No

closeContext procedure

Releases a context handle previously opened by createContext function.

procedure closeContext (
  p_ctxId  in ctxHandle 
);

Parameter|Description|Mandatory ---|---|--- p_ctxId|Context handle.|Yes

addSheet function

Adds a new empty sheet and returns a sheetHandle value to be used with related subprograms.

function addSheet (
  p_ctxId       in ctxHandle
, p_sheetName   in varchar2
, p_tabColor    in varchar2 default null
, p_sheetIndex  in pls_integer default null
, p_state       in pls_integer default null
)
return sheetHandle;

Parameter|Description|Mandatory ---|---|--- p_ctxId|Context handle.|Yes p_sheetName|Sheet name.|Yes p_tabColor|Tab color of the new sheet.|No p_sheetIndex|Sheet tab index in the workbook. If omitted, the sheet is added at the end of the list, after the last existing index.|No p_state|Visibility state. One of ST_VISIBLE (default), ST_HIDDEN, ST_VERYHIDDEN.|No

Notes :
The list of sheet indices specified via p_sheetIndex may be sparse.
For example, if one adds sheet 'A' at index 2, sheet 'B' at index 4 and sheet 'C' at index 1, the resulting workbook will show sheets 'C', 'A' and 'B' in that order.

The ST_VERYHIDDEN

ExcelGen

Install / Use

README