Read data from excel through VB.NET

Read data from excel through VB.NET

• MarchJ

  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.

  
  

• Claus Kessel

  Hi Prasanth,
  
  I ran your question by the engineer:
  
  

  I think our customer misunderstand me.  < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  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)

  
  
  

• kennydmac

  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. < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  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)

  
  
  

• Animesh

  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

   

  
  

• SYED HANIF SH

  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

  
  
  

• Jaifima

  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

   

   

  
  

• phousley17

  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
  
  
  

• vmishka

  Hi Prasanth,
  
  Here's some information from our support engineer:
  
  

  The partner is wonder why he got the abc, not the αβγ。< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  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)

  
  
  

• AlDeb

  Per the support engineer:
  
  Please check the value before using MessageBox.Show(…).< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  If it is a DBNull, it can not be convert to a string.
  
  
  -brenda (ISV Buddy Team)

  
  
  

• KillerAppz

  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

  
  

• Noordin

  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

   

  
  

• ajay4074

  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 < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
  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

  
  
  
• Read data from excel through VB.NET