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
SanjeevSapre 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 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 ?
santhosh 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

Refresh selected row(s) in an Interactive Grid

In my previous post I blogged about pushing changed rows from the dabatase into an Interactive Grid. The use case I'll cover right here is probably more common - and therefore more useful!

Until we had the IG, we showed the data in a report (Interactive or Classic). Changes to the data where made by popping up a form page, making changes, saving and refreshing the report upon closing the dialog. Or by clicking an icon / button / link in your report that makes some changes to the data (like changing a status) and ... refresh the report.  That all works fine, but the downsides are: The whole dataset is returned from the server to the client - again and again. And if your pagination size is large, that does lead to more and more network traffic, more interpretation by the browser and more waiting time for the end user.The "current record" might be out of focus after the refresh, especially by larger pagination sizes, as the first rows will be shown. Or (even worse) while you…

Dockerize your APEX development environment

Nowadays Docker is everywhere. It is one of the main components of Continuous Integration / Continuous Development environments. That alone indicates Docker has to be seen more as a Software Delivery Platform than as a replacement of a virtual machine.

However ...

If you are running an Oracle database using Docker on your local machine to develop some APEX application, you will probably not move that container is a whole to test and production environments. Because in that case you would not only deliver a new APEX application to the production environment - which is a good thing - but also overwrite the data in production with the data from your development environment. And that won't make your users very excited.
So in this set up you will be using Docker as a replacement of a Virtual Machine and not as a Delivery Platform.
And that's exactly the way Martin is using it as he described in this recent blog post. It is an ideal way to get up and running with an Oracle database …

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 processing y…