Skip to content
/ ExcelGen Public

ExcelGen is an Oracle PL/SQL utility to create MS Excel files (.xlsx, .xlsb)

License

MIT license
43 stars 10 forks Branches Tags Activity
Star
Notifications You must be signed in to change notification settings

mbleron/ExcelGen

BranchesTags

Repository files navigation

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, and data validation.

Content

What's New in...

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 :

  1. Install ExcelGen and dependencies using script install.sql.
  2. 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

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

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 state is only available programmatically. It indicates that the sheet is hidden and cannot be shown in the Excel UI (contrary to ST_HIDDEN).

addTable function

This function adds a new table to a given sheet, and returns a handle to be used in related subprograms.
The table may be based on a SQL query string (VARCHAR2 or CLOB) or a weakly-typed ref cursor.

function addTable (
  p_ctxId            in ctxHandle
, p_sheetId          in sheetHandle
, p_query            in varchar2
, p_paginate         in boolean default false
, p_pageSize         in pls_integer default null
, p_anchorRowOffset  in pls_integer default null
, p_anchorColOffset  in pls_integer default null
, p_anchorTableId    in tableHandle default null
, p_anchorPosition   in pls_integer default null
, p_maxRows          in integer default null
, p_excludeCols      in varchar2 default null
)
return tableHandle;
function addTable (
  p_ctxId            in ctxHandle
, p_sheetId          in sheetHandle
, p_query            in clob
, p_paginate         in boolean default false
, p_pageSize         in pls_integer default null
, p_anchorRowOffset  in pls_integer default null
, p_anchorColOffset  in pls_integer default null
, p_anchorTableId    in tableHandle default null
, p_anchorPosition   in pls_integer default null
, p_maxRows          in integer default null
, p_excludeCols      in varchar2 default null
)
return tableHandle;
function addTable (
  p_ctxId            in ctxHandle
, p_sheetId          in sheetHandle
, p_rc               in sys_refcursor
, p_paginate         in boolean default false
, p_pageSize         in pls_integer default null
, p_anchorRowOffset  in pls_integer default null
, p_anchorColOffset  in pls_integer default null
, p_anchorTableId    in tableHandle default null
, p_anchorPosition   in pls_integer default null
, p_maxRows          in integer default null
, p_excludeCols      in varchar2 default null
)
return tableHandle;
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_query SQL query string (VARCHAR2 or CLOB data type).
Bind variables (if any) can be set via setBindVariable procedure.		 Yes
p_rc Input ref cursor. Yes
p_paginate Enables pagination of the input data source over multiple sheets.
Use p_pageSize parameter to control the maximum number of rows per sheet.		 No
p_pageSize Maximum number of rows per sheet, when pagination is enabled.
If set to NULL, Excel sheet limit is used (1,048,576 rows).		 No
p_anchorRowOffset Row offset of the top-left cell of the table.
If p_anchorTableId is not NULL, this offset is relative to the table position specified by p_anchorPosition, otherwise it is an absolute offset in the sheet.		 No
p_anchorColOffset Column offset of the top-left cell of the table.
If p_anchorTableId is not NULL, this offset is relative to the table position specified by p_anchorPosition, otherwise it is an absolute offset in the sheet.		 No
p_anchorTableId Handle of the anchor table. No
p_anchorPosition Position in the anchor table from which row and column offsets are applied.
One of TOP_LEFT, TOP_RIGHT, BOTTOM_RIGHT, BOTTOM_LEFT.		 No
p_maxRows Maximum number of rows to fetch from the underlying query.
If set to NULL, no limit is applied except Excel sheet limit (unless pagination is enabled).		 No
p_excludeCols A comma-separated list of column indices and/or names to exclude from the SQL query result in the spreadsheet output.
Excluded column values are still accessible internally, to be referenced in an hyperlink expression.
Column names are case-sensitive and must be enclosed within quotation marks, e.g. '1, 2, "MY_COLUMN"'.		 No

Notes :
Allowed SQL column data types are : VARCHAR2, CHAR, NUMBER, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, CLOB, or ANYDATA, which must encapsulate one of the former scalar types.

Pagination of the query results is only possible when this is the only table of the sheet.

When pagination is enabled, three substitution variables may be used to generate unique names from the input sheet name pattern :

  • PNUM : current page number
  • PSTART : first row number of the current page
  • PSTOP : last row number of the current page

For example :
sheet${PNUM} will be expanded to sheet1, sheet2, sheet3, etc.
${PSTART}-${PSTOP} will be expanded to 1-1000, 1001-2000, 2001-3000, etc. assuming a page size of 1000 rows.

Table positioning may be absolute in the owning sheet, or relative to another table.
For the former method, row and column offsets correspond to row and column (1-based) indices respectively, and for the latter, offsets are 0-based from one the four anchor table corners:

relativePositioning

In this example, the absolute position (row, column) of Cell1 is (7, 5), while relative positions from Table1 corners are:

TOP_LEFT: (4, 3)
TOP_RIGHT: (4, 1)
BOTTOM_RIGHT: (2, 1)
BOTTOM_LEFT: (2, 3)

putCell procedure

Adds or overwrites a cell identified by its row and column indices, in a given sheet. Cell addressing may be absolute or relative to a given table object previously defined.

procedure putCell (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_rowIdx          in pls_integer
, p_colIdx          in pls_integer
, p_value           in anydata default null
, p_style           in cellStyleHandle default null 
, p_anchorTableId   in tableHandle default null
, p_anchorPosition  in pls_integer default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_rowIdx 1-based row index of the cell, if p_anchorTableId is NULL. Otherwise represents a 0-based row offset from the table anchor position. Yes
p_colIdx 1-based column index of the cell, if p_anchorTableId is NULL. Otherwise represents a 0-based column offset from the table anchor position. Yes
p_value Cell value, provided as an ANYDATA instance.
Supported encapsulated types are: NUMBER, VARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and CLOB.		 No
p_style Handle to a cell style created via makeCellStyle or makeCellStyleCss function. No
p_anchorTableId Handle of the anchor table. No
p_anchorPosition Position in the anchor table from which row and column offsets are applied.
One of TOP_LEFT, TOP_RIGHT, BOTTOM_RIGHT, BOTTOM_LEFT.		 No

Three convenience procedures are also provided to directly put a numeric, string or date value: putNumberCell, putStringCell and putDateCell.

putNumberCell procedure

Sets a numeric value in a given cell. See putCell procedure for a description of common parameters.

procedure putNumberCell (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_rowIdx          in pls_integer
, p_colIdx          in pls_integer
, p_value           in number
, p_style           in cellStyleHandle default null 
, p_anchorTableId   in tableHandle default null
, p_anchorPosition  in pls_integer default null
);

putStringCell procedure

Sets a string value in a given cell. See putCell procedure for a description of common parameters.

procedure putStringCell (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_rowIdx          in pls_integer
, p_colIdx          in pls_integer
, p_value           in varchar2
, p_style           in cellStyleHandle default null 
, p_anchorTableId   in tableHandle default null
, p_anchorPosition  in pls_integer default null
);

putDateCell procedure

Sets a date value in a given cell. See putCell procedure for a description of common parameters.

procedure putDateCell (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_rowIdx          in pls_integer
, p_colIdx          in pls_integer
, p_value           in date
, p_style           in cellStyleHandle default null 
, p_anchorTableId   in tableHandle default null
, p_anchorPosition  in pls_integer default null
);

putRichTextCell procedure

Sets a rich text content in a given cell. See putCell procedure for a description of common parameters.

procedure putRichTextCell (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_rowIdx          in pls_integer
, p_colIdx          in pls_integer
, p_value           in varchar2
, p_style           in cellStyleHandle default null 
, p_anchorTableId   in tableHandle default null
, p_anchorPosition  in pls_integer default null
);

The value passed to this procedure (p_value) must be a VARCHAR2 string containing a valid XHTML content.
See Style specifications/Rich Text for more information about the expected syntax and supported XHTML formatting elements.

putFormulaCell procedure

Sets a formula in a given cell.
See putCell procedure for a description of common parameters.

procedure putFormulaCell (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_rowIdx          in pls_integer
, p_colIdx          in pls_integer
, p_value           in varchar2
, p_style           in cellStyleHandle default null 
, p_anchorTableId   in tableHandle default null
, p_anchorPosition  in pls_integer default null
, p_refStyle        in pls_integer default null
);
Parameter Description Mandatory
p_value Formula string. Yes
p_refStyle Cell reference style used in the formula.
One of ExcelFmla.REF_A1 (default) or ExcelFmla.REF_R1C1.		 No

Example:

declare
  ...
  sheet1  ExcelGen.sheetHandle := ExcelGen.addSheet(ctx, 'sheet1');
begin  
  
  ExcelGen.putFormulaCell(ctx, sheet1, 1, 2, 'RC[-1]+1', p_refStyle => ExcelFmla.REF_R1C1);
  
  ExcelGen.putFormulaCell(ctx, sheet1, 2, 2, 'SUM($A$1:$A$10)');
  
  ... 
end;

putHyperlinkCell procedure

Sets an hyperlink in a given cell.
See putCell procedure for a description of common parameters.

procedure putHyperlinkCell (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_rowIdx          in pls_integer
, p_colIdx          in pls_integer
, p_location        in varchar2
, p_linkName        in varchar2 default null
, p_style           in cellStyleHandle default null 
, p_anchorTableId   in tableHandle default null
, p_anchorPosition  in pls_integer default null
);
Parameter Description Mandatory
p_location Cf. addTableHyperlinkColumn. Yes
p_linkName Cf. addTableHyperlinkColumn. No

addDataValidationRule procedure

Adds a data validation rule to a collection of ranges.

procedure addDataValidationRule (
  p_ctxId             in ctxHandle
, p_sheetId           in sheetHandle
, p_type              in varchar2
, p_cellRange         in ExcelTypes.ST_Sqref
, p_value1            in varchar2
, p_value2            in varchar2 default null
, p_operator          in varchar2 default null
, p_allowBlank        in boolean default true
, p_showDropDown      in boolean default null
, p_showErrorMessage  in boolean default null
, p_errorMsg          in varchar2 default null
, p_errorTitle        in varchar2 default null
, p_errorStyle        in varchar2 default null
, p_showInputMessage  in boolean default null
, p_promptMsg         in varchar2 default null
, p_promptTitle       in varchar2 default null
, p_refStyle1         in pls_integer default null
, p_refStyle2         in pls_integer default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_type Type of validation.
One of 'whole', 'decimal', 'list', 'date', 'time', 'textLength', 'custom'.		 Yes
p_cellRange A sequence of ranges to which this validation rule applies, as an ExcelTypes.ST_Sqref collection.
Each range represents a rectangular area or a single cell. Helper functions makeCellRef and makeCellRange may be used to create a range expression out of individual row and column indices.
Following Excel conventions, the top-left cell of the last range in the sequence will be used as a point of origin to resolve relative references occurring in this rule's formulas.		 Yes
p_value1 A formula string representing the first operand of the operator, or the Source/Formula for List/Custom types. Yes
p_value2 A formula string representing the second operand of the operator, when applicable. No
p_operator Relational operator, when applicable.
One of 'between', 'notBetween', 'equal', 'notEqual', 'greaterThan', 'greaterThanOrEqual', 'lessThan', 'lessThanOrEqual'.		 No
p_allowBlank "Ignore blank" flag. No
p_showDropDown "In-cell dropdown" flag. No
p_showErrorMessage "Show error alert" flag. No
p_errorMsg Error message. No
p_errorTitle Error title. No
p_errorStyle Error alert style.
One of 'stop' (default), 'warning', 'information'.		 No
p_showInputMessage "Show input message" flag. No
p_promptMsg Input message. No
p_promptTitle Input title. No
p_refStyle1 Cell reference style of the first formula (p_value1).
One of ExcelFmla.REF_A1 (default) or ExcelFmla.REF_R1C1.		 No
p_refStyle2 Cell reference style of the second formula (p_value2). No

setTableColumnValidationRule procedure

Adds a data validation rule to a given table column.

procedure setTableColumnValidationRule (
  p_ctxId             in ctxHandle
, p_sheetId           in sheetHandle
, p_tableId           in tableHandle
, p_columnId          in pls_integer
, p_type              in varchar2
, p_value1            in varchar2
, p_value2            in varchar2 default null
, p_operator          in varchar2 default null
, p_allowBlank        in boolean default true
, p_showDropDown      in boolean default null
, p_showErrorMessage  in boolean default null
, p_errorMsg          in varchar2 default null
, p_errorTitle        in varchar2 default null
, p_errorStyle        in varchar2 default null
, p_showInputMessage  in boolean default null
, p_promptMsg         in varchar2 default null
, p_promptTitle       in varchar2 default null
, p_refStyle1         in pls_integer default null
, p_refStyle2         in pls_integer default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_columnId Column id (1-based index). Yes
p_type Cf. addDataValidationRule. Yes
p_cellRange Cf. addDataValidationRule. Yes
p_value1 Cf. addDataValidationRule. Yes
p_value2 Cf. addDataValidationRule. No
p_operator Cf. addDataValidationRule. No
p_allowBlank Cf. addDataValidationRule. No
p_showDropDown Cf. addDataValidationRule. No
p_showErrorMessage Cf. addDataValidationRule. No
p_errorMsg Cf. addDataValidationRule. No
p_errorTitle Cf. addDataValidationRule. No
p_errorStyle Cf. addDataValidationRule. No
p_showInputMessage Cf. addDataValidationRule. No
p_promptMsg Cf. addDataValidationRule. No
p_promptTitle Cf. addDataValidationRule. No
p_refStyle1 Cf. addDataValidationRule. No
p_refStyle2 Cf. addDataValidationRule. No

addSheetFromQuery procedure and function

Adds a new sheet based on a SQL query string (VARCHAR2 or CLOB), with optional pagination.
Available both as a procedure and a function.
The function returns a sheetHandle value to be used with related subprograms.

procedure addSheetFromQuery (
  p_ctxId       in ctxHandle
, p_sheetName   in varchar2
, p_query       in varchar2
, p_tabColor    in varchar2 default null
, p_paginate    in boolean default false
, p_pageSize    in pls_integer default null
, p_sheetIndex  in pls_integer default null
, p_maxRows     in integer default null
, p_state       in pls_integer default null
, p_excludeCols in varchar2 default null
);
procedure addSheetFromQuery (
  p_ctxId       in ctxHandle
, p_sheetName   in varchar2
, p_query       in clob
, p_tabColor    in varchar2 default null
, p_paginate    in boolean default false
, p_pageSize    in pls_integer default null
, p_sheetIndex  in pls_integer default null
, p_maxRows     in integer default null
, p_state       in pls_integer default null
, p_excludeCols in varchar2 default null
);
function addSheetFromQuery (
  p_ctxId       in ctxHandle
, p_sheetName   in varchar2
, p_query       in varchar2
, p_tabColor    in varchar2 default null
, p_paginate    in boolean default false
, p_pageSize    in pls_integer default null
, p_sheetIndex  in pls_integer default null
, p_maxRows     in integer default null
, p_state       in pls_integer default null
, p_excludeCols in varchar2 default null
)
return sheetHandle;
function addSheetFromQuery (
  p_ctxId       in ctxHandle
, p_sheetName   in varchar2
, p_query       in clob
, p_tabColor    in varchar2 default null
, p_paginate    in boolean default false
, p_pageSize    in pls_integer default null
, p_sheetIndex  in pls_integer default null
, p_maxRows     in integer default null
, p_state       in pls_integer default null
, p_excludeCols in varchar2 default null
)
return sheetHandle;
Parameter Description Mandatory
p_ctxId Cf. addSheet. Yes
p_sheetName Cf. addSheet. Yes
p_query Cf. addTable. Yes
p_tabColor Cf. addSheet. No
p_paginate Cf. addTable. No
p_pageSize Cf. addTable. No
p_sheetIndex Cf. addSheet. No
p_maxRows Cf. addTable. No
p_state Cf. addSheet. No
p_excludeCols Cf. addTable. No

addSheetFromCursor procedure and function

Adds a new sheet based on a weakly-typed ref cursor, with optional pagination.
Available both as a procedure and a function.
The function returns a sheetHandle value to be used with related subprograms.

procedure addSheetFromCursor (
  p_ctxId       in ctxHandle
, p_sheetName   in varchar2
, p_rc          in sys_refcursor
, p_tabColor    in varchar2 default null
, p_paginate    in boolean default false
, p_pageSize    in pls_integer default null
, p_sheetIndex  in pls_integer default null
, p_maxRows     in integer default null
, p_state       in pls_integer default null
, p_excludeCols in varchar2 default null
);
function addSheetFromCursor (
  p_ctxId       in ctxHandle
, p_sheetName   in varchar2
, p_rc          in sys_refcursor
, p_tabColor    in varchar2 default null
, p_paginate    in boolean default false
, p_pageSize    in pls_integer default null
, p_sheetIndex  in pls_integer default null
, p_maxRows     in integer default null
, p_state       in pls_integer default null
, p_excludeCols in varchar2 default null
)
return sheetHandle;
Parameter Description Mandatory
p_ctxId Cf. addSheet. Yes
p_sheetName Cf. addSheet. Yes
p_rc Cf. addTable. Yes
p_tabColor Cf. addSheet. No
p_paginate Cf. addTable. No
p_pageSize Cf. addTable. No
p_sheetIndex Cf. addSheet. No
p_maxRows Cf. addTable. No
p_state Cf. addSheet. No
p_excludeCols Cf. addTable. No

setBindVariable procedure

This procedure binds a value to a variable from the SQL query associated with a table.
It is overloaded in two ways:

  • By variable data type: NUMBER, VARCHAR2 or DATE
  • By referencing either a table, or only a sheet handle. In the latter case, the first table of the sheet will be used.

⚠️ Legacy overloads expecting a sheet name are deprecated and will be removed in a future version.

procedure setBindVariable (
  p_ctxId      in ctxHandle
, p_sheetId    in sheetHandle
, p_tableId    in tableHandle
, p_bindName   in varchar2
, p_bindValue  in number
);
procedure setBindVariable (
  p_ctxId      in ctxHandle
, p_sheetId    in sheetHandle
, p_tableId    in tableHandle
, p_bindName   in varchar2
, p_bindValue  in varchar2
);
procedure setBindVariable (
  p_ctxId      in ctxHandle
, p_sheetId    in sheetHandle
, p_tableId    in tableHandle
, p_bindName   in varchar2
, p_bindValue  in date
);
procedure setBindVariable (
  p_ctxId       in ctxHandle
, p_sheetId     in sheetHandle
, p_bindName    in varchar2
, p_bindValue   in number
);
procedure setBindVariable (
  p_ctxId      in ctxHandle
, p_sheetId    in sheetHandle
, p_bindName   in varchar2
, p_bindValue  in varchar2
);
procedure setBindVariable (
  p_ctxId      in ctxHandle
, p_sheetId    in sheetHandle
, p_bindName   in varchar2
, p_bindValue  in date
);

Deprecated:

procedure setBindVariable (
  p_ctxId      in ctxHandle
, p_sheetName  in varchar2
, p_bindName   in varchar2
, p_bindValue  in number
);
procedure setBindVariable (
  p_ctxId      in ctxHandle
, p_sheetName  in varchar2
, p_bindName   in varchar2
, p_bindValue  in varchar2
);
procedure setBindVariable (
  p_ctxId      in ctxHandle
, p_sheetName  in varchar2
, p_bindName   in varchar2
, p_bindValue  in date
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetName Sheet name. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_bindName Bind variable name. Yes
p_bindValue Bind variable value. Yes

setHeader procedure

This procedure adds a header row to the first table of a given sheet. It should be used only when the sheet contains a single table.
By default, column names are derived from the SQL source query, but they can be customized individually using setColumnFormat procedure.

procedure setHeader (
  p_ctxId       in ctxHandle
, p_sheetName   in varchar2
, p_style       in cellStyleHandle default null
, p_frozen      in boolean default false
, p_autoFilter  in boolean default false
);
procedure setHeader (
  p_ctxId       in ctxHandle
, p_sheetId     in sheetHandle
, p_style       in cellStyleHandle default null
, p_frozen      in boolean default false
, p_autoFilter  in boolean default false
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetName Sheet name. Yes
p_sheetId Sheet handle. Yes
p_style Handle to a cell style created via makeCellStyle or makeCellStyleCss function. No
p_frozen Set this parameter to true in order to freeze the header row. No
p_autoFilter Set this parameter to true in order to add an automatic filter to this sheet. No

setTableFormat procedure

This procedure applies a table layout to the first table of a given sheet. It should be used only when the sheet contains a single table.

procedure setTableFormat (
  p_ctxId      in ctxHandle
, p_sheetId    in sheetHandle
, p_style      in varchar2 default null
);

Deprecated:

procedure setTableFormat (
  p_ctxId      in ctxHandle
, p_sheetName  in varchar2
, p_style      in varchar2 default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetName Sheet name. Yes
p_sheetId Sheet handle. Yes
p_styleName Name of a predefined Excel table style to apply.
See Predefined table styles for a list of available styles.		 No

setSheetProperties procedure

Sets sheet-level properties.

procedure setSheetProperties (
  p_ctxId                in ctxHandle
, p_sheetId              in sheetHandle
, p_activePaneAnchorRef  in varchar2 default null
, p_showGridLines        in boolean default null
, p_showRowColHeaders    in boolean default null
, p_defaultRowHeight     in number default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_activePaneAnchorRef For frozen panes, reference of the top-left cell of the bottom-right pane.
For example, passing 'A2' will freeze the first row of the sheet.		 No
p_showGridLines Hide or show grid lines on the sheet. Default is true (show). No
p_showRowColHeaders Hide or show row and column headers on the sheet. Default is true (show). No
p_defaultRowHeight Default row height on this sheet, in points. No

setTableProperties procedure

Sets table-level properties.

procedure setTableProperties (
  p_ctxId              in ctxHandle
, p_sheetId            in sheetHandle
, p_tableId            in tableHandle
, p_style              in varchar2 default null
, p_showFirstColumn    in boolean default false
, p_showLastColumn     in boolean default false
, p_showRowStripes     in boolean default true
, p_showColumnStripes  in boolean default false
, p_tableName          in varchar2 default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_style Name of a predefined Excel table style to apply.
See Predefined table styles for a list of available styles.		 No
p_showFirstColumn Highlight first column of the table. Default is false. No
p_showLastColumn Highlight last column of the table. Default is false. No
p_showRowStripes Hide or show row stripes. Default is true (show). No
p_showColumnStripes Hide or show column stripes. Default is false (hide). No
p_tableName Table name.
Must be unique among all tables and other names defined in the workbook. If NULL, a system-generated name will be assigned to this table at creation time.		 No

setTableHeader procedure

This procedure adds a header row to a given table, with optional cell style and auto-filtering.
By default, column names are derived from the SQL source query, but they can be customized individually using setTableColumnProperties procedure.

procedure setTableHeader (
  p_ctxId       in ctxHandle
, p_sheetId     in sheetHandle
, p_tableId     in tableHandle
, p_style       in cellStyleHandle default null
, p_autoFilter  in boolean default false
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_style Cell style handle created via makeCellStyle or makeCellStyleCss function. No
p_autoFilter If set to true, adds an automatic filter to each column. No

setTableColumnProperties procedure

This procedure sets table-level column properties: column name, cell style and header style. If a table header is displayed, a column name set this way overrides the SQL name from the source query.

procedure setTableColumnProperties (
  p_ctxId        in ctxHandle
, p_sheetId      in sheetHandle
, p_tableId      in pls_integer
, p_columnId     in pls_integer
, p_columnName   in varchar2 default null
, p_style        in cellStyleHandle default null
, p_headerStyle  in cellStyleHandle default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_columnId Column index, referring to its position in the list of visible table columns.
This value might differ from the original index in the SQL source if other columns have been excluded or added afterwards.		 Yes
p_columnName Column name. No
p_style Cell style handle created via makeCellStyle or makeCellStyleCss function. No
p_headerStyle Header cell style handle created via makeCellStyle or makeCellStyleCss function.
This style inherits from the table header style defined via setTableHeader procedure, in priority to higher-level settings (sheet column, sheet or workbook).		 No

setTableColumnFormat procedure

This procedure sets table-level column number/date format.

procedure setTableColumnFormat (
  p_ctxId     in ctxHandle
, p_sheetId   in sheetHandle
, p_tableId   in pls_integer
, p_columnId  in pls_integer
, p_format    in varchar2
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_columnId Column index. See setTableColumnProperties. Yes
p_format Format string.
It takes precedence over existing settings for NUMBER, DATE or TIMESTAMP data types at workbook, sheet and sheet column level.
The format must follow MS Excel proprietary syntax.		 Yes

setTableRowProperties procedure

This procedure sets table-level row properties: cell style.

procedure setTableRowProperties (
  p_ctxId    in ctxHandle
, p_sheetId  in sheetHandle
, p_tableId  in pls_integer
, p_rowId    in pls_integer
, p_style    in cellStyleHandle
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_rowId Local row index, relative to the beginning of the table. Yes
p_style Cell style handle created via makeCellStyle or makeCellStyleCss function. Yes

addTableColumn procedure

This procedure adds a new calculated (formula-based) column to a given table.
The column is added at the end of the existing column list, see addTableColumnBefore and addTableColumnAfter procedures to insert the new column at a specific position.

procedure addTableColumn (
  p_ctxId     in ctxHandle
, p_sheetId   in sheetHandle
, p_tableId   in pls_integer
, p_name      in varchar2
, p_value     in varchar2
, p_refStyle  in pls_integer default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_name Column name. Yes
p_value Formula string. Yes
p_refStyle Cell reference style.
Cf. putFormulaCell procedure.		 No

addTableColumnBefore procedure

This procedure adds a new calculated column to a given table, before a given column.

procedure addTableColumnBefore (
  p_ctxId     in ctxHandle
, p_sheetId   in sheetHandle
, p_tableId   in pls_integer
, p_name      in varchar2
, p_value     in varchar2
, p_columnId  in pls_integer
, p_refStyle  in pls_integer default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_name Column name. Yes
p_value Formula string. Yes
p_columnId Column index before which to insert the new column. The index may refer to an excluded column (see addTable). Yes
p_refStyle Cell reference style. No

addTableColumnAfter procedure

This procedure adds a new calculated column to a given table, after a given column.

procedure addTableColumnAfter (
  p_ctxId     in ctxHandle
, p_sheetId   in sheetHandle
, p_tableId   in pls_integer
, p_name      in varchar2
, p_value     in varchar2
, p_columnId  in pls_integer
, p_refStyle  in pls_integer default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_name Column name. Yes
p_value Formula string. Yes
p_columnId Column index after which to insert the new column. The index may refer to an excluded column (see addTable). Yes
p_refStyle Cell reference style. No

addTableHyperlinkColumn procedure

This procedure adds a new hyperlink column to a given table.
The column is added at the end of the existing column list

procedure addTableHyperlinkColumn (
  p_ctxId     in ctxHandle
, p_sheetId   in sheetHandle
, p_tableId   in tableHandle
, p_name      in varchar2
, p_location  in varchar2
, p_linkName  in varchar2 default null
)
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_name Column name. Yes
p_location Hyperlink target. May be parameterized (see below). Yes
p_linkName Friendly name. May be parameterized (see below). No

Internally, ExcelGen creates a new formula-based column using HYPERLINK function.
In a table context, we may want the link target or link name to depend on values from other columns in the same row. To achieve that easily, the p_location and p_linkName parameters allow placeholders for other columns. A placeholder may reference an excluded column.
Example:
'https://www.google.com/search?q=${MY_COLUMN_1}'

addTableHyperlinkColumnBefore procedure

This procedure adds a new hyperlink column to a given table, before a given column.

procedure addTableHyperlinkColumnBefore (
  p_ctxId      in ctxHandle
, p_sheetId    in sheetHandle
, p_tableId    in tableHandle
, p_name       in varchar2
, p_columnId   in pls_integer
, p_location   in varchar2
, p_linkName   in varchar2 default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_name Column name. Yes
p_columnId See addTableColumnBefore. Yes
p_location See addTableHyperlinkColumn. Yes
p_linkName See addTableHyperlinkColumn. No

addTableHyperlinkColumnAfter procedure

This procedure adds a new hyperlink column to a given table, after a given column.

procedure addTableHyperlinkColumnAfter (
  p_ctxId      in ctxHandle
, p_sheetId    in sheetHandle
, p_tableId    in tableHandle
, p_name       in varchar2
, p_columnId   in pls_integer
, p_location   in varchar2
, p_linkName   in varchar2 default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle. Yes
p_name Column name. Yes
p_columnId See addTableColumnAfter. Yes
p_location See addTableHyperlinkColumn. Yes
p_linkName See addTableHyperlinkColumn. No

setDateFormat procedure

This procedure sets the format applied to DATE values in the resulting spreadsheet file.
It is overloaded to operate either globally in the workbook or at a given sheet level, which takes precedence.
Current limitation: the format won't apply to date values resulting from formulas.

The format must follow MS Excel proprietary syntax.
Default is dd/mm/yyyy hh:mm:ss.

procedure setDateFormat (
  p_ctxId   in ctxHandle
, p_format  in varchar2
);
procedure setDateFormat (
  p_ctxId   in ctxHandle
, p_sheetId in sheetHandle
, p_format  in varchar2
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_format Date format string. Yes

setTimestampFormat procedure

This procedure sets the format applied to TIMESTAMP values in the resulting spreadsheet file.
It is overloaded to operate either globally in the workbook or at a given sheet level, which takes precedence.
Current limitation: the format won't apply to timestamp values resulting from formulas.

Default is dd/mm/yyyy hh:mm:ss.000.

procedure setTimestampFormat (
  p_ctxId   in ctxHandle
, p_format  in varchar2
);
procedure setTimestampFormat (
  p_ctxId   in ctxHandle
, p_sheetId in sheetHandle
, p_format  in varchar2
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_format Timestamp format string. Yes

setNumFormat procedure

This procedure sets the format applied to NUMBER values in the resulting spreadsheet file.
It is overloaded to operate either globally in the workbook or at a given sheet level, which takes precedence.
Current limitation: the format won't apply to numeric values resulting from formulas.

The format must follow MS Excel proprietary syntax.
Default is NULL, meaning the General cell format will apply.

procedure setNumFormat (
  p_ctxId   in ctxHandle
, p_format  in varchar2
);
procedure setNumFormat (
  p_ctxId   in ctxHandle
, p_sheetId in sheetHandle
, p_format  in varchar2
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_format Number format string. Yes

setRowProperties procedure

This procedure sets custom sheet-level row properties for : style, row height.

procedure setRowProperties (
  p_ctxId    in ctxHandle
, p_sheetId  in sheetHandle
, p_rowId    in pls_integer
, p_style    in cellStyleHandle default null
, p_height   in number default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_rowId Row index. Yes
p_style Cell style handle created via makeCellStyle or makeCellStyleCss function. No
p_height Row height in points. No

setColumnProperties procedure

This procedure sets custom sheet-level column properties for : style, column header, column width.

The column header parameter is used to set a custom column name when the sheet is composed of a single table. If there's more than one table defined, it applies to the first table found.

procedure setColumnProperties (
  p_ctxId     in ctxHandle
, p_sheetId   in sheetHandle
, p_columnId  in pls_integer
, p_style     in cellStyleHandle default null
, p_header    in varchar2 default null
, p_width     in number default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_columnId Column id (1-based index). Yes
p_style Handle to a cell style created via makeCellStyle or makeCellStyleCss function. No
p_header Column header. Takes precedence over the column name from the source SQL query when the sheet is composed of a single table. No
p_width Column width.
From ECMA-376 Standard Part 1 (§ 18.3.1.13): measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, …, 9 as rendered in the normal style's font (currently "Calibri 11pt").		 No

setColumnFormat procedure

This procedure sets custom column-level properties: number/date format, column header, column width.

It provides the same functionalities as setColumnProperties except that it only sets the number/date format part of the cell style.

procedure setColumnFormat (
  p_ctxId     in ctxHandle
, p_sheetId   in sheetHandle
, p_columnId  in pls_integer
, p_format    in varchar2 default null
, p_header    in varchar2 default null
, p_width     in number default null
);
Parameter Description Mandatory
p_ctxId Cf. setColumnProperties. Yes
p_sheetId Cf. setColumnProperties. Yes
p_columnId Cf. setColumnProperties. Yes
p_format Format string.
It takes precedence over existing workbook or sheet-level settings for NUMBER, DATE or TIMESTAMP data types.
The format must follow MS Excel proprietary syntax.		 No
p_header Cf. setColumnProperties. No
p_width Cf. setColumnProperties. No

setDefaultStyle procedure

This procedure sets the default cell style at workbook or sheet level.
See Style inheritance for more information about style propagation at lower levels.

procedure setDefaultStyle (
  p_ctxId    in ctxHandle
, p_style    in cellStyleHandle
);
procedure setDefaultStyle (
  p_ctxId    in ctxHandle
, p_sheetId  in sheetHandle
, p_style    in cellStyleHandle
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_style Handle to a cell style created via makeCellStyle or makeCellStyleCss function. Yes

mergeCells procedure

This procedure declares a range of cells to be merged in the resulting sheet.
The range may be defined by a string expression (overload 1), or by its position and span across rows and columns (overload 2). The latter allows relative positioning from a previously defined table object.

In Excel, the style of the resulting cell is that of the top-left cell of the range, except for borders which are usually not rendered correctly. That's why an optional p_style parameter is provided to apply a given style to the range, as if an additional call to setRangeStyle were made with p_outsideBorders forced to true.

procedure mergeCells (
  p_ctxId    in ctxHandle
, p_sheetId  in sheetHandle
, p_range    in varchar2
, p_style    in cellStyleHandle default null
);
procedure mergeCells (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_rowOffset       in pls_integer
, p_colOffset       in pls_integer
, p_rowSpan         in pls_integer
, p_colSpan         in pls_integer
, p_anchorTableId   in tableHandle default null
, p_anchorPosition  in pls_integer default null
, p_style           in cellStyleHandle default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_range Range of cells to merge.
For example: 'C2:E5'.		 Yes
p_rowOffset 1-based row index of the top-left cell of the range, if p_anchorTableId is NULL. Otherwise represents a 0-based row offset from the table anchor position. Yes
p_colOffset 1-based column index of the top-left cell of the range, if p_anchorTableId is NULL. Otherwise represents a 0-based column offset from the table anchor position. Yes
p_rowSpan Number of rows this range spans. Yes
p_colSpan Number of columns this range spans. Yes
p_anchorTableId Handle of the anchor table. No
p_anchorPosition Position in the anchor table from which row and column offsets are applied.
One of TOP_LEFT, TOP_RIGHT, BOTTOM_RIGHT, BOTTOM_LEFT.		 No
p_style Handle to a cell style created via makeCellStyle or makeCellStyleCss function. No

setRangeStyle procedure

This procedure sets the style applied to a given range of cells.
Just like mergeCells procedure, the range may be defined by a string expression (overload 1), or by its position and span across rows and columns (overload 2). The latter allows relative positioning from a previously defined table object.

procedure setRangeStyle (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_range           in varchar2
, p_style           in cellStyleHandle
, p_outsideBorders  in boolean default false
);
procedure setRangeStyle (
  p_ctxId           in ctxHandle
, p_sheetId         in sheetHandle
, p_rowOffset       in pls_integer
, p_colOffset       in pls_integer
, p_rowSpan         in pls_integer
, p_colSpan         in pls_integer
, p_anchorTableId   in tableHandle default null
, p_anchorPosition  in pls_integer default null
, p_style           in cellStyleHandle
, p_outsideBorders  in boolean default false
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_range Range of cells.
For example: 'C2:E5'.		 Yes
p_rowOffset 1-based row index of the top-left cell of the range, if p_anchorTableId is NULL. Otherwise represents a 0-based row offset from the table anchor position. Yes
p_colOffset 1-based column index of the top-left cell of the range, if p_anchorTableId is NULL. Otherwise represents a 0-based column offset from the table anchor position. Yes
p_rowSpan Number of rows this range spans. Yes
p_colSpan Number of columns this range spans. Yes
p_anchorTableId Handle of the anchor table. No
p_anchorPosition Position in the anchor table from which row and column offsets are applied.
One of TOP_LEFT, TOP_RIGHT, BOTTOM_RIGHT, BOTTOM_LEFT.		 No
p_style Handle to a cell style created via makeCellStyle or makeCellStyleCss function. Yes
p_outsideBorders Whether to apply this style's border component to the outside of the range only. Default is false.
See example below.		 No

Example

  ExcelGen.setRangeStyle(
    p_ctxId   => ctx
  , p_sheetId => sheet1
  , p_range   => 'B2:C3'
  , p_style   => ExcelGen.makeCellStyleCss(ctx, 'background:yellow;border:medium solid red')
  );

range-style-1

With outside borders only:

  ExcelGen.setRangeStyle(
    p_ctxId          => ctx
  , p_sheetId        => sheet1
  , p_range          => 'B2:C3'
  , p_style          => ExcelGen.makeCellStyleCss(ctx, 'background:yellow;border:medium solid red')
  , p_outsideBorders => true
  );

range-style-2

makeCellRef function

This function creates a cell reference string from given column and row indices (1-based).
For example, makeCellRef(54,8) will return 'BB8'.

function makeCellRef (
  p_colIdx  in pls_integer
, p_rowIdx  in pls_integer
)
return varchar2;
Parameter Description Mandatory
p_colIdx Column index. Yes
p_rowIdx Row index. Yes

makeCellRange function

This function creates a range string expression out of given column and row indices (1-based).
If last row and column indices are not specified, then a single cell reference is returned.

function makeCellRange (
  p_startRowIdx  in pls_integer
, p_startColIdx  in pls_integer
, p_endRowIdx    in pls_integer default null
, p_endColIdx    in pls_integer default null
)
return ExcelTypes.ST_Ref;
Parameter Description Mandatory
p_startRowIdx First row index. Yes
p_startColIdx First column index. Yes
p_endRowIdx Last row index. Yes
p_endColIdx Last column index. Yes

setEncryption procedure

This procedure sets the password used to encrypt the document, along with the minimum compatible Office version necessary to open it.

procedure setEncryption (
  p_ctxId       in ctxHandle
, p_password    in varchar2
, p_compatible  in pls_integer default OFFICE2007SP2
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_password Password. Yes
p_compatible Minimum compatible Office version for encryption.
One of OFFICE2007SP1, OFFICE2007SP2, OFFICE2010, OFFICE2013, OFFICE2016. Default is OFFICE2007SP2.		 No

setCoreProperties procedure

This procedure sets various file properties (metadata) as specified by the Dublin Core Metadata Initiative (DCMI).

procedure setCoreProperties (
  p_ctxId        in ctxHandle
, p_creator      in varchar2 default null
, p_description  in varchar2 default null
, p_subject      in varchar2 default null
, p_title        in varchar2 default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_creator Creator property.
If NULL, defaults to the product name as returned by getProductName function.		 No
p_description Description property. No
p_subject Subject property. No
p_title Title property. No

getProductName function

This function returns a string containing the current ExcelGen identifier and version number, e.g. EXCELGEN-3.6.0.

function getProductName
return varchar2;

getFileContent function

This function builds the spreadsheet file and returns it as a temporary BLOB.

function getFileContent (
  p_ctxId  in ctxHandle
)
return blob;

createFile procedure

This procedure builds the spreadsheet file and write it directly to a directory.

procedure createFile (
  p_ctxId      in ctxHandle
, p_directory  in varchar2
, p_filename   in varchar2
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_directory Target directory.
Must be a valid Oracle directory name.		 Yes
p_filename File name. Yes

getRowCount function

This function returns the total number of rows fetched for a given table.
The table may be partitioned over multiple sheets.
This function must be called after getFileContent or createFile.

function getRowCount (
  p_ctxId    in ctxHandle
, p_sheetId  in sheetHandle 
, p_tableId  in tableHandle default null
) 
return pls_integer;
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_sheetId Sheet handle. Yes
p_tableId Table handle.
If NULL or omitted, the first table of the sheet is used.		 No

STYLING

makeRgbColor function

This function builds an RGBA color value in hexadecimal notation from individual Red, Green and Blue components supplied as unsigned 8-bit integers (0-255), and optional Alpha component as a floating-point number between 0 and 1.
For example:
makeRgbColor(219,112,147) will return #DB7093.
makeRgbColor(0,255,0,.2) will return #00FF0033.

function makeRgbColor (
  r  in uint8
, g  in uint8
, b  in uint8
, a  in number default null
)
return varchar2;
Parameter Description Mandatory
r Red component value. Yes
g Green component value. Yes
b Blue component value. Yes
a Alpha component value.
See Color specification for details on how ExcelGen interprets this value.		 No

makeBorderPr function

This function builds an instance of a cell border edge, from a border style name and a color.

function makeBorderPr (
  p_style  in varchar2 default null
, p_color  in varchar2 default null
)
return CT_BorderPr;
Parameter Description Mandatory
p_style Border style name.
See Border styles for a list of available styles.		 No
p_color Border color. No

makeBorder function

This function builds an instance of a cell border (all edges).
It is overloaded to accept either individual edge formatting (left, right, top and bottom), or the same format for all edges.

Overload 1 :

function makeBorder (
  p_left    in CT_BorderPr default makeBorderPr()
, p_right   in CT_BorderPr default makeBorderPr()
, p_top     in CT_BorderPr default makeBorderPr()
, p_bottom  in CT_BorderPr default makeBorderPr()
)
return CT_Border;

Overload 2 :

function makeBorder (
  p_style  in varchar2
, p_color  in varchar2 default null
)
return CT_Border;

Overload 2 is a shorthand for :

makeBorder(
  p_left    => makeBorderPr(p_style, p_color)
, p_right   => makeBorderPr(p_style, p_color)
, p_top     => makeBorderPr(p_style, p_color)
, p_bottom  => makeBorderPr(p_style, p_color)
)
Parameter Description Mandatory
p_left Left edge format, as returned by makeBorderPr function. No
p_right Right edge format, as returned by makeBorderPr function. No
p_top Top edge format, as returned by makeBorderPr function. No
p_bottom Bottom edge format, as returned by makeBorderPr function. No
p_style Border style name. Yes
p_color Border color. No

makeFont function

This function builds an instance of a cell font.

function makeFont (
  p_name       in varchar2 default null
, p_sz         in pls_integer default null
, p_b          in boolean default false
, p_i          in boolean default false
, p_color      in varchar2 default null
, p_u          in varchar2 default null
, p_vertAlign  in varchar2 default null
, p_strike     in boolean default false
)
return CT_Font;
Parameter Description Mandatory
p_name Font name. Yes
p_sz Font size, in points. Yes
p_b Bold font style (true|false). No
p_i Italic font style (true|false). No
p_color Font color. No
p_u Underline style.
One of none, single, double, singleAccounting, doubleAccounting. Default is none.		 No
p_vertAlign Font vertical alignment: superscript or subscript.
One of superscript, subscript, or baseline (default).		 No
p_strike Font strikethrough effect (true|false). No

makePatternFill function

This function builds an instance of a cell pattern fill.

function makePatternFill (
  p_patternType  in varchar2
, p_fgColor      in varchar2 default null
, p_bgColor      in varchar2 default null
)
return CT_Fill;
Parameter Description Mandatory
p_patternType Pattern type.
See Pattern types for a list of available types.		 Yes
p_fgColor Foreground color of the pattern. No
p_bgColor Background color of the pattern. No

Note:
For a solid fill (no pattern), the color must be specified using the foreground color parameter.

makeGradientFill function

This function builds an instance of a cell linear gradient fill.

function makeGradientFill (
  p_degree  in number default null
, p_stops   in CT_GradientStopList default null
)
return CT_Fill;
Parameter Description Mandatory
p_degree Angle of the linear gradient in degree.
Default is 0, which represents a left-to-right gradient. Increasing the angle rotates the gradient line clockwise.
NB: Excel presumably calculates the gradient in a square box before scaling it to the cell dimensions. So, for example, a gradient rotated 45° right will have its midpoint matching the diagonal of the cell, regardless of its dimensions:
gradient-45		 No
p_stops List of color-stop points as a collection of CT_GradientStop instances.
If the list is NULL, color-stops may be added later via addGradientStop procedure.		 No

makeGradientStop function

This function builds an instance of a color-stop point to be used in a linear gradient fill definition.

function makeGradientStop (
  p_position  in number
, p_color     in varchar2
)
return CT_GradientStop;
Parameter Description Mandatory
p_position Position of this color-stop point on the gradient line.
It must be a number between 0 and 1, where 0 represents the starting point and 1 the ending point of the line.		 Yes
p_color Color of this stop point. Yes

addGradientStop procedure

This procedure adds a new color-stop point to an existing linear gradient fill.

procedure addGradientStop (
  p_fill      in out nocopy CT_Fill
, p_position  in number
, p_color     in varchar2
);
Parameter Description Mandatory
p_fill Gradient fill instance. Yes
p_position Cf. makeGradientStop. Yes
p_color Cf. makeGradientStop. Yes

makeAlignment function

This function builds an instance of a cell alignment.

function makeAlignment (
  p_horizontal    in varchar2 default null
, p_vertical      in varchar2 default null
, p_wrapText      in boolean default false
, p_textRotation  in number default null
, p_verticalText  in boolean default false
, p_indent        in number default null
)
return CT_CellAlignment;
Parameter Description Mandatory
p_horizontal Horizontal alignment type, one of left, center or right. No
p_vertical Vertical alignment type, one of top, center or bottom. No
p_wrapText Cell text wrapping. Default is false. No
p_textRotation Text rotation angle in degree, between -90° and 90°.
Angle zero is the default horizontal text layout, negative values represent clockwise rotation.		 No
p_verticalText Vertical text layout (true|false).
The text is stacked vertically instead of horizontally (the default).		 No
p_indent Text indentation level in the cell, specified as an unsigned integer between 0 and 250, where one unit of indentation corresponds to 3 space characters in the default font.
Indentation has an effect only when horizontal alignment is set to left, right or distributed.		 No

Note:
p_textRotation is not compatible with p_verticalText. If both are specified, p_textRotation takes precedence.

makeCellStyle function

This function builds an instance of a cell style, composed of optional number format, font, fill and border specifications, and returns a handle to it.

function makeCellStyle (
  p_ctxId       in ctxHandle
, p_numFmtCode  in varchar2 default null
, p_font        in CT_Font default null
, p_fill        in CT_Fill default null
, p_border      in CT_Border default null
, p_alignment   in CT_CellAlignment default null
)
return cellStyleHandle;
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_numFmtCode Number format code. No
p_font Font style instance, as returned by makeFont function. No
p_fill Fill style instance, as returned by makePatternFill function. No
p_border Border style instance, as returned by makeBorder function. No
p_alignment Cell alignment instance, as returned by makeAlignment function. No

Example:

This sample code creates a cell style composed of the following facets :

  • Font : Calibri, 11 pts, bold face
  • Fill : YellowGreen solid fill
  • Border : Thick red edges
  • Alignment : Horizontally centered
declare

  ctxId      ExcelGen.ctxHandle;
  cellStyle  ExcelGen.cellStyleHandle;

begin
  ...
 
  cellStyle := ExcelGen.makeCellStyle(
                 p_ctxId     => ctxId
               , p_font      => ExcelGen.makeFont('Calibri',11,true)
               , p_fill      => ExcelGen.makePatternFill('solid','YellowGreen')
               , p_border    => ExcelGen.makeBorder('thick','red')
               , p_alignment => ExcelGen.makeAlignment(horizontal => 'center')
               );
  ...

makeCellStyleCss function

This function builds an instance of a cell style, from a CSS style string, and returns a handle to it.
See Style specifications/CSS for all supported features.

function makeCellStyleCss (
  p_ctxId  in ctxHandle
, p_css    in varchar2
)
return cellStyleHandle;
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_css CSS string. Yes

Example:

declare

  ctxId      ExcelGen.ctxHandle;
  cellStyle  ExcelGen.cellStyleHandle;
  
begin
  ...
 
  cellStyle := ExcelGen.makeCellStyleCss(
                 p_ctxId => ctxId
               , p_css   => 'font:bold 11pt Calibri;
                             background:YellowGreen;
                             border:thick solid red;
                             text-align:center'
               );
  ...

putDefinedName procedure

This procedure defines a workbook or sheet-level defined name.

procedure putDefinedName (
  p_ctxId     in ctxHandle
, p_name      in varchar2
, p_value     in varchar2
, p_scope     in sheetHandle default null
, p_comment   in varchar2 default null
, p_cellRef   in varchar2 default null
, p_refStyle  in pls_integer default null
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_name Name of this defined name. Yes
p_value Reference (formula string) for the name. Yes
p_scope Scope of this name, either a sheetHandle for a sheet-level name, or NULL (the default) for global workbook level. No
p_comment Comment for this name. No
p_cellRef Optional point of origin (cell reference) to resolve offsets when the name uses relative cell references. Default is A1. See example below. No
p_refStyle Cell reference style used in the formula.
One of ExcelFmla.REF_A1 (default) or ExcelFmla.REF_R1C1.		 No

Examples:

declare
  ...
  sheet1  ExcelGen.sheetHandle := ExcelGen.addSheet(ctx, 'sheet1');
  sheet2  ExcelGen.sheetHandle := ExcelGen.addSheet(ctx, 'sheet2');
begin  
  
  ExcelGen.putDefinedName(ctx, 'TEST1', 'sheet2!$A$1', p_comment => 'This is a workbook-level name');
  ExcelGen.putDefinedName(ctx, 'TEST2', 'sheet1!$A$1', sheet1);
  ExcelGen.putDefinedName(ctx, 'TEST3', 'sheet1!$A$1', sheet2);
  ExcelGen.putDefinedName(ctx, 'TEST3', 'SUM(sheet1!$A$2:$B$6)');
  ExcelGen.putDefinedName(ctx, 'TEST4', 'CSC(1)');
  ExcelGen.putDefinedName(ctx, 'TEST5', 'TEST4 + 1');
  ExcelGen.putDefinedName(ctx, 'TEST6', 'sheet2:sheet1!$A$1');
  ExcelGen.putDefinedName(ctx, 'TEST7', 'INDIRECT("$A$1")');
  ExcelGen.putDefinedName(ctx, 'TEST8', 'sheet1!C3', sheet1, p_cellRef => 'B2');
  
  ... 
end;

In the example above, TEST8 refers to the relative cell reference C3. Internally, Excel saves it as row and column offsets, so that the actual reference changes depending on the cell the name is used. That's why ExcelGen needs a point of origin to compute those offsets in the first place.
Here, if B2 is used as origin, C3 defines an offset of one row down and one column to the right.

setCellReferenceStyle procedure

This procedure sets the cell reference style used to display formula expressions in the Excel UI, which is A1-style by default.
This setting does not control the default reference style used to parse formula via putDefinedName, addTableColumn or putFormulaCell procedures.

procedure putDefinedName (
  p_ctxId     in ctxHandle
, p_refStyle  in pls_integer
);
Parameter Description Mandatory
p_ctxId Context handle. Yes
p_refStyle Cell reference style, one of ExcelFmla.REF_A1 or ExcelFmla.REF_R1C1. No

Style specifications

CSS

As of ExcelGen 3, it is possible to declare cell styles using CSS syntax, in addition to the built-in styling API.
Standard CSS properties and values relevant for cell styling are supported, e.g. border, font, background, color etc. along with MS Office extensions.

Examples

cssStyle1
CSS declaration:

font:14pt "Arial Black";
color:red;
border:double #5B9BD5;
text-align:center;
vertical-align:middle;

Equivalent Style API declaration via makeCellStyle :

p_font => ExcelGen.makeFont(p_name => 'Arial Black', p_sz => 14, p_color => 'red'), 
p_border => ExcelGen.makeBorder(p_style => 'double', p_color => '#5B9BD5'), 
p_alignment => ExcelGen.makeAlignment(p_horizontal => 'center', p_vertical => 'center')

cssStyle2
CSS declaration:

font-style:italic;text-decoration:underline;background:orange

Equivalent Style API declaration via makeCellStyle :

p_font => ExcelGen.makeFont(p_i => true, p_u => 'single'), 
p_fill => ExcelGen.makePatternFill('solid', 'orange')

cssStyle3
CSS declaration:

font-weight:bold;border-top:medium dashed;border-bottom:medium dashed;vertical-align:top

Equivalent Style API declaration via makeCellStyle :

p_font => ExcelGen.makeFont(p_b => true), 
p_border => ExcelGen.makeBorder(p_top => ExcelGen.makeBorderPr('mediumDashed')
                              , p_bottom => ExcelGen.makeBorderPr('mediumDashed')), 
p_alignment => ExcelGen.makeAlignment(p_vertical => 'top')

Supported CSS properties

Hereafter is the exhaustive list of supported properties and values, and implementation-specific features.
Since there is no exact match between MS Office styling elements and CSS properties, mapping tables are also provided further down in the document.

Default values are underlined, and MSO extensions are italicized.

Name Description Standard
border border shorthand property.
Not all combinations of individually supported border-style and border-width values actually match an Excel border style. See Border CSS Mapping for details.		 ✔️
border-style border-style shorthand property.
Supported values: none, solid, dashed, dotted, double, hairline, dot-dash, dot-dot-dash, dot-dash-slanted.		 ✔️
border-width border-width shorthand property.
Supported values: thin, medium, thick.		 ✔️
border-color border-color shorthand property.
Supported values: see Color specification.		 ✔️
border-left border-left shorthand property. ✔️
border-right border-right shorthand property. ✔️
border-top border-top shorthand property. ✔️
border-bottom border-bottom shorthand property. ✔️
border-left-style border-left-style property. ✔️
border-left-width border-left-width property. ✔️
border-left-color border-left-color property. ✔️
border-right-style border-right-style property. ✔️
border-right-width border-right-width property. ✔️
border-right-color border-right-color property. ✔️
border-top-style border-top-style property. ✔️
border-top-width border-top-width property. ✔️
border-top-color border-top-color property. ✔️
border-bottom-style border-bottom-style property. ✔️
border-bottom-width border-bottom-width property. ✔️
border-bottom-color border-bottom-color property. ✔️
font font shorthand property.
ExcelGen only supports font-family, font-size, font-style and font-weight components.		 ✔️
font-family Font family name. ✔️
font-size Font size.
ExcelGen only supports value in point unit, e.g. 11pt.		 ✔️
font-style Font style.
Supported values: normal, italic.		 ✔️
font-weight Font weight.
Supported values: normal, bold.		 ✔️
text-decoration text-decoration shorthand property.
ExcelGen only supports text-decoration-line and text-decoration-style components.		 ✔️
text-decoration-line Kind of text decoration.
Supported values: none, or at least one of underline, line-through.
The value line-through maps to Excel font effect 'Strikethrough'.		 ✔️
text-decoration-style Style of the text decoration line.
Supported values: solid, double, single-accounting, double-accounting.
This style only applies in conjunction with underline decoration line value.		 ✔️
vertical-align vertical-align property.
Supported values: top, middle, bottom, justify, distributed, baseline, sub, super.
Last two values sub and super map to Excel font effects 'Subscript' and 'Superscript' respectively.		 ✔️
text-align text-align property.
Supported values: left, center, right, justify, fill, center-across, distributed.		 ✔️
white-space white-space property.
Supported values: pre, pre-wrap.
Default value is pre, which maps to the default "no wrap" mode for a cell.		 ✔️
color color property, sets the text and text decoration color.
Supported values: see Color specification.		 ✔️
background background shorthand property.
ExcelGen only supports a single background-color or background-image component (see below).		 ✔️
background-color background-color property, sets the background color of the cell.
When the pattern type is set to none (the default), specifying a color via this property defines a solid fill.
Supported values: see Color specification.		 ✔️
background-image background-image property.
ExcelGen only supports a single gradient value specified via linear-gradient() function.		 ✔️
rotate rotate property.
Excel only supports a single angle value in deg, turn or rad unit, in the range [-90°, 90°].
Following CSS convention, a positive angle value rotates clockwise.		 ✔️
text-orientation text-orientation property.
The only supported value is upright, which maps to "Vertical Text" font property in Excel.
Contrary to CSS specs, and for the sake of simplicity, this property does not require an explicit vertical writing-mode property set alongside it.		 ✔️
mso-char-indent-count Text indentation level, in the range [0, 250].
See p_indent parameter of makeAlignment function for more details.		 ❌
mso-pattern Fill pattern type.
Supported values: none, gray-50, gray-75, gray-25, horz-stripe, vert-stripe, reverse-dark-down, diag-stripe, diag-cross, thick-diag-cross, thin-horz-stripe, thin-vert-stripe, thin-reverse-diag-stripe, thin-diag-stripe, thin-horz-cross, thin-diag-cross, gray-125, gray-0625.

See Pattern CSS Mapping for details.		 ❌
mso-number-format Number/Date format string.
e.g. "0.00", "yyyy-mm-dd"		 ❌

Color specification

Color values expected in style-related subprograms may be specified using one of the following conventions :

  • Hexadecimal notation, with an optional alpha channel (i.e. transparency), prefixed with a hash sign: #RRGGBB[AA], e.g. #7FFFD4.
    Function makeRgbColor can be used to build such a color code from individual RGBA components.

  • Named color from the CSS Color 4 specification, e.g. Aquamarine. The name is case-insensitive.

  • CSS only: rgb() or rgba() functions. Both syntaxes rgb(R, G, B[, A]) and rgb(R G B[ / A]) are supported.

Note: Although the ECMA-376 standard seems to support it, the alpha channel is actually ignored when Excel renders the color on screen. There's also no way to specify the alpha component via Excel UI.
However, ExcelGen supports it somehow by blending colors having an alpha component with a white background, thus generating a new opaque color visually equivalent to the transparent color.

For example, the following code snippet

  for i in 0 .. 10 loop
    ExcelGen.putCell(ctx, sheet1, 1, i+1, p_style => ExcelGen.makeCellStyleCss(ctx, 'background-color:#FF0000'||to_char(i*25.5,'FM0X')));
  end loop;

will generate:
Shades of red

Gradient

ExcelGen supports a linear gradient cell background specified via the built-in API makeGradientFill, or CSS background/background-image properties.
Internally, an Excel gradient fill is composed of a sequence of color stops, and an angle of rotation for the gradient line. Each stop is defined by its position along the gradient line, from 0 to 1 inclusive, and a color. Between two adjacent stops, the color is determined by linear interpolation, the 50% color mix being half-way.

ExcelGen implements the linear-gradient() CSS function with the following differences from the standard :

  • Transition hint:
    When linear-gradient defines a color transition hint, the transition to and from the 50% mix is not linear but exponential. Since Excel does not support that natively, ExcelGen uses a linear transition instead.

  • Gradient angle:
    First, please bear in mind that Excel and CSS have different conventions regarding angle zero. In Excel, it corresponds to a left-to-right orientation while CSS defines its zero as a bottom-to-top orientation. And to confuse things a bit more, the optional angle parameter in linear-gradient() CSS function does not default to 0deg but corresponds to a top-to-bottom orientation, i.e. 180deg.

    CSS Orientation CSS angle* Excel angle
    to top 0deg / 0turn -90
    to top right 45deg / 0.125turn -45
    to right 90deg / 0.25turn 0
    to bottom right 135deg / 0.375turn 45
    to bottom 180deg / 0.5turn 90
    to bottom left 225deg / 0.625turn 135
    to left 270deg / 0.75turn 180
    to top left 315deg / 0.875turn 225

    (*) when rendered in a square box

    Somehow, Excel renders a gradient fill as if the cell were a square box, then scales it to the actual cell dimensions. Therefore, there is a difference in the way the angle is interpreted in Excel vs. CSS.
    If the gradient orientation is specified as a "side-or-corner" value in CSS, it will be rendered identically in Excel, as per the above table.
    However, a 45deg CSS angle in an arbitrary rectangular box is not the same as the corresponding Excel angle (-45° in this case):

    css-gradient-45deg excel-gradient-45deg
    standard CSS Excel

  • Color stop positions:
    Color stop and transition hint positions outside the range [0% - 100%] are not supported.

Examples

background-image: linear-gradient(to top, #dbdcd7 0%, #dddcd7 24%, #e2c9cc 30%, #e7627d 46%, #b8235a 59%, #801357 71%, #3d1635 84%, #1c1a27 100%)

css-gradient-example-1
 

background: linear-gradient(89.2deg, rgb(0, 0, 0) 10.4%, rgb(255, 0, 0) 37.1%, rgb(255, 216, 51) 64.3%, rgb(255, 255, 255) 90.5%)

css-gradient-example-2  

background: 
    linear-gradient(109.6deg, 
        rgb(33, 25, 180) 11.2%, 
        rgb(253, 29, 29) 55.2%, 
        rgb(252, 176, 69) 91.1%
    )

css-gradient-example-3  

Rich Text

ExcelGen supports Rich Text for cell content. That allows the user to style sequences of characters (aka "text runs") individually, instead of formatting the cell text as a whole.
A Rich Text content must be specified as a string representing a valid XHTML fragment. Here are the supported formatting elements:

Tag Description
<b></b> Puts text in bold
<i></i> Puts text in italic
<s></s> Strikethrough
<u></u> Underlines text with a single line
<span style=""></span> Applies CSS provided in the style attribute
<sub></sub> Puts text in subscript
<sup></sup> Puts text in superscript
<br/> Puts a line break

If the cell already has a font style defined, text runs will inherit from it.

Examples

  • Rich Text with cell style inheritance
    In this first example, the Rich Text feature is used to apply a different color to each character, while the font size and weight is set at cell level:
style1 := ExcelGen.makeCellStyleCss(ctx, 'font-size:20pt;font-weight:bold');

ExcelGen.putRichTextCell(
  p_ctxId => ctx
, p_sheetId => sheet1
, p_rowIdx => 1
, p_colIdx => 1
, p_value => 
   '<span style="color:#FF0000">R</span>
    <span style="color:#FFFF00">A</span>
    <span style="color:#00FF00">I</span>
    <span style="color:#00FFFF">N</span>
    <span style="color:#0000FF">B</span>
    <span style="color:#FF00FF">O</span>
    <span style="color:#FF0000">W</span>'
, p_style => style1
);

rich-text-01

  • Inline formatting with nested styling elements
ExcelGen.putRichTextCell(
  p_ctxId   => ctx
, p_sheetId => sheet1
, p_rowIdx  => 2
, p_colIdx  => 1
, p_value   => 
  'The chemical formula of glucose is <span style="color:blue;font-weight:bold">'||regexp_replace('C6H12O6','(\d+)','<sub>\1</sub>')||'</span>'
);

rich-text-02

Predefined table styles

Light
TableStyleLight

Medium
TableStyleMedium

Dark
TableStyleDark

Border styles

BorderStyles

Border CSS Mapping

border-width →
↓ border-style		 thin medium thick
none none none none
solid thin medium thick
dashed dashed mediumDashed mediumDashed
dotted dotted dotted dotted
double double double double
hairline hair hair hair
dot-dash dashDot mediumDashDot mediumDashDot
dot-dot-dash dashDotDot mediumDashDotDot mediumDashDotDot
dot-dash-slanted slantDashDot slantDashDot slantDashDot

Alignments

Horizontal alignment

Alignment type CSS text-align value
left left
center center
right right
fill fill
justify justify
centerContinuous center-across
distributed distributed

Vertical alignment

Alignment type CSS vertical-align value
top top
center middle
bottom bottom
justify justify
distributed distributed

CSS values sub and super are also supported and map to Excel font attributes 'Subscript' and 'Superscript' respectively.

Pattern types

PatternTypes

Pattern CSS Mapping

Pattern type mso-pattern value
none -
solid none
mediumGray gray-50
darkGray gray-75
lightGray gray-25
darkHorizontal horz-stripe
darkVertical vert-stripe
darkDown reverse-dark-down
darkUp diag-stripe
darkGrid diag-cross
darkTrellis thick-diag-cross
lightHorizontal thin-horz-stripe
lightVertical thin-vert-stripe
lightDown thin-reverse-diag-stripe
lightUp thin-diag-stripe
lightGrid thin-horz-cross
lightTrellis thin-diag-cross
gray125 gray-125
gray0625 gray-0625

Style inheritance

Cell styles may be defined at different levels according to the below diagram.
When a given style component is not set at a specific level, it will automatically inherit its value from the containing element, in cascade.
style-inheritance-diagram

Caveats:

  • A style component defined at workbook or sheet level is "virtual", i.e. it will only apply when a lower-level and explicitly defined style inherits from it.
    For example, setting a cell background at sheet level does not automatically set that background to all cells of the sheet, but only to those cells whose styles inherit from the sheet-level style, either directly or through its containing (table) row or (table) column.
  • Column-level settings, even inherited, take precedence over row-level's. For instance, the following piece of code sets the horizontal alignment for column #2, the background color for row #2, and a default background color at sheet level: 
    ExcelGen.setColumnProperties(ctx, sheet1, 2, ExcelGen.makeCellStyleCss(ctx, 'text-align:center'));
ExcelGen.setRowProperties(ctx, sheet1, 2, ExcelGen.makeCellStyleCss(ctx, 'background-color:lightgreen'));
ExcelGen.setDefaultStyle(ctx, sheet1, ExcelGen.makeCellStyleCss(ctx, 'background-color:violet'));
ExcelGen.putStringCell(ctx, sheet1, 2, 2, 'X');
    Since the style declared for column #2 does not set the background color component, it inherits the sheet-level value, and since column-level value takes precedence, cell at B2 will have a violet background:
    style-inheritance

Formula Support

ExcelGen supports the following formula-related features for both XLSX and XLSB formats:

What is not supported (yet):

Formulas must be entered in English locale, specifically:

  • Decimal separator = . (dot)
  • Argument separator, union operator, array row-item separator = , (comma)
  • Array row separator = ; (semicolon)
  • English function names

Supported Excel functions

The list of supported functions is available here.

Examples

Single query to sheet mapping, with header formatting

employees.sql → employees.xlsx

declare

  ctxId      ExcelGen.ctxHandle;
  sheetId    ExcelGen.sheetHandle;
  sqlQuery   varchar2(32767) := 'select * from hr.employees';
  
begin
  
  ctxId := ExcelGen.createContext();  
  sheetId := ExcelGen.addSheetFromQuery(ctxId, 'sheet1', sqlQuery);
      
  ExcelGen.setHeader(
    ctxId
  , sheetId
  , p_style => ExcelGen.makeCellStyle(
                 p_ctxId => ctxId
               , p_font  => ExcelGen.makeFont('Calibri',11,true)
               , p_fill  => ExcelGen.makePatternFill('solid','LightGray')
               )
  , p_frozen     => true
  , p_autoFilter => true
  );
  
  ExcelGen.setDateFormat(ctxId, 'dd/mm/yyyy');
  
  ExcelGen.createFile(ctxId, 'TEST_DIR', 'employees.xlsx');
  ExcelGen.closeContext(ctxId);
  
end;
/

Multiple queries, with table layout

dept-emp.sql → dept-emp.xlsx

declare

  ctxId   ExcelGen.ctxHandle;
  sheet1  ExcelGen.sheetHandle;
  sheet2  ExcelGen.sheetHandle;
  
begin
  
  ctxId := ExcelGen.createContext();
  
  -- add dept sheet
  sheet1 := ExcelGen.addSheetFromQuery(ctxId, 'dept', 'select * from hr.departments');
  ExcelGen.setHeader(ctxId, sheet1, p_autoFilter => true);
  ExcelGen.setTableFormat(ctxId, sheet1, 'TableStyleLight2');

  -- add emp sheet
  sheet2 := ExcelGen.addSheetFromQuery(ctxId, 'emp', 'select * from hr.employees where salary >= :1 order by salary desc');
  ExcelGen.setBindVariable(ctxId, sheet2, '1', 7000);  
  ExcelGen.setHeader(ctxId, sheet2, p_autoFilter => true);
  ExcelGen.setTableFormat(ctxId, sheet2, 'TableStyleLight7');
  
  ExcelGen.setDateFormat(ctxId, 'dd/mm/yyyy');
  
  ExcelGen.createFile(ctxId, 'TEST_DIR', 'dept-emp.xlsx');
  ExcelGen.closeContext(ctxId);
  
end;
/

Ref cursor paginated over multiple sheets

all-objects.sql → all-objects.xlsx

declare
  
  ctxId      ExcelGen.ctxHandle;
  sheetId    ExcelGen.sheetHandle;
  rc         sys_refcursor;
  
begin
  
  open rc for 
  select * from all_objects where owner = 'SYS';

  ctxId := ExcelGen.createContext();
  
  sheetId := ExcelGen.addSheetFromCursor(
    p_ctxId     => ctxId
  , p_sheetName => 'sheet${PNUM}'
  , p_rc        => rc
  , p_tabColor  => 'DeepPink'
  , p_paginate  => true
  , p_pageSize  => 10000
  );
    
  ExcelGen.setHeader(
    ctxId
  , sheetId
  , p_style  => ExcelGen.makeCellStyle(ctxId, p_fill => ExcelGen.makePatternFill('solid','LightGray'))
  , p_frozen => true
  );
  
  ExcelGen.createFile(ctxId, 'TEST_DIR', 'all-objects.xlsx');
  ExcelGen.closeContext(ctxId);
  
end;
/

Multisheet with pagination, another example

multisheet-paginated.sql → multisheet-paginated.xlsx

declare

  ctxId    ExcelGen.ctxHandle;
  sheet1   ExcelGen.sheetHandle;
  sheet2   ExcelGen.sheetHandle;
  sheet3   ExcelGen.sheetHandle;
  
begin
  
  ctxId := ExcelGen.createContext();
  
  -- adding a new sheet in position 3
  sheet1 := ExcelGen.addSheetFromQuery(ctxId, 'c', 'select * from hr.employees where department_id = :1', p_sheetIndex => 3);
  ExcelGen.setBindVariable(ctxId, sheet1, '1', 30);
  ExcelGen.setTableFormat(ctxId, sheet1, 'TableStyleLight1');
  ExcelGen.setHeader(ctxId, sheet1, p_autoFilter => true, p_frozen => true);
  
  -- adding a new sheet in last position (4)
  sheet2 := ExcelGen.addSheetFromQuery(ctxId, 'b', 'select * from hr.employees');
  ExcelGen.setTableFormat(ctxId, sheet2, 'TableStyleLight2');
  ExcelGen.setHeader(ctxId, sheet2, p_autoFilter => true, p_frozen => true);
  
  -- adding a new sheet in position 1, with a 10-row pagination
  sheet3 := ExcelGen.addSheetFromQuery(ctxId, 'a${PNUM}', 'select * from hr.employees', p_paginate => true, p_pageSize => 10, p_sheetIndex => 1);
  ExcelGen.setHeader(ctxId, sheet3, p_autoFilter => true, p_frozen => true);

  ExcelGen.createFile(ctxId, 'TEST_DIR', 'multisheet-paginated.xlsx');
  ExcelGen.closeContext(ctxId);
  
end;
/

Creating an encrypted XLSB file

encrypted.sql → encrypted.xlsb

declare
  ctxId    ExcelGen.ctxHandle;
  sheetId  ExcelGen.sheetHandle;
begin
  ctxId := ExcelGen.createContext(ExcelGen.FILE_XLSB);
  sheetId := ExcelGen.addSheet(ctxId, 'data', p_tabColor => 'red');
  ExcelGen.putStringCell(ctxId, sheetId, 1, 1, 'Some sensitive information');
  ExcelGen.setEncryption(ctxId, 'Pass123', ExcelGen.OFFICE2016);
  ExcelGen.createFile(ctxId, 'TEST_DIR', 'encrypted.xlsb');
  ExcelGen.closeContext(ctxId);
end;
/

Setting sheet or column-level cell formats

number-formats.sql → number-formats.xlsx

declare
  ctxId    ExcelGen.ctxHandle;
  sheet1   ExcelGen.sheetHandle;
  sheet2   ExcelGen.sheetHandle;
  rc       sys_refcursor;
begin
  
  ctxId := ExcelGen.createContext(ExcelGen.FILE_XLSX);
  
  open rc for
  select sysdate D1
       , sysdate D2
       , 1 N1
       , 1.26 N2
       , systimestamp T1
       , systimestamp T2
  from dual ;
  
  sheet1 := ExcelGen.addSheetFromCursor(ctxId, 'a', rc);
  ExcelGen.setHeader(ctxId, sheet1);
  
  -- column #1 format
  ExcelGen.setColumnFormat(ctxId, sheet1, 1, 'dd/mm/yyyy');
  
  -- column #4 format
  ExcelGen.setColumnFormat(ctxId, sheet1, 4, '0.0');
  
  -- column #5 format
  ExcelGen.setColumnFormat(ctxId, sheet1, 5, 'dd/mm/yyyy hh:mm:ss');
  
  -- default sheet-level date format
  ExcelGen.setDateFormat(ctxId, sheet1, 'yyyy-mm');
  
  -- default sheet-level number format
  ExcelGen.setNumFormat(ctxId, sheet1, '0.00');
  
  -- default sheet-level timestamp format
  ExcelGen.setTimestampFormat(ctxId, sheet1, 'hh:mm:ss.000');
  
  -- another sheet with default wookbook-level formats
  sheet2 := ExcelGen.addSheetFromQuery(ctxId, 'b', 'select sysdate D1, 12345 N1, systimestamp T1 from dual');

  ExcelGen.createFile(ctxId, 'TEST_DIR', 'number-formats.xlsx');
  ExcelGen.closeContext(ctxId);
  
end;
/

Cell merging

merged-cells.sql → merged-cells.xlsx

declare

  ctx         ExcelGen.ctxHandle;
  sheet1      ExcelGen.sheetHandle;
  rowIdx      pls_integer := 1;
  colIdx      pls_integer := 1;
  alignment1  ExcelTypes.CT_CellAlignment := ExcelGen.makeAlignment(p_horizontal => 'center', p_vertical => 'center');
  
begin

  ctx := ExcelGen.createContext(ExcelGen.FILE_XLSX);
  sheet1 := ExcelGen.addSheet(ctx, 'sheet1');

  ExcelGen.putCell(ctx, sheet1, 1, 1, anydata.ConvertVarchar2('TEST'), p_style => ExcelGen.makeCellStyle(ctx, p_alignment => alignment1)
  );
  
  ExcelGen.putNumberCell(ctx, sheet1, 2, 1, 1, ExcelGen.makeCellStyle(ctx, p_fill => ExcelGen.makePatternFill('solid','chartreuse'), p_alignment => alignment1));
  ExcelGen.putNumberCell(ctx, sheet1, 2, 3, 2, ExcelGen.makeCellStyle(ctx, p_fill => ExcelGen.makePatternFill('solid','gold'), p_alignment => alignment1));
  ExcelGen.putNumberCell(ctx, sheet1, 4, 1, 3, ExcelGen.makeCellStyle(ctx, p_fill => ExcelGen.makePatternFill('solid','hotpink'), p_alignment => alignment1));
  ExcelGen.putNumberCell(ctx, sheet1, 4, 3, 4, ExcelGen.makeCellStyle(ctx, p_fill => ExcelGen.makePatternFill('solid','deepskyblue'), p_alignment => alignment1));
  
  ExcelGen.putStringCell(ctx, sheet1, 3, 2, 'ABC');
  
  ExcelGen.mergeCells(ctx, sheet1, 'A1:D1');
  ExcelGen.mergeCells(ctx, sheet1, 'A2:B3');
  ExcelGen.mergeCells(ctx, sheet1, 'C2:D3');
  ExcelGen.mergeCells(ctx, sheet1, 'A4:B5');
  ExcelGen.mergeCells(ctx, sheet1, 'C4:D5');
  
  ExcelGen.setRowProperties(ctx, sheet1, 1, p_height => 30);

  ExcelGen.createFile(ctx, 'TEST_DIR', 'merged-cells.xlsx');
  ExcelGen.closeContext(ctx);

end;
/

Relative positioning

relative-positioning.sql → relative-positioning.xlsx

Master-details

Another example of relative positioning.
master-details.sql → master-details.xlsx

Variant column data type

Handling of an ANYDATA source column.
anydata-column.sql → anydata-column.xlsx

Color spectrum demo

Creates a rainbow-like matrix made of 36,360 cells of different colors.
color-spectrum.sql → color-spectrum-thumb

Weave pattern demo

Creates a weave pattern using cell background color and gradient pattern.
weave-pattern.sql → weave-pattern-thumb

Style showcase

Shows available cell styling options.
style-showcase.sql → style-showcase.xlsx

Formulas and Names

test-formula.xlsx

declare

  ctx     ExcelGen.ctxHandle := ExcelGen.createContext(ExcelGen.FILE_XLSX);
  sheet1  ExcelGen.sheetHandle := ExcelGen.addSheet(ctx, 'sheet1');
  table1  ExcelGen.tableHandle := ExcelGen.addTable(ctx, sheet1, 'select level as "col1" from dual connect by level <= 5');
  
begin
  ExcelGen.setTableHeader(ctx, sheet1, table1, p_style => ExcelGen.makeCellStyleCss(ctx, 'font-weight:bold;color:blue'));
  
  -- adding a defined name
  ExcelGen.putDefinedName(ctx, 'TEST1', 'SUM(RC[-1]:R[1]C[-1])', p_scope => sheet1, p_refStyle => ExcelFmla.REF_R1C1);
  
  -- adding a calculated column to table1
  ExcelGen.addTableColumn(ctx, sheet1, table1, 'col2', '2*A2+1');
  
  -- adding another calculated column, based on the defined name
  ExcelGen.addTableColumn(ctx, sheet1, table1, 'col3', 'TEST1');
  
  -- adding a single-cell, relatively positioned formula
  ExcelGen.putFormulaCell(ctx, sheet1, 1, 0, '"Total=" & SUM(R2C:R[-1]C)', p_anchorTableId => table1, p_anchorPosition => ExcelGen.BOTTOM_RIGHT, p_refStyle => ExcelFmla.REF_R1C1);
  
  -- uncomment this to have the resulting workbook use R1C1 cell-reference style:
  --ExcelGen.setCellReferenceStyle(ctx, ExcelFmla.REF_R1C1);
  
  ExcelGen.createFile(ctx, 'TEST_DIR', 'test-formula.xlsx');
  ExcelGen.closeContext(ctx); 
end;
/

Hyperlinks

test-links.xlsx

declare
  ctx     ExcelGen.ctxHandle := ExcelGen.createContext(ExcelGen.FILE_XLSX);
  sheet1  ExcelGen.sheetHandle := ExcelGen.addSheet(ctx, 'sheet1');
  table1  ExcelGen.tableHandle;
begin
  -- query-based table with excluded column "NAME"
  table1 := ExcelGen.addTable(ctx, sheet1, q'{select level as id, 'TEST'||level as name from dual connect by level <= 100}', p_excludeCols => '"NAME"');
  ExcelGen.setTableHeader(ctx, sheet1, table1);
  
  -- new hyperlink column "LINK" based on "NAME" and "VC1" column
  ExcelGen.addTableHyperlinkColumnAfter(ctx, sheet1, table1, p_name => 'LINK', p_columnId => 2, p_location => 'scheme://test/${VC1}', p_linkName => '${NAME}');
  ExcelGen.addTableColumn(ctx, sheet1, table1, 'VC1', 'RC1+1', ExcelFmla.REF_R1C1);
  
  -- intra-sheet link
  ExcelGen.putHyperlinkCell(
    p_ctxId          => ctx
  , p_sheetId        => sheet1
  , p_rowIdx         => 1
  , p_colIdx         => 0
  , p_location       => '#A1'
  , p_linkName       => 'Back to top'
  , p_anchorTableId  => table1
  , p_anchorPosition => ExcelGen.BOTTOM_RIGHT
  , p_style          => ExcelGen.makeCellStyleCss(ctx, 'background-color:yellow')
  );
  
  ExcelGen.setTableProperties(ctx, sheet1, table1, 'TableStyleLight1');
  
  ExcelGen.createFile(ctx, 'TEST_DIR', 'test-links.xlsx');
  
  ExcelGen.closeContext(ctx);
end;
/

Data validation

Creates a workbook with various data validation rules.
data-validation.sql → test-dataval.xlsx

Copyright and license

Copyright 2020-2024 Marc Bleron. Released under MIT license.

About

ExcelGen is an Oracle PL/SQL utility to create MS Excel files (.xlsx, .xlsb)

Topics

excel plsql

Resources

Readme

License

MIT license
Activity

Stars

43 stars

Watchers

10 watching

Forks

10 forks
Report repository

Releases 9

v4.1 Latest
Sep 4, 2024
+ 8 releases

Packages

No packages published

Contributors 2

Languages