How to Call Rep2excel

Rep2excel provides 2 user interfaces

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

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;

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