How to create neatly formatted Excel documents using PL/SQL?

If there is a requirement to produce output from an application into Excel, you would probably create a CSV (Comma Separated File) with the data and start Excel to show the data - at least that's what I did...until now. The drawback of this solution is that you could only produce data and no nice layout. But Excel is also capable of opening HTML-files and using this you could create Excel files with data and magnificent layout!

Let me give an example:
1. Create a procedure to show the data in formatted in an HTML table.

CREATE OR REPLACE PROCEDURE display_emp_list IS
  v_emp_count NUMBER(5);
  v_empno NUMBER(8);
  v_ename VARCHAR2(50);
  v_job emp.job%TYPE;
  v_sal emp.sal%TYPE;
  v_bg_color VARCHAR2(10) := '';
  CURSOR c_emp IS
    SELECT empno,
           initcap(ename),
           job,
           sal
    FROM emp
    ORDER BY ename;
BEGIN
  SELECT COUNT(*)
  INTO v_emp_count
  FROM emp;
  owa_util.mime_header('application/ms-excel',   FALSE);
  htp.p('Content-Disposition: filename="test.xls"');
  owa_util.http_header_close;
  htp.htmlopen;
  htp.bodyopen;
  htp.header(5,   '<font color="RED"> <center> EMPLOYEE INFO </center></font>');
  htp.tableopen(cattributes=>'border="2",bordercolor="BLACK",width="60%",align="CENTER"');
  htp.tablerowopen(cattributes => 'bgcolor="GREEN"');
  htp.tableheader(cattributes => 'colspan="4", align="CENTER"'
                 ,cvalue => '<font face="ARIAL" size="2.5" color="WHITE">'
                             || 'Employees. (List Count:' || v_emp_count || ')');
  htp.tablerowclose;
  htp.tablerowopen(cattributes => 'align="LEFT", bgcolor="YELLOW"');
  htp.tableheader(cvalue => '<font face="ARIAL" size="2.25" color="BLUE">' || 'No.');
  htp.tableheader(cvalue => '<font face="ARIAL" size="2.25" color="BLUE">' || 'Name');
  htp.tableheader(cvalue => '<font face="ARIAL" size="2.25" color="BLUE">' || 'Job');
  htp.tableheader(cvalue => '<font face="ARIAL" size="2.25" color="BLUE">' || 'Sal');
  htp.tablerowclose;
  OPEN c_emp;
  FETCH c_emp
  INTO v_empno,
       v_ename,
       v_job,
       v_sal;
  WHILE c_emp % FOUND
  LOOP
    IF MOD(v_emp_count,   2) = 0
  THEN
      v_bg_color := 'SILVER';
    ELSE
      v_bg_color := 'WHITE';
    END IF;
    htp.tablerowopen(cattributes => 'bgcolor=' || v_bg_color);
    htp.tabledata(cattributes => 'align="LEFT"'
                 ,cvalue => '<font face="ARIAL" size="2" color="BLACK">' || v_empno);
    htp.tabledata(cattributes => 'align="LEFT"'
                 ,cvalue => '<font face="ARIAL" size="2" color="BLACK">' || v_ename);
    htp.tabledata(cattributes => 'align="LEFT"'
                 ,cvalue => '<font face="ARIAL" size="2" color="BLACK">' || v_job);
    htp.tabledata(cattributes => 'align="LEFT"'
                 ,cvalue => '<font face="ARIAL" size="2" color="BLACK">' || v_sal);
    htp.tablerowclose;
    v_emp_count := v_emp_count -1;
    FETCH c_emp
    INTO v_empno,
         v_ename,
         v_job,
         v_sal;
  END LOOP;
  CLOSE c_emp;
  htp.tablerowclose;
  htp.tableclose;
  htp.bodyclose;
  htp.htmlclose;
EXCEPTION
  WHEN no_data_found
THEN
  NULL;
END;

2. In the browser the table looks like this:

3. By adding the owa_util lines in the procedure and executing it in your browser you’ll get a download option window:

4. If you select “Openen met” (”Open with”) Excel starts and the document looks like this:

So that creates the possiblity to create Excel output just the way your user likes it!