Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Visual Basic [VB] (http://www.go4expert.com/articles/visual-basic-tutorials/)
-   -   Handling the large numeric values while exporting to excel. (http://www.go4expert.com/articles/handling-numeric-values-exporting-excel-t20794/)

sameer_havakajoka 28Jan2010 16:46

Handling the large numeric values while exporting to excel.
 

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


shabbir 6Feb2010 09:28

Re: Handling the large numeric values while exporting to excel.
 
Nominate this Article for Article of the month - Jan 2010

shabbir 18Feb2010 10:55

Re: Handling the large numeric values while exporting to excel.
 
If you liked this articles do vote for it for Article of the month - January 2010

harrypeter86 13Aug2010 09:38

Re: Handling the large numeric values while exporting to excel.
 
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


All times are GMT +5.5. The time now is 02:14.