Opening an Excel spreadsheet in Gridview and some...

[b][red]This message was edited by gennygabs at 2006-6-13 6:36:24[/red][/b][hr]

I have an assignment that involves displaying data to a gridview using an Excel spreadsheet as a database and that each cells's backcolor have different colors via runtime (code).

I tried using obdcconnection and obdcdataadapter..i don't know if it's the right method to use. Since when i would configure the data asks me in the later part of the wizard to use SQL statement and it doesn't show any data since it's not a database but a spreadsheet.

I've found other method in which it uses "as excel.application" and it's sorts but when you run the program it opens microsoft excel and runs the spreadsheet from there. What i need is use a datagrid to display the data from the spreadsheet.

If my idea, of using obdcconnection and obdcdataadapter, is right..does anyone of you guys knows how to configure it right wherein i can select the data that would appear..and possibly use SQL statements as well?

My other question is how can i change the backcolor of each cell/data via code?

Thank you

An update:

I've found this code at:

Paul Clement
On Wed, 23 Jun 2004 13:02:01 -0700, "Frans Vander Meiren" wrote:


Anyone who can explain me how to load and edit an Excel-file in a DataGrid via an odbcConnection?

The following should get you started:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:My DocumentsBook20.xls;Extended Properties=""Excel 8.0;HDR=NO"""

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet8$]", ExcelConnection)

Dim ds As New DataSet("Workbooks")

da.Fill(ds, "Sheet8")

DataGrid1.SetDataBinding(ds, "Sheet8")

My only problem here is that what do i do with [sheet8$]? I tried replacing it with "A1" the first sheet but i always get this error:

"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in"

I'm sure it with the SQL statement..but i'm totally uncertain of what to replace [sheet8$] and "Sheet8" with.

please help

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!