Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 17 June 2013

How to send Oracle Report output into Excel

After creating the report you can send report output into Excel sheet. To do so add or create a SEND_EXCEL formula column and write the PL/SQL code as per the report output. Do not forget to create User parameter send_excel with the same datatype and width and also the specified directory where you want to send the Excel file in disk drive (which you mentioned inside cf_send_excel PL/SQL formula column path.
Create formula column CF_SEND_EXCEL
Column Type: Formula
Data type: Character – 20
Create User Parameters send_excel
Name: send_excel
Datatype: Character – 20
List of Values: Static Values
Add Value: YES, NO
Create place holder column cp_2
Column Type: Placeholder
Datatype: Number - 10
Value if Null: 0

Then write PL/SQL on the object CF_SEND_EXCEL
function CF_send_excelFormula return CHAR is
    out_file text_io.file_type;
    string varchar2(1000);
begin
if :send_excel = 'YES' then
       if  :cp_2 = 0 then
         out_file := text_io.fopen('C:\Oracle_Excel\Monthly_Trial_Bal_Report_Per_Acc.XLS','w');
         string   := 'Acctno'||chr(9)||
                     'm desc'||chr(9)||
                     'Branch'||chr(9)||
                     'Deptno'||chr(9)||
                     'Year'||chr(9)||
                     'Month'||chr(9)||
                     'Debit'||chr(9)||
              'Credit'||chr(9)||
              'Net Balance'||chr(10)||
                     :ABAL_ACCTNO||CHR(9)||
                     :m_desc||chr(9)||
                     :ABAL_BRN||chr(9)||
                     :ABAL_DEPT||chr(9)||
                     :YEAR||chr(9)||
                     :MONTH||chr(9)||
                     :CP_DB||chr(9)||
                     :CP_CR||chr(9)||
                     :CF_CALC||chr(10);
        text_io.putf(out_file,string);
        text_io.fclose(out_file);
        :cp_2 := 1;
       elsif :cp_2 = 1 then
         out_file := text_io.fopen('C:\Oracle_Excel\Monthly_Trial_Bal_Report_Per_Acc.XLS','a');
          string   :=:ABAL_ACCTNO||CHR(9)||
                     :m_desc||chr(9)||
                     :ABAL_BRN||chr(9)||
                     :ABAL_DEPT||chr(9)||
                     :YEAR||chr(9)||
                     :MONTH||chr(9)||
                     :CP_DB||chr(9)||
                     :CP_CR||chr(9)||
                     :CF_CALC||chr(10);
         text_io.putf(out_file,String);
         text_io.fclose(out_file);
       end if;
END IF;
RETURN (null);
end;
cp_2 is a placeholder column defined to identify send_excel column condition value either ‘YES’ or ‘NO’. If the cp_2 value is 0 (Initially cp_2 value is 0 i.e ‘NO’) then it will display report output only but NOT sent into Excel file and if cp_2 value is 1 then it will display the report output as well as sent the defined output into Excel sheet.
out_file text_io.file_type;
string varchar2(1000);
It defines the maximum length of send_excel output file destination. You cannot put the output destination more than 1000.
out_file := text_io.fopen('C:\Oracle_Excel\Monthly_Trial_Bal_Report_Per_Acc.XLS','w');
fclose, fopen: is the defined function to open and close output file.
CHR(9) – is a horizontal tab
CHR(10) – is a Line feed
CHR(13) – is a Carriage return.
Note: If you are passing your parameter through form then add cf_send_excel into parameter form and remove any other parameter from report.

0 comments:

Post a Comment