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.

1 comments:

  1. If you want your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (even if they're dating somebody else now) you need to watch this video
    right away...

    (VIDEO) Get your ex CRAWLING back to you...?

    ReplyDelete