filling datagridview with excel file data

[friend]

Hello all,

I have the following code to fill the datagridview with excel sheet
data

MyConnection = New System.Data.OleDb.OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data source="
& filePath & ";Extended Properties='Excel
8.0;HDR=NO;'")

MyConnection.Open()

Dim schemaTable As DataTable = MyConnection.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, New object() {Nothing, Nothing, Nothing,
"TABLE"})

Dim dataRow As DataRow = schemaTable.Rows(0)

MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * From [" +
DataRow("TABLE_NAME") + "]", MyConnection)

MyCommand.TableMappings.Add("Table", "TestTable")

DtSet = New System.Data.DataSet

MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)

This code can fill the datagridview, but sometimes the data is missing
in some of the rows. and sometimes I am able to get complete data.

thanks for any help.

 

[friend]

Hello all,

I have the following code to fill the datagridview with excel sheet
data

MyConnection = New System.Data.OleDb.OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data source="
&                       filePath & ";Extended Properties='Excel
8.0;HDR=NO;'")

MyConnection.Open()

Dim schemaTable As DataTable = MyConnection.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, New      object() {Nothing, Nothing, Nothing,
"TABLE"})

Dim dataRow As DataRow = schemaTable.Rows(0)

MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * From [" +
DataRow("TABLE_NAME") + "]", MyConnection)

MyCommand.TableMappings.Add("Table", "TestTable")

DtSet = New System.Data.DataSet

MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)

This code can fill the datagridview, but sometimes the data is missing
in some of the rows. and sometimes I am able to get complete data.

thanks for any help.

This problem exists when there text fields and numbers combined in
excel sheet as follows:

Excel sheet:

abcd 123 343 ererr
12 rrtrt 44 gffd
122 dfgf dfdf 3454

in datagridview it is displayed as below:

343 ererr
12 rrtrt 44 gffd
122 dfgf 3454


thank you all

 

[friend]

Hello all,

I have the following code to fill the datagridview with excel sheet
data

MyConnection = New System.Data.OleDb.OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data source="
&                       filePath & ";Extended Properties='Excel
8.0;HDR=NO;'")

Dim schemaTable As DataTable = MyConnection.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, New      object() {Nothing, Nothing, Nothing,
"TABLE"})

Dim dataRow As DataRow = schemaTable.Rows(0)

MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * From [" +
DataRow("TABLE_NAME") + "]", MyConnection)

MyCommand.TableMappings.Add("Table", "TestTable")

DtSet = New System.Data.DataSet

MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)

This code can fill the datagridview, but sometimes the data is missing
in some of the rows. and sometimes I am able to get complete data.

thanks for any help.

This problem exists when there text fields and numbers combined in
excel sheet as follows:

Excel sheet:

abcd     123   343   ererr
12         rrtrt   44     gffd
122       dfgf   dfdf    3454

in datagridview it is displayed as below:

                     343   ererr
12        rrtrt    44     gffd
122      dfgf             3454

thank you all

got it thanks

 

[friend]

Hello all,

I have the following code to fill the datagridview with excel sheet
data

MyConnection = New System.Data.OleDb.OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data source="
&                       filePath & ";Extended Properties='Excel
8.0;HDR=NO;'")

Dim schemaTable As DataTable = MyConnection.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, New      object() {Nothing, Nothing, Nothing,
"TABLE"})

Dim dataRow As DataRow = schemaTable.Rows(0)

MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * From [" +
DataRow("TABLE_NAME") + "]", MyConnection)

MyCommand.TableMappings.Add("Table", "TestTable")

DtSet = New System.Data.DataSet

MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)

This code can fill the datagridview, but sometimes the data is missing
in some of the rows. and sometimes I am able to get complete data.

thanks for any help.

This problem exists when there text fields and numbers combined in
excel sheet as follows:

Excel sheet:

abcd     123   343   ererr
12         rrtrt   44     gffd
122       dfgf   dfdf    3454

in datagridview it is displayed as below:

                     343   ererr
12        rrtrt    44     gffd
122      dfgf             3454

thank you all

I solved this by keeping IMEX=1 in extended properties of
oledbconnectionstring.

but if i keep in this way...if i have 1234567E+13 in excel, this is
interpreted as text and I am getting same in the datagridview.
Instead i need to get 12345678901234 in datagridview keeping cell
formatting in excel as "Standard".


Thanks for any help.