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
for Handling the large numeric values while exporting to excel, I meet same problem, I used a vb.net excel component, it's name spire.xls to this. e-iceblue.com/Introduce/excel-for-net-introduce.html