Need help with an export to excel procedure
Hi,
Need help with the following...
I have a report in APEX, and use a pl/sql pkg to solve the export to excel.
For this export to excel, we use a kind of "print", using the following header style:
v_head :=
'<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=UTF-8">'
|| '<meta name=ProgId content=Excel.Sheet>'
|| '<meta name=Generator content="Microsoft Excel 9">'
|| '<link rel=File-List href="./Sheet1-File/filelist.xml">'
|| '<link rel=Edit-Time-Data href="./Sheet1-File/editdata.mso">'
|| '<link rel=OLE-Object-Data href="./Sheet1-File/oledata.mso">'
|| '<!--[if gte mso 9]><xml>'
|| '<o:DocumentProperties>'
In this header we setup types of styles to format a cell in excel, like this:
.style0'
|| '{mso-number-format:General;'
|| 'text-align:general;'
|| 'vertical-align:bottom;'
|| 'white-space:nowrap;'
|| 'mso-rotate:0;'
|| 'mso-background-source:auto;'
|| 'mso-pattern:auto;'
|| 'color:windowtext;'
|| 'font-size:10.0pt;'
|| 'font-weight:400;'
|| 'font-style:normal;'
|| 'text-decoration:none;'
|| 'font-family:Arial;'
|| 'mso-generic-font-family:auto;'
|| 'mso-font-charset:0;'
|| 'border:none;'
|| 'mso-protection:locked visible;'
|| 'mso-style-name:Standard;'
|| 'mso-style-id:0;}'
|| '.xl25'
|| '{mso-style-parent:style0;'
|| 'border:.5pt solid windowtext;'
|| 'background:#ffffff;'
|| 'mso-pattern:auto none;}'
|| '.xl44'
|| '{mso-style-parent:xl25;'
|| 'mso-number-format:"0.0%";'
|| 'mso-style-name:Percent;'
|| 'border:.5pt solid windowtext;'
|| 'background:#ffffff;'
|| 'mso-pattern:auto none;}'
Now, when we "print" a value to export it to excel, we format it with the desired style... like this:
v_print_row := v_print_row || '<td class=xl25>'||c.field|| '</td>'||chr(10);
Now the problem is the following, though we have found the correct format to show a number as desired... when opening the exported excel file, we have 2 issues:
1- The excel file sets a warning saying that the number is stored as text.
2- When clicking on the cell and selecting from the context menu, the "format cell" option, it shows it as a number.
Is there a solution for this? I mean, that the number actually is considered to be a number by excel, instead of warning me that the number is stored as text?
As a curious thing, we created a style of percentage, and the only way to make it consider it as a number was to set something like this:
v_print_row := v_print_row || '<td class=xl44>=' ||c.field|| '/100</td>'||chr(10);
And this has been the only way that it set the percentage as a number, all rows except for 2, which represented numbers for 0.0%; 12.0%.
Perhaps someone can help me to bye pointing out what I am missing?
Thanks a lot!