I came across a PL/sql piece of code which exports data to Excel by generating HTML, but the export to Excel was not supporting multibyte charaters like Chinese, Portuguese ,Turkish, Polish, Scandinavian characters. When it encounters any mutilbyte character it use to put some junk characters in the Excel report.
So here i will point out how to generate multibyte or UTF-8 characters in excel report.
Ideally, the notation to generate HTML from pl/sql code is done by "htp.p" statements show as below:
sample code:
htp.p('<table BORDER="1" CELLSPACING="0" CELLPADDING="0" width=50%>
<tr><td><b>From:</b></td><td>ABC Financing</td></tr>
<tr><td><b>To:</b></td><td>Cash Apps</td></tr>
<tr><td><b>Date:</b></td><td align="left">'||trunc(sysdate)||'</td></tr>
</table><br>');
htp.p('<table width=80% CELLSPACING="0" CELLPADDING="4" border=1>
You can use the Oracle supplied custom package to generate Excel file by calling
OWA_UTIL.MIME_HEADER('application/vnd.ms-excel');
Subsequently you can print whatever data you want your exported excel to hold.
Here is the catch!!! It exports data to excel report but fails to export multibyte characters. So to support this we need to add few lines of code which will form the HTML header.
Below procedure adds the header to HTML generated :
c_utf_charset VARCHAR2(200) := '
<META http-equiv=Content-Type content="text/html; charset=UTF-8">';
PROCEDURE headerExcel(in_title VARCHAR2 DEFAULT c_title)
IS
BEGIN
htp.p('
<HTML>
<HEAD>
<TITLE>' || in_title || '</TITLE>' || c_utf_charset );
END headXL;
Call this procedure just after :
OWA_UTIL.MIME_HEADER('application/vnd.ms-excel');
headerExcel('Sample Text')
So here i will point out how to generate multibyte or UTF-8 characters in excel report.
Ideally, the notation to generate HTML from pl/sql code is done by "htp.p" statements show as below:
sample code:
htp.p('<table BORDER="1" CELLSPACING="0" CELLPADDING="0" width=50%>
<tr><td><b>From:</b></td><td>ABC Financing</td></tr>
<tr><td><b>To:</b></td><td>Cash Apps</td></tr>
<tr><td><b>Date:</b></td><td align="left">'||trunc(sysdate)||'</td></tr>
</table><br>');
htp.p('<table width=80% CELLSPACING="0" CELLPADDING="4" border=1>
You can use the Oracle supplied custom package to generate Excel file by calling
OWA_UTIL.MIME_HEADER('application/vnd.ms-excel');
Subsequently you can print whatever data you want your exported excel to hold.
Here is the catch!!! It exports data to excel report but fails to export multibyte characters. So to support this we need to add few lines of code which will form the HTML header.
Below procedure adds the header to HTML generated :
c_utf_charset VARCHAR2(200) := '
<META http-equiv=Content-Type content="text/html; charset=UTF-8">';
PROCEDURE headerExcel(in_title VARCHAR2 DEFAULT c_title)
IS
BEGIN
htp.p('
<HTML>
<HEAD>
<TITLE>' || in_title || '</TITLE>' || c_utf_charset );
END headXL;
Call this procedure just after :
OWA_UTIL.MIME_HEADER('application/vnd.ms-excel');
headerExcel('Sample Text')
