Solution - Generate UTF-8 excel with multiple worksheets
59640Jul 5 2006 — edited Jul 24 2007Dear all,<p>
I found the solution on how to generate UTF-8 excel workbooks with multiple worksheets.<p>
here is the code:<p>
DECLARE<p>
CURSOR c_RU IS<br>
SELECT name FROM russiantable.name<br>
WHERE ROWNUM < 200;<br>
<br><br>
CURSOR c_FR IS<br>
SELECT name FROM frenchtable<br>
WHERE ROWNUM < 200;<br>
begin<br><br>
-- Generate an UTF-8 file, no ascii file<br>
owa_util.mime_header( 'text/html', FALSE, 'utf-8' );<br><br>
-- Set the name of the file<br>
htp.p('Content-Disposition: attachment; filename="export.excel_html"');<br>
-- Close the HTTP Header<br>
owa_util.http_header_close;<br><br>
htp.prn('<?xml version="1.0" encoding="utf-8"?> <br>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" <br>
xmlns:o="urn:schemas-microsoft-com:office:office"<br>
xmlns:x="urn:schemas-microsoft-com:office:excel"<br>
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"<br>
xmlns:html="http://www.w3.org/TR/REC-html40"><br>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"><br>
<WindowHeight>8580</WindowHeight><br>
<WindowWidth>15180</WindowWidth><br>
<WindowTopX>120</WindowTopX><br>
<WindowTopY>45</WindowTopY><br>
<ProtectStructure>False</ProtectStructure><br>
<ProtectWindows>False</ProtectWindows><br>
</ExcelWorkbook><br>
<Styles><br>
<Style ss:ID="Default" ss:Name="Normal"><br>
<Alignment ss:Vertical="Bottom"/><br>
<Borders/><br>
<FFont/><br>
<Interior/><br>
<NumberFormat/><br>
<Protection/><br>
</Style> <br>
<Style ss:ID="s22"><br>
<FFont x:Family="Swiss" ss:Bold="1" ss:Underline="Single"/> <br>
</Style></Styles><br>
' );<br><br>
--- RU<br>
htp.prn('<Worksheet ss:Name="RU"><br>
<Table><br>
<Row><Cell ss:StyleID="s22"><Data ss:Type="String">NAME</Data></Cell></Row>');<br><br>
FOR crec in c_RU LOOP<br>
htp.p('<Row><Cell><Data ss:Type="String">'|| crec.name ||'</Data></Cell></Row>');<br>
end loop;<br>
htp.prn('</Table></Worksheet>');<br><br>
--FR<br>
htp.prn('<Worksheet ss:Name="FR"><br>
<Table><br>
<Row><Cell ss:StyleID="s22"><Data ss:Type="String">NAME</Data></Cell></Row>');<br><br>
FOR crec in c_FR LOOP<br>
htp.p('<Row><Cell><Data ss:Type="String">'|| crec.name ||'</Data></Cell></Row>');<br>
end loop;<br>
htp.prn('</Table></Worksheet>');<br><br>
-- close<br><br>
htp.prn('</Workbook>');<br>
htmldb_application.g_unrecoverable_error := true;<br>
end;<br>
</small>
<br><br><br>
This code generates the following XML:<br><br>
<?xml version="1.0" encoding="utf-8"?> <br>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" <br>
xmlns:o="urn:schemas-microsoft-com:office:office" <br>
xmlns:x="urn:schemas-microsoft-com:office:excel" <br>
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" <br>
xmlns:html="http://www.w3.org/TR/REC-html40"> <br>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <br>
<WindowHeight>8580</WindowHeight> <br>
<WindowWidth>15180</WindowWidth> <br>
<WindowTopX>120</WindowTopX> <br>
<WindowTopY>45</WindowTopY> <br>
<ProtectStructure>False</ProtectStructure> <br>
<ProtectWindows>False</ProtectWindows> <br>
</ExcelWorkbook> <br>
<Styles> <br>
<Style ss:ID="Default" ss:Name="Normal"> <br>
<Alignment ss:Vertical="Bottom"/> <br>
<Borders/> <br>
<FFont/> <br>
<Interior/> <br>
<NumberFormat/> <br>
<Protection/> <br>
</Style> <br>
<Style ss:ID="s22"> <br>
<FFont x:Family="Swiss" ss:Bold="1" ss:Underline="Single"/> <br>
</Style></Styles> <br><br>
<Worksheet ss:Name="FR"> <br>
<Table> <br>
<Row><Cell ss:StyleID="s22"><Data ss:Type="String">NAME</Data></Cell></Row> <br>
<Row><Cell><Data ss:Type="String">Fraéè</Data></Cell></Row><br>
<Row><Cell><Data ss:Type="String">Valby</Data></Cell></Row><br>
....<br>
</Table></Worksheet><Worksheet ss:Name="RU"> <br>
<Table> <br>
<Row><Cell ss:StyleID="s22"><Data ss:Type="String">NAME</Data></Cell></Row> <br>
....
</Table></Worksheet></Workbook><br>