Exportar a Excel

Siento no poder ayudarte en lo referente a programación para exportar datos a excel, pero es que no soy programador. Tengo nociones, evidentemente de programación con forms, pero no ejerzo de profesional, con lo que te invito a que le plantees la pregunta a un experto en desarrollo; en esta misma sección, donde me encontraste a mi hay expertos que se dedican a eso concretamente.
Espero no sea una molestia, pero es mejor para ti y mejor para agilizar la solución de tu duda.

Te adjunto una nota de Oracle espero que te sirva:
Introduction ============ This article explains the basic concepts of OLE automation and in particular how to use the OLE automation facilities within Oracle Forms V4.5 to automate Microsoft Excel. Code examples are included. Basic OLE Automation principles =============================== Objects are the fundamental components of OLE applications. Every element of an OLE server application can be represented as an object. Each of these objects is defined by its properties (physical and logical characteristics) and its methods (actions which the object can perform). OLE server applications expose the properties and methods of their component objects to other Windows applications. OLE client applications can programmatically manipulate OLE server applications through reading/writing their exposed properties and invoking their exposed methods. This process of 'remote control' of OLE server applications from OLE client applications is known as 'OLE Automation'. The original OLE ('Object Linking and Embedding') specification, created in 1991 concentrated on the creation of compound documents via the linking or embedding of server application documents inside container applications (a process from which OLE gained its original and now obsolete title). The much broader OLE2 specification introduced the concept of OLE automation along with a number of other extensions to the original OLE specification. Only applications supporting the OLE2 specification can therefore participate in OLE automation. Separate aspects of the OLE2 specification cover OLE automation client and OLE automation server functionality so an application must support the respective aspects of the OLE2 specification to function as an OLE automation client, OLE automation server or both. Before writing code to perform OLE automation, it is necessary for an application developer to understand the following things: o The object classes exposed by the OLE automation server and the relationships between them (the OLE automation server's 'Object Model'). o The properties of the OLE automation server's objects, their datatypes and valid values o The methods of the OLE automation server's objects, their syntax and arguments o The methods used by the OLE automation client to access the methods and properties of the OLE automation server's objects This article will describe the Object Model of Excel and the methods used by Oracle Forms to access the methods and properties of Excel's objects. For detailed information on the specific properties and methods of Excel's objects refer to the documentation provided by Microsoft. The Microsoft Excel Object Model ================================ Microsoft Excel can function as an OLE automation client or OLE automation server. This section describes the Object Model which enables Excel to be used as an OLE automation server. The simplest way to examine Excel's Object Model is to bring up the Object Browser within Excel itself. To do this, select/create a Macro Module and then select 'Object Browser' from the 'View' menu. Once the Object Browser is displayed, select the Excel Object Library from the dropdown listbox. The Object Browser displays the available object classes on the left and the properties/methods of a selected object class on the right. To view the definition of an object class, including an indication of where it fits into the object model, select it from the left hand list and press the help button. To view a definition of a method or property select it from the right hand list and press the help button. Each object class belongs to one of three categories: o individual object - an individual element of an Excel application (e.g. Workbook, Worksheet, Cell). o collection object - an object containing several other objects of the same type (e.g. the Workbooks collection contains all the open Workbook objects). The collection object can use the Add method to create new objects within its own collection. o metacollection object - a collection object containing objects of more than one type. (e.g. the DrawingObjects collection contains all the graphic objects (Rectangle, Oval etc.) and control objects (Button, Editbox etc.) on a given worksheet). There are a vast number of object classes available within Excel, ranging from the top level Application object down to a Font object defining the font used for a textbox. The structure of Excel's object model is therefore quite complex and extends down many levels. The diagram below illustrates the relationships between the significant object classes at the topmost levels of the object model. Collection object classes are indicated in uppercase text. Note that an object class can appear at more than one place in the object model (i.e. the Excel object model is not a truly hierarchical structure). application ADDINS addin NAMES name DIALOGS dialog MENUBARS menubar MENUS menu MENUITEMS menuitem TOOLBARS toolbar TOOLBARBUTTONS toolbarbutton WINDOWS window WORKBOOKS workbook CHARTS chart DIALOGSHEETS dialogsheet MODULES module NAMES name STYLES style WINDOWS window WORKSHEETS worksheet NAMES name range CHARTOBJECTS chartobject CHARTS chart SERIESCOLLECTIONS seriescollection POINTS point For further detail on the Excel object model refer to the documentation supplied by Microsoft. Oracle Forms and OLE Automation =============================== Oracle Forms can operate as an OLE automation client only. OLE automation client functionality is implemented in Oracle Forms through a number of built-in PL/SQL procedures and functions contained in the OLE2 PL/SQL package. The OLE2 PL/SQL package provides a PL/SQL API for creating OLE automation server objects and accessing the properties and methods of these objects. The OLE2 PL/SQL package defines two additional PL/SQL datatypes which are used by the OLE2 built-ins: OBJ_TYPE A handle to an OLE object LIST_TYPE A handle to an OLE argument list Each of the PL/SQL procedures and functions in the OLE2 package is described below along with its PL/SQL specification: Object Management CREATE_OBJ Creates an OLE object and returns an object handle. CREATE_OBJ(OBJECT IN VARCHAR2) RETURN OBJ_TYPE RELEASE_OBJ Deallocates all resources for an OLE object created by CREATE_OBJ and destroys the object handle. RELEASE_OBJ(OBJECT IN OBJ_TYPE) Object Property Access GET_CHAR_PROPERTY Reads a character property of an OLE object. GET_CHAR_PROPERTY(OBJECT IN OBJ_TYPE, PROPERTY IN VARCHAR2, ARGLIST IN LIST_TYPE) RETURN VARCHAR2 GET_NUM_PROPERTY Reads a number property of an OLE object. GET_NUM_PROPERTY(OBJECT IN OBJ_TYPE, PROPERTY IN VARCHAR2, ARGLIST IN LIST_TYPE) RETURN NUMBER GET_OBJ_PROPERTY Reads an object property of an OLE object. GET_OBJ_PROPERTY(OBJECT IN OBJ_TYPE, PROPERTY IN VARCHAR2, ARGLIST IN LIST_TYPE) RETURN OBJ_TYPE SET_PROPERTY Sets the value of a number or character property of an OLE object. SET_PROPERTY(OBJECT IN OBJ_TYPE, PROPERTY IN VARCHAR2, VALUE IN NUMBER, ARGLIST IN LIST_TYPE) or SET_PROPERTY(OBJECT IN OBJ_TYPE, PROPERTY IN VARCHAR2, VALUE IN VARCHAR2, ARGLIST IN LIST_TYPE) Object Method Execution INVOKE Executes a method of an OLE object which returns nothing. INVOKE(OBJECT IN OBJ_TYPE, METHOD IN VARCHAR2, ARGLIST IN LIST_TYPE) INVOKE_CHAR Executes a method of an OLE object which returns a character string. INVOKE(OBJECT IN OBJ_TYPE, METHOD IN VARCHAR2, ARGLIST IN LIST_TYPE) RETURN VARCHAR2 INVOKE_NUM Executes a method of an OLE object which returns a number. INVOKE(OBJECT IN OBJ_TYPE, METHOD IN VARCHAR2, ARGLIST IN LIST_TYPE) RETURN NUMBER INVOKE_OBJ Executes a method of an OLE object which returns an object handle. INVOKE(OBJECT IN OBJ_TYPE, METHOD IN VARCHAR2, ARGLIST IN LIST_TYPE) RETURN OBJ_TYPE Argument List Management CREATE_ARGLIST Creates an argument list to be used by an invoked method and returns an argument list handle. CREATE_ARGLIST RETURN LIST_TYPE ADD_ARG Appends a number or character string argument to an argument list. ADD_ARG(LIST IN LIST_TYPE, VALUE IN NUMBER) or ADD_ARG(LIST IN LIST_TYPE, VALUE IN VARCHAR2) DESTROY_ARGLIST Destroys an argument list created by CREATE_ARGLIST. DESTROY_ARGLIST(LIST IN LIST_TYPE) Exception Handling LAST_EXCEPTION Returns the most recent OLE exception code. Some examples of conditions when OLE exceptions are raised are o sending OLE commands to an inactive server application o invoking non-existent methods LAST_EXCEPTION RETURN NUMBER The OLE2 PL/SQL package can be used to automate an independently executing OLE automation server application. It can also be used to automate an embedded or linked OLE object associated with an OLE container item in an Oracle Forms application. The remainder of this article will look at automating Excel using both methods. Automating an independently executing Excel application ======================================================= Before any OLE automation to Excel can be performed, the Excel application must be started. This is achieved through the creation of an OLE object representing the Excel application. Since the application object is the topmost object in the Excel object model, the creation of the application object (and an object handle for it) establishes an entry point to the Excel object model from which OLE automation can begin. If you require Excel to appear on the screen after it has been started, the Visible property of the application object should be set to True. The following PL/SQL example creates an Excel application object, obtains an object handle to it and displays Excel on the screen: DECLARE application OLE2.OBJ_TYPE; BEGIN application:=OLE2.CREATE_OBJ('Excel.Application'); OLE2.SET_PROPERTY(application, 'Visible', 'True'); END; At this point there are no open documents in Excel and OLE automation is restricted only to the properties and methods of the application object itself. To perform more extensive OLE automation, objects further down the Excel object model must be created. The following PL/SQL example extends the previous example by creating a new Workbook, creating a new Worksheet within the Workbook, obtaining an object handle to a specific cell in the new Worksheet and setting the cell contents to a specific value ( comments are preceded by -- ) : DECLARE -- Declare the OLE objects application OLE2.OBJ_TYPE; workbooks OLE2.OBJ_TYPE; workbook OLE2.OBJ_TYPE; worksheets OLE2.OBJ_TYPE; worksheet OLE2.OBJ_TYPE; cell OLE2.OBJ_TYPE; -- Declare handles to OLE argument lists args OLE2.LIST_TYPE; BEGIN -- Start Excel and make it visible application:=OLE2.CREATE_OBJ('Excel.Application'); OLE2.SET_PROPERTY(application, 'Visible', 'True'); -- Return object handle to the Workbooks collection workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks'); -- Add a new Workbook object to the Workbooks collection workbook:=OLE2.INVOKE_OBJ(workbooks,'Add'); -- Return object handle to the Worksheets collection for the -- Workbook worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets'); -- Add a new Worksheet to the Worksheets collection worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add'); -- Return object handle to cell A1 on the new Worksheet args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, 1); OLE2.ADD_ARG(args, 1); cell:=OLE2.INVOKE_OBJ(worksheet, 'Cells', args); OLE2.DESTROY_ARGLIST(args); -- Set the contents of the cell to 'Hello Excel!' OLE2.SET_PROPERTY(cell, 'Value', 'Hello Excel!'); -- Release the OLE objects OLE2.RELEASE_OBJ(cell); OLE2.RELEASE_OBJ(worksheet); OLE2.RELEASE_OBJ(worksheets); OLE2.RELEASE_OBJ(workbook); OLE2.RELEASE_OBJ(workbooks); OLE2.RELEASE_OBJ(application); END; The following points are worth noting : o To create object handles to a Workbook or Worksheet object it is necessary to create object handles to the associated collection object and then invoke the 'Add' method for this collection object. Due to the large number of collection objects in Excel's Object Model this is a requirement for many objects. o To create an object handle to a specific cell it is necessary to add the row and column numbers of the referenced cell to an argument list and use this argument list in the Cells method of the Worksheet object. o It is important to release all objects and argument lists as early as possible to minimise Windows resource usage and at the very least these objects should be released at the end of the procedure. Generally, the best approach for developing PL/SQL code to perform more sophisticated OLE automation to Excel is to use Excel's own macro recorder to record the macro commands for a given task and translate these macro commands into the appropriate PL/SQL function and procedure calls. The following PL/SQL example fetches employee names and salaries from the EMP table, populates a range of cells on an Excel worksheet and creates a chart of the data in the Excel range. The macro commands required to create the chart were recorded using Excel's macro recorder and are indicated in the comments. DECLARE -- Declare handles to OLE objects application OLE2.OBJ_TYPE; workbooks OLE2.OBJ_TYPE; workbook OLE2.OBJ_TYPE; worksheets OLE2.OBJ_TYPE; worksheet OLE2.OBJ_TYPE; cell OLE2.OBJ_TYPE; chartobjects OLE2.OBJ_TYPE; chartobject OLE2.OBJ_TYPE; chart OLE2.OBJ_TYPE; seriescollection OLE2.OBJ_TYPE; series OLE2.OBJ_TYPE; charttitle OLE2.OBJ_TYPE; -- Declare handles to OLE argument lists args OLE2.LIST_TYPE; -- Declare a SQL cursor to be used to fetch the records from -- the database. CURSOR C1 IS select ename, sal from emp; -- Declare the PL/SQL variables which will hold the data -- returned from the database. ename varchar2(30); sal number(8,2); BEGIN -- Create handle to application object application:=OLE2.CREATE_OBJ('Excel.Application'); -- Create a Workbooks collection and add new Workbook to -- Workbooks collection workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks'); workbook:=OLE2.INVOKE_OBJ(workbooks,'Add'); -- Create a Worksheets collection and add new Worksheet to -- Worksheets collection worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets'); worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add'); -- Execute query to fetch employee records OPEN C1; -- Fetch each employee record and pass values of employee name -- and salary into Excel (employee names in first column of -- worksheet and salaries in second column). FOR ctr IN 1..14 LOOP -- Fetch one record and populate PL/SQL variables FETCH C1 INTO ename, sal; -- Create handle to cell in column 1 of appropriate row in -- worksheet. (The arguments to the Cells method are the row -- number and column number of the cell). args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, ctr); OLE2.ADD_ARG(args, 1); cell:=OLE2.INVOKE_OBJ(worksheet, 'Cells', args); OLE2.DESTROY_ARGLIST(args); -- Put value of employee name into this cell OLE2.SET_PROPERTY(cell, 'Value', ename); -- Create handle to cell in column 2 of appropriate row in -- worksheet. args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, ctr); OLE2.ADD_ARG(args, 2); cell:=OLE2.INVOKE_OBJ(worksheet, 'Cells', args); OLE2.DESTROY_ARGLIST(args); -- Put value of the employee salary into this cell OLE2.SET_PROPERTY(cell, 'Value', sal); END LOOP; -- Create a 3D bar chart of the data in the range A1 to B14. -- The recorded Excel macro code to perform this is: -- -- ActiveSheet.ChartObjects.Add(150, 0, 500, 250).Select -- ActiveChart.SeriesCollection.Add -- Source:="Sheet17!$A$1:$B$14", _ -- Rowcol:=xlColumns, SeriesLabels:=False, -- CategoryLabels:=True, _ -- Replace:=False -- ActiveChart.HasTitle = True -- ActiveChart.ChartTitle.Text = "Employee Salaries" -- ActiveChart.Type = xl3DColumn -- Create ChartObjects collection on the active worksheet chartobjects:=OLE2.INVOKE_OBJ(worksheet,'ChartObjects'); -- Add a new chartobject to this collection. The arguments to the -- Add method are the X,Y coordinates of the topleft and -- bottomright pixel coordinates of the chartobject args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, 150); OLE2.ADD_ARG(args, 0); OLE2.ADD_ARG(args, 500); OLE2.ADD_ARG(args, 250); chartobject:=OLE2.INVOKE_OBJ(chartobjects,'Add', args); OLE2.DESTROY_ARGLIST(args); -- Create handle to the Chart within the ChartObject chart:=OLE2.GET_OBJ_PROPERTY(chartobject,'Chart'); -- Create handle to the SeriesCollection within the Chart seriescollection:=OLE2.INVOKE_OBJ(chart,'SeriesCollection'); -- Add a new data Series to the SeriesCollection. -- Creating a new data series displays the data on the chart. args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, 'Sheet17!$A$1:$B$14' ); -- Excel constant xlColumns=2 OLE2.ADD_ARG(args, 2 ); -- Excel constant False=0 OLE2.ADD_ARG(args, 0 ); -- Excel constant True=1 OLE2.ADD_ARG(args, 1 ); OLE2.ADD_ARG(args, 0 ); series:=OLE2.INVOKE_OBJ(seriescollection,'Add', args ); OLE2.DESTROY_ARGLIST(args); -- Create a title object on the chart OLE2.SET_PROPERTY(chart,'HasTitle', 1); -- Get the object handle for the title object charttitle:=OLE2.GET_OBJ_PROPERTY(chart,'ChartTitle'); -- Set the text for the chart title OLE2.SET_PROPERTY(charttitle,'Text','Employee Salaries'); -- Set chart type to 3D bar chart (Excel constant xl3DColumn=-4100) OLE2.SET_PROPERTY(chart,'Type', -4100); -- Enable user to view the Excel application to see results. OLE2.SET_PROPERTY(application, 'Visible', 'True'); -- Release all OLE object handles OLE2.RELEASE_OBJ(cell); OLE2.RELEASE_OBJ(charttitle); OLE2.RELEASE_OBJ(seriescollection); OLE2.RELEASE_OBJ(chart); OLE2.RELEASE_OBJ(chartobject); OLE2.RELEASE_OBJ(chartobjects); OLE2.RELEASE_OBJ(worksheet); OLE2.RELEASE_OBJ(worksheets); OLE2.RELEASE_OBJ(workbook); OLE2.RELEASE_OBJ(workbooks); OLE2.RELEASE_OBJ(application); END; The following points are worth noting : o In order to improve performance the Visible property of the application is only set to True at the end to avoid unnecessary screen redraws. o When using an argument list with a different set of arguments it is necessary to destroy and recreate the argument list. Failing to do this will result in a new set of arguments being appended to an old set. o There are three different ways of obtaining an object handle to an object, CREATE_OBJ, INVOKE_OBJ and GET_OBJ_PROPERTY. Which of these to use depends on the object being referenced. o Excel macros use a number of predefined numeric constants (e.g. xl3Dcolumn, xlColumns, True, False). These cannot be used as arguments in an argument list. Instead you must use the numeric values of these constants. To get a list of numeric constants, select Constants in the Excel Object Browser. To find out the numeric value of a specific Excel constant, run the following Excel macro statement: MsgBox <constantname> (e.g. MsgBox xl3Dcolumn returns -4100) o The ADD_ARG procedure can only add number or character arguments to an argument list. This means that you cannot invoke any OLE server methods which take object handles as arguments. In the charting example it would be easier to use the ChartWizard method for the Chart object to define the data series source, chart type, title etc. together. However, since the ChartWizard method requires an object argument to indicate the range of cells to be charted it is not possible to invoke it using OLE automation from Oracle Forms. Instead, it is necessary to manually construct the chart and record separate macro commands to define the data series, chart type, title etc. It is not always necessary to make the Excel application visible when performing OLE automation. The following example invokes Excel in the background to perform a calculation and return a result: DECLARE -- Declare handles to OLE objects application OLE2.OBJ_TYPE; -- Declare handles to OLE argument lists args OLE2.LIST_TYPE; BEGIN application:=OLE2.CREATE_OBJ('Excel.Application'); -- Create an argument list consisting of the value of field1 and -- and field2 to be used as the arguments to the Power function. args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, :Block1.Field1); OLE2.ADD_ARG(args, :Block1.Field2); -- Invoke the Power function in Excel and return the numerical -- result back into Field3. :Block1.Field3:=OLE2.INVOKE_NUM(application, 'Power', args); -- Destroy the argument list OLE2.DESTROY_ARGLIST(args); -- Release the OLE2 object handle OLE2.RELEASE_OBJ(application); END; If the tasks you want to perform in Excel are completely self-contained and do not require any parameters to be passed in from Oracle Forms, it may be preferable to create a VBA macro within Excel itself to perform these tasks and invoke the macro via OLE automation (VBA, or Visual Basic for Applications, is Excel's own macro programming language). The following example opens the workbook "DEMO.XLS" and executes the VBA macro called "MyMacro": DECLARE application ole2.obj_type; workbooks ole2.obj_type; workbook ole2.obj_type; args ole2.list_type; BEGIN application:=ole2.create_object('Excel.Application'); ole2.set_property(application, 'Visible', 'True'); workbooks:=ole2.GET_OBJ_PROPERTY(application, 'Workbooks'); args:=ole2.create_arglist; ole2.add_arg(args, 'C:\My Documents\DEMO.XLS'); workbook:=ole2.invoke_obj(workbooks,'Open',args); ole2.destroy_arglist(args); args:=ole2.create_arglist; ole2.add_arg(args, 'MyMacro'); ole2.invoke(application, 'Run', args); ole2.destroy_arglist(args); ole2.release_obj(workbook); ole2.release_obj(workbooks); ole2.release_obj(application); END; Automating Embedded or Linked OLE2 objects ========================================== The original concept behind OLE concerned the embedding or linking of objects created by an OLE server application inside a document created by a different application (referred to as an OLE container application). It is possible to combine this aspect of OLE with OLE automation to automate an embedded or linked object. Oracle Forms includes a special OLE container item into which an OLE object can be embedded or linked. The OLE object classes which can be embedded or linked into an OLE container are registered in the Windows OLE registration database when an OLE server application is installed. The 'OLE Class' property of an Oracle Forms OLE container indicates which object class it contains and must be one of those listed in the OLE registration database. The 'OLE Tenant Types' property indicates whether the OLE container holds an embedded or linked OLE object. It is possible to automate an embedded or linked Excel worksheet using the OLE2 PL/SQL package in conjunction with the following PL/SQL procedures from the separate PL/SQL built-in package FORMS_OLE: ACTIVATE_SERVER Activates an OLE server application associated with an OLE container item and prepares it for OLE automation. Takes the name or item id of an Oracle Forms OLE container item as an argument. GET_INTERFACE_POINTER Returns an OLE object handle to the OLE object associated with an OLE container item. Takes the name or item id of an Oracle Forms OLE container item as an argument. The advantage of using the FORMS_OLE procedures is that the entry point to the Excel object model does not have to be the application level. This avoids having to create object handles for objects in the Excel object model between the application object and the object being automated, resulting in simpler code. The following example illustrates how these FORMS_OLE procedures can be used in conjunction with the OLE2 procedures. This example changes the contents of cell A1 of an Excel worksheet embedded in the OLE container item called 'EXCEL_SHEET' on block 'BLOCK1' of an Oracle Forms application: DECLARE worksheet OLE2.OBJ_TYPE; cell OLE2.OBJ_TYPE; args OLE2.LIST_TYPE; BEGIN FORMS_OLE.ACTIVATE_SERVER('BLOCK1.EXCEL_SHEET'); worksheet:=FORMS_OLE.GET_INTERFACE_POINTER('BLOCK1.EXCEL_SHEET'); args:=OLE2.CREATE_ARGLIST; OLE2.ADD_ARG(args, 1); OLE2.ADD_ARG(args, 1); cell:=OLE2.INVOKE_OBJ(worksheet, 'Cells', args); OLE2.DESTROY_ARGLIST(args); OLE2.SET_PROPERTY(cell, 'Value', 'Hello Excel!'); OLE2.RELEASE_OBJ(cell); OLE2.RELEASE_OBJ(worksheet); END;

This Excel Oracle adapter allows users to replace the aftermarket stereo with no cutting and soldering and in the meantime, it is used to rewire all plugs. Mainly all students are buying term papers online at bestessaytips and it is better to expedite the solution of your doubts.