0 Replies Latest reply: Mar 11, 2013 10:29 AM by 965378 RSS

    From Oracle forms 6i to Excel/BG color

    965378
      hey All.
      I'm trying to export data from a datablock to excel.
      I've used a query I've assembled from many forums. but instead of coloring only the cell, this query is color all the excel Sheet. I'll post the procedure. Any idea?

      PROCEDURE TEST IS
      i_reporttitle VARCHAR2(200) := 'My excel';
      r number := 2;
      c number := 1;--column position
      i_orientation VARCHAR2(200) := '1';
      i number :=1;--row number
      /* definiton for commonly used variables and constants and exceptions */
      ExcelID ole2.obj_type;
      ExcelWorkBooksID ole2.obj_type;
      ExcelWorkBookID ole2.obj_type;
      ExcelWorkSheetsID ole2.obj_type;
      ExcelWorkSheetID ole2.obj_type;
      ExcelCellID ole2.obj_type;
      ExcelFontID ole2.obj_type;
      ExcelPageSetupID ole2.obj_type;
      ExcelArgs ole2.list_type;

      V_CEL OLE2.OBJ_TYPE;--
      V_INTERIOR ole2.obj_type;--


      BEGIN
      ExcelID := ole2.create_obj('Excel.Application');
      ExcelWorkBooksID := ole2.get_obj_property(ExcelID, 'Workbooks');
      ExcelWorkBookID := ole2.invoke_obj(ExcelWorkBooksID, 'Add');
      ExcelWorkSheetsID := ole2.get_obj_property(ExcelWorkBookID, 'Worksheets');
      ExcelWorkSheetID := ole2.invoke_obj(ExcelWorkSheetsID, 'Add');
      ExcelPageSetupID := ole2.get_obj_property(ExcelWorkSheetID, 'PageSetup');

      V_CEL     := ole2.get_obj_property(ExcelWorkSheetID, 'cells', ExcelArgs);
      V_CEL     := ole2.get_obj_property(ExcelCellId, 'cells', ExcelArgs);
      V_INTERIOR := ole2.get_obj_property(V_CEL,'Interior');--
      ------------------FILL THE HEADERS---------------------
      ole2.set_property(ExcelPageSetupID, 'Orientation',i_orientation);
      ole2.release_obj(ExcelPageSetupID);
      GO_BLOCK('TEST_BLK');

      LOOP
           
      ExcelArgs := ole2.create_arglist;
      ole2.add_arg(ExcelArgs,i);
      ole2.add_arg(ExcelArgs,c);
      ExcelCellId := ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
      ole2.destroy_arglist(ExcelArgs);
      ole2.set_property(ExcelCellId, 'Value', :TEST_BLK.ITEM1);
      ole2.set_property(V_INTERIOR,'ColorIndex',3); should be red
      ExcelFontId := ole2.get_obj_property(ExcelCellId, 'Font');
      ole2.set_property(ExcelFontId, 'Bold', 'True');
      ole2.set_property(ExcelFontId, 'Size', '10');
      ole2.release_obj(ExcelFontId);
      ole2.release_obj(ExcelCellId);

      ole2.release_obj(v_interior);--
      exit when :SYSTEM.LAST_RECORD = 'TRUE';
      next_record;
      i := i+1;
      end loop;
      ole2.set_property(ExcelID, 'Visible','TRUE');-- ... and release the allocated resources because they are no longer used by forms

      ole2.release_obj(ExcelWorkSheetID);
      ole2.release_obj(ExcelWorkSheetsID);
      ole2.release_obj(ExcelWorkBookID);
      ole2.release_obj(ExcelWorkBooksID);
      ole2.release_obj(ExcelID);
      END;

      and btw i have no idea how this query works i just copy pasted paragraphs from here and there.