APEX

Dear 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>