PROCEDURE Export_Excel_bySQL (data_SQL IN VARCHAR2, worksheet_name IN VARCHAR2 DEFAULT 'Hoja1') IS APPLICATION OLE2.OBJ_TYPE; WORKBOOKS OLE2.OBJ_TYPE; WORKBOOK OLE2.OBJ_TYPE; WORKSHEETS OLE2.OBJ_TYPE; WORKSHEET OLE2.OBJ_TYPE; Arglist OLE2.LIST_TYPE; CELL OLE2.OBJ_TYPE; r INTEGER; c INTEGER; i INTEGER; tit_long INTEGER; ini INTEGER; pos INTEGER; file_name_cl VARCHAR2 (32767); user_cancel EXCEPTION; Workfont OLE2.OBJ_TYPE; WorkInterior OLE2.OBJ_TYPE; SQL_cmd VARCHAR2(2000); -- Definir datos del Cursor que se ingresa por parametro conn_id EXEC_SQL.conntype; cursor_id EXEC_SQL.curstype; colNpos PLS_INTEGER; colName VARCHAR2(30); colLen PLS_INTEGER; colType PLS_INTEGER; auxCursor PLS_INTEGER; TYPE table_rec IS RECORD(title VARCHAR2(30), type VARCHAR2(10), long NUMBER ); TYPE table_def IS TABLE OF table_rec INDEX BY BINARY_INTEGER; table_exp table_def; colNumber NUMBER; colVarchar VARCHAR2(200); colDate DATE; --Inner Proc. PROCEDURE put_cell (Row_num NUMBER, Col_num NUMBER, put_value VARCHAR2, font_name VARCHAR2 DEFAULT NULL , font_size BINARY_INTEGER DEFAULT NULL , font_style VARCHAR2 DEFAULT NULL , /*here you can pass BOLD for bold, ITALIC for italic etc*/ font_color BINARY_INTEGER DEFAULT NULL ) IS BEGIN Arglist := OLE2.create_arglist; OLE2.add_arg (Arglist, row_num); OLE2.add_arg (Arglist, col_num); cell := OLE2.get_obj_property (Worksheet, 'Cells', Arglist); OLE2.destroy_arglist (Arglist); OLE2.set_property (cell, 'Value', put_value); Workfont := OLE2.get_obj_property (cell, 'Font'); WorkInterior := OLE2.get_obj_property (cell, 'Interior'); IF font_name IS NOT NULL THEN OLE2.set_property (Workfont, 'Name', font_name); END IF; IF font_size IS NOT NULL THEN OLE2.set_property (Workfont, 'Size', font_size); END IF; IF font_style IS NOT NULL THEN OLE2.set_property (Workfont, font_style, 1); END IF; IF font_color IS NOT NULL THEN OLE2.set_property (Workfont, 'ColorIndex', font_color); END IF; OLE2.RELEASE_OBJ (workinterior); OLE2.RELEASE_OBJ (workfont); OLE2.RELEASE_OBJ (cell); END; BEGIN --Open Excel Application and make it visible APPLICATION := OLE2.CREATE_OBJ ('Excel.Application'); OLE2.SET_PROPERTY (APPLICATION, 'Visible', TRUE); ------------------------------------------------------------------------- --Open a specified file where i want to export data /* Arglist := OLE2.create_arglist; OLE2.add_arg(Arglist,'C:\temp\test.xls'); WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS'); WORKBOOK := OLE2.INVOKE_OBJ(WORKBOOKS, 'Open',Arglist); OLE2.destroy_arglist(Arglist); */ /*open new file*/ WORKBOOKS := OLE2.GET_OBJ_PROPERTY (APPLICATION, 'WORKBOOKS'); WORKBOOK := OLE2.INVOKE_OBJ (WORKBOOKS, 'Add'); ------------------------------------------------------------------------- ---Initilize work sheet Worksheet := OLE2.get_obj_property(Application,'Activesheet'); OLE2.set_property(Worksheet,'Name',worksheet_name); /*Print Titles and Data*/ -- Crear y abrir un cursor y ejecutar el SQL que se envió por parámetro en data_SQL BEGIN cursor_id := EXEC_SQL.OPEN_CURSOR; EXEC_SQL.PARSE(cursor_ID, data_SQL); -- Cargar los titulos en el Excel y definir (DEFINE_COLUMN) los campos que se van a leer en el SQL c := 0; LOOP c := c + 1; BEGIN -- Obtener datos de la columna EXEC_SQL.DESCRIBE_COLUMN(cursor_ID, c, -- nro. de columna colName, -- npmbre de la columna dada por el SQL colLen, -- longitud en bytes colType -- datatype ); -- Setear Datos table_exp(c).title := colName; table_exp(c).type := colType; table_exp(c).long := colLen; -- Definir la variable que contendra el valor de esta columna luego de ejecutar el SQL IF colType = EXEC_SQL.NUMBER_TYPE THEN EXEC_SQL.DEFINE_COLUMN(cursor_ID, c, colNumber); ELSIF colType = EXEC_SQL.VARCHAR2_TYPE THEN EXEC_SQL.DEFINE_COLUMN(cursor_ID, c, colVarchar, colLen); ELSIF colType = EXEC_SQL.DATE_TYPE THEN EXEC_SQL.DEFINE_COLUMN(cursor_ID, c, colDate); ELSE RAISE EXEC_SQL.package_error; END IF; -- Grabar en el Excel el Titulo put_Cell (1, -- fila 1 de la planilla excel c, colName, font_style => 'BOLD' ); EXCEPTION WHEN EXEC_SQL.INVALID_COLUMN_NUMBER THEN EXIT; END; END LOOP; c := c - 1; EXCEPTION WHEN exec_sql.package_error THEN Message('ERROR GRL-0001 (E): error al llamar al MS-Excel. (PRUTIL01.Export_Excel_byCursor). ' || CHR(10) || exec_sql.last_error_mesg || CHR(10) || ' Rows:' || TO_CHAR(exec_sql.last_Row_Count) ); END ; -- Cargar los datos en el Excel BEGIN auxCursor := EXEC_SQL.EXECUTE(cursor_ID); r := 2; WHILE EXEC_SQL.FETCH_ROWS(cursor_ID) > 0 LOOP FOR i IN 1 .. c LOOP -- Determinar el datatype del dato a leer y exportar a Excel IF table_exp(i).type = EXEC_SQL.NUMBER_TYPE THEN EXEC_SQL.COLUMN_VALUE(cursor_ID, i, colNumber); put_Cell (r, i, colNumber); ELSIF table_exp(i).type = EXEC_SQL.VARCHAR2_TYPE THEN EXEC_SQL.COLUMN_VALUE(cursor_ID, i, colVarchar); put_Cell (r, i, colVarchar); ELSIF table_exp(i).type = EXEC_SQL.DATE_TYPE THEN EXEC_SQL.COLUMN_VALUE(cursor_ID, i, colDate); put_Cell (r, i, colDate); ELSE NULL; END IF; END LOOP; r := r + 1; END LOOP; EXCEPTION WHEN exec_sql.package_error THEN Message('ERROR GRL-0001 (E): error al llamar al MS-Excel. (PRUTIL01.Export_Excel_byCursor). ' || CHR(10) || exec_sql.last_error_mesg || CHR(10) || ' Rows:' || TO_CHAR(exec_sql.last_Row_Count) ); END; EXEC_SQL.CLOSE_CURSOR(cursor_ID); /* -- Save the Excel file created Arglist := OLE2.Create_Arglist; OLE2.Add_Arg (Arglist, 'c:\temp\test.xls'); OLE2.Invoke (workbook, 'Save', Arglist); OLE2.Destroy_Arglist (Arglist); */ -- release workbook OLE2.RELEASE_OBJ (worksheet); OLE2.RELEASE_OBJ (workbook); OLE2.RELEASE_OBJ (workbooks); OLE2.RELEASE_OBJ (application); END;