Export to Excel From Oracle Forms

PROCEDURE display_table(

pc$tablename IN VARCHAR2

) IS

inbcols PLS_INTEGER := 0;

inbrows PLS_INTEGER := 0;

inbsheets PLS_INTEGER := 0;

lc$line VARCHAR2(32767);

lc$head VARCHAR2(4000);

lc$type VARCHAR2(4000);

c NUMBER;

d NUMBER;

col_cnt PLS_INTEGER;

rec_tab DBMS_SQL.desc_tab;

v VARCHAR2(4000);

col_num NUMBER;

source_cursor INTEGER;

result INTEGER;

lc$query VARCHAR2(1000);

lc$count VARCHAR2(1000);

lc$where VARCHAR2(1024);

lc$order VARCHAR2(1024);

lc$rowid VARCHAR2(100);

lc$image VARCHAR2(32767);

application client_ole2.obj_type;

workbooks client_ole2.obj_type;

workbook client_ole2.obj_type;

worksheets client_ole2.obj_type;

worksheet client_ole2.obj_type;

cell client_ole2.obj_type;

hfontobj2 client_ole2.obj_type;

v_interior client_ole2.obj_type;

args client_ole2.list_type;

RANGE client_ole2.obj_type;

range_col client_ole2.obj_type;

BEGIN

-- current ROW --

:GLOBAL.current_row := '0';

-- current CELL --

:GLOBAL.current_cell := '0';

IF :bl1.LIMIT IS NOT NULL THEN

lc$query := 'SELECT t.* FROM '

|| pc$tablename

|| ' t WHERE ROWNUM <= '

|| :bl1.LIMIT;

lc$count := 'SELECT Count(*) FROM '

|| pc$tablename

|| ' WHERE ROWNUM <= '

|| :bl1.LIMIT;

ELSE

lc$query := 'SELECT t.* FROM ' || pc$tablename || ' t';

lc$count := 'SELECT Count(*) FROM ' || pc$tablename;

END IF;

IF :bl1.WHERE IS NOT NULL THEN

lc$where := REPLACE(

REPLACE(REPLACE(:bl1.where, 'where', ''), 'WHERE', ''),

'Where',

''

);

lc$query := lc$query || ' AND ' || lc$where;

lc$count := lc$count || ' AND ' || lc$where;

END IF;

IF :bl1.ORDER IS NOT NULL THEN

lc$order := REPLACE(

REPLACE(

REPLACE(:bl1.ORDER, 'order by', ''),

'ORDER BY',

''

),

'Order by',

''

);

lc$query := lc$query || ' ORDER BY ' || lc$order;

lc$count := lc$count || ' ORDER BY ' || lc$order;

END IF;

-- Count the rows --

source_cursor := DBMS_SQL.open_cursor;

DBMS_SQL.parse(source_cursor, lc$count, 1);

DBMS_SQL.define_column(source_cursor, 1, v, 4000);

result := DBMS_SQL.EXECUTE(source_cursor);

IF DBMS_SQL.fetch_rows(source_cursor) > 0 THEN

DBMS_SQL.column_value(source_cursor, 1, v);

inbrows := v;

END IF;

DBMS_SQL.close_cursor(source_cursor);

---for excel--

application := client_ole2.create_obj('Excel.Application');

client_ole2.set_property(application, 'Visible', 'True');

workbooks := client_ole2.get_obj_property(application, 'Workbooks');

workbook := client_ole2.invoke_obj(workbooks, 'Add');

worksheets := client_ole2.get_obj_property(workbook, 'Worksheets');

worksheet := client_ole2.invoke_obj(worksheets, 'Add');

client_ole2.set_property(worksheet, 'Name', :t_name);

-- retrieve the columns of the query --

c := DBMS_SQL.open_cursor;

DBMS_SQL.parse(c, lc$query, 1);

d := DBMS_SQL.EXECUTE(c);

DBMS_SQL.describe_columns(c, col_cnt, rec_tab);

inbcols := 0;

FOR i IN rec_tab.FIRST .. rec_tab.LAST

LOOP

lc$head := rec_tab(i).col_name;

inbcols := inbcols + 1;

args := client_ole2.create_arglist;

client_ole2.add_arg(args, 1);

client_ole2.add_arg(args, inbcols);

cell := client_ole2.get_obj_property(worksheet, 'Cells', args);

client_ole2.destroy_arglist(args);

client_ole2.set_property(cell, 'Value', lc$head);

hfontobj2 := client_ole2.get_obj_property(cell, 'FONT');

client_ole2.set_property(hfontobj2, 'BOLD', TRUE );

client_ole2.set_property(hfontobj2, 'ITALIC', TRUE );

client_ole2.set_property(hfontobj2, 'Name', 'Times New Roman');

client_ole2.set_property(hfontobj2, 'Size', 10);

client_ole2.set_property(hfontobj2, 'ColorIndex', 6); --foreground color

v_interior := client_ole2.get_obj_property(cell, 'Interior'); --background

client_ole2.set_property(v_interior, 'ColorIndex', 10); --background color Number

client_ole2.RELEASE_OBJ(v_interior);

client_ole2.RELEASE_OBJ(hfontobj2);

client_ole2.RELEASE_OBJ(cell);

END LOOP;

DBMS_SQL.close_cursor(c);

--------------------

-- Get the data --

--------------------

source_cursor := DBMS_SQL.open_cursor;

DBMS_SQL.parse(source_cursor, lc$query, 1);

-- Define the columns --

FOR i IN 1 .. rec_tab.LAST

LOOP

DBMS_SQL.define_column(source_cursor, i, v, 4000);

END LOOP;

result := DBMS_SQL.EXECUTE(source_cursor);

-- Fetch the rows from the source query --

inbrows := 1;

inbsheets := 1;

LOOP

IF DBMS_SQL.fetch_rows(source_cursor) > 0 THEN

----------------------------------

-- get column values of the row --

----------------------------------

--if iNbrows >= 2 then

IF inbrows >= 65536 THEN

RANGE := client_ole2.invoke_obj(worksheet, 'UsedRange');

range_col := client_ole2.invoke_obj(RANGE, 'Columns');

client_ole2.invoke(range_col, 'AutoFit');

client_ole2.RELEASE_OBJ(RANGE);

client_ole2.RELEASE_OBJ(range_col);

inbrows := 2;

worksheet := client_ole2.invoke_obj(worksheets, 'Add');

client_ole2.set_property(

worksheet,

'Name',

:t_name || inbsheets

);

c := DBMS_SQL.open_cursor;

DBMS_SQL.parse(c, lc$query, 1);

d := DBMS_SQL.EXECUTE(c);

DBMS_SQL.describe_columns(c, col_cnt, rec_tab);

inbcols := 0;

FOR i IN rec_tab.FIRST .. rec_tab.LAST

LOOP

lc$head := rec_tab(i).col_name;

inbcols := inbcols + 1;

args := client_ole2.create_arglist;

client_ole2.add_arg(args, 1);

client_ole2.add_arg(args, inbcols);

cell := client_ole2.get_obj_property(

worksheet,

'Cells',

args

);

client_ole2.destroy_arglist(args);

client_ole2.set_property(cell, 'Value', lc$head);

hfontobj2 := client_ole2.get_obj_property(

cell,

'FONT'

);

client_ole2.set_property(hfontobj2, 'BOLD', TRUE );

client_ole2.set_property(hfontobj2, 'ITALIC', TRUE );

client_ole2.set_property(

hfontobj2,

'Name',

'Times New Roman'

);

client_ole2.set_property(hfontobj2, 'Size', 10);

client_ole2.set_property(hfontobj2, 'ColorIndex', 6); --foreground color

v_interior :=

client_ole2.get_obj_property(cell, 'Interior'); --background

client_ole2.set_property(

v_interior,

'ColorIndex',

10

); --background color Number

client_ole2.RELEASE_OBJ(v_interior);

client_ole2.RELEASE_OBJ(hfontobj2);

client_ole2.RELEASE_OBJ(cell);

END LOOP;

DBMS_SQL.close_cursor(c);

inbsheets := inbsheets + 1;

ELSE

inbrows := inbrows + 1;

END IF;

FOR i IN 1 .. rec_tab.LAST

LOOP

DBMS_SQL.column_value(source_cursor, i, v);

lc$line := NVL(v, ' ');

args := client_ole2.create_arglist;

client_ole2.add_arg(args, inbrows);

client_ole2.add_arg(args, i);

cell := client_ole2.get_obj_property(

worksheet,

'Cells',

args

);

client_ole2.destroy_arglist(args);

client_ole2.set_property(cell, 'Value', lc$line);

END LOOP;

ELSE

RANGE := client_ole2.invoke_obj(worksheet, 'UsedRange');

range_col := client_ole2.invoke_obj(RANGE, 'Columns');

client_ole2.invoke(range_col, 'AutoFit');

client_ole2.RELEASE_OBJ(RANGE);

client_ole2.RELEASE_OBJ(range_col);

EXIT;

END IF;

END LOOP;

DBMS_SQL.close_cursor(source_cursor);

END;