Render Excel Spreadsheet in IE

A while back we had a requirement to generate a lot of heavily formatted Excel type reports out of our application. The users also wanted this integrated seamlessly into the application so that when the report was run, it displayed in the same Internet Explorer window. After a bit of research and plenty of goggling, we came up with this solution (this demo uses a copy of the emp table from the Scott schema):

Appologies in advance if the SQL / PL/SQL or HTML format gets a bit messed up but it should compile ok.

N.b. I must first point out that this only works in Office 95/2003 and IE. Firefox will generate the report but opens Excel to do so. Please take any of this code and extend it to support Office 2007 and Firefox. It is only meant as a guide / proof of concept.

Credit for this one goes to my colleague David Blake as he did the lifting on this one.
  1. Create this package (I create this in its own schema and have a public execute but creating it in the schema you are working is fine)

CREATE OR REPLACE PACKAGE excel_lib
IS


PROCEDURE create_column (

pv_span_i IN VARCHAR2 DEFAULT '1',
pv_style_i IN VARCHAR2);


PROCEDURE create_cell (
pv_class_i IN VARCHAR2 DEFAULT NULL,
pv_align_i IN VARCHAR2 DEFAULT NULL,
pv_colspan_i IN VARCHAR2 DEFAULT NULL,
pv_rowspan_i IN VARCHAR2 DEFAULT NULL,
pv_value_i IN VARCHAR2 DEFAULT NULL,
pv_value_number_i IN NUMBER DEFAULT NULL,
pv_valign_i IN VARCHAR2 DEFAULT NULL
);


PROCEDURE create_file_header (
pv_sheetname_i IN VARCHAR2,
pv_send_to_excel_i IN BOOLEAN DEFAULT TRUE
);


PROCEDURE new_row (
pv_height_i IN VARCHAR2 DEFAULT NULL,
pv_style_i IN VARCHAR2 DEFAULT NULL
);


PROCEDURE end_row;
PROCEDURE end_file;
END excel_lib;
/

CREATE OR REPLACE PACKAGE BODY excel_lib
IS
 PROCEDURE create_column (pv_span_i IN VARCHAR2 DEFAULT '1', pv_style_i IN VARCHAR2)
 IS
 BEGIN

 HTP.p ('<col style="' || pv_style_i || '" span="' || pv_span_i || '">');

 END create_column;

 

 PROCEDURE create_cell (

 pv_class_i          IN   VARCHAR2 DEFAULT NULL,

 pv_align_i          IN   VARCHAR2 DEFAULT NULL,

 pv_colspan_i        IN   VARCHAR2 DEFAULT NULL,

 pv_rowspan_i        IN   VARCHAR2 DEFAULT NULL,

 pv_value_i          IN   VARCHAR2 DEFAULT NULL,

 pv_value_number_i   IN   NUMBER DEFAULT NULL,

 pv_valign_i         IN   VARCHAR2 DEFAULT NULL

 )

 IS

 lv_html_string   VARCHAR2 (500) DEFAULT '<td';

 BEGIN

 -- Add class attribute

 IF pv_class_i IS NOT NULL

 THEN

 lv_html_string := lv_html_string || ' class="' || pv_class_i || '"';

 END IF;

 

 -- Add align attribute

 IF pv_align_i IS NOT NULL

 THEN

 lv_html_string := lv_html_string || ' align="' || pv_align_i || '"';

 END IF;

 

 -- Add x:num attribute

 IF pv_value_number_i IS NOT NULL

 THEN

 lv_html_string :=

 lv_html_string || ' x:num="' || TO_CHAR (pv_value_number_i)

 || '"';

 END IF;

 

 -- Add colspan attribute

 IF pv_colspan_i IS NOT NULL

 THEN

 lv_html_string :=

 lv_html_string || ' colspan="' || TO_CHAR (pv_colspan_i)

 || '"';

 END IF;

 

 IF pv_rowspan_i IS NOT NULL

 THEN

 lv_html_string :=

 lv_html_string || ' rowspan="' || TO_CHAR (pv_rowspan_i)

 || '"';

 END IF;

 

 

 IF pv_valign_i IS NOT NULL

 THEN

 lv_html_string :=

 lv_html_string || ' valign="' || TO_CHAR (pv_valign_i)

 || '"';

 END IF;

 

 -- Finish <td> tag

 lv_html_string :=

 lv_html_string

 || '>'

 || REPLACE (SUBSTR (pv_value_i,

 1,

 LENGTH (pv_value_i) - LENGTH (LTRIM (pv_value_i))

 ),

 ' ',

 '&nbsp;'

 )

 || LTRIM (pv_value_i)

 || '</td>';

 -- write out the cell details to the page

 HTP.p (lv_html_string);

 END create_cell;

 

 PROCEDURE new_row (

 pv_height_i   IN   VARCHAR2 DEFAULT NULL,

 pv_style_i    IN   VARCHAR2 DEFAULT NULL

 )

 IS

 BEGIN

 IF pv_height_i IS NULL AND pv_style_i IS NULL

 THEN

 HTP.p ('<tr>');

 ELSE

 HTP.p ('<tr height="' || pv_height_i || '" style="' || pv_style_i || '">');

 END IF;

 END new_row;

 

 PROCEDURE end_row

 IS

 BEGIN

 HTP.p ('</tr>');

 END end_row;

 

 PROCEDURE create_file_header (

 pv_sheetname_i       IN   VARCHAR2,

 pv_send_to_excel_i   IN   BOOLEAN DEFAULT TRUE

 )

 IS

 BEGIN

 IF pv_send_to_excel_i

 THEN

 OWA_UTIL.mime_header ('application/vnd.ms-excel');

 END IF;

 

 HTP.p

 (   '

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">

<meta NAME=ProgId content=Excel.Sheet>

<meta NAME=Generator content="Microsoft Excel 9">

<style>

<!--table

 {mso-displayed-decimal-separator:"\.";

 mso-displayed-thousand-separator:"\,";

 font-family:Arial, sans-serif;

 font-size:9pt;

 color:black;}');

 

htp.p ('@page

 {margin:1.0in .75in 1.0in .75in;

 mso-header-margin:.5in;

 mso-footer-margin:.5in;}

-->

</style>

<!--[if gte mso 9]><xml>

 <x:ExcelWorkbook>

 <x:ExcelWorksheets>

 <x:ExcelWorksheet>

 <x:Name>' || pv_sheetname_i || TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24.MI') || '</x:Name>

 <x:WorksheetOptions>

 <x:Zoom>75</x:Zoom>

 <x:Selected/>

 <x:ProtectContents>False</x:ProtectContents>

 <x:ProtectObjects>False</x:ProtectObjects>

 <x:ProtectScenarios>False</x:ProtectScenarios>

 </x:WorksheetOptions>

 </x:ExcelWorksheet>

 </x:ExcelWorksheets>

 <x:WindowHeight>15210</x:WindowHeight>

 <x:WindowWidth>21900</x:WindowWidth>

 <x:WindowTopX>480</x:WindowTopX>

 <x:WindowTopY>120</x:WindowTopY>

 <x:ProtectStructure>False</x:ProtectStructure>

 <x:ProtectWindows>False</x:ProtectWindows>

 </x:ExcelWorkbook>

</xml><![endif]-->

<title>' || pv_sheetname_i || TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24.MI') || '</title>

</head>

<BODY LINK=blue vlink=purple>

<TABLE x:str border=0 cellpadding=0 cellspacing=0 style="border-collapse:collapse;table-layout:fixed">'

 );

 END create_file_header;

PROCEDURE end_file

IS

 BEGIN

 HTP.p ('</table></body></html>');

 END end_file;

END Excel_Lib;
/


2. Create your CSS to format the report

CREATE OR REPLACE PROCEDURE report_css
AS
BEGIN
htp.p ('.xlBlue
 {vertical-align:middle;
 border:.5pt solid black;
 background:aqua;
 font-weight:700;}
.xlSilver
 {vertical-align:middle;
 border:.5pt solid black;
 background:silver;
 font-weight:700;}
.xlPlain
 {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
 vertical-align:top;}
.xlText
 {mso-number-format:"\@";
 vertical-align:top;}
.xlYellow
 {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
 background:yellow;
 font-weight:700;}
.xlHighlightedNumber
 {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
 background:#FF8080;}
.xlBold
 {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
   font-weight:700;}
.xlUnderline
    {mso-number-format:"\#\,\#\#0\.00\;\\\(\#\,\#\#0\.00\\\)";
    font-weight:700;
    text-decoration:underline;
 text-underline-style:single;
}');

END report_css;
/


3. Create your Report. Note that you build up your reports using an HTML Table row / cell layout format

CREATE OR REPLACE PROCEDURE report1
IS
   CURSOR c_emp
   IS
      SELECT *
        FROM emp;
BEGIN

   -- Writes the MIME type etc to allow excel to run in IE
   excel_lib.create_file_header ('My Report ');
   
    report_css; -- Writes your custom CSS to the page
      
   excel_lib.new_row ();
   excel_lib.create_cell (pv_value_i      => 'Emp No',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Emp Name',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Job',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Manager',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Hire Date',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Sal',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Commission',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.create_cell (pv_value_i      => 'Dept No',
                          pv_class_i      => 'xlYellow',
                          pv_align_i      => 'center'
                         );
   excel_lib.end_row;

   FOR x IN c_emp
   LOOP
      excel_lib.new_row ();
      excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.empno),
                             pv_align_i      => 'right'
                            );
      excel_lib.create_cell (pv_value_i => x.ename);
      excel_lib.create_cell (pv_value_i => x.job);
      excel_lib.create_cell (pv_value_i => x.mgr);
      excel_lib.create_cell (pv_value_i      => TO_CHAR (x.hiredate,
                                                         'DD-Mon-RRRR'
                                                        ),
                             pv_align_i      => 'right'
                            );

      IF x.sal >= 3000
      THEN
         excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.sal),
                                pv_align_i      => 'right',
                                pv_class_i      => 'xlBlue'
                               );
      ELSE
         excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.sal),
                                pv_align_i      => 'right'
                               );
      END IF;

      IF x.comm IS NULL
      THEN
         excel_lib.create_cell (pv_value_i => ' ');
      ELSE
         excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.comm),
                                pv_align_i      => 'right',
                                pv_class_i      => 'xlHighlightedNumber'
                               );
      END IF;

      excel_lib.create_cell (pv_value_i      => TO_NUMBER (x.deptno),
                             pv_align_i      => 'right'
                            );
      excel_lib.end_row;
   END LOOP;
END report1;
/


4. Run your Report

Issue a public grant on the report package and then enter the following url (note that any requests going via mod_plsql will be fine here. I used the apex dad as it is the simplest way to demo)

http://localhost:7777/pls/apex/duncan.report1

Hope that all makes sense

Comments

Mike said…
Have a question I thought you might be able to answer. I am creating a .xls file with html with some xml at the top to set some formatting. I want to set tag x:Zoom>80/x:Zoom which opens the doc with zoom at 80%. Problem is some columns display as ######. If I set it to tag x:Zoom>100/x:Zoom, open it then zoom it to 80% (or any size) it adjusts the text and displays it and does not display ######. Thanks in advance. -Mike
21 May 2008 at 18:51
Post a Comment

Popular posts from this blog

Custom Authentication / Authorisation Schemes (Part 1)

I often see posts on the APEX forum asking how to implement custom Authentication / Authorisation schemes within their applications. The following is something I have used in several apps over the last couple of years and provides a great base for securing your application. The code base is largley based on the rather excellent article " Storing Passwords in the Database " found here with a few tweaks and changes. Firstly, just to clarify, Authentication Schemes control access to the application and Authorisation Schemes control access to page items / regions and even pages themselves. Implementing your own Authentication I tend to set up an APP_USERS table that stores Username and encrypted passwords that I Authenticate against when page 101 is submitted. All this will be explained in detail as we go. Create the APP_USERS table CREATE TABLE APP_USERS ( USERNAME VARCHAR2(10), PASSWORD VARCHAR2 (255) ); Create the Application Security Package CREATE OR REPLACE PACKAGE app_sec...
Read more

Custom Error Handling in APEX

If like me you have written several PL/SQL procedures within your APEX app, you will have had to deal with the issue of error handling. As a general rule, I try to hide all Oracle Error Messages from the user and replace these with something more meaningful for example: If a user breaks a unique key by trying to insert the same value twice, the following error message is not uncommon: ORA-00001 Unique constraint violated There is little or no merit rendering this message to a user as those outside the Oracle community, may not be able to deduce what went wrong. We can capture this error and translate it to a user friendly message explaining what went wrong for example: The record you tried to create already exists. Please try again In order to achieve this in APEX, the process is relativly simple. Set up your Custom Error Page 1. Create a new page with the following attributes: Page type: Blank Page Page Number: your choice (for this example I used page 500) Page Alias: ERRPAGE Name:...
Read more

Sum to Parent Nodes in Hierarchy Queries: CONNECT_BY_ROOT

In one of our Applicaitons, the table DEPT contains a self referncing join (Pigs Ear) as it models our organsational department hierarchy. For example: CREATE TABLE DEPT ( DEPT_ID NUMBER NOT NULL ,PARENT_ID NUMBER ,DEPT_NAME VARCHAR2 (100) NOT NULL ,EMPLOYEES NUMBER NOT NULL ,CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID) ,CONSTRAINT DEPT_FK01 FOREIGN KEY (PARENT_ID) REFERENCES DEPT (DEPT_ID) ); INSERT INTO DEPT VALUES (1,NULL,'IT', 100); INSERT INTO DEPT VALUES (2,1,'DEVELOPMENT', 12); INSERT INTO DEPT VALUES (3,1,'SUPPORT', 15); INSERT INTO DEPT VALUES (4,1,'TEST', 25); INSERT INTO DEPT VALUES (5,2,'PL/SQL', 2); INSERT INTO DEPT VALUES (6,2,'Java', 1); INSERT INTO DEPT VALUES (7,2,'SQL', 11); INSERT INTO DEPT VALUES (8,2,'C++', 3); INSERT INTO DEPT VALUES (9,4,'Functional', 3); INSERT INTO DEPT VALUES (10,4,'Non Functional', 5); COMMIT; A quick tree walk using CONNECT BY PRIOR shows you the Parent / Child rel...
Read more