使用Client_OLE2将数据从Oracle Forms 12c导出到Excel

Question

请帮助我使用client_ole2将数据从oracle表单导出到excel的共享示例过程。

Answer 1

然后这应该会起作用：

declare
source_cursor    Integer;
l_colCnt         Number            Default 0;
l_descTbl        Dbms_sql.desc_tab;
newval1          Varchar2 (4000);
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;
colour           CLIENT_OLE2.OBJ_TYPE;
cell             CLIENT_OLE2.OBJ_TYPE;
RANGE            CLIENT_OLE2.OBJ_TYPE;
range_col        CLIENT_OLE2.OBJ_TYPE;
range_row        CLIENT_OLE2.OBJ_TYPE;
args             CLIENT_OLE2.LIST_TYPE;
rows_processed   Number;
row_n            Number;
VAL              Varchar2 (100);
x                Number;
filename         Varchar2 (200);
csql varchar2(4000);
BEGIN
csql := 'select column1, column2 from table1';
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
def_error (SQLERRM);
RETURN;
END;
application := CLIENT_OLE2.CREATE_OBJ ('Excel.Application');
CLIENT_OLE2.SET_PROPERTY (application, 'Visible', 'False');
workbooks := CLIENT_OLE2.GET_OBJ_PROPERTY (application, 'Workbooks');
workbook := CLIENT_OLE2.GET_OBJ_PROPERTY (workbooks, 'Add');
worksheets := CLIENT_OLE2.GET_OBJ_PROPERTY (workbook, 'Worksheets');
args := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG (args, 1);
worksheet := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheets, 'Item', args);
CLIENT_OLE2.DESTROY_ARGLIST (args);
For T In 1 .. l_colCnt
LOOP
BEGIN
Dbms_Sql.define_Column (source_cursor, T, newval1, 4000);
args := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG (args, 1);
CLIENT_OLE2.ADD_ARG (args, T);                                                      --Next column
cell := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheet, 'Cells', args);
CLIENT_OLE2.DESTROY_ARGLIST (args);
colour := client_ole2.get_obj_property (cell, 'Borders');
client_ole2.set_property (colour, 'ColorIndex', 1);
client_ole2.Release_obj (colour);
colour := client_ole2.get_obj_property (cell, 'Interior');
client_ole2.set_property (colour, 'ColorIndex', 15);
client_ole2.Release_obj (colour);
CLIENT_OLE2.SET_PROPERTY (cell, 'Value', l_descTbl (T).col_name);
CLIENT_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 := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG (args, row_n + 1);
CLIENT_OLE2.ADD_ARG (args, T);                                                --Next column
cell := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheet, 'Cells', args);
CLIENT_OLE2.DESTROY_ARGLIST (args);
colour := client_ole2.get_obj_property (cell, 'Borders');
client_ole2.set_property (colour, 'ColorIndex', 1);
client_ole2.Release_obj (colour);
CLIENT_OLE2.SET_PROPERTY (cell, 'Value', newval1);
CLIENT_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 := CLIENT_OLE2.GET_OBJ_PROPERTY (worksheet, 'UsedRange');
range_col := CLIENT_OLE2.GET_OBJ_PROPERTY (RANGE, 'Columns');
range_row := CLIENT_OLE2.GET_OBJ_PROPERTY (RANGE, 'Rows');
CLIENT_OLE2.INVOKE (range_col, 'AutoFit');
CLIENT_OLE2.INVOKE (range_row, 'AutoFit');
CLIENT_OLE2.Release_obj (RANGE);
CLIENT_OLE2.Release_obj (range_col);
CLIENT_OLE2.Release_obj (range_row);
-- Get filename and path
filename :=  'Yourexcel.xls';
-- Save as worksheet
IF Nvl (filename, '0') <> '0'
Then
CLIENT_OLE2.SET_PROPERTY (application, 'Visible', 'True');
args := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG (args, filename);
CLIENT_OLE2.INVOKE (worksheet, 'SaveAs', args);
CLIENT_OLE2.DESTROY_ARGLIST (args);
END IF;
--  CLIENT_OLE2.INVOKE( workbook ,'Close');
CLIENT_OLE2.Release_obj (worksheet);
CLIENT_OLE2.Release_obj (worksheets);
CLIENT_OLE2.Release_obj (workbook);
CLIENT_OLE2.Release_obj (workbooks);
CLIENT_OLE2.Release_obj (application);
END;