Introduction
This solution is to prevent the conversion of large numeric values in scientific notation while exporting to excel.
Background
This solution deals with the situation when there is a large numeric value, and while exporting to excel it gets converted to scientific notation. Here, we check if the value to be written in the excel cell is of Numeric type or not. If the value is of numeric type, we append " ' " before the data. This converts the data to be displayed in Excel sheet to be converted to string format. This conversion to string in turn prevents the values to be converted to scientific notation. The only limitation solution is that if we want to read the same excel file using code then we need to handle for these numeric values.
The code
Code: VB.NET
Code:
Dim ds As New DataSet
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
ds.Tables.Add()
ds.Tables(0).Rows.Add()
ds.Tables(0).Columns.Add()
ds.Tables(0).Columns.Add()
ds.Tables(0).Rows(0).Item(0) = "23213231323313323123133232232331"
ds.Tables(0).Rows(0).Item(1) = "11111111111111111111111111111111"
grdvwTest.DataSource = ds
grdvwTest.DataBind()
End Sub
Protected Sub btnTest_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnTest.Click
DataToExcel()
End Sub
Public Sub DataToExcel()
Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
wBook = excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()
Dim dt As System.Data.DataTable = ds.Tables(0)
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName
Next
For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
If IsNumeric(dr(dc.ColumnName)) Then
excel.Cells(rowIndex + 1, colIndex) = "'" & dr(dc.ColumnName)
Else
excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
End If
Next
Next
wSheet.Columns.AutoFit()
Dim strFileName As String = "D:\ss.xls"
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try
If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If
wBook.SaveAs(strFileName)
excel.Workbooks.Open(strFileName)
excel.Visible = True
End Sub
