Prasanth.P.P


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

 




Re: Read data from excel through VB.NET

MS ISV Buddy Team


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)







Re: Read data from excel through VB.NET

Prasanth.P.P

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






Re: Read data from excel through VB.NET

MS ISV Buddy Team

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)






Re: Read data from excel through VB.NET

Prasanth.P.P

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





Re: Read data from excel through VB.NET

MS ISV Buddy Team

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)






Re: Read data from excel through VB.NET

Prasanth.P.P

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

 





Re: Read data from excel through VB.NET

MS ISV Buddy Team

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






Re: Read data from excel through VB.NET

Prasanth.P.P

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

 





Re: Read data from excel through VB.NET

MS ISV Buddy Team

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)






Re: Read data from excel through VB.NET

Prasanth.P.P

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

 

 





Re: Read data from excel through VB.NET

ChandraSekhar

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






Re: Read data from excel through VB.NET

Srini111

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.