Prev: How can I unbind a GridView?
Next: Binary stream '0' does not contain a valid BinaryHeader
From: friend on 4 Sep 2009 03:43
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.
From: friend on 4 Sep 2009 04:31
On Sep 4, 9:43 am, friend <lavanyaredd...(a)gmail.com> wrote:
> 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
From: friend on 4 Sep 2009 07:21
On Sep 4, 10:31 am, friend <lavanyaredd...(a)gmail.com> wrote:
> On Sep 4, 9:43 am, friend <lavanyaredd...(a)gmail.com> wrote:
>
>
>
> > 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

got it thanks
From: friend on 4 Sep 2009 07:37
On Sep 4, 10:31 am, friend <lavanyaredd...(a)gmail.com> wrote:
> On Sep 4, 9:43 am, friend <lavanyaredd...(a)gmail.com> wrote:
>
>
>
> > 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

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.

 | 
Pages: 1
Prev: How can I unbind a GridView?
Next: Binary stream '0' does not contain a valid BinaryHeader