How to call Microsoft Excel from Oracle Reports/Forms 6i
Excel import/export, Microsoft Excel, Microsoft Excel import/export, Oracle 6i, Oracle 6i import/export, Oracle developers, Oracle Forms, Oracle Forms 6i, Oracle import/export, Oracle Reports
how to call excel from oracle report/forms 6i
plz tell me the step by step process how can we do it
my e-mailid is lightmobeen@yahoo.com
if any one know the process plz send me mail sonsist of code,me very thankful to u for this
Software/Hardware used:
Software/Hardware used:
ASKED:
November 26, 2008 10:55 AM
UPDATED:
November 21, 2010 12:17 PM
Answer Wiki:
You could use the DDE package. Dynamic Data Exchange (DDE) is a mechanism by which applications can communicate and exchange data in Windows.
In this case, you would need to use the function App_Begin. This is the syntax:
The following code can help you specifically about how to implement th DDE package.
here is an example which uses <b>scott </b>schema. and DEPT Table.
First take a tabular database block connected to DEPT Table. Take two buttons on the canvas. write the following codes in the when-button-pressed trigger
DECLARE
filename VARCHAR2(256);
ConvID PLS_INTEGER;
AppID PLS_INTEGER;
Buffer VARCHAR2(5);
I NUMBER(3):=1;
J NUMBER(5):=1;
V_NUMBER VARCHAR2(5);
LEN NUMBER(5);
DNAME VARCHAR2(14);
LOC VARCHAR2(13);
BEGIN
filename := GET_FILE_NAME(File_Filter=> 'EXCEL Files (*.XLS)|*.XLS|');
AppID := DDE.App_Begin('C:Program FilesMicrosoft OfficeOffice10EXCEL.EXE'||' '||filename,DDE.APP_MODE_MINIMIZED);
ConvID := DDE.Initiate('EXCEL.EXE',filename);
LOOP
BEGIN
BUFFER:=NULL;
V_NUMBER:=NULL;
I:=1;
DDE.Request(ConvID,'R'||J||'C1',Buffer,DDE.CF_TEXT,1000);
DDE.Request(ConvID,'R'||J||'C2',dname,DDE.CF_TEXT,1000);
DDE.Request(ConvID,'R'||J||'C3',loc,DDE.CF_TEXT,1000);
IF ASCII(BUFFER)=13 THEN
EXIT;
END IF;
LEN:=LENGTH(Buffer);
LOOP
EXIT WHEN I>LEN;
IF SUBSTR(BUFFER,I,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
V_NUMBER:=V_NUMBER||SUBSTR(BUFFER,I,1);
I:=I+1;
ELSE
I:=I+1;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
exit;
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;
:DEPTNO:=TO_NUMBER(V_NUMBER);
:DNAME:=DNAME;
:LOC:=LOC;
J:=J+1;
NEXT_RECORD;
END LOOP;
EXCEPTION
WHEN DDE.DDE_APP_NOT_FOUND THEN
MESSAGE('APP NOT FOUND');
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE('APP FAILED');
WHEN DDE.DDE_INIT_FAILED THEN
MESSAGE('INIT FAILED');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE('CLIENT FAILED TO ESTABLISH CONVERSATION');
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;
This will load Excel data on the oracle form, and after that everything is same as Oracle forms' classic performance.
Now to create an Excel file from Database In another button write the following codes.
DECLARE
CURSOR C1 IS
SELECT DEPTNO,DNAME,LOC
FROM DEPT;
filename VARCHAR2(256);
ConvID PLS_INTEGER;
AppID PLS_INTEGER;
Buffer VARCHAR2(5);
I NUMBER(3):=1;
J NUMBER(5):=1;
V_NUMBER VARCHAR2(5);
LEN NUMBER(5);
DNAME VARCHAR2(14);
LOC VARCHAR2(13);
OUT_FILE TEXT_IO.FILE_TYPE;
BEGIN
filename := GET_FILE_NAME(File_Filter=> 'EXCEL Files (*.XLS)|*.XLS|',DIALOG_TYPE=>2);
OUT_FILE:=TEXT_IO.FOPEN(filename,'W');
TEXT_IO.FCLOSE(OUT_FILE);
AppID := DDE.App_Begin('C:Program FilesMicrosoft OfficeOffice10EXCEL.EXE'||' '||filename,DDE.APP_MODE_MINIMIZED);
--DDE.APP_FOCUS(APPID);
ConvID := DDE.Initiate('EXCEL.EXE',filename);
FOR R IN C1
LOOP
DDE.POKE(ConvID,'R'||J||'C1',R.DEPTNO,DDE.CF_TEXT,1000);
DDE.POKE(ConvID,'R'||J||'C2',R.DNAME,DDE.CF_TEXT,1000);
DDE.POKE(ConvID,'R'||J||'C3',R.LOC,DDE.CF_TEXT,1000);
J:=J+1;
END LOOP;
TEXT_IO.FCLOSE(OUT_FILE);
EXCEPTION
WHEN DDE.DDE_APP_NOT_FOUND THEN
MESSAGE('APP NOT FOUND');
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE('APP FAILED');
WHEN DDE.DDE_INIT_FAILED THEN
MESSAGE('INIT FAILED');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE('CLIENT FAILED TO ESTABLISH CONVERSATION');
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;
Try this if you have any problem in implementing the code please mail me at<b> mamun_iuk@yahoo.com</b>
---------------------------------------
DDE.Request devuelve al final del valor dos carácteres no imprimibles que se pueden quitar asi:
replace(replace(buffer,chr(13),''),chr(10),'')
13 es retorno de carro
10 es salto de linea
To see all answers submitted to the Answer Wiki: View Answer History.
thanks a lot for this
DDE is only open EXE file or specific file
but my problem is that to open when i call report through parameter output will be generated into excel form.give me this answer
I have never generated XLS files, but I’m almost sure it can be done with the help of some library.
How about generating a CSV file, and open it with excel ? will that work for you ?
Using the TEXT_IO package you could write your program output to a file (csv), and after that, you could call Excel to open it, as described above.
For example:
Declare
myCSV text_io.file_type;
app PLS_INTEGER;
Begin
myCSV := text_io.fopen(‘c:test.csv’,'w’);
text_io.put_line(myCSV,’test1,test2,test3′);
text_io.fclose(myCSV);
app := dde.app_begin(‘C:Program FilesMicrosoft OfficeOffice12EXCEL.EXE c:test.csv’,dde.App_Mode_Maximized);
end;
Some exception handlers need to be added…
Trying to post again, as it seems the editor removed the backslashes.
declare myCSV text_io.file_type; app PLS_INTEGER; Begin myCSV := text_io.fopen('c:test.csv','w'); text_io.put_line(myCSV,'test1,test2,test3'); text_io.fclose(myCSV); app := dde.app_begin('C:Program FilesMicrosoft OfficeOffice12EXCEL.EXE c:test.csv',dde.App_Mode_Maximized); end;This is the translation of the last answer added by Miryanpacheco:
DDE.Request returns two unprintable characters at the end of the buffer, which can be removed this way:
13 is carriage return
10 is line feed