Wednesday, 9 May 2007

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

3 comments:

Don 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

Alex said...

Some weeks ago I exported text file in my MS excel file and my file was damaged after that.This situation I could work out with help-recover xls files.This tool I found in a community.It is free as far as I know,besides that application can recover Excel information if somehow it was corrupted.

Alex said...

To my mind on my PC I was different tools. But one tool I couldn't find there - Excel file recovery xlsx,which solved my important problem with corrupted sheets in xls file. I found somewhere in the Internet and didn't pay in hard cash anything as far as I kept in mind.