ORA EXCEL Reference Guide

ORA_EXCEL Professional
Reference guide
ORA_EXCEL is PL/SQL package for Oracle database

that produces Microsoft Excel XSLX documents
Contents
Function ORA_EXCEL.new_document.....................................................................................................................3
Procedure ORA_EXCEL.new_document ..................................................................................................................4
Function ORA_EXCEL.add_sheet .............................................................................................................................5
Procedure ORA_EXCEL.add_sheet .......................................................................................................................... 6
Function ORA_EXCEL.add_row................................................................................................................................7
Procedure ORA_EXCEL.add_row ............................................................................................................................. 8
Procedure ORA_EXCEL.set_row_height ..................................................................................................................9
Procedure ORA_EXCEL.set_cell_value ..................................................................................................................10
Procedure ORA_EXCEL.set_cell_font ....................................................................................................................16
Procedure ORA_EXCEL.set_default_font ..............................................................................................................18
Procedure ORA_EXCEL.set_cell_bold ....................................................................................................................20
Procedure ORA_EXCEL.set_cell_italic ...................................................................................................................22
Procedure ORA_EXCEL.set_cell_underline............................................................................................................24
Procedure ORA_EXCEL.set_cell_color ...................................................................................................................26
Procedure ORA_EXCEL.set_cell_bg_color .............................................................................................................28
Procedure ORA_EXCEL.set_cell_align_left ............................................................................................................30
Procedure ORA_EXCEL.set_cell_align_right ..........................................................................................................32
Procedure ORA_EXCEL.set_cell_align_center .......................................................................................................34
Procedure ORA_EXCEL.set_cell_vert_align_top ...................................................................................................36
Procedure ORA_EXCEL.set_cell_vert_align_middle ..............................................................................................38
Procedure ORA_EXCEL.set_cell_vert_align_bottom .............................................................................................40
Procedure ORA_EXCEL.set_column_width ...........................................................................................................42
Procedure ORA_EXCEL.set_cell_border_top.........................................................................................................44
Procedure ORA_EXCEL.set_cell_border_bottom ..................................................................................................46
Procedure ORA_EXCEL.set_cell_border_left.........................................................................................................48
Procedure ORA_EXCEL.set_cell_border_right ......................................................................................................51
Procedure ORA_EXCEL.set_cell_border ................................................................................................................54
Procedure ORA_EXCEL.set_cell_wrap_text...........................................................................................................56
Procedure ORA_EXCEL.merge_cells ......................................................................................................................58
Procedure ORA_EXCEL.merge_rows .....................................................................................................................60
Procedure ORA_EXCEL.set_cell_format ................................................................................................................62
Procedure ORA_EXCEL.query_to_sheet ................................................................................................................64
Procedure ORA_EXCEL. set_cell_formula .............................................................................................................67

Procedure ORA_EXCEL. set_cell_rotate_text ........................................................................................................69
Procedure ORA_EXCEL. set_sheet_margins ..........................................................................................................71
Procedure ORA_EXCEL. set_sheet_landscape.......................................................................................................74
Procedure ORA_EXCEL. set_sheet_paper_size .....................................................................................................76
Procedure ORA_EXCEL. set_sheet_header_text ...................................................................................................79
Procedure ORA_EXCEL. set_sheet_footer_text ....................................................................................................81
Procedure ORA_EXCEL. set_cell_hyperlink ...........................................................................................................83
Procedure ORA_EXCEL. set_cell_indent_left ........................................................................................................85
Procedure ORA_EXCEL. set_cell_indent_right ......................................................................................................87
Procedure ORA_EXCEL.set_cell_comment............................................................................................................90
Procedure ORA_EXCEL. hide_column ...................................................................................................................92
Procedure ORA_EXCEL.hide_row ..........................................................................................................................95
Procedure ORA_EXCEL.set_cells_filter ..................................................................................................................97
Procedure ORA_EXCEL.save_to_file ....................................................................................................................100
Procedure ORA_EXCEL.save_to_blob..................................................................................................................102
Procedure ORA_EXCEL.set_1904_date_system ..................................................................................................103
Package methods description and usage examples
Function ORA_EXCEL.new_document
Description:
pls_integer ORA_EXCEL.new_document
Create new Excel document
Mandatory parameters:
No parameters
Optional parameters:
No parameters
Returns:
Returns current document id
Example:
DECLARE
doc_id PLS_INTEGER;
sheet_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
doc_id := ORA_EXCEL.new_document;
sheet_id := ORA_EXCEL.add_sheet('My Sheet', doc_id);
row_id := ORA_EXCEL.add_row(doc_id, sheet_id);
ORA_EXCEL.set_cell_value('A', 'Hello World!', doc_id, sheet_id, row_id);
ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx', doc_id);
END;
Output:
Procedure ORA_EXCEL.new_document
Description:
ORA_EXCEL.new_document
Create new Excel document and returns the document id.
No parameters
No parameters
Returns:
Procedure, does not return any value.
Example:
BEGIN
ORA_EXCEL.new_document;
ORA_EXCEL.add_sheet('My Sheet');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Hello World!');
ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');
END;
Output:
Function ORA_EXCEL.add_sheet
Description:
pls_integer ORA_EXCEL.add_sheet(sheet_name varchar2,
[doc_id pls_integer])
Adds sheet to current document or document with document id specified with parameter doc_id
sheet_name name of added sheet, max. 31 characters long
doc_id document id, default value is current document id
Returns:
Returns current sheet id
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
sheet_id := ORA_EXCEL.add_sheet('My Sheet 1', doc_id);
ORA_EXCEL.set_cell_value('A', 'Hello World!', doc_id, sheet_id, row_id);
sheet_id := ORA_EXCEL.add_sheet('My Sheet 2', doc_id);
ORA_EXCEL.set_cell_value('A', 'Hello World on second sheet!', doc_id,
sheet_id, row_id);
END;
Output:
Procedure ORA_EXCEL.add_sheet
Description:
ORA_EXCEL.add_sheet(sheet_name varchar2,
[doc_id pls_integer])
Adds sheet to current document or document with id of the document specified with parameter doc_id
sheet_name name of added sheet, max. 31 characters long
doc_id document id, default value is current document id
Returns:
Procedure, does not return any value.
Example:
BEGIN
ORA_EXCEL.add_sheet('My Sheet 1');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Hello World!');
ORA_EXCEL.add_sheet('My Sheet 2');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Hello World on second sheet!');
END;
Output:
Function ORA_EXCEL.add_row
Description:
pls_integer ORA_EXCEL.add_row([doc_id pls_integer, sheet_id pls_integer])
Adds row to current sheet or sheet with id of the sheet specified with parameter sheet_id
no parameters
doc_id id of the document of document where row will be added

sheet_id id of the sheet where row will be added
Returns:
Returns current row id
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
sheet_id := ORA_EXCEL.add_sheet('My Sheet', doc_id);
ORA_EXCEL.set_cell_value('A', 'First row', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('A', 'Second row', doc_id, sheet_id, row_id);

END;
Output:
Procedure ORA_EXCEL.add_row
Description:
ORA_EXCEL.add_row([doc_id pls_integer, sheet_id pls_integer])
Adds row to current sheet or sheet with id of the sheet specified with parameter sheet_id
no parameters
doc_id id of the document of document where row will be added

sheet_id id of the sheet where row will be added
Returns:
Procedure, does not return any value
Example:
BEGIN
ORA_EXCEL.add_sheet('My Sheet');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'First row');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Second row');
END;
Output:
Procedure ORA_EXCEL.set_row_height
Description:
ORA_EXCEL.set_row_height(height number
[, doc_id pls_integer, sheet_id pls_integer, row_id
pls_integer])
Change row height
height row height
doc_id id of document
sheet_id id of sheet
row_id id of row on which height will be adjusted
Returns:
Example:
BEGIN
ORA_EXCEL.add_sheet('My sheet');
ORA_EXCEL.add_row;
ORA_EXCEL.set_row_height(40);
ORA_EXCEL.set_cell_value('A', 'Cell with custom row height');
END;
Output:
Procedure ORA_EXCEL.set_cell_value
Description:
ORA_EXCEL.set_cell_value(name varchar2, value varchar2,
row_id pls_integer])
Set cell value with string value type
name cell name example 'A' (first cell in the sheet)

value cell value
10
[doc_id pls_integer, sheet_id pls_integer,
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 100);
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
sheet_id := ORA_EXCEL.add_sheet('My sheet', doc_id);
ORA_EXCEL.set_row_height(15, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('A', 100, doc_id, sheet_id, row_id);
END;
11
Output:
Description:
ORA_EXCEL.set_cell_value(name varchar2, value date,
Set cell value with date value type

value cell value
Returns:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
12
ORA_EXCEL.set_cell_value('A', TO_DATE('2011-11-15', 'yyyy-mm-dd'), doc_id,

sheet_id, row_id);
END;
Output:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', TO_DATE('2011-11-15', 'yyyy-mm-dd'));
END;
Output:
13
Description:
ORA_EXCEL.set_cell_value(name varchar2, value number,
Set cell value with number value type

value cell value
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
END;
14
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
END;
Output:
15
Procedure ORA_EXCEL.set_cell_font
Description:
ORA_EXCEL.set_cell_font(cell_name varchar, font_name varchar2
[, font_size pls_integer, doc_id
pls_integer, sheet_id pls_integer, row_id pls_integer])
Set cell font family and size
cell_name cell name

font_name font family
font_size font size

doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with custom font');
ORA_EXCEL.set_cell_font('A', 'Times New Roman', 14);
END;
16
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Cell with custom font', doc_id,
sheet_id, row_id);
ORA_EXCEL.set_cell_font('A', 'Times New Roman', 14);
END;
17
Output:
Procedure ORA_EXCEL.set_default_font
Description:
ORA_EXCEL.set_default_font(font_name varchar2, font_size pls_integer
Set default font family and size for whole document
font_name font family

font_size font size
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.set_default_font('Arial bold', 12);
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell A');
ORA_EXCEL.set_cell_value('B', 'Cell B');
18
[, doc_id pls_integer])
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_default_font('Arial bold', 12);
ORA_EXCEL.set_cell_value('A', 'Cell A', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('B', 'Cell B', doc_id, sheet_id, row_id);
END;
19
Output:
Procedure ORA_EXCEL.set_cell_bold
Description:
ORA_EXCEL.set_cell_bold(name varchar2
pls_integer])
Set the cell text bold
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Bolded text');
ORA_EXCEL.set_cell_bold('A');
END;
20
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Bolded text', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_bold('A', doc_id, sheet_id, row_id);
END;
21
Output:
Procedure ORA_EXCEL.set_cell_italic
Description:
ORA_EXCEL.set_cell_italic(name varchar2
pls_integer])
Set the cell text italic
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Italic text');
ORA_EXCEL.set_cell_font('A', 'Verdana', 14);
ORA_EXCEL.set_cell_italic('A');
22
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Italic text', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_font('A', 'Verdana', 14, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_italic('A', doc_id, sheet_id, row_id);
END;
23
Output:
Procedure ORA_EXCEL.set_cell_underline
Description:
ORA_EXCEL.set_cell_underline(name varchar2
pls_integer])
Set the cell text underline
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Underlined text');
ORA_EXCEL.set_cell_underline('A');
END;
24
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Underlined text', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_underline('A', doc_id, sheet_id, row_id);
END;
25
Output:
Procedure ORA_EXCEL.set_cell_color
Description:
ORA_EXCEL.set_cell_color(name varchar2, color varchar2
[, doc_id pls_integer, sheet_id pls_integer,
Set the text color
name cell name

color RGB color in hex format (for example FF0000 red)
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Colored text');
ORA_EXCEL.set_cell_color('A', 'FF0000');
END;
26
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Colored text', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_color('A', 'FF0000', doc_id, sheet_id, row_id);
END;
27
Output:
Procedure ORA_EXCEL.set_cell_bg_color
Description:
ORA_EXCEL.set_cell_bg_color(name varchar2, color varchar2
[, doc_id pls_integer, sheet_id
pls_integer, row_id pls_integer])
Color the background of cell
name cell name

color RGB color in hex format (for example FF0000 red)
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Background color');
ORA_EXCEL.set_cell_bg_color('A', '999999');
28
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Background color', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_bg_color('A', '999999', doc_id, sheet_id, row_id);
END;
29
Output:
Procedure ORA_EXCEL.set_cell_align_left
Description:
ORA_EXCEL.set_cell_align_left(name varchar2
pls_integer])
Align text left
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Left aligned text');
ORA_EXCEL.set_column_width('A', 40);
ORA_EXCEL.set_cell_align_left('A');
30
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Left aligned text', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_align_left('A', doc_id, sheet_id, row_id);
END;
31
Output:
Procedure ORA_EXCEL.set_cell_align_right
Description:
ORA_EXCEL.set_cell_align_right(name varchar2
pls_integer])
Align text right
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Right aligned text');
ORA_EXCEL.set_cell_align_right('A');
32
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Right aligned text', doc_id, sheet_id,
row_id);
ORA_EXCEL.set_cell_align_right('A', doc_id, sheet_id, row_id);
END;
33
Output:
Procedure ORA_EXCEL.set_cell_align_center
Description:
ORA_EXCEL.set_cell_align_center(name varchar2
pls_integer])
Center text
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Centered text');
ORA_EXCEL.set_cell_align_center('A');
34
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Centered text', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_align_center('A', doc_id, sheet_id, row_id);
END;
35
Output:
Procedure ORA_EXCEL.set_cell_vert_align_top
Description:
ORA_EXCEL.set_cell_vert_align_top(name varchar2
pls_integer])
Align text to the top of the cell
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Top aligned text');
ORA_EXCEL.set_cell_vert_align_top('A');
36
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Top aligned text', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_vert_align_top('A', doc_id, sheet_id, row_id);
END;
37
Output:
Procedure ORA_EXCEL.set_cell_vert_align_middle
Description:
ORA_EXCEL.set_cell_vert_align_middle(name varchar2
Align text so that is centered between the top and bottom of the cell
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Middle aligned text');
ORA_EXCEL.set_cell_vert_align_middle('A');
38
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Middle aligned text', doc_id, sheet_id,
row_id);
ORA_EXCEL.set_cell_vert_align_middle('A', doc_id, sheet_id, row_id);
END;
39
Output:
Procedure ORA_EXCEL.set_cell_vert_align_bottom
Description:
ORA_EXCEL.set_cell_vert_align_bottom(name varchar2
Align text to the bottom of the cell
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Bottom aligned text');
ORA_EXCEL.set_cell_vert_align_bottom('A');
40
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Bottom aligned text', doc_id, sheet_id,
row_id);
ORA_EXCEL.set_cell_vert_align_bottom('A', doc_id, sheet_id, row_id);
END;
41
Output:
Procedure ORA_EXCEL.set_column_width
Description:
ORA_EXCEL.set_column_width(name varchar2, width number
Set column width
name cell name

width column width
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Column with custom width');
42
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Column with custom width', doc_id, sheet_id,
row_id);
ORA_EXCEL.set_column_width('A', 40, doc_id, sheet_id);
END;
43
Output:
Procedure ORA_EXCEL.set_cell_border_top
Description:
ORA_EXCEL.set_cell_border_top(name varchar2
[, style varchar2, color VARCHAR2, doc_id pls_integer,
sheet_id pls_integer, row_id pls_integer])
Set top cell border style and color
name cell name
style border style (allowed values: thin, thick, double)

color RGB border color in hex format (for example FF0000 red)
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
44
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with default top border');
ORA_EXCEL.set_cell_border_top('A');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with double style top border');
ORA_EXCEL.set_cell_border_top('A', 'double');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with thick style and blue top border');
ORA_EXCEL.set_cell_border_top('A', 'thick', '0000FF');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Cell with default top border',
doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_border_top('A', NULL, NULL, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('A', 'Cell with double style top border', doc_id,
sheet_id, row_id);
ORA_EXCEL.set_cell_border_top('A', 'double', NULL, doc_id, sheet_id,
45
row_id);
ORA_EXCEL.set_cell_value('A', 'Cell with thick style and blue top border',
ORA_EXCEL.set_cell_border_top('A', 'thick', '0000FF', doc_id, sheet_id,
row_id);
END;
Output:
Procedure ORA_EXCEL.set_cell_border_bottom
Description:
ORA_EXCEL.set_cell_border_bottom(name varchar2
[, style varchar2, color VARCHAR2, doc_id

Set bottom cell border style and color
name cell name

doc_id document id
sheet_id sheet id
row_id row id
46
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with default bottom border');
ORA_EXCEL.set_cell_border_bottom('A');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with double style bottom border');
ORA_EXCEL.set_cell_border_bottom('A', 'double');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with thick style and blue color bottom
border');
ORA_EXCEL.set_cell_border_bottom('A', 'thick', '0000FF');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
47

ORA_EXCEL.set_cell_value('A', 'Cell with default bottom border', doc_id,
sheet_id, row_id);
ORA_EXCEL.set_cell_border_bottom('A', NULL, NULL, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('A', 'Cell with double style bottom border', doc_id,
sheet_id, row_id);
ORA_EXCEL.set_cell_border_bottom('A', 'double', NULL, doc_id, sheet_id,
row_id);
ORA_EXCEL.set_cell_value('A', 'Cell with thick style and blue color bottom
border', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_border_bottom('A', 'thick', '0000FF', doc_id, sheet_id,
row_id);
END;
Output:
Procedure ORA_EXCEL.set_cell_border_left
Description:
ORA_EXCEL.set_cell_border_left(name varchar2
[, style varchar2, color VARCHAR2, doc_id pls_integer,
Set left cell border style and color
48
name cell name

doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.set_column_width('B', 50);
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('B', 'Cell with default left border');
ORA_EXCEL.set_cell_border_left('B');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('B', 'Cell with double style left border');
ORA_EXCEL.set_cell_border_left('B', 'double');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('B', 'Cell with thick style and blue color left
border');
ORA_EXCEL.set_cell_border_left('B', 'thick', '0000FF');
END;
49
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_column_width('B', 50, doc_id, sheet_id);
ORA_EXCEL.set_cell_value('B', 'Cell with default left border', doc_id,
sheet_id, row_id);
ORA_EXCEL.set_cell_border_left('B', NULL, NULL, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('B', 'Cell with double style left border', doc_id,
sheet_id, row_id);
ORA_EXCEL.set_cell_border_left('B', 'double', NULL, doc_id, sheet_id,
row_id);
ORA_EXCEL.set_cell_value('B', 'Cell with thick style and blue color left
ORA_EXCEL.set_cell_border_left('B', 'thick', '0000FF', doc_id, sheet_id,
row_id);
END;
50
Output:
Procedure ORA_EXCEL.set_cell_border_right
Description:
ORA_EXCEL.set_cell_border_right(name varchar2
[, style varchar2, color VARCHAR2, doc_id

Set right cell border style and color
name cell name

doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
51
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with default right border');
ORA_EXCEL.set_cell_border_right('A');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with double style right border');
ORA_EXCEL.set_cell_border_right('A', 'double');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with thick style and blue color right
border');
ORA_EXCEL.set_cell_border_right('A', 'thick', '0000FF');
END;
Output:
52
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Cell with default right border', doc_id,
sheet_id, row_id);
ORA_EXCEL.set_cell_border_right('A', NULL, NULL, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('A', 'Cell with double style right border', doc_id,
sheet_id, row_id);
ORA_EXCEL.set_cell_border_right('A', 'double', NULL, doc_id, sheet_id,
row_id);
ORA_EXCEL.set_cell_value('A', 'Cell with thick style and blue color right
ORA_EXCEL.set_cell_border_right('A', 'thick', '0000FF', doc_id, sheet_id,
row_id);
END;
Output:
53
Procedure ORA_EXCEL.set_cell_border
Description:
ORA_EXCEL.set_cell_border(name varchar2
[, style varchar2, color varchar2, doc_id pls_integer,
Set all cell borders (top, bottom, left and right) style and color
name cell name

doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.set_column_width('B', 50);
ORA_EXCEL.add_row;
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('B', 'Cell with default border');
ORA_EXCEL.set_cell_border('B');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('B', 'Cell with double style border');
ORA_EXCEL.set_cell_border('B', 'double');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('B', 'Cell with thick style and blue color border');
ORA_EXCEL.set_cell_border('B', 'thick', '0000FF');
END;
54
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_column_width('B', 50, doc_id, sheet_id);
ORA_EXCEL.set_cell_value('B', 'Cell with default border', doc_id, sheet_id,
row_id);
ORA_EXCEL.set_cell_border('B', NULL, NULL, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('B', 'Cell with double style border', doc_id,
sheet_id, row_id);
ORA_EXCEL.set_cell_border('B', 'double', NULL, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('B', 'Cell with thick style and blue color border',
ORA_EXCEL.set_cell_border('B', 'thick', '0000FF', doc_id, sheet_id, row_id);
END;
55
Output:
Procedure ORA_EXCEL.set_cell_wrap_text
Description:
ORA_EXCEL.set_cell_wrap_text (name varchar2,
pls_integer])
Make all content visible within a cell by displaying it on multiple lines
name cell name
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'This is some long text which will is wrapped
to the next line');
56
ORA_EXCEL.set_cell_wrap_text('A');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'This is some long text which will is wrapped
to the next line', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_wrap_text('A', doc_id, sheet_id, row_id);
END;
57
Output:
Procedure ORA_EXCEL.merge_cells
Description:
ORA_EXCEL.merge_cells(cell_from varchar2, cell_to varchar2
Join horizontal cells into one larger cell
cell_from begining cell to join

cell_to ending cell to join
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
58
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Merged cells');
ORA_EXCEL.merge_cells('A', 'B');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Merged cells', doc_id, sheet_id, row_id);
ORA_EXCEL.merge_cells('A', 'B', doc_id, sheet_id, row_id);
END;
59
Output:
Procedure ORA_EXCEL.merge_rows
Description:
ORA_EXCEL.merge_rows (name varchar2, num_rows pls_integer
Join vertical cells into one larger cell
name name of begining cell

num_rows number of rows to merge
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
60
ORA_EXCEL.set_cell_value('A', 'Content in vertically merged cells');

ORA_EXCEL.merge_rows('A', 2);
ORA_EXCEL.set_cell_vert_align_middle('A');
END;
Example:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Content in vertically merged cells', doc_id,
sheet_id, row_id);
ORA_EXCEL.merge_rows('A', 2, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_vert_align_middle('A', doc_id, sheet_id, row_id);
END;
61
Output:
Procedure ORA_EXCEL.set_cell_format
Description:
ORA_EXCEL.set_cell_format (cell_name varchar2, format varchar2
Set format of the cell content
cell_name cell name

format format string
doc_id document id
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
62
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 1001.99);
ORA_EXCEL.set_cell_format('A', '0.00');
ORA_EXCEL.set_cell_value('B', SYSDATE);
ORA_EXCEL.set_cell_format('B', 'd.m.yyyy');
END;
Output:
Exmaple:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 1001.99, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_format('A', '0.00', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('B', SYSDATE, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_format('B', 'd.m.yyyy', doc_id, sheet_id, row_id);
END;
63
Output:
Procedure ORA_EXCEL.query_to_sheet
Description:
ORA_EXCEL.query_to_sheet(query varchar2
[, show_column_names boolean default true, doc_id

pls_integer default current_doc_id, sheet_id pls_integer default current_sheet_id])
Get result from query and write it to sheet
query query string
show_column_names boolean parameter, true show column names, false do not show column
names on first row
doc_id document id
sheet_id sheet id
row_id row id
Returns:
64
Example:
BEGIN
ORA_EXCEL.add_sheet('Employees');
ORA_EXCEL.query_to_sheet('select * from employees');
ORA_EXCEL.add_sheet('Departments');
ORA_EXCEL.query_to_sheet('select * from departments');
ORA_EXCEL.add_sheet('Locations');
ORA_EXCEL.query_to_sheet('select * from locations');
ORA_EXCEL.save_to_file('EXPORT_DIR', example.xlsx');
END;
Output:
65
Exmaple:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
sheet_id := ORA_EXCEL.add_sheet('Employees');
ORA_EXCEL.query_to_sheet('select * from employees', TRUE, doc_id, sheet_id);
sheet_id := ORA_EXCEL.add_sheet('Departments');
ORA_EXCEL.query_to_sheet('select * from departments', FALSE, doc_id,
sheet_id);
sheet_id := ORA_EXCEL.add_sheet('Locations');
ORA_EXCEL.query_to_sheet('select * from locations', TRUE, doc_id, sheet_id);
END;
Output:
66
Procedure ORA_EXCEL. set_cell_formula

Description:
ORA_EXCEL.set_cell_formula(name VARCHAR2,
formula VARCHAR2,
[doc_id PLS_INTEGER DEFAULT current_doc_id,
sheet_id PLS_INTEGER DEFAULT current_sheet_id,
row_id PLS_INTEGER DEFAULT current_row_id])
Sets cell formula
name - name of the cell where value will be added

formula - formula that will be used to calculate cell value
doc_id document id
sheet_id - id of sheet
row_id - id of row on which height will be adjusted
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('B', 20);
ORA_EXCEL.set_cell_value('C', 10 + 20);
ORA_EXCEL.set_cell_formula('C', 'SUM(A1+B1)');
-- Use ORA_EXCEL.current_row_id for current rown number
END;
67
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('B', 20, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('C', 10 + 20, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_formula('C', 'SUM(A1+B1)');
-- Use ORA_EXCEL.current_row_id for current rown number
END;
68
Output:
Procedure ORA_EXCEL. set_cell_rotate_text

Description:
ORA_EXCEL. set_cell_rotate_text(name VARCHAR2,
degrees INTEGER,
Rotates text to a diagonal angle
name - name of cell content will be centered

degrees - degree from 90 to 180 which will be used to rotate text
doc_id document id
row_id - id of row on which height will be adjusted
Returns:
Example:
BEGIN
69
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'This is rotated text');
ORA_EXCEL.set_cell_rotate_text('A', 45);
ORA_EXCEL.set_cell_value('B', 'This is rotated text');
ORA_EXCEL.set_cell_rotate_text('B', 90);
ORA_EXCEL.set_cell_value('C', 'This is rotated text');
ORA_EXCEL.set_cell_rotate_text('c', 180);
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'This is rotated text 1',
ORA_EXCEL.set_cell_rotate_text('A', 45, doc_id, sheet_id, row_id);
70
ORA_EXCEL.set_cell_value('B', 'This is rotated text 2',

ORA_EXCEL.set_cell_rotate_text('B', 90, doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('C', 'This is rotated text 3',
ORA_EXCEL.set_cell_rotate_text('c', 180, doc_id, sheet_id, row_id);
END;
Output:
Procedure ORA_EXCEL. set_sheet_margins

Description:
ORA_EXCEL. set_sheet_margins(left_margin NUMBER,
right_margin NUMBER,
top_margin NUMBER,
bottom_margin NUMBER,
header_margin NUMBER,
footer_margin NUMBER,
[sheet_id PLS_INTEGER DEFAULT current_sheet_id])
Rotates text to a diagonal angle
left_margin - margin size on the left side of sheet
71
right_margin - margin size on the right side of sheet

top_margin - margin size on the top side of sheet
bottom_margin - margin size on the bottom side of sheet
header_margin - margin size on the header side of sheet
footer_margin - margin size on the footer side of sheet
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'This is sheet with custom margins');
ORA_EXCEL.set_sheet_margins(left_margin => 10,
right_margin => 20,
top_margin => 30,
bottom_margin => 40,
header_margin => 50,
footer_margin => 60);
END;
72
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
73

ORA_EXCEL.set_cell_value('A', 'This is sheet with custom margins',
ORA_EXCEL.set_sheet_margins(left_margin => 10,
right_margin => 20,
top_margin => 30,
bottom_margin => 40,
header_margin => 50,
footer_margin => 60,
sheet_id => sheet_id);
END;
Output:
Procedure ORA_EXCEL. set_sheet_landscape

Description:
ORA_EXCEL. set_sheet_landscape(sheet_id PLS_INTEGER DEFAULT current_sheet_id)
Sets sheet orientation to landscape
74
Returns:
Example:
BEGIN
ORA_EXCEL.set_sheet_landscape;
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'This is in landscape mode');
END;
Output:
Example:
DECLARE
75
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_sheet_landscape(sheet_id);
ORA_EXCEL.set_cell_value('A', 'This is in landscape mode',
END;
Output:
Procedure ORA_EXCEL. set_sheet_paper_size

Description:
ORA_EXCEL. set_sheet_paper_size(paper_size INTEGER,
sheet_id PLS_INTEGER DEFAULT current_sheet_id)
Sets sheet paper size
76
paper_size - paper size of sheet
Paper size values:
1 - Letter (8-1/2 in. x 11 in.)
2 - Letter Small (8-1/2 in. x 11 in.)
3 - Tabloid (11 in. x 17 in.)
4 - Ledger (17 in. x 11 in.)
5 - Legal (8-1/2 in. x 14 in.)
6 - Statement (5-1/2 in. x 8-1/2 in.)
7 - Executive (7-1/2 in. x 10-1/2 in.)
8 - A3 (297 mm x 420 mm)
9 - A4 (210 mm x 297 mm)
10 - A4 Small (210 mm x 297 mm)
11 - A5 (148 mm x 210 mm)
12 - B4 (250 mm x 354 mm)
13 - A5 (148 mm x 210 mm)
14 - Folio (8-1/2 in. x 13 in.)
15 - Quarto (215 mm x 275 mm)
16 - 10 in. x 14 in.
17 - 11 in. x 17 in.
18 - Note (8-1/2 in. x 11 in.)
19 - Envelope #9 (3-7/8 in. x 8-7/8 in.)
20 - Envelope #10 (4-1/8 in. x 9-1/2 in.)
21 - Envelope #11 (4-1/2 in. x 10-3/8 in.)
22 - Envelope #12 (4-1/2 in. x 11 in.)
23 - Envelope #14 (5 in. x 11-1/2 in.)
24 - C size sheet
25 - D size sheet
26 - E size sheet
27 - Envelope DL (110 mm x 220 mm)
28 - Envelope C5 (162 mm x 229 mm)
33 - Envelope B4 (250 mm x 353 mm)
36 - Envelope (110 mm x 230 mm)
37 - Envelope Monarch (3-7/8 in. x 7-1/2 in.)
38 - Envelope (3-5/8 in. x 6-1/2 in.)
39 - U.S. Standard Fanfold (14-7/8 in. x 11 in.)
40 - German Legal Fanfold (8-1/2 in. x 13 in.)
41 - German Legal Fanfold (8-1/2 in. x 13 in.)
77
Returns:
Example:
BEGIN
ORA_EXCEL.set_sheet_paper_size(9);
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'This sheet with A4 papers size');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
78
ORA_EXCEL.set_sheet_paper_size(9, sheet_id);
ORA_EXCEL.set_cell_value('A', 'This sheet with A4 papers size',
END;
Output:
Procedure ORA_EXCEL. set_sheet_header_text

Description:
ORA_EXCEL. set_sheet_header_text(header_text VARCHAR2,
Sets sheet header text
header_text - text that will be displayed on sheets header, limited to 1000 characters
79
Returns:
Example:
BEGIN
ORA_EXCEL.set_sheet_header_text('Header text');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Sheet with header text');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
80

ORA_EXCEL.set_sheet_header_text('Header text', sheet_id);
ORA_EXCEL.set_cell_value('A', 'Sheet with header text',
END;
Output:
Procedure ORA_EXCEL. set_sheet_footer_text

Description:
ORA_EXCEL. set_sheet_footer_text(header_text VARCHAR2,
Sets sheet foter text
footer_text - text that will be displayed on sheets footer, limited to 1000 characters
81
Returns:
Example:
BEGIN
ORA_EXCEL.set_sheet_footer_text(Footer text');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Sheet with footer text');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_sheet_footer_text('Footer text', sheet_id);
82
ORA_EXCEL.set_cell_value('A', 'Sheet with footer text',

END;
Output:
Procedure ORA_EXCEL. set_cell_hyperlink

Description:
ORA_EXCEL.set_cell_hyperlink(name VARCHAR2,
hyperlink VARCHAR2,
doc_id PLS_INTEGER DEFAULT current_doc_id,
row_id PLS_INTEGER DEFAULT current_row_id)
Sets hyperlink for cell
name - cell name

hyperlink - hyperlink that will be set on cell
doc_id - id of dpcument
83
row_id - id of row
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'www.google.com');
ORA_EXCEL.set_cell_hyperlink('A', 'http://www.google.com');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
84
BEGIN
ORA_EXCEL.set_cell_value('A', 'www.google.com',
ORA_EXCEL.set_cell_hyperlink('A', 'http://www.google.com',

END;
Output:
Procedure ORA_EXCEL. set_cell_indent_left

Description:
ORA_EXCEL.set_cell_indent_left(name VARCHAR2,
indent INTEGER,
Sets left indent within the cell
85
name - name of cell content will be indented from the left side
indent - number of indent from left site of cell
row_id - id of row
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Left indented text');
ORA_EXCEL.set_cell_indent_left('A', 5);
END;
Output:
86
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Left indented text',
ORA_EXCEL.set_cell_indent_left('A', 5,
END;
Output:
Procedure ORA_EXCEL. set_cell_indent_right

Description:
ORA_EXCEL.set_cell_indent_right(name VARCHAR2,
indent INTEGER,
87

Sets right indent within the cell
name - name of cell content will be indented from the right side
indent - number of indent from right site of cell
row_id - id of row
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Right indented text');
ORA_EXCEL.set_cell_indent_right('A', 5);
END;
88
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Right indented text',
ORA_EXCEL.set_cell_indent_right('A', 5, doc_id, sheet_id, row_id);
END;
89
Output:
Procedure ORA_EXCEL.set_cell_comment
Description:
ORA_EXCEL. set_cell_comment(name VARCHAR2,
autohr VARCHAR2,
comment_text VARCHAR2,
comment_box_width NUMBER DEFAULT 100,
comment_box_height NUMBER DEFAULT 60,
Sets right indent within the cell
name - cell name

author - name of the autor of the comment
comment_text - comment text for the cell
comment_box_width - width of comment box
comment_box_height - height of comment box
90
row_id - id of row
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Cell with comment');
ORA_EXCEL.set_cell_comment('A', 'John', 'This is comment for cell', 100, 50);
END;
Output:
Example:
DECLARE
91
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Cell with comment', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_comment('A', 'John', 'This is comment for cell', 100, 50,
END;
Output:
Procedure ORA_EXCEL. hide_column

Description:
ORA_EXCEL. hide_column(name VARCHAR2,
doc_id PLS_INTEGER DEFAULT current_doc_id,
Hides column
92
name - name of the column which will be hidden
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A',
ORA_EXCEL.set_cell_value('B',
ORA_EXCEL.hide_column('B');
ORA_EXCEL.set_cell_value('C',
ORA_EXCEL.set_cell_value('D',
'Cell 1');
'Cell 2');
'Cell 3');
'Cell 4');
END;
93
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Cell 1', doc_id,
ORA_EXCEL.set_cell_value('B', 'Cell 2', doc_id,
ORA_EXCEL.hide_column('B', doc_id, sheet_id);
ORA_EXCEL.set_cell_value('C', 'Cell 3', doc_id,
ORA_EXCEL.set_cell_value('D', 'Cell 4', doc_id,
sheet_id, row_id);
sheet_id, row_id);
sheet_id, row_id);
sheet_id, row_id);

END;
94
Output:
Procedure ORA_EXCEL.hide_row
Description:
ORA_EXCEL.hide_row(doc_id PLS_INTEGER DEFAULT current_doc_id,
Hides row
row_id - unique identificator of row which will be hidden
Returns:
Example:
BEGIN
95
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'First row');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Second row');
ORA_EXCEL.hide_row;
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Third row');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
96

ORA_EXCEL.set_cell_value('A', 'First row', doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('A', 'Second row', doc_id, sheet_id, row_id);
ORA_EXCEL.hide_row(doc_id, sheet_id, row_id);
ORA_EXCEL.set_cell_value('A', 'Third row', doc_id, sheet_id, row_id);
END;
Output:
Procedure ORA_EXCEL.set_cells_filter
Description:
ORA_EXCEL. set_cells_filter(cell_from VARCHAR2,
cell_to VARCHAR2,
sheet_id PLS_INTEGER DEFAULT current_sheet_id])
Sets column auto filter between defined columns range
97
cell_from - cell name with row number from which auto filter will start, example: A1
cell_to - cell name with row number where auto filter will end, example: A5
Returns:
Example:
BEGIN
ORA_EXCEL.query_to_sheet('select * from employees');
ORA_EXCEL.set_cells_filter('A1', 'K1');
END;
98
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
BEGIN
ORA_EXCEL.query_to_sheet('select * from employees', TRUE, doc_id, sheet_id);
ORA_EXCEL.set_cells_filter('A1', 'K1', doc_id, sheet_id);
END;
99
Output:
Procedure ORA_EXCEL.save_to_file
Description:
ORA_EXCEL.save_to_file(directory_name varchar2, file_name varchar2
Save generated Excel document to file
directory_name Oracle directory

file_name file name (example my_document.xlsx)
doc_id document id
100
sheet_id sheet id
row_id row id
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Saved excel document');
END;
Output:
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Saved excel document', doc_id, sheet_id,
row_id);
END;
101
Output:
Procedure ORA_EXCEL.save_to_blob
Description:
ORA_EXCEL.save_to_blob(blob_file in out blob
Save generated Excel document to PL/SQL BLOB variable type
blob_file PL/SQL BLOB variable where binary content of Excel worksheet will be stored
doc_id document id
Returns:
Example:
DECLARE
generated_excel BLOB;
BEGIN
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', 'Saved excel document');
102
ORA_EXCEL.save_to_blob(generated_excel);
-- Excel document is stored into generated_excel variable
END;
Example:
DECLARE
doc_id PLS_INTEGER;
row_id PLS_INTEGER;
generated_excel BLOB;
BEGIN
ORA_EXCEL.set_cell_value('A', 'Saved excel document', doc_id, sheet_id,
row_id);
ORA_EXCEL.save_to_blob(generated_excel, doc_id);
-- Excel document is stored into generated_excel variable
END;
Procedure ORA_EXCEL.set_1904_date_system
Description:
ORA_EXCEL.set_1904_date_system
Set generated Excel document date setting to use January 1st of 1904 as start year
Returns:
Example:
BEGIN
ORA_EXCEL.set_1904_date_system;
ORA_EXCEL.add_sheet('Date system test');
ORA_EXCEL.add_row;
ORA_EXCEL.set_cell_value('A', SYSDATE);
dbms_output.put_line(TO_CHAR(SYSDATE, 'dd.mm.yyyy hh24:mi:ss'));
ORA_EXCEL.save_to_file('EXPORT_DIR', 'example92.xlsx');
END;
103
Description:
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
END;
Description:
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
END;
104
Description:
Returns:
Example:
BEGIN
ORA_EXCEL.add_row;
105
END;
106

ORA EXCEL Reference Guide

Uploaded by

Copyright:

Available Formats

ORA EXCEL Reference Guide

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

ORA EXCEL Reference Guide

Uploaded by

Copyright:

Available Formats

ORA_EXCEL Professional

ORA_EXCEL is PL/SQL package for Oracle database

Procedure ORA_EXCEL. set_cell_formula .............................................................................................................67

Package methods description and usage examples

Create new Excel document

Create new Excel document and returns the document id.

sheet_name name of added sheet, max. 31 characters long

doc_id document id, default value is current document id

sheet_name name of added sheet, max. 31 characters long

doc_id document id, default value is current document id

doc_id id of the document of document where row will be added

ORA_EXCEL.set_cell_value('A', 'Second row', doc_id, sheet_id, row_id);

doc_id id of the document of document where row will be added

[, doc_id pls_integer, sheet_id pls_integer, row_id

Change row height

height row height

Set cell value with string value type

name cell name example 'A' (first cell in the sheet)

[doc_id pls_integer, sheet_id pls_integer,

[doc_id pls_integer, sheet_id pls_integer,

Set cell value with date value type

name cell name example 'A' (first cell in the sheet)

ORA_EXCEL.set_cell_value('A', TO_DATE('2011-11-15', 'yyyy-mm-dd'), doc_id,

[doc_id pls_integer, sheet_id pls_integer,

Set cell value with number value type

name cell name example 'A' (first cell in the sheet)

[, font_size pls_integer, doc_id

pls_integer, sheet_id pls_integer, row_id pls_integer])

Set cell font family and size

cell_name cell name

font_size font size

Set default font family and size for whole document

font_name font family

[, doc_id pls_integer, sheet_id pls_integer, row_id

Set the cell text bold

name cell name

[, doc_id pls_integer, sheet_id pls_integer, row_id

Set the cell text italic

name cell name

[, doc_id pls_integer, sheet_id pls_integer, row_id

Set the cell text underline

name cell name

[, doc_id pls_integer, sheet_id pls_integer,

Set the text color

name cell name

[, doc_id pls_integer, sheet_id

pls_integer, row_id pls_integer])

Color the background of cell

name cell name

[, doc_id pls_integer, sheet_id pls_integer, row_id

Align text left

name cell name

[, doc_id pls_integer, sheet_id pls_integer, row_id

Align text right

name cell name

[, doc_id pls_integer, sheet_id pls_integer, row_id

name cell name

[, doc_id pls_integer, sheet_id pls_integer, row_id

Align text to the top of the cell