Home

Export to Excel (Multiple Sheets)

How-To Instructions
-------------------
1) copy and run the excel_xml.sql code as a script in the SQL Workshop
2a) create a button to submit a URL and set "URL Target" to: EBERHARD.excel_xml
or
2b) create a "display as text" item and set "Source value or expression" to: <a href="EBERHARD.excel_xml">Link</A>

* Note: For a basic intro into Excel's XML support visit: http://blogs.msdn.com/brian_jones/archive/2005/06/27/433152.aspx

excel_xml.sql

sql

create or replace procedure excel_xml
as

l_xml_header varchar2(2000);
 l_xml_body clob;
 l_xml_footer varchar2(100);

begin
owa_util.mime_header('application/txt');

l_xml_header := '<?xml version="1.0"?>'||chr(10)||
'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'||chr(10)||
'xmlns:o="urn:schemas-microsoft-com:office:office"'||chr(10)||
'xmlns:x="urn:schemas-microsoft-com:office:excel"'||chr(10)||
'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'||chr(10)||
'xmlns:html="http://www.w3.org/TR/REC-html40">'||chr(10)||
'<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'||chr(10)||
'<WindowHeight>8580</WindowHeight>'||chr(10)||
'<WindowWidth>15180</WindowWidth>'||chr(10)||
'<WindowTopX>120</WindowTopX>'||chr(10)||
'<WindowTopY>45</WindowTopY>'||chr(10)||
'<ProtectStructure>False</ProtectStructure>'||chr(10)||
'<ProtectWindows>False</ProtectWindows>'||chr(10)||
'</ExcelWorkbook>'||chr(10)||
'<Styles>'||chr(10)||
'<Style ss:ID="Default" ss:Name="Normal">'||chr(10)||
'<Alignment ss:Vertical="Bottom"/>'||chr(10)||
'<Borders/>'||chr(10)||
'<Font/>'||chr(10)||
'<Interior/>'||chr(10)||
'<NumberFormat/>'||chr(10)||
'<Protection/>'||chr(10)||
'</Style>'||chr(10)||
'<Style ss:ID="s22">'||chr(10)||
'<Font x:Family="Swiss" ss:Bold="1" ss:Underline="Single"/>'||chr(10)||
'</Style>'||chr(10)||
'</Styles>';

htp.p(l_xml_header);

-- 
-- SQL for sheet grouping
--
for xx in (select deptno, dname from dept)
loop
  --
  -- Create the next worksheet
  --
  l_xml_body:=l_xml_body||'<Worksheet ss:Name="'||xx.dname||'"><Table>';

--
  -- Add a header row to the Sheet
  --
  l_xml_body:=l_xml_body||'<Row><Cell ss:StyleID="s22"><Data ss:Type="String">EMP #</Data></Cell>'||
                          '<Cell ss:StyleID="s22"><Data ss:Type="String">NAME</Data></Cell>'||
                          '<Cell ss:StyleID="s22"><Data ss:Type="String">JOB</Data></Cell>'||
                          '<Cell ss:StyleID="s22"><Data ss:Type="String">HIREDATE</Data></Cell></Row>';

--
  -- SQL for the sheet rows
  --
  for z in (select empno, ename, job, hiredate 
              from emp  
             where deptno = xx.deptno)
  loop
  --
  -- Add the detail row
  --
     l_xml_body := l_xml_body||'<Row><Cell><Data ss:Type="String">'||
         z.empno||'</Data></Cell><Cell><Data ss:Type="String">'||
         z.ename||'</Data></Cell><Cell><Data ss:Type="String">'||
         z.job  ||'</Data></Cell><Cell><Data ss:Type="String">'||
         z.hiredate||'</Data></Cell></Row>'||chr(10);

end loop;
  l_xml_body := l_xml_body||'</Table></Worksheet>';
 
end loop;
htp.p(l_xml_body);
htp.p('</Workbook>');
end;
/
grant execute on excel_xml to public
/