Export to Excel – How to prevent Long numbers from scientific Notation
Posted by sarbashish on November 30, 2012
If you have a varchar field in your database which can accommodate more than 12 characters and one of your row has a data which is a pure number for example 12312312312349875 and you export the data in excel and you will see the data displaying as 1.23123E+16.
If you are entering data in excel then you will get around by putting a single quote in the front to change the formatting. See screenshot below:
Now to fix the issue in the export to excel routine of your program you need to do cell formatting so that excel does not represent the long number using scientific notation.
Sample Code
<%
Response.Clear
Response.CacheControl = “no-cache”
Response.AddHeader “Pragma”, “no-cache”
Response.Expires = -1
Response.ContentType = “application/vnd.ms-excel”
Dim FileName
FileName = “TestDB Lookup-” & month(now)&”-“&day(now)&”-“&year(now)&”.xls”
Response.AddHeader “Content-Disposition”, “inline;filename=” & FileName
%>
<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″>
<!–[if gte mso 9]>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheet>
<x:WorksheetOptions>
<x:DisplayGridlines/>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
<![endif]–>
</head>
<body>
<table border=”0″>
<tr>
<td>ID</td>
<td>Name</td>
</tr><tr>
<td style=”mso-number-format:\@;”>01234567890123456567678788989909000030</td>
<td>Sarbashish B</td>
</tr>
</table>
</body>
</html>
This entry was posted on November 30, 2012 at 3:15 am and is filed under Classic ASP. Tagged: Displaying long numeric values in Export to Excel, Export to Excel - How to prevent Long numbers from scientific Notation, Leading zeros disappears when exporting report to Excel. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Leave a comment