Below is the example to create an excel file in Oracle Forms.
Pass the Sql query string to the below procedure to generate an Excel file using Ole2 package.
Sample Code:
PROCEDURE Create_Excel_File (CSQL Varchar2)
Is
source_cursor Integer;
l_colCnt Number Default 0;
l_descTbl Dbms_sql.desc_tab;
newval1 Varchar2 (4000);
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
colour OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
RANGE OLE2.OBJ_TYPE;
range_col OLE2.OBJ_TYPE;
range_row OLE2.OBJ_TYPE;
args OLE2.LIST_TYPE;
rows_processed Number;
row_n Number;
VAL Varchar2 (100);
x Number;
filename Varchar2 (200);
BEGIN
BEGIN
source_cursor := Dbms_Sql.open_Cursor;
Dbms_Sql.parse (source_cursor, cSql, 2);
Dbms_Sql.describe_Columns (c => source_cursor, col_cnt => l_colCnt, desc_t => l_descTbl);
EXCEPTION
When Others
Then
Error_Message (SQLERRM);
RETURN;
END;
application := OLE2.CREATE_OBJ (‘Excel.Application’);
OLE2.SET_PROPERTY (application, ‘Visible’, ‘False’);
workbooks := OLE2.GET_OBJ_PROPERTY (application, ‘Workbooks’);
workbook := OLE2.GET_OBJ_PROPERTY (workbooks, ‘Add’);
worksheets := OLE2.GET_OBJ_PROPERTY (workbook, ‘Worksheets’);
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG (args, 1);
worksheet := OLE2.GET_OBJ_PROPERTY (worksheets, ‘Item’, args);
OLE2.DESTROY_ARGLIST (args);
For T In 1 .. l_colCnt
LOOP
BEGIN
Dbms_Sql.define_Column (source_cursor, T, newval1, 4000);
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG (args, 1);
OLE2.ADD_ARG (args, T); –Next column
cell := OLE2.GET_OBJ_PROPERTY (worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST (args);
colour := ole2.get_obj_property (cell, ‘Borders’);
ole2.set_property (colour, ‘ColorIndex’, 1);
ole2.Release_obj (colour);
colour := ole2.get_obj_property (cell, ‘Interior’);
ole2.set_property (colour, ‘ColorIndex’, 15);
ole2.Release_obj (colour);
OLE2.SET_PROPERTY (cell, ‘Value’, l_descTbl (T).col_name);
OLE2.Release_obj (cell);
EXCEPTION
When Others
Then
Null;
END;
END LOOP;
Rows_processed := Dbms_Sql.EXECUTE (source_cursor);
row_n := 1;
LOOP
IF Dbms_Sql.fetch_Rows (source_cursor) > 0
Then
For T In 1 .. l_colCnt
LOOP
BEGIN
Dbms_Sql.column_Value (source_cursor, T, newval1);
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG (args, row_n + 1);
OLE2.ADD_ARG (args, T); –Next column
cell := OLE2.GET_OBJ_PROPERTY (worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST (args);
colour := ole2.get_obj_property (cell, ‘Borders’);
ole2.set_property (colour, ‘ColorIndex’, 1);
ole2.Release_obj (colour);
OLE2.SET_PROPERTY (cell, ‘Value’, newval1);
OLE2.Release_obj (cell);
EXCEPTION
When Others
Then
EXIT;
END;
newval1 := Null;
END LOOP;
Else
EXIT;
END IF;
row_n := row_n + 1;
END LOOP;
Dbms_Sql.close_Cursor (source_cursor);
— Autofit columns
RANGE := OLE2.GET_OBJ_PROPERTY (worksheet, ‘UsedRange’);
range_col := OLE2.GET_OBJ_PROPERTY (RANGE, ‘Columns’);
range_row := OLE2.GET_OBJ_PROPERTY (RANGE, ‘Rows’);
OLE2.INVOKE (range_col, ‘AutoFit’);
OLE2.INVOKE (range_row, ‘AutoFit’);
OLE2.Release_obj (RANGE);
OLE2.Release_obj (range_col);
OLE2.Release_obj (range_row);
— Get filename and path
filename := ‘Yourexcel.xls’;
— Save as worksheet
IF Nvl (filename, ‘0’) <> ‘0’
Then
OLE2.SET_PROPERTY (application, ‘Visible’, ‘True’);
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG (args, filename);
OLE2.INVOKE (worksheet, ‘SaveAs’, args);
OLE2.DESTROY_ARGLIST (args);
END IF;
— OLE2.INVOKE( workbook ,’Close’);
OLE2.Release_obj (worksheet);
OLE2.Release_obj (worksheets);
OLE2.Release_obj (workbook);
OLE2.Release_obj (workbooks);
OLE2.Release_obj (application);
END;
