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.