YoungEngineer

How can I bind an excel worksheet data to a datagridview Im trying to load an excel sheet with numbers to my form, im trying to get as much of a look and feel as excel as possible.


Re: .NET Framework Data Access and Storage Displays Excel worksheet in DataGridView

cverdon

Hi,

What you want is to embed the speadsheet control that is part of Office Web Components:
http://www.microsoft.com/downloads/details.aspx familyid=7287252C-402E-4F72-97A5-E0FD290D4B76&displaylang=en

The .net datagrid is not a replacement for a spreadsheet.

Regards,
Charles





Re: .NET Framework Data Access and Storage Displays Excel worksheet in DataGridView

YoungEngineer

The office web components is more related to the web...im actually trying to create a windows form application. Is there anything comparable for that




Re: .NET Framework Data Access and Storage Displays Excel worksheet in DataGridView

cverdon

OWC are com components. They can be used on the web, but they can also be rehosted in windows forms applications.

Look at the documentation, there are some .net wrapper classes provided :)

Charles





Re: .NET Framework Data Access and Storage Displays Excel worksheet in DataGridView

Paul P Clement IV


Below is some sample code that should help. If the Worksheet does not contain a column header just remove HDR=Yes from the connection string.

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Documents and Settings\nfisppc\My Documents\Active Accounts for Deleted Firms.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

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

ExcelConnection.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter

da.SelectCommand = New System.Data.OleDb.OleDbCommand("Select * from [Sheet1$]", ExcelConnection)

Dim ds As New DataSet

da.Fill(ds, "Sheet1")

frmMain.DataGrid1.SetDataBinding(ds, "Sheet1")

ExcelConnection.Close()






Re: .NET Framework Data Access and Storage Displays Excel worksheet in DataGridView

Unmesh Gundecha

Hi Paul,

We want to read an Excel file in a DataGridView and then again export it to Excel. Could you please tell me how we can export the contents of DataGridView back to an Excel file

Thanks in advance,

Unmesh





Re: .NET Framework Data Access and Storage Displays Excel worksheet in DataGridView

cverdon

If you use the OleDbDataAdapter, you just have to create Update, Delete and Insert commands similar to the SelectCommand Paul provided and then call Update on the adapter.

Charles





Re: .NET Framework Data Access and Storage Displays Excel worksheet in DataGridView

Unmesh Gundecha

Hi Charles,

Is there any alternative for updating data back to Excel. I tried using OleDbDataAdapter however it is not working as expected. Is there any way to create connection to Excel through Data Source Wizard

Thanks,

Unmesh





Re: .NET Framework Data Access and Storage Displays Excel worksheet in DataGridView

VMazur

There are several alternative ways to move data back to Excel.

1. Use Excel automation. It is COM-based and works out of process, so you should be very careful about cleaning memory, otherwise Excel application could be left in a memory running until you kill it as a process.

2. Use Microsoft Office Tools for .NET. They allow utilizing all the functionality of Excel and allow working with the individual cells. So you will get rich functionality. But this library is still COM based and works extremely slow. If you need good performance, it could the issue.

3. If you are using Excel 2002 or newer, then you could save your data into Excel XML format. It is pretty straightforward and you could write your own code for this. This format is not supported in older versions of Excel.

4. Use third party component or tool that export data into Excel. There are several tools outside, which I am sure will do what you need.

Following link could help you with your decision

http://support.microsoft.com/kb/306022/en-us






Re: .NET Framework Data Access and Storage Displays Excel worksheet in DataGridView

Paul P Clement IV


You may want to indicate what type of errors you are encountering or why it isn't working as expected.