TechnicalBits

Following PL/SQL block will help you to export or download table data directly in your browser with .xsl format (workbook with multiple sheets or tabs).
As shown below :

declare
    l_clob clob;
    l_blob blob;
    l_sql_delimiter varchar2(30);
    l_lang_context  integer := DBMS_LOB.DEFAULT_LANG_CTX;
    l_warning       integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
    l_dest_offset   integer := 1;
    l_source_offset integer := 1;


 PROCEDURE run_query(p_sql IN VARCHAR2) IS
    v_v_val     VARCHAR2(4000);
    v_n_val     NUMBER;
    v_d_val     DATE;
    v_ret       NUMBER;
    c           NUMBER;
    d           NUMBER;
    col_cnt     INTEGER;
    f           BOOLEAN;
    rec_tab     DBMS_SQL.DESC_TAB;
    col_num     NUMBER;
  BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    -- parse the SQL statement
    DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
    -- start execution of the SQL statement
    d := DBMS_SQL.EXECUTE(c);
    -- get a description of the returned columns
    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
    -- bind variables to columns
    FOR j in 1..col_cnt
    LOOP
      CASE rec_tab(j).col_type
        WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
        WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
        WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
      ELSE
        DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
      END CASE;
    END LOOP;
    -- Output the column headers

    l_clob := l_clob||'<ss:Row>';
    FOR j in 1..col_cnt
    LOOP
      l_clob := l_clob||'<ss:Cell>';
      l_clob := l_clob||'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>';
      l_clob := l_clob||'</ss:Cell>';
    END LOOP;
    l_clob := l_clob||'</ss:Row>';
    -- Output the data
    LOOP
      v_ret := DBMS_SQL.FETCH_ROWS(c);
      EXIT WHEN v_ret = 0;
      l_clob := l_clob||'<ss:Row>';
      FOR j in 1..col_cnt
      LOOP
        CASE rec_tab(j).col_type
          WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                      l_clob := l_clob||'<ss:Cell>';
                      l_clob := l_clob||'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>';
                      l_clob := l_clob||'</ss:Cell>';
          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                      l_clob := l_clob||'<ss:Cell>';
                      l_clob := l_clob||'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>';
                      l_clob := l_clob||'</ss:Cell>';
          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                      l_clob := l_clob||'<ss:Cell ss:StyleID="OracleDate">';
                      l_clob := l_clob||'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>';
                      l_clob := l_clob||'</ss:Cell>';
        ELSE
          DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
          l_clob := l_clob||'<ss:Cell>';
          l_clob := l_clob||'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>';
          l_clob := l_clob||'</ss:Cell>';
        END CASE;
      END LOOP;
      l_clob := l_clob||'</ss:Row>';
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(c);
  END;
  --
  PROCEDURE start_workbook IS
  BEGIN
    l_clob := l_clob||'<?xml version="1.0"?>';
    l_clob := l_clob||'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">';
  END;
  PROCEDURE end_workbook IS
  BEGIN
    l_clob := l_clob||'</ss:Workbook>';
  END;
  --
  PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
  BEGIN
    l_clob := l_clob||'<ss:Worksheet ss:Name="'||p_sheetname||'">';
    l_clob := l_clob||'<ss:Table>';
  END;
  PROCEDURE end_worksheet IS
  BEGIN
    l_clob := l_clob||'</ss:Table>';
    l_clob := l_clob||'</ss:Worksheet>';
  END;
  --
  PROCEDURE set_date_style IS
  BEGIN
    l_clob := l_clob||'<ss:Styles>';
    l_clob := l_clob||'<ss:Style ss:ID="OracleDate">';
    l_clob := l_clob||'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>';
    l_clob := l_clob||'</ss:Style>';
    l_clob := l_clob||'</ss:Styles>';
  END;



begin
l_clob := null;
dbms_lob.createtemporary(l_blob, true);
--l_clob :='akshay patil';
-- Generate your file content here in l_clob.

htp.flush;
    htp.init;
sys.OWA_UTIL.mime_header ('application/octet', FALSE);
-- sys.owa_util.mime_header( 'application/octet-stream', FALSE,'UTF-8' );
sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_clob ));
-- sys.htp.p('Content-Disposition: inline; filename="FILENAME.TXT"' );
sys.HTP.p('Content-Disposition: attachment; filename="myfile.xls"');
sys.owa_util.http_header_close;

 start_workbook;
 set_date_style;
 Start_Worksheet('Customers');
 run_query('select * from u_customer where rownum<10');
 end_worksheet;
 Start_Worksheet('Employees');
 Run_Query('select * from u_user where rownum<10');
 end_worksheet;
 end_workbook;

dbms_lob.converttoblob (
dest_lob    => l_blob,
src_clob    => l_clob,
amount      => DBMS_LOB.LOBMAXSIZE,
dest_offset => l_dest_offset,
src_offset  => l_source_offset,
blob_csid   => DBMS_LOB.DEFAULT_CSID,
lang_context=> l_lang_context,
warning     => l_warning
);

sys.wpg_docload.download_file(l_blob);
apex_application.stop_apex_engine;
exception when others then
sys.htp.prn('error: '||sqlerrm);
apex_application.stop_apex_engine;
end;

Below PL/SQL Block will help you to download file from specific directory path to your browser.

 First you need to register the directory path of file with oracle using below syntax.

CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';
where:
directory_name: Database-unique directory name.
path_name: Operating system directory path (note that if the OS is case-sensitive, the name must be exact)

grant read, write on directory directory_name to database_schema

DECLARE
 
  V_Amount  Integer;
  V_Src_Loc Bfile;
  v_b       BLOB;


BEGIN

  DBMS_LOB.CREATETEMPORARY(V_B,true);
   v_src_loc  := BFILENAME('MY_DIR', 'myfile.xls');
--MY_DIR is the directory registered with a path where file is present.

   Sys.Dbms_Lob.Open(V_Src_Loc, Dbms_Lob.Lob_Readonly);
   V_Amount := SYS.Dbms_Lob.Getlength(V_Src_Loc);

   htp.flush;
   htp.init;
-- Set the MIME type
   OWA_UTIL.mime_header ('application/octet', FALSE);
   htp.p('Content-length: ' || V_Amount );
   HTP.p('Content-Disposition: attachment; filename="myfile.xls"');
   OWA_UTIL.http_header_close;


   Sys.Dbms_Lob.Loadfromfile(V_B, V_Src_Loc, V_Amount);
   Sys.Dbms_Lob.Close(V_Src_Loc);
  UTL_FILE.FREMOVE(location=>'MY_DIR',filename=>'myfile.xls');

   wpg_docload.download_file(V_B);
   apex_application.stop_apex_engine;



 exception
 when others then
   htp.p('Error '||SQLERRM);

END;

Following PL/SQL Block generates workbook (.xsl) file with two worksheet's named as Employees and Customers containing detail level data about employee and customer.
By using below code,you can directly export the various sql queries output with multiple sheets into single workbook.

To store generated workbook at specific path, first you need to register that path with oracle using below syntax.

CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';
where:
directory_name: Database-unique directory name.
path_name: Operating system directory path (note that if the OS is case-sensitive, the name must be exact)

grant read, write on directory directory_name to database_schema

DECLARE
  v_fh        UTL_FILE.FILE_TYPE;
  v_dir       VARCHAR2(30) := 'MY_DIR'; --Directory Name
  V_File      Varchar2(30) := 'myfile.xls';
  
  V_Amount  Integer;
  V_Src_Loc Bfile;
  v_b       BLOB;
  
  PROCEDURE run_query(p_sql IN VARCHAR2) IS
    v_v_val     VARCHAR2(4000);
    v_n_val     NUMBER;
    v_d_val     DATE;
    v_ret       NUMBER;
    c           NUMBER;
    d           NUMBER;
    col_cnt     INTEGER;
    f           BOOLEAN;
    rec_tab     DBMS_SQL.DESC_TAB;
    col_num     NUMBER;
  BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    -- parse the SQL statement
    DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
    -- start execution of the SQL statement
    d := DBMS_SQL.EXECUTE(c);
    -- get a description of the returned columns
    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
    -- bind variables to columns
    FOR j in 1..col_cnt
    LOOP
      CASE rec_tab(j).col_type
        WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
        WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
        WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
      ELSE
        DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
      END CASE;
    END LOOP;
    -- Output the column headers
    UTL_FILE.PUT_LINE(v_fh,'<ss:Row>');
    FOR j in 1..col_cnt
    LOOP
      UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
      UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>');
      UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
    END LOOP;
    UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
    -- Output the data
    LOOP
      v_ret := DBMS_SQL.FETCH_ROWS(c);
      EXIT WHEN v_ret = 0;
      UTL_FILE.PUT_LINE(v_fh,'<ss:Row>');
      FOR j in 1..col_cnt
      LOOP
        CASE rec_tab(j).col_type
          WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
                      UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>');
                      UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Cell ss:StyleID="OracleDate">');
                      UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>');
                      UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
        ELSE
          DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
          UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
          UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
          UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
        END CASE;
      END LOOP;
      UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(c);
  END;
  --
  PROCEDURE start_workbook IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'<?xml version="1.0"?>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">');
  END;
  PROCEDURE end_workbook IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'</ss:Workbook>');
  END;
  --
  PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'<ss:Worksheet ss:Name="'||p_sheetname||'">');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Table>');
  END;
  PROCEDURE end_worksheet IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'</ss:Table>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Worksheet>');
  END;
  --
  PROCEDURE set_date_style IS
  BEGIN
    UTL_FILE.PUT_LINE(v_fh,'<ss:Styles>');
    UTL_FILE.PUT_LINE(v_fh,'<ss:Style ss:ID="OracleDate">');
    UTL_FILE.PUT_LINE(v_fh,'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Style>');
    UTL_FILE.PUT_LINE(v_fh,'</ss:Styles>');
  END;
BEGIN
  v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w',32767);
  start_workbook;
  set_date_style;
  Start_Worksheet('Customers');
  run_query('select * from customer where rownum<10');
  end_worksheet;
  Start_Worksheet('Employees');
  Run_Query('select * from user where rownum<10');
  end_worksheet; 
  end_workbook;
  UTL_FILE.FCLOSE(v_fh);
  
END;