Creating Excel File in Oracle Forms

最新推荐文章于 2025-09-09 08:37:13 发布

weixin_34138056

最新推荐文章于 2025-09-09 08:37:13 发布

阅读量110

点赞数

文章标签：数据库

本文介绍了一种使用Oracle Forms和Ole2包创建Excel文件的方法。通过传递SQL查询字符串到指定过程，可以自动生成包含查询结果的Excel文档，并对单元格进行格式设置。

Below is the example to create an excel file in Oracle Forms.

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