Home » D2k » Creating Excel File in Oracle Forms

Creating Excel File in Oracle Forms

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

 

1 thought on “Creating Excel File in Oracle Forms”

  1. Hi, Vinish, I hope in these strange times that you and yours are well.
    Here's my question: using "stuff like" above, e.g., OLE2 package and Oracle Forms 6i (don't ask, that's a novel, we ARE upgrading) ... how can I write an Excel file _without_ having Excel on the server / computer that is doing the writing? I understand all the looping, I've been working with OFR6i _way_ too long, and can write all kinds of SQL and PL/SQL ... but what I cannot figure out is ... "How to search" for my answer?

    So far, everything is just some variation on the above, as to using OLE2 package bits to write Excel. Well, that's all lovely. I've gotten that to work. BUT BUT BUT it only works on a PC that has _EXCEL_ installed. If the box running the program does NOT have Excel, no go, it does nothing, doesn't even give me an error code.

    I _may_ be able to install Excel on the server in question (we use RDP to a server that runs forms ... long, long story, but must keep it running for another year) ... but I would prefer NOT to install Excel. So ... how do I get it to work?

    Thanks for your help and any direction you can provide.

    Kindest regards,

    Suanne Michelle

Comments are closed.