1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Handling the large numeric values while exporting to excel.

Discussion in 'Visual Basic [VB]' started by sameer_havakajoka, Jan 28, 2010.

  1. sameer_havakajoka

    sameer_havakajoka New Member

    Joined:
    Sep 14, 2009
    Messages:
    271
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Sleeping
    Location:
    Hava Ke Paro Me

    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
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
  4. harrypeter86

    harrypeter86 New Member

    Joined:
    Aug 13, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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
     

Share This Page