Skip to main content

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!

Comments

Anonymous said…
Roel,
I tried executing you proc. It executed fine, but I do not know where to retrieve the "test.xls" file from. I tried specifying a path such as "c:\test.xls", but it is not there.

Let me know
Thanks
Roel said…
How and where did you start the procedure? From within SQLPlus, TOAD or SQLDeveloper or from within your browser - assuming you have a webserver with a DAD installed?
Anonymous said…
I tried to execute it from TOAD.
Roel said…
The procedure shown in the blog is run from within the browser (using a DAD - e.g. using XE). Then you will be prompted what to do with the tet.xls file. You can see some of the output in TOAD by using the owa_util.showpage procedure.
Anonymous said…
hey roel, your above code is working on excel 2003, but do you think above code is supported by excel 2000 forwards ?
Roel said…
I didn't test it, but I expect it to work from Excel 2000 upwards, because there are no 'new features' used.
Anonymous said…
How could I run this as a standalone procedure??

I see you have a internet explorer.
But I am using PL/SQL developer.

I am trying to find a way to create a excel sheet from Oracle and came across you article.

Thanks,
Roel said…
If you want to store the result on a file system or in the database, you should use create a function (similar to the shown procedure) which uses htf-functions instead of the htp-procedures (use search-and-replace) and returning the total content (in a clob). The result can be saved to disk (using utl_file) or somewhere in the database.

HTH
Roel
Anonymous said…
I have generated a package in pl/sql to generate excel file. This basically creates Excel in xml format. Please find it here.
http://sanjeev-oracle-world.blogspot.com
Arun DIixt said…
Hi,

if we can also create tabs in excel workbook. i mean more than two sheets in a excel workbook.
Roel said…
Take a look at http://radio.weblogs.com/0137094/2006/10/26.html for information on ExcelDocumentType. That's a great solution for creating complex spreadsheets!
Anonymous said…
can I run from sqlplus?
Anonymous said…
I tried this , it works fine but the rows and columns outside the html table in the excel file have no formatting. Is there a solution to open the results as excel and still retain the formatting ?
kaparthi said…
how to reduce the table row hight and width in htp.tablerow
Alex said…
Yesterday when I worked with excel files I saved it and went to bed,next morning I saw that my file corrupted,but in some hours to me came a friend,he advised me-repairing Excel,as he said this tool is reliable and has free status,it repairing Excel files, except the possibility to export recovered data into a new Microsoft Excel file,repairing Microsoft Excel files,will learn more about this problem and about how to repair an Excel file,contains the most features of registered program for repairing Excel files, except the possibility to export recovered data into a new Microsoft Excel file.
Anonymous said…
Some days ago I convert my excel file in other format and unfortunately it was damaged.In this situation friend said me about one nice tool-xlsx viewer.As he said it helped to him and has free status.Moreover program helped me too and it can farther recovery of corrupted worksheets in xltm, xlsx, xlt, xls, xlsm, xlam and xltx formats.
Anonymous said…
Test.xls is getting created in the procedure. What should be done if the excel file is already existing and formatted. Can i add some additional formatting for an existing formatted .xls file.Need help on this.TIA
Anonymous said…
Hey,

Check out this PL/SQL tool kit we started using. It let's the PL/SQL developer call a couple of procs to do this and more. It supports functions that our business folks in finance love. It's at http://www.peak42solutions.com. It has a weird name excellant.

Thanks,

Bill
Alex said…
Excel files are very popular on my PC. Once I lost all of them. I was upset, but I kept one's head and entered the Inet. I discovered there - this file is not in a recognizable format Excel, which restored whole excel files quite simply.
Tim Ward said…
I've searched all over the place, but haven't been able to get this to work....

I know this is an old post, does it still work though?
Anonymous said…
You can also try ORA_EXCEL package which can generate formatted excel xlsx files from PL/SQQ, for more info please chech http://www.oraexcel.com

Popular posts from this blog

apex_application.g_f0x array processing in Oracle 12

If you created your own "updatable reports" or your custom version of tabular forms in Oracle Application Express, you'll end up with a query that looks similar to this one: then you disable the " Escape special characters " property and the result is an updatable multirecord form. That was easy, right? But now we need to process the changes in the Ename column when the form is submitted, but only if the checkbox is checked. All the columns are submitted as separated arrays, named apex_application.g_f0x - where the "x" is the value of the "p_idx" parameter you specified in the apex_item calls. So we have apex_application.g_f01, g_f02 and g_f03. But then you discover APEX has the oddity that the "checkbox" array only contains values for the checked rows. Thus if you just check "Jones", the length of g_f02 is 1 and it contains only the empno of Jones - while the other two arrays will contain all (14) rows. So for

Filtering in the APEX Interactive Grid

Remember Oracle Forms? One of the nice features of Forms was the use of GLOBAL items. More or less comparable to Application Items in APEX. These GLOBALS where often used to pre-query data. For example you queried Employee 200 in Form A, then opened Form B and on opening that Form the Employee field is filled with that (GLOBAL) value of 200 and the query was executed. So without additional keys strokes or entering data, when switching to another Form a user would immediately see the data in the same context. And they loved that. In APEX you can create a similar experience using Application Items (or an Item on the Global Page) for Classic Reports (by setting a Default Value to a Search Item) and Interactive Reports (using the  APEX_IR.ADD_FILTER  procedure). But what about the Interactive Grid? There is no APEX_IG package ... so the first thing we have to figure out is how can we set a filter programmatically? Start with creating an Interactive Grid based upon the good old Employ

Stop using validations for checking constraints !

 If you run your APEX application - like a Form based on the EMP table - and test if you can change the value of Department to something else then the standard values of 10, 20, 30 or 40, you'll get a nice error message like this: But it isn't really nice, is it? So what do a lot of developers do? They create a validation (just) in order to show a nicer, better worded, error message like "This is not a valid department".  And what you then just did is writing code twice : Once in the database as a (foreign key) check constraint and once as a sql statement in your validation. And we all know : writing code twice is usually not a good idea - and executing the same query twice is not enhancing your performance! So how can we transform that ugly error message into something nice? By combining two APEX features: the Error Handling Function and the Text Messages! Start with copying the example of an Error Handling Function from the APEX documentation. Create this function