Home » Developer & Programmer » Forms » Populating the data in respective excel columns
| Populating the data in respective excel columns [message #400138] |
Sun, 26 April 2009 09:09  |
hamdard
Messages: 37
Registered: July 2005
|
Member |
|
|
hi,
I have this procedure to populate the data from forms to excel. This is working fine but I have one problem. In the arguments, I'm passing the title, select statement and for columns, I'm passing 'Y' or 'N'. based on 'Y' and 'N', the column will be shown or not shown in the excel sheet. e.g: if I pass 'Y' for empno, 'Y' for ename, 'N' for sal and 'Y' for deptno then in the excel sheet, empno will be showm on first column, ename on 2nd column, 3rd column will be empty and deptno on 4th column.
Now because third column is empty, I want to move deptno column(4th column) to third column.
This can be achieved if I assign a correct values to variable c. But how to get that exact value, I'm not able to do this. I tried a lot but not successful.
Anybody please help. This is the procedure I'm using
PROCEDURE DYNAMIC_CURSOR (PC$REP_HEADING IN VARCHAR2, PC$SEL_STATEMENT IN VARCHAR2, P_EMPNO IN VARCHAR2, P_ENAME IN VARCHAR2,
P_SAL IN VARCHAR2, P_DEPTNO IN VARCHAR2)
IS
cursor_number EXEC_SQL.CursType;
-- Variables for the data to be returned into
v_empno number;
v_ename varchar2(50);
v_sal number;
v_deptno number;
-- Control variables
LN$count Number;
LC$sql_order Varchar2(3000);
i_reporttitle VARCHAR2(200) := PC$REP_HEADING;
r number := 2;
c number := 1;
i_orientation VARCHAR2(200) := '2';
i number :=3;
/* definiton for commonly used variables and constants and exceptions */
ExcelID client_ole2.obj_type;
ExcelWorkBooksID client_ole2.obj_type;
ExcelWorkBookID client_ole2.obj_type;
ExcelWorkSheetsID client_ole2.obj_type;
ExcelWorkSheetID client_ole2.obj_type;
ExcelCellID client_ole2.obj_type;
ExcelFontID client_ole2.obj_type;
ExcelPageSetupID client_ole2.obj_type;
ExcelArgs client_ole2.list_type;
-- Define th ecolors if they are to be used
colour client_ole2.obj_type;
myBlue CONSTANT number(8) := 16711680; --FF0000
myGreen CONSTANT number(8) := 65280; --00FF00
myRed CONSTANT number(8) := 255; --0000FF
myDkGreen CONSTANT number(8) := 32768; --008000
myBlack CONSTANT number(8) := 0; --000000
BEGIN
ExcelID := client_ole2.create_obj('Excel.Application');
ExcelWorkBooksID := client_ole2.get_obj_property(ExcelID, 'Workbooks');
ExcelWorkBookID := client_ole2.invoke_obj(ExcelWorkBooksID, 'Add');
ExcelWorkSheetsID := client_ole2.get_obj_property(ExcelWorkBookID, 'Worksheets');
ExcelWorkSheetID := client_ole2.invoke_obj(ExcelWorkSheetsID, 'Add');
ExcelPageSetupID := client_ole2.get_obj_property(ExcelWorkSheetID, 'PageSetup');
client_ole2.set_property(ExcelPageSetupID, 'Orientation',i_orientation);
client_ole2.release_obj(ExcelPageSetupID);
/************************************************************************************************************
*********** Set the Report Title, Color, Cell Font etc *****************
************************************************************************************************************/
--This is the Report Heading
ExcelArgs := client_ole2.create_arglist;
client_ole2.add_arg(ExcelArgs,1);
client_ole2.add_arg(ExcelArgs,8);
ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
client_ole2.destroy_arglist(ExcelArgs);
client_ole2.set_property(ExcelCellId, 'Value', i_reporttitle);
ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
client_ole2.set_property(ExcelFontId, 'Bold', 'True');
client_ole2.set_property(ExcelFontId, 'Size', '15');
client_ole2.set_property(ExcelFontId, 'Color', myGreen);
colour:=client_ole2.get_obj_property(ExcelCellId, 'Interior');
client_ole2.set_property(colour, 'ColorIndex', 15);
client_ole2.release_obj(ExcelFontId);
client_ole2.release_obj(ExcelCellId);
for j in 7..12 loop
-- Set the Color of Row1, Col7
ExcelArgs := client_ole2.create_arglist;
client_ole2.add_arg(ExcelArgs,1);
client_ole2.add_arg(ExcelArgs,j);
ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
client_ole2.destroy_arglist(ExcelArgs);
ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
client_ole2.set_property(ExcelFontId, 'Bold', 'True');
client_ole2.set_property(ExcelFontId, 'Size', '15');
client_ole2.set_property(ExcelFontId, 'Color', myGreen); -- 16711680
colour:=client_ole2.get_obj_property(ExcelCellId, 'Interior');
client_ole2.set_property(colour, 'ColorIndex', 15);
client_ole2.release_obj(ExcelFontId);
client_ole2.release_obj(ExcelCellId);
end loop;
/************************************************************************************************************
*********** Report Title Finished Here *****************
************************************************************************************************************/
-- Open the cursor --
cursor_number := Exec_SQL.Open_cursor;
-- build the complete SQL order --
LC$sql_order := PC$SEL_STATEMENT;
-- Parse the SQL order --
EXEC_SQL.PARSE(cursor_number, LC$sql_order);
-- Define the columns for the data to be returned --
EXEC_SQL.DEFINE_COLUMN(cursor_number,1,v_empno);
EXEC_SQL.DEFINE_COLUMN(cursor_number,2, v_ename, 50);
EXEC_SQL.DEFINE_COLUMN(cursor_number,3,v_sal);
EXEC_SQL.DEFINE_COLUMN(cursor_number,4, v_deptno);
-- Execute the Cursor --
LN$count := EXEC_SQL.EXECUTE(cursor_number);
-- Loop and fetch each row from the result set --
While EXEC_SQL.FETCH_ROWS(cursor_number) > 0 Loop
EXEC_SQL.COLUMN_VALUE(cursor_number,1,v_empno);
EXEC_SQL.COLUMN_VALUE(cursor_number,2,v_ename);
EXEC_SQL.COLUMN_VALUE(cursor_number,3,v_sal);
EXEC_SQL.COLUMN_VALUE(cursor_number,4,v_deptno);
if P_EMPNO = 'Y' then
ExcelArgs := client_ole2.create_arglist;
client_ole2.add_arg(ExcelArgs,i);
client_ole2.add_arg(ExcelArgs,c);
ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
client_ole2.destroy_arglist(ExcelArgs);
client_ole2.set_property(ExcelCellId, 'Value', v_empno);
ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
client_ole2.set_property(ExcelFontId, 'Bold', 'True');
client_ole2.set_property(ExcelFontId, 'Size', '10');
client_ole2.release_obj(ExcelFontId);
client_ole2.release_obj(ExcelCellId);
end if;
if p_ename = 'Y' then
ExcelArgs := client_ole2.create_arglist;
client_ole2.add_arg(ExcelArgs,i);
client_ole2.add_arg(ExcelArgs,c+1);
ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
client_ole2.destroy_arglist(ExcelArgs);
client_ole2.set_property(ExcelCellId, 'Value', v_ename);
ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
client_ole2.set_property(ExcelFontId, 'Bold', 'True');
client_ole2.set_property(ExcelFontId, 'Size', '10');
client_ole2.release_obj(ExcelFontId);
client_ole2.release_obj(ExcelCellId);
end if;
if p_sal = 'Y' then
ExcelArgs := client_ole2.create_arglist;
client_ole2.add_arg(ExcelArgs,i);
client_ole2.add_arg(ExcelArgs,c+2);
ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
client_ole2.destroy_arglist(ExcelArgs);
client_ole2.set_property(ExcelCellId, 'Value', v_sal);
ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
client_ole2.set_property(ExcelFontId, 'Bold', 'True');
client_ole2.set_property(ExcelFontId, 'Size', '10');
client_ole2.release_obj(ExcelFontId);
client_ole2.release_obj(ExcelCellId);
end if;
if p_deptno = 'Y' then
ExcelArgs := client_ole2.create_arglist;
client_ole2.add_arg(ExcelArgs,i);
client_ole2.add_arg(ExcelArgs,c+3);
ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
client_ole2.destroy_arglist(ExcelArgs);
client_ole2.set_property(ExcelCellId, 'Value', v_deptno);
ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
client_ole2.set_property(ExcelFontId, 'Bold', 'True');
client_ole2.set_property(ExcelFontId, 'Size', '10');
client_ole2.release_obj(ExcelFontId);
client_ole2.release_obj(ExcelCellId);
end if;
i := i + 1;
End Loop ;
-- Close the cursors
EXEC_SQL.CLOSE_CURSOR(cursor_number);
client_ole2.set_property(ExcelID, 'Visible','TRUE');
-- ... and release the allocated resources because they are no longer used by forms
client_ole2.release_obj(ExcelWorkSheetID);
client_ole2.release_obj(ExcelWorkSheetsID);
client_ole2.release_obj(ExcelWorkBookID);
client_ole2.release_obj(ExcelWorkBooksID);
client_ole2.release_obj(ExcelID);
EXCEPTION
When EXEC_SQL.INVALID_CONNECTION Then
message('Unexpected Invalid Connection error from EXEC_SQL');
When EXEC_SQL.PACKAGE_ERROR Then
message('Unexpected error from EXEC_SQL: '||to_char(EXEC_SQL.LAST_ERROR_CODE)|| EXEC_SQL.LAST_ERROR_MESG);
If EXEC_SQL.IS_OPEN(cursor_number) Then
EXEC_SQL.CLOSE_CURSOR(cursor_number);
message('Exception - Cleaned up Cursor');
End if;
END;
regards
|
|
|
|
| Re: Populating the data in respective excel columns [message #400185 is a reply to message #400138] |
Mon, 27 April 2009 01:05  |
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
|
Senior Member |
|
|
cut and paste (move)
Excelargs := ole2.create_arglist;
ole2.add_arg(Excelargs,'D:D');
ccol := ole2.get_obj_property(ExcelworksheetID,'Columns', Excelargs);
ole2.destroy_arglist(Excelargs);
Excelargs := ole2.create_arglist;
ole2.add_arg(Excelargs,'3:50');
rrow := ole2.get_obj_property(ccol, 'Rows', Excelargs);
ole2.destroy_arglist(Excelargs);
ole2.invoke(rrow, 'select');
ole2.invoke(rrow,'Cut');
Excelargs := ole2.create_arglist;
ole2.add_arg(Excelargs,'C:C');
ccol := ole2.get_obj_property(ExcelworksheetID,'Columns', Excelargs);
ole2.destroy_arglist(Excelargs);
Excelargs := ole2.create_arglist;
ole2.add_arg(Excelargs,'3:3');
rrow := ole2.get_obj_property(Ccol,'Rows', Excelargs);
ole2.destroy_arglist(Excelargs);
ole2.invoke(rrow,'Select');
It is hardcode same but you have to change/generalize as per you requirements.
paste work, you have do your self and i feel u can
kanish
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 10:43:15 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
