Home » Developer & Programmer » Forms » excel new row (10g, xp , forms 6i)
excel new row [message #328712] Sun, 22 June 2008 01:55 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I am using the following code to export data to excel. the problem is
it repeats the data in the same row. Can I write the data ie. rows of data to rows of excel using following code. Please help.

the code is used under when button pressed in forms 6i.

PROCEDURE excel_file IS

cursor dtl is select * from centres;

row_num number;
col_num number;
cell_val number;

app_acc OLE2.OBJ_TYPE;
dbs OLE2.OBJ_TYPE;
dc OLE2.OBJ_TYPE;
args_acc OLE2.OBJ_TYPE;
app OLE2.OBJ_TYPE;
args OLE2.LIST_TYPE;
args2 OLE2.LIST_TYPE;
ws OLE2.OBJ_TYPE;
wb OLE2.OBJ_TYPE;
wss OLE2.OBJ_TYPE;
wbs OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
ctr1 NUMBER := 0;
ctr2 NUMBER := 0;
ctr3 PLS_INTEGER := 0; --NUMBER := 0;
m_row number:=1;
m_len number:=0;

m_flag number:=0;

Begin

---------------- Initialise Excel

app := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(app, 'Visible','True');
wbs := OLE2.GET_OBJ_PROPERTY(app,'Workbooks');
wb := OLE2.INVOKE_OBJ(wbs,'Add');
wss := OLE2.GET_OBJ_PROPERTY(app,'Worksheets');
ws := OLE2.INVOKE_OBJ(wss,'Add');

---

For I in 1..2 loop

args := OLE2.CREATE_ARGLIST;

OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column

cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);

if I=1 then
OLE2.SET_PROPERTY(cell, 'Value','Auth. Date');
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value','ICD Doc.No');
end if;

OLE2.RELEASE_OBJ(cell);
end loop;
--- Heading

-- Data

For J in Dtl loop

For I in 1..2 loop

-- Repeat Row
args := OLE2.CREATE_ARGLIST;

OLE2.ADD_ARG(args, m_row); --- Row
OLE2.ADD_ARG(args, I); --- Column

cell := OLE2.GET_OBJ_PROPERTY(ws,'Cells', args);
OLE2.DESTROY_ARGLIST(args);

if I=1 then
OLE2.SET_PROPERTY(cell, 'Value',J.centre_id);
elsif I=2 then
OLE2.SET_PROPERTY(cell, 'Value',J.centre);
end if;
end loop;
end loop;


---- End

OLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(ws);
OLE2.RELEASE_OBJ(wbs);
OLE2.RELEASE_OBJ(wb);
OLE2.RELEASE_OBJ(app);

END;

Re: excel new row [message #337092 is a reply to message #328712] Tue, 29 July 2008 21:35 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

Have you considered writing out the data into a 'csv' file?

Have you verifyied that your loops are working? That is, put the data out into a flat file as well as excel and see if you get the same data duplication.

David
Previous Topic: Sending Attchement with Mail in Form 6i-DB(Oracle 9i)
Next Topic: Menu
Goto Forum:
  


Current Time: Mon Dec 05 04:54:32 CST 2016

Total time taken to generate the page: 0.09683 seconds