Tuesday, 14 June 2016

Excel Merge and Download using Custom Template in APEX


For Merging cells in Excel and for other CSS in download property, i.e  to create 
down loadable XLS formatted sheets from any version of APEX use the following steps:

1. Create a new blank page in the application using Customized template. In that customized template remove all the contents in the page, like navigation menus, APEX page Toolbar, and others , since by putting this in a Before Header process on a page, the entire page will be downloaded as a native Excel Spreadsheet.

2. Create PLSQL Region with no template.

-- Should use recursion for a full tree, but we only need executives. 
DECLARE
   l_dirtotal   NUMBER (12, 2);
BEGIN
   HTP.p
      ('<table border=1 white-space=nowrap><tr style="background-color:silver;">
    <th colspan=5>Employee</th><th>Title</th><th>Salary</th>
    <th>Direct Report Total</th></tr>'
      );

   FOR c_exec IN (SELECT *
                    FROM employee
                   WHERE direct_report_id IS NULL)
   LOOP
      SELECT SUM (salary)
        INTO l_dirtotal
        FROM employee
       WHERE direct_report_id = c_exec.ID;

      HTP.p
         (   '<tr height=80><th align=right><img src="'
          || c_exec.image
          || '"></th><th colspan=4 style="white-space:nowrap;font-size:36px;color:green;background-color:#CCFFCC;">'
          || c_exec.NAME
          || '</th><th>'
          || c_exec.title
          || '</th>
    <th style="font-size:36px;">'
          || TO_CHAR (c_exec.salary, '$999G999G999D00')
          || '</th><th>'
          || TO_CHAR (l_dirtotal, '$999G999G999G999D00')
          || '</th></tr>'
         );

      -- Layer 1
      FOR c_vp IN (SELECT *
                     FROM employee
                    WHERE direct_report_id = c_exec.ID)
      LOOP
         SELECT SUM (salary)
           INTO l_dirtotal
           FROM employee
          WHERE direct_report_id = c_vp.ID;

         HTP.p
            (   '<tr height=55><th colspan=3 align=right><img src="'
             || c_vp.image
             || '"></td><td colspan=2 style="font-size:24px;color:#0000CC;white-space:nowrap;background-color:#CCCCFF;">'
             || c_vp.NAME
             || '</td><td style="text-size:18px;text-align:center;">'
             || c_vp.title
             || '</td><td style="color:red;text-align:right;font-size:20px;">'
             || TO_CHAR (c_vp.salary, '$999G999G999D00')
             || '</td><td>'
             || TO_CHAR (l_dirtotal, '$999G999G999G999D00')
             || '</td></tr>'
            );

         -- Layer 2
         FOR c_grunt IN (SELECT *
                           FROM employee
                          WHERE direct_report_id = c_vp.ID)
         LOOP
            HTP.p
               (   '<tr height=50><th colspan=4 align=right>
        <img src="'
                || c_grunt.image
                || '"></td><td style="text-size:14px;white-space:nowrap;">'
                || c_grunt.NAME
                || '</td><td style="text-align:center;">'
                || c_grunt.title
                || '</td><td style="text-align:right;">'
                || TO_CHAR (c_grunt.salary, '$999G999G999D00')
                || '</td></tr>'
               );
         END LOOP;
      END LOOP;
   END LOOP;

   HTP.p ('</table>');
END;
(Modify the code as per the requirement)


3. Create Before Header process to set HTTP Headers.

BEGIN
   OWA_UTIL.mime_header ('application/vnd.ms-excel', FALSE);
   HTP.prn ('Content-Disposition: attachment; filename="spreadsheet.xls"
'  );
   OWA_UTIL.http_header_close;
END;


4. Redirect the Download button in your screen to this new page.

Sample Output:






















1 comment:

  1. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad

    ReplyDelete