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

 

Answer 1

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)

Answer 2

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

Answer 3

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)

Answer 4

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

Answer 5

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)

Answer 6

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

 

Answer 7

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

Answer 8

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

 

Answer 9

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)

Answer 10

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

 

 

Answer 11

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

Answer 12

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.

Answer 13

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()

 

Answer 14

simple excel read.

http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm

http://vb.net-informations.com/excel-2007/vb.net_excel_2007_open_file.htm

tks,

suz.

 

 

 

 

Answer 15

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