How to call Microsoft Excel from Oracle Reports/Forms 6i

20 pts.
Tags:
Microsoft Excel
Microsoft Excel import/export
Oracle 6i
Oracle 6i import/export
Oracle development
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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 Files\Microsoft Office\Office10\EXCEL.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 Files\Microsoft Office\Office10\EXCEL.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

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Exporacle
    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
    20 pointsBadges:
    report
  • carlosdl
    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...
    69,920 pointsBadges:
    report
  • carlosdl
    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;
    69,920 pointsBadges:
    report
  • carlosdl
    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:
    replace(replace(buffer,chr(13),''),chr(10),'')
    13 is carriage return 10 is line feed
    69,920 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following