How to convert a report into excel sheet

Questions by oswal.anand   answers by oswal.anand

Showing Answers 1 - 18 of 18 Answers

Shyam Kumar Gorthi

  • Jan 4th, 2006
 

after running the report click on print and then check the print to file option. after saving the file can be opened by pressing shift button and right clicking on the file and choosing open with and then excel

  Was this answer useful?  Yes

smithamd

  • Jan 10th, 2006
 

In After Report Trigger, you can create a file in excel format using Text.IO and pressing TAB for column breakup

  Was this answer useful?  Yes

yogesh amritkar

  • Mar 20th, 2006
 

Pl. give Me solution and with Example.yogesh

  Was this answer useful?  Yes

Mukts

  • Mar 31st, 2006
 

set system parameters

destype=file

and

desformat=delimited

  Was this answer useful?  Yes

venkateswarlu

  • Apr 12th, 2006
 

Can u please explain how to conver reports 3.0 to excel sheets

Thanks and Regards,

Venkat

  Was this answer useful?  Yes

ravi shekhar

  • Apr 14th, 2006
 

hw 2 upload from SAP to excel??

  Was this answer useful?  Yes

altaf ahmed

  • Jun 27th, 2006
 

I am using report designer 2.5 Version.  Kindly advise how to convert the reports into Excel Format.

I will be grateful for the solution.

Regards,

Altaf

  Was this answer useful?  Yes

nikhil

  • Sep 6th, 2006
 

how to convert the report into excel sheet format.

  Was this answer useful?  Yes

asha

  • Nov 4th, 2006
 

plz tell me How to convert a report into excel sheet

with example

  Was this answer useful?  Yes

MANISH SHARMA

  • Feb 20th, 2007
 

 hey try this example......well its procedure...u can apply on the report(triggers) like on a button or something...
procedure U_1ButtonAction is
CURSOR cur is
  SELECT A.EMP_CODE,A.EMP_TITLE ||' '||A.EMP_FNAME ||' '||A.EMP_LNAME EMP_NAME,A.EMP_DEPT,A.EMP_TYPE,EMP_DOJ
FROM BPAYT030 A WHERE A.EMP_TYPE = DECODE(:P_EMPTYPE,'A',A.EMP_TYPE,:P_EMPTYPE)
AND A.EMP_DEPT = DECODE(:P_EMPDEPT,'A',A.EMP_DEPT,:P_EMPDEPT)  AND EMP_STATUS ='Y';
--AND A.JOB_TITLE  = DECODE(:P_JOB,'A',A.EMP_DEPT,:P_JOB)
V_OUTFILE TEXT_IO.FILE_TYPE;
V_OUTSTRING    VARCHAR2(1000);
L_FILENAME     VARCHAR2(100);
L_SR           NUMBER:=0;
L_DESC         VARCHAR2(200);
L_DESC1        VARCHAR2(200);
BEGIN
L_FILENAME := 'C:'||'EMPLOYEE LIST'||TO_CHAR(SYSDATE,'DDMMRR')||'.CSV';
 V_outfile := text_io.fopen(L_FILENAME,'W');    
 V_outstring := ','||','||'EMPLOYEE TYPE/DEPARTMENT WISE DETAILS';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring :='';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring := :P_COMP;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
  --EXCEL FILE HEADS
 V_outstring :='EMPLOYEE TYPE'||','||'DEPARTMENT'||','||'CODE'||','||'DOJ' ||','||'EMPLOYEE NAME';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
FOR REC IN CUR LOOP
SELECT CODE_DESC INTO L_DESC FROM BCOMT011 WHERE TYPE_CODE  = REC.EMP_TYPE;
SELECT CODE_DESC INTO L_DESC1 FROM BCOMT011 WHERE TYPE_CODE  = REC.EMP_DEPT;
--DATA IN EXCEL
V_outstring :=  L_DESC||','||L_DESC1||','||REC.EMP_CODE||','||EC.EMP_DOJ||','||REC.EMP_NAME;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
END LOOP;
text_io.fclose(v_outfile);
SRW.MESSAGE(100,'File '||L_FILENAME|| ' Generated Successfully....');
EXCEPTION        
WHEN OTHERS THEN
SRW.MESSAGE('1',SQLERRM);
text_io.fclose(v_outfile);
END;

sujoy ganguly

  • Feb 21st, 2007
 

how to update excel data into  data base (sql server 2000 [7.0]) through forms of d2k or any other mode??

  Was this answer useful?  Yes

Chandra K. Ravi

  • Apr 11th, 2007
 

==========================================IN QUERY========================XL= FORMULLA COLUMN
=======================================CNT IS A PLACE HOLDER COLUMN
function xlFormula return Number is
convid pls_integer;
begin
 if :XLS='Y'  then 
   if :cnt is null then
   :cnt:='2';
  end if;
ConvID := DDE.INITIATE('EXCEL', 'Sheet1');
       DDE.POKE(ConvID, 'r1'||'c1','date', DDE.CF_TEXT, 1000);
      DDE.POKE(ConvID, 'r1'||'c2','voucherno ', DDE.CF_TEXT, 1000);
      
     
      DDE.POKE(ConvID, 'r1'||'c3','description', DDE.CF_TEXT, 1000);
      DDE.POKE(ConvID, 'r1'||'c4','debit', DDE.CF_TEXT, 1000);
         DDE.POKE(ConvID, 'r1'||'c5','credit', DDE.CF_TEXT, 1000);
 
      DDE.POKE(ConvID, 'r'||:cnt||'c1',(to_char(:dt,'dd-mm-yy')), DDE.CF_TEXT, 1000);
      DDE.POKE(ConvID, 'r'||:cnt||'c2',nvl(:id,'0'), DDE.CF_TEXT, 1000);
      DDE.POKE(ConvID, 'r'||:cnt||'c3',nvl(:narration,' '),DDE.CF_TEXT, 1000);
      DDE.POKE(ConvID, 'r'||:cnt||'c4',nvl(:credit,' '), DDE.CF_TEXT, 1000);
      DDE.POKE(ConvID, 'r'||:cnt||'c5',nvl(:debit,' '), DDE.CF_TEXT, 1000);
    
DDE.TERMINATE(Convid);
:cnt:=:cnt+1;
END IF;
RETURN('0');
end;


BEFORE REPORT==============================TRIGGER====================================
DECLARE
  AppID PLS_INTEGER;
begin
 
    AppID := DDE.APP_BEGIN('C:Program FilesMicrosoft OfficeOfficeEXCEL.EXE',
    DDE.APP_MODE_MAXIMIZED);
    return (TRUE);
END;
=======================================================================================
declare
out_file Text_IO.File_Type;
flnm varchar2(20);

begin
out_file:=Text_IO.Fopen('a.xls', 'w');
Text_IO.New_Line(out_file);
Text_IO.Put(out_file, :eid);
Text_IO.Put(out_file, CHR(9));
Text_IO.Put(out_file, :NM);
Text_IO.Fclose (out_file);
end;
===============================  D2K FORM 6I  TO EXCEL ===================================

declare
out_file Text_IO.File_Type;
flnm varchar2(200);

begin
flnm := GET_FILE_NAME('H:', 'file_name.xls', 'XLS Files (*.xls)|*.xls|', NULL, SAVE_FILE, TRUE);

out_file:=Text_IO.Fopen(flnm, 'w');
LOOP
Text_IO.New_Line(out_file);
Text_IO.Put(out_file, :eid);
Text_IO.Put(out_file, CHR(9));
Text_IO.Put(out_file, :NM);
IF :system.last_record = 'TRUE' THEN
EXIT;
ELSE
next_record;
END IF;
END LOOP;
Text_IO.Fclose (out_file);
end;
==============================================EXCEL TO ORACLE====================================
-------------------- THIS PROGRAM IS WRITTEN BY CHANDRA ---------------------------------------

DECLARE
 in_file   CLIENT_Text_IO.File_Type;
  line      VARCHAR2(1800);
  filename  VARCHAR2(30);
  ID1 VARCHAR2(12);
  NM VARCHAR2(100);
  LN VARCHAR2(30);
BEGIN
  filename:=CLIENT_GET_FILE_NAME('C:','TEST2.csv');
  in_file := CLIENT_Text_IO.Fopen(filename, 'r');
 LOOP
    CLIENT_Text_IO.get_line(in_file, line);
    :text_item5:=line; ---------FOR REFERENCE ONLY (HOW DATA STORE IN VARIABLE)
    SELECT SUBSTR(LINE,1,INSTR(LINE,',')-1) INTO ID1 FROM DUAL;
    SELECT SUBSTR(SUBSTR(LINE,INSTR(LINE,',')+1),1,INSTR(SUBSTR(LINE,INSTR(LINE,',')+1), ',')-1) INTO NM FROM DUAL;
    SELECT SUBSTR(SUBSTR(LINE,INSTR(LINE,',')+1),
           INSTR(SUBSTR(LINE,INSTR(LINE,',')+1),',')+1) INTO LN FROM DUAL;

    INSERT INTO TEST_CHAND VALUES(ID1,NM,LN);  ----- INSERTING THE DATA INTO THE ORACLE TABLE
    COMMIT;
    CLIENT_Text_IO.New_Line;                                
 END LOOP;
 
EXCEPTION
  WHEN no_data_found THEN
    CLIENT_Text_IO.Put_Line('Closing the file...');
    CLIENT_Text_IO.Fclose(in_file);
END;




mob : 09920266127

venu

  • Apr 29th, 2007
 

If you are presently developing report in .rdf
then you can convert the report in to excel form with owa_sylk utility it is a oracle supplied package use this package in pl/sql program and try by creating executable as pl/sql stored procedure  instead of oracle reports  

Any queries do mail me

  Was this answer useful?  Yes

Arup Ratan Banerjee

  • Jun 11th, 2007
 

Steps:-
--------------

1) Download rep2excel.exe from the site:-
    http://www.brothersoft.com/downloads/html-to-excel.html
 
2) the from Oracle report save the file as <report_name>.html
3) From command prompt run
    rep2excel <source_file_name>  <destination_file_name> <delinitted>

Mohammed Ilyas

  • Oct 1st, 2011
 

Sir, where should i write this code.


==========================================IN QUERY========================XL= FORMULLA COLUMN
=======================================CNT IS A PLACE HOLDER COLUMN
function xlFormula return Number is
convid pls_integer;
begin
if :XLS='Y' then
if :cnt is null then
:cnt:='2';
end if;
ConvID := DDE.INITIATE('EXCEL', 'Sheet1');
DDE.POKE(ConvID, 'r1'||'c1','date', DDE.CF_TEXT, 1000);
DDE.POKE(ConvID, 'r1'||'c2','voucherno ', DDE.CF_TEXT, 1000);


DDE.POKE(ConvID, 'r1'||'c3','description', DDE.CF_TEXT, 1000);
DDE.POKE(ConvID, 'r1'||'c4','debit', DDE.CF_TEXT, 1000);
DDE.POKE(ConvID, 'r1'||'c5','credit', DDE.CF_TEXT, 1000);

DDE.POKE(ConvID, 'r'||:cnt||'c1',(to_char(:dt,'dd-mm-yy')), DDE.CF_TEXT, 1000);
DDE.POKE(ConvID, 'r'||:cnt||'c2',nvl(:id,'0'), DDE.CF_TEXT, 1000);
DDE.POKE(ConvID, 'r'||:cnt||'c3',nvl(:narration,' '),DDE.CF_TEXT, 1000);
DDE.POKE(ConvID, 'r'||:cnt||'c4',nvl(:credit,' '), DDE.CF_TEXT, 1000);
DDE.POKE(ConvID, 'r'||:cnt||'c5',nvl(:debit,' '), DDE.CF_TEXT, 1000);

DDE.TERMINATE(Convid);
:cnt:=:cnt+1;
END IF;
RETURN('0');
end;



  Was this answer useful?  Yes

subrat

  • Oct 13th, 2011
 

set system parameters

destype=file

and

desformat=spreadsheet
(100% tested) and secure

  Was this answer useful?  Yes

parash_3380

  • Oct 19th, 2011
 

Hi Mr. Subrat,
very good but
when i set system parameters
as
destype=file

and

desformat=spreadsheet

it is just showing on browser that "Successfully run" and nothing
where as if i give

destype=cache

and

desformat=spreadsheet

then output is in excel but format is totally different then my design view.

Thnx and regards
Parash Prasad

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions