Rep2excel User's Guide

Last updated: 03/14/2007

Index

• Overview
• Call Rep2excel through command line
• Deploy Rep2excel in web server, call it through http link
• Integrate Rep2excel into your application
• Setup Rep2excel [Default Parameters] [E-mail Settings]

Overview

Thank you very much to choose rep2excel to export your oracle report to ms-excel! Oracle Reports have not provided any convenient way to get report output in ms-excel format so far, you are looking for some solution to solve this problem. We know what you need and created this tool -- rep2excel.

Rep2excel is developed for oracle report only, so using rep2excel may be the most perfect way to export oracle report data to ms-excel. But rep2excel is a win32 console application, it doesn't have user-friendly interface, so it is not easy to master if you knows little about CGI (Common Gateway Interface), however, please don't be worry because rep2excel works fine with default setting.

Rep2excel provide 2 user interface:

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

Call Rep2excel through command line

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

Please use rep2excel --help to get the usage .
 

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!

Deploy Rep2excel in web server, call it through http link

Rep2excel support 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]

How to install rep2excel on a web-server.

1)Please make sure your web-server support CGI, the Apache is recommended.

2)Copy files.
Copy rep2excel.exe to the CGI directory.
   If your server is OHS, the default CGI directory is ORACLE_HOME\apache\apache\cgi-bin\
   If your server is Apache 2, the default CGI directory is C:\Program Files\Apache Group\Apache2\cgi-bin\
   If your server is MS-IIS, the default CGI dirctory is C:\Inetpub\Scripts\
   If other server, please refer to help of the server.

Copy the r2xls folder to the root of the web server.
If your server is Apache 2, the default CGI directory is C:\Program Files\Apache Group\Apache2\htdocs\

3)Use IE to test whether rep2excel works. Sample URL, you should changed the port and server address.
   If your server is OHS, the URL may be http://server_name:7777/cgi-bin/rep2excel.exe
   If your server is Apache 2, th URL may be http://server_name:80/cgi-bin/rep2excel.exe
   If your server is MS-IIS, the URL may be http://server_name:80/scripts/rep2excel.exe
A web page will appear if you open the URL above.

4)If rep2excel works, try your first conversion with rep2excel!
   Please input the full location of an oracle report output file into File Path test box, then click Submit button. The location of the file may be a URL, e.g. http://server_name:7777/dev60cgi/rwcgi60?server=REPORT_SERVER_CONN_STR&report=REPORT_NAME.rdf&userid=scott/tiger@test&destype=cache&desformat=HTMLCSS 

click the open button, IE will show you excel output

Integrate Rep2excel into your application

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.
You original code:
declare v_url varchar2(1000); begin v_url := 'http://SVR_NAME:7777/dev60cgi/rwcgi60?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?server=rep_svr&report=test_report.rdf&userid=scott/tiger@test&destype=cache&desformat=HTMLCSS'; --Modify begin v_url:=encode_url(v_url);--Please don't omit this step; web.show_document('http://web-server:7777/cgi-bin/rep2excel.exe?filepath='||v_url||'&job=preview','_blank'); --Modify end end;

The URL encoding.

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".

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;

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

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