none
Read data from excel through VB.NET

    Question

  • Hi friends,
    I need to read symbols(like ALPHA,BETA,MEU) from excel and show it in a data grid through VB.NET.Later insert it into Oracle 8i DB.
    I am able to read entire data but i am getting 'a' instead of ALPHA symbol and 'b' instead of BETA symbol.

    I am querying excel via OLEDB provider.The following snippet is using for accessing excel.

    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    Dim MyConnection As System.Data.OleDb.OleDbConnection
    MyConnection = New System.Data.OleDb.OleDbConnection( _
    "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source=" & ExcelFilePath & "; " & _
    "Extended Properties=Excel 8.0")

    ' Select the data from Sheet1 ([in-house$]) of the workbook.
    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [in-house$]", MyConnection)

    DS = New System.Data.DataSet
    MyCommand.Fill(DS)
    Dt = DS.Tables(0)
    DataGrid1.DataSource = Dt

    Please help me to resolve it

     

    Tuesday, November 29, 2005 12:45 PM

Answers

  • Hi Prasanth,

    Here's some information from our support engineer:

    The partner is wonder why he got the abc, not the αβγ。

    It is designed by EXCEL. When the cell data is αβγ in the EXCEL, you will find that the font of the cell is symbol and the exact data showed in the fx above the booksheet is abc, not the αβγ. So when you get it from the OLEDB, you will get the abc, not the αβγ.

    As the font can not be got through OLEDB provider, we can not convert the data by its font.

    In order to resolved the issue. We have two choices.

    1. We can input αβγ in the EXCEL through the IME, not using the font of the EXCEL.
    2. We can use the EXCEL automation model to read the data in EXCEL and convert them to a special data. When the font is symbol and the data is a, you can convert it to α.

    I have given the reason why the data you read is not you expected, and some suggestions to work it around. Please let me know, if your issue is resolved or you have any question.

    -brenda (ISV Buddy Team)

    Monday, December 05, 2005 6:00 PM
  • Per the support engineer:

    Please check the value before using MessageBox.Show(…).

    If it is a DBNull, it can not be convert to a string.


    -brenda (ISV Buddy Team)

    Tuesday, December 13, 2005 5:41 PM
  • Hello Prasanth,

    The support engineer can not reproduce the issue that you reported and needs some specific details so that he can try to determine the problem.

    1. Which code line reports the error?
    2. Which cell is accessed when the error is reported?
    3. What is data and font of the cell?

    You can get the font of a cell by right click the cell -> Format Cells -> Font Tab-> Font

    Thursday, December 15, 2005 5:32 PM
  • Hi Brenda and ISV Team,

    Thanks a lot for your great help ,I done it successfully .............

    As per your suggestions i created a method like this,I will be calling this when the character is symbol...

    'Method for cunicode conversion

    Private Function ConvertUnicode(ByVal ChrAlphabet As String) As Char

    Select Case ChrAlphabet

    Case "a" 'ALPHA

    Return (ChrW(&H3B1))

    Case "b" 'BETA

    Return (ChrW(&H3B2))

    Case "g" 'GAMMA

    Return (ChrW(&H3B3))

    Case "d" 'DELTA

    Return (ChrW(&H3B4))

    Case "e" 'EPSILON

    Return (ChrW(&H3B2))

    Case "o" 'OMICRON

    Return (ChrW(&H3BF))

    Case Else 'SYMBOL

    Return (ChrW(&H39E))

    End Select

    End Function

    Regards

    Prasanth

     

     

    Friday, December 23, 2005 2:46 PM

All replies

  • Hi Prasanth,

    Here's some information from our support engineer:

    The partner is wonder why he got the abc, not the αβγ。

    It is designed by EXCEL. When the cell data is αβγ in the EXCEL, you will find that the font of the cell is symbol and the exact data showed in the fx above the booksheet is abc, not the αβγ. So when you get it from the OLEDB, you will get the abc, not the αβγ.

    As the font can not be got through OLEDB provider, we can not convert the data by its font.

    In order to resolved the issue. We have two choices.

    1. We can input αβγ in the EXCEL through the IME, not using the font of the EXCEL.
    2. We can use the EXCEL automation model to read the data in EXCEL and convert them to a special data. When the font is symbol and the data is a, you can convert it to α.

    I have given the reason why the data you read is not you expected, and some suggestions to work it around. Please let me know, if your issue is resolved or you have any question.

    -brenda (ISV Buddy Team)

    Monday, December 05, 2005 6:00 PM
  • Hi Brenda,

    Hearty Thanks for your kind response.

    I tried to incorporate in the 2nd way.But i am getting the same,And i havent find a way to convert to special.

    This is the snippet which i tried

    Dim strPath As String = "C:\Metabolic\MetabolicProfiling\Database for small projects group.xls"
    xlBook = GetObject(strPath)
    xlBook.Application.Visible =
    False
    xlBook.Windows(1).Visible = False
    Dim irow As Integer
    Dim jcol As Integer
    Dim obj As Excel.Range

    For irow = 2 To 10
       obj = CType(xlBook.Worksheets(2).Cells(irow, 1), Range)
       MessageBox.Show(obj.Value)
    Next

    xlBook.Close()
    xlBook =
    Nothing

    If you have any code snippets for convert them to a special data.Pls send to me.Hope i can store this data in a nvarchar column of oracle database.

    Prasanth.P.P

    Tuesday, December 06, 2005 11:40 AM
  • Hi Prasanth,

    I ran your question by the engineer:

    I think our customer misunderstand me.  

    The data in the cell is always “abc” whenever how we access it.

    The second way means, we should convert the data ourselves. When we get the range, we can check the type. If the type is “symbol”, we can change a to α.

    For example:

                Range rg;

                If (rg.font == “symbol”)

                {

                            If (rg.Text == “a”)

                            {

                                        // convert it to α

                            }          

    }

    It is a suggestion, I hope it will help.

    Best Regards, Tian Qiang Chen


    -brenda (ISV Buddy Team)

    Wednesday, December 07, 2005 5:01 PM
  • Hi
    Thanks for u r reply.
    I tried to develop an applcation in VB.NET as per your reply.But i am getting an exception like this ,when the cell contains 'α'.

    Exception MessageL: "Cast from type 'DBNull' to type 'String' is not valid." 

    This is the code i am using to fetch data.

      Dim oXL As New Excel.Application
            Dim oWBK As Excel.Workbook
            Dim oWS As Excel.Worksheet
            Dim oRNG As Excel.Range
            Try
                oWBK = oXL.Workbooks.Open(strPath)
                oWS = oXL.Worksheets(2)
                For irow As Integer = 2 To 10
                    oRNG = oWS.Cells(irow, 1)
                    MessageBox.Show(oRNG.Value)
                    MessageBox.Show(oRNG.Font.Name)
                Next
                oWBK.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                oWBK = Nothing
                oXL = Nothing
            End Try

    These are the first 2 rows in excel sheet

    col1   col2
    ----------------------------------
    phenylacetylglycine C10H11NO3 
    α-hydroxyhippurate C9H9NO4 


    Expecting good suggestions from you.

    Prasanth

    Monday, December 12, 2005 5:48 AM
  • Per the support engineer:

    Please check the value before using MessageBox.Show(…).

    If it is a DBNull, it can not be convert to a string.


    -brenda (ISV Buddy Team)

    Tuesday, December 13, 2005 5:41 PM
  • Hi

    Thanks for your immediate response

    As per previous query,I am trying to show cell value and cell font name.
    I am getting font name as System.DBNull,
    The value of the cell is 'α-hydroxyhippurate'
    Could you please tell me whats the problem ..?


    Regards
    Prasanth

     

    Wednesday, December 14, 2005 4:32 AM
  • Hello Prasanth,

    The support engineer can not reproduce the issue that you reported and needs some specific details so that he can try to determine the problem.

    1. Which code line reports the error?
    2. Which cell is accessed when the error is reported?
    3. What is data and font of the cell?

    You can get the font of a cell by right click the cell -> Format Cells -> Font Tab-> Font

    Thursday, December 15, 2005 5:32 PM
  • HI

    Thanks for your immediate responses.

    As per the previous post,i am providing details

    1. Getting Font name as null
        MessageBox.Show(oRNG.Font.Name)

    2. This is the cell value:

    a-hydroxyhippurate

    3. I can see font name  text is empty when go by right click the cell -> Format Cells -> Font Tab-> Font

    I tried to set  the font as symbol of this cell,At the time cell text 'a-hydroxyhippurate' is changing

    to 'a-hydroxyhippurate'.

    Could you please tell me how to solve this issues..

    Regards

    Prasanth.P.P

     

    Monday, December 19, 2005 6:45 AM
  • More info from the support engineer:

    As the data in the cell is “a-hydroxyhippurate“, there are 2 fonts in the cell. So the font of the cell is empty.

    If there are 2 or more fonts in a cell, we can check the font of the characters.

    For example:

                oWBK = oXL.Workbooks.Open("c:\2.xls")

                oWS = oXL.Worksheets(1)

                For irow As Integer = 1 To 2

                    oRNG = oWS.Cells(irow, 1)

                    MessageBox.Show(oRNG.Value)

     

                    If IsDBNull(oRNG.Font.Name) Then

                        Dim i As Integer

                        For i = 1 To oRNG.Characters.Count

                            MessageBox.Show(oRNG.Characters(i, 1).Font.Name)

                        Next

                    Else

                        MessageBox.Show(oRNG.Font.Name)

                    End If

     

                Next           

    This sample shows the fonts of each character in a cell, if there are 2 or more fonts in a cell.

     

    -brenda (ISV Buddy Team)

    Tuesday, December 20, 2005 5:29 PM
  • Hi Brenda and ISV Team,

    Thanks a lot for your great help ,I done it successfully .............

    As per your suggestions i created a method like this,I will be calling this when the character is symbol...

    'Method for cunicode conversion

    Private Function ConvertUnicode(ByVal ChrAlphabet As String) As Char

    Select Case ChrAlphabet

    Case "a" 'ALPHA

    Return (ChrW(&H3B1))

    Case "b" 'BETA

    Return (ChrW(&H3B2))

    Case "g" 'GAMMA

    Return (ChrW(&H3B3))

    Case "d" 'DELTA

    Return (ChrW(&H3B4))

    Case "e" 'EPSILON

    Return (ChrW(&H3B2))

    Case "o" 'OMICRON

    Return (ChrW(&H3BF))

    Case Else 'SYMBOL

    Return (ChrW(&H39E))

    End Select

    End Function

    Regards

    Prasanth

     

     

    Friday, December 23, 2005 2:46 PM
  • Hi Prasanth,

     

    U need to change the connection

     

    Please see the code below and try to execute ur code

     

    Dim MyConnection As System.Data.OleDb.OleDbConnection

    Dim myPath As String = "D:\CourseFootprints.xls"

    Try

       Dim DS As System.Data.DataSet

       Dim strPath, strSheet As String

      strPath = "D:\Test.xls"

      strSheet = "Sheet1"

      Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

      MyConnection = New System.Data.OleDb.OleDbConnection( _

      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & strPath & ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;""")

      ' Select the data from Sheet1 of the workbook.

      MyCommand = New System.Data.OleDb.OleDbDataAdapter( _

      "select * from [" & strSheet & "$]", MyConnection)

      DS = New System.Data.DataSet

      MyCommand.Fill(DS)

      Dim dv As DataView

      dv = DS.Tables(0).DefaultView

      dgGridVw.DataSource = dv

      MyConnection.Close()

    Catch ex As Exception

           MsgBox(ex.Message)

    End Try

    Sunday, April 22, 2007 6:44 AM
  • Hi,

    I have one application where date from excel sheet should display in the datagrid.

    But my .dotnet application is running on remote server and the excel sheet which user selects is from local system so when I'm using the same code in my application its throwing me the following error.

     

    *****************************

    Cannot find table 0.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.IndexOutOfRangeException: Cannot find table 0.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

     

    **************************************

    above code is working fine if both excel and application running on the same system. Please reply me ASAP how to do this on the remote server.

    Monday, May 21, 2007 10:26 PM
  • Hi Srini111,

    I don't know what are you using for reading Excel files in VB.NET, but if you are using Excel Automation , you need to have MS Excel application installed on the remote machine.

    If you can't resolve your issue, try this Excel VB.NET library. It supports imports from Excel to DataTable within just one method call and you can use it without the need for Excel app:

    Dim ef As New ExcelFile()
    ef.LoadXls("ExcelData.xls")
    
    ' Initialize DataTable (skip this if you have DataTable definition)
    Dim dt As New DataTable()
    dt.Columns.Add("name", GetType(String))
    dt.Columns.Add("birth", GetType(DateTime))
    
    ' Extract data to DataTable
    ws.ExtractToDataTable(dt, ef.Worksheets(0).Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow, ef.Worksheets(0).Rows(0), ef.Worksheets(0).Columns(0))
    
    DataGrid1.DataSource = dt.DefaultView
    DataGrid1.DataBind()

     

    Thursday, April 01, 2010 7:25 AM
  • You can also use .NET Managed reader for Excel from www.xporttools.net It provides functionality to read data as any other managed provider without using Jet, ACE or Excel itself
    Val Mazur (MVP)

    http://www.xporttools.net

    Tuesday, October 26, 2010 10:17 PM