This discussion is archived
0 Replies Latest reply: Mar 11, 2013 8:29 AM by 965378 RSS

From Oracle forms 6i to Excel/BG color

965378 Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points