How to Call Rep2excel

Rep2excel provides 2 user interfaces

1. Command line interface
2. Common Gateway Interface (CGI)

You can

• Call Rep2excel manually
• Call Rep2excel from your application

Rep2excel support command line interface, you can call it through dos prompt, you can also call it by HOST built-in in Oracle Forms.

Test Rep2excel in dos prompt

1. Click Start menu, click Run, input cmd and then click OK, start the dos prompt.
   
2. Change current directory to the rep2excel installation directory.
3. Please use rep2excel --help to get the usage .

The output of Rep2excel looks like:
 

For example, the file d:\working\rep\emp_list2.htm will be converted, see below

D:\working\Rep2excel111\Release>rep2excel -i:d:\working\rep\emp_list2.htm
Input file : d:\working\rep\emp_list2.htm
Output file: d:\working\rep\emp_list2.xls
..................................................
..................................................
Finish succefully!

Rep2excel supports CGI interface, you can install rep2excel on a web-server which supports CGI and invoke this program using WEB.SHOW_DOCUMENT built-in.[Demo Online]

First, you should install rep2excel as CGI service. See here.

Once rep2excel is installed, try your first conversion with rep2excel!

Please fill the full location of an oracle report output file into File Path box, then click Submit button.

The location of the file may be a URL, e.g.

• http://www.lv2000.com/getreport.php
• /r2xls/simple-ora-rep.htm
• http://server_name:7777/dev60cgi/rwcgi60?server=REPORT_SERVER_CONN_STR&report=REPORT_NAME.rdf&userid=scott/tiger@test&destype=cache&desformat=HTMLCSS 

IE will show you excel output

Index

1. Call Rep2excel through HOST built-in in Oracle forms.
2. Call rep2excel from oracle forms from web.
3. Call rep2excel from web page

Call Rep2excel through HOST built-in in Oracle forms.

Sample code

Declare v_conn_str varchar2(50):= 'scott/tiger@devdb'; --To be changed v_rep2excel_path varchar2(300) := 'c:\ThePath\rep2excel.exe'; --To be changed -- v_random_name varchar2(100); v_cmd_str varchar2(300); v_input_path varchar2(300); v_output_path varchar2(300); Begin select to_char(round(dbms_random.value(100000, 999999)))||to_char(sysdate,'DDHH24MISS') into v_random_name from dual; v_input_path := 'c:\tempfiles\'||v_random_name||'.htm'; v_output_path:= 'c:\tempfiles\'||v_random_name||'.xls'; -- Generate html output v_cmd_str := 'RWRUN60 REPORT=C:\rdf\abc.rdf USERID='||v_conn_str; v_cmd_str := v_cmd_str || ' DESTYPE=FILE DESFORMAT=HTML' ; v_cmd_str := v_cmd_str || ' DESNAME='||v_input_path||' batch=yes'); HOST(v_cmd_str); -- Convert html into excel HOST('CMD /C START '||v_rep2excel_path||' -i:'|| v_input_path || ' -o:'|| v_output_path || ' -open'); End;

Call rep2excel from oracle forms from web.

You can call rep2excel from oracle forms using web.show_document built-in.
If you are now using web.show_document to open a HTML file generated by oracle report server, you can invoke rep2excel to get excel easily.

The following sample code demonstrates how to change your code to work with rep2excel.
Your original code:

declare
v_url varchar2(1000);
begin
v_url := 'http://SVR_NAME:7777/dev60cgi/rwcgi60?';
v_url := v_url || 'server=rep_svr&report=test_report.rdf&userid=scott/tiger@test&destype=cache&desformat=HTMLCSS';
web.show_document(v_url,'_blank');
end;

Please change the first parameter of web.show_document, like this

declare
v_url varchar2(1000);
begin
v_url := 'http://SVR_NAME:7777/dev60cgi/rwcgi60?';
v_url := v_url || 'server=rep_svr&report=test_report.rdf&userid=scott/tiger@test&destype=cache&desformat=HTMLCSS';

--Modify begin
--v_url:=encode_url(v_url);--This step is optional
web.show_document('http://web-server:7777/cgi-bin/rep2excel.exe?filepath='||v_url||'&job=getexcel','_blank');
--Modify end
end;

Sample code of Forms/Reports 10gR2.

declare
repid REPORT_OBJECT;
v_rep VARCHAR2(100);
rep_status VARCHAR2(50);
v_url_to_access_rep2excel varchar2(200) ; -- The url that link to Rep2excel.
v_url_to_get_html_report varchar2(500) ; -- The url that is used to download report from the report server.
v_the_final_url varchar2(500) ; -- The url that call Rep2excel to download html report and convert to excel.

BEGIN

repid := find_report_object('REPORT6');
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_EXECUTION_MODE,BATCH);
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_COMM_MODE,SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_DESTYPE,CACHE);
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_DESFORMAT,'HTML'); -- The Rep2excel support report in html or htmlcss format only.
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_SERVER,'REP_192_ORACLEAS2'); --To be changed according to your environment.
SET_REPORT_OBJECT_PROPERTY(repid,REPORT_OTHER,'paramform=no');

v_rep := RUN_REPORT_OBJECT(repid);
rep_status := REPORT_OBJECT_STATUS(v_rep);

WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED')
LOOP
rep_status := report_object_status(v_rep);
END LOOP;
IF rep_status = 'FINISHED' THEN

v_url_to_get_html_report := 'http://192.168.241.138/reports/rwservlet/getjobid'||
substr(v_rep,instr(v_rep,'_',-1)+1)||'?'||'server=rep_192_oracleas2'; --To be changed according to your environment.

v_url_to_access_rep2excel := '/cgi-bin/rep2excel.exe' ; -- We assume that you put the Rep2excel to the folder: ORACLE_HOME/Apache/Apache/cgi-bin

v_the_final_url := v_url_to_access_rep2excel || '?filepath=' || v_url_to_get_html_report ; -- See how to pass parameter to Rep2excel.
if :OUTPUT_TYPE ='excel' then
WEB.SHOW_DOCUMENT( v_the_final_url ,'_blank'); -- Call Rep2excel, and then Rep2excel will download the report output, and then serve back excel
else
WEB.SHOW_DOCUMENT( v_url_to_get_html_report ,'_blank'); -- Get original html output
end if;
ELSE
message('Error when running report');
END IF;
END;

There is a example included in rep2excel installation patch. You can download the latest version
from http://www.lv2000.com/examples/run_rep_example.zip

The URL encoding (optional).

In an encoded string, blanks are changed into + and special characters are made by the % symbol, followed by the hexadecimal digits of the ASCII code.
For example the character "-" is substituted by the string "%2d".

Note: The URL encoding is optional, actually Rep2excel can detect the correct URL.

The sample code of function encode_url is

FUNCTION encode_url(p_url varchar) RETURN varchar IS v_len number(5); v_cur number(5); v_url varchar2(2000):=NULL; v_chr varchar2(1); v_num number(3); v_num_high number(3); v_num_low number(3); BEGIN v_len := lengthb(p_url); v_cur:=1; loop exit when v_cur>v_len; v_chr := substrb(p_url,v_cur,1); v_num := ascii(v_chr); if v_num=32 then v_url:=v_url||'+'; elsif (v_num>=65 and v_num<=90) or (v_num>=97 and v_num<=122) or (v_num>=48 and v_num<=57) then v_url:=v_url||v_chr; else v_url:=v_url||'%'; v_num_high := floor(v_num/16); if v_num_high>=10 then v_chr:=chr(v_num_high+55); else v_chr:=chr(v_num_high+48); end if; v_url:=v_url||v_chr; v_num_low:=mod(v_num,16); if v_num_low>=10 then v_chr:=chr(v_num_low+55); else v_chr:=chr(v_num_low+48); end if; v_url:=v_url||v_chr; end if; v_cur:=v_cur+1; end loop; return v_url; END;

Call rep2excel from web page

If you call oracle report server from a html form, you can also call rep2excel to convert the oracle report output. and you will have to do some coding, see the sample code:

function runReport()
{
// semi-constants for JavaScript 8-)
var cgiexe = "rwcgi60.exe";
var slash = "/";
var colon = ":";
var qmark = "?";
var paramsep = "&";
/**
** URL parameter values
** We should check for nulls, http://, etc. but not for now ...
*/
var WEBHOST = document.REPFORM.WEBHOST.value;
var WEBPORT = document.REPFORM.WEBPORT.value
var SERVER= document.REPFORM.SERVER.value;
var REPORT = document.REPFORM.REPORT.value;
var USERID= document.REPFORM.USERID.value;
var dtlist = document.REPFORM.DESTYPE;
var DESTYPE = dtlist.options[dtlist.selectedIndex].value;
var dflist = document.REPFORM.DESFORMAT;
var DESFORMAT = dflist.options[dflist.selectedIndex].value;
// construct the final URL given the parameters

//Leo Change begin
var URL = "http://" + WEBHOST + colon + WEBPORT + "/dev60cgi/"
+ cgiexe + qmark +
"server=" + SERVER + paramsep +
"report=" + REPORT + paramsep +
"userid=" + USERID + paramsep +
"destype=" + DESTYPE + paramsep ;
if (DESFORMAT == "REP2EXCEL")
{
URL = URL + "desformat=HTML"
// URL = "http://localhost:7777/cgi-bin/rep2excel.exe?filepath=" + escape(URL);
URL = "http://" + WEBHOST + colon + WEBPORT + "/dev60cgi/rep2excel.exe?filepath=" + escape(URL);

if (document.REPFORM.mailto.value != "") //Send excel file
URL = URL + "&mailto=" + document.REPFORM.mailto.value;

}else
{
URL = URL + "desformat=" + DESFORMAT;
}
// If you have any question about this form,
// please do not hesidate to contact support@lv2000.com .
//Leo Change End

runWindow = window.open(URL);
}

For more details, please refer to the example from http://www.lv2000.com/examples/run_rep_example.zip