Pass the Sql query string to the below procedure to generate an Excel file using Ole2 package.
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;
Download demo form from the following link Generate Excel Report
Hi, Vinish, I hope in these strange times that you and yours are well.
Here's my question: using "stuff like" above, e.g., OLE2 package and Oracle Forms 6i (don't ask, that's a novel, we ARE upgrading) ... how can I write an Excel file _without_ having Excel on the server / computer that is doing the writing? I understand all the looping, I've been working with OFR6i _way_ too long, and can write all kinds of SQL and PL/SQL ... but what I cannot figure out is ... "How to search" for my answer?
So far, everything is just some variation on the above, as to using OLE2 package bits to write Excel. Well, that's all lovely. I've gotten that to work. BUT BUT BUT it only works on a PC that has _EXCEL_ installed. If the box running the program does NOT have Excel, no go, it does nothing, doesn't even give me an error code.
I _may_ be able to install Excel on the server in question (we use RDP to a server that runs forms ... long, long story, but must keep it running for another year) ... but I would prefer NOT to install Excel. So ... how do I get it to work?
Thanks for your help and any direction you can provide.
Kindest regards,
Suanne Michelle