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!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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]
Hi,

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 adapter..it 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: http://www.dotnet247.com/247reference/msgs/53/267986.aspx


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

Hi

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

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)
ExcelConnection.Open()

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 system.data.dll"

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.