Monday, July 24, 2006

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:
HTML-table in the browser
3. By adding the owa_util lines in the procedure and executing it in your browser you’ll get a download option window:
Download option window
4. If you select “Openen met” (”Open with”) Excel starts and the document looks like this:
Results in Excel
So that creates the possiblity to create Excel output just the way your user likes it!
Post a Comment