Opening an Excel spreadsheet in Gridview and some... - Programmers Heaven

Howdy, Stranger!

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


Opening an Excel spreadsheet in Gridview and some...

gennygabsgennygabs Posts: 12Member
[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.