Блог

Пожалуйста, помогите мне поделиться примером процедуры экспорта данных в excel из oracle forms с помощью client_ole2.

Тогда это должно сработать:

 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 =gt; source_cursor, col_cnt =gt; l_colCnt, desc_t =gt; 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) gt; 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') lt;gt; '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;