在Datagrid1中修改单元格的数据如何及时更新Excel表？

[YII2er]

有一个窗体Form1，其上有一Datagrid1。

现已成功地将这个Datagrid1与一个Excel 2003文件test.xls连接在一起，程序一运行就能把sheet1中的数据显示到Datagrid1中来。

问题：
　　　1、如果在Datagrid1中修改了一个数据，比如A1单元格，怎样才能让test.xls及时更新，以便让其它的单元格也能相应地及时产生变化?

　　　比如，test.xls中A1的值是50，B1用了公式是=A1/2，结果自然是25。我想当我在Datagrid1中将A1改为60时，Datagrid1中的B1能即时地由25变为30。

2、在一切修改结束后，怎样把更新的数据保存到test.xls中。

　　　3、在Datagrid1中修改一个单元格，它的值发生了变化，这是什么事件，是不是Datagrid1的textchanger事件？

[YII2er]

附上连接Test.xls并显示到Datagrid1部分的代码：

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\TEST.XLS; " & _
"Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)

DS = New System.Data.DataSet()
MyCommand.Fill(DS)
DataGrid1.DataSource = DS.Tables(0).DefaultView
MyConnection.Close()

End Sub

[buzhubuxing]

一个笨一点的办法呢就是创建一个excel对象，将这个对象同test1.xls联系上。当datagrid中数据修改了后，可以通过dataset的current row来获得当前数据。然后将修改后的数据写入回excel文件即可。cell.value...

你通过dataset已经连接上excel表了，好像可以直接通过update dataset来达到更新excel表中内容的目的。我也是刚接触vb.net和ado.net，跟以前的vb6不大一样，还不是很熟悉。希望能够起个抛砖引玉的作用。

[buzhubuxing]

发一篇文章供你参考。希望有用处。

HOW TO: Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
対象製品
This article was previously published under Q316934
IN THIS TASK
SUMMARY
How to Use the Jet OLE DB Provider With Microsoft Excel Workbooks
Connection String
Data Types
Table Naming Conventions
How to Use Excel Workbooks As ADO.NET Data Sources
Retrieve Records
Add and Update Records
Delete Records
Create New Workbooks and Tables
Step-by-Step
Sample Code
Try It Out
Cell Formatting
Limitations
REFERENCES
SUMMARY
This article discusses how you can use ADO.NET to retrieve data from a Microsoft Excel workbook, modify data in an existing workbook, or add data to a new workbook. To access Excel workbooks with ADO.NET, you can use the Jet OLE DB provider; this article provides the information that you need so that you can use the Jet OLE DB provider when Excel is the target data source.

back to the top
How to Use the Jet OLE DB Provider With Microsoft Excel Workbooks
The Microsoft Jet database engine can access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. To open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks:
Excel 3.0
Excel 4.0
Excel 5.0
Excel 8.0
NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97), 9.0 (2000) and 10.0 (2002) workbooks. The examples in this article use Excel workbooks in the Excel 2000 and Excel 2002 format.

back to the top
Connection String
To access an Excel workbook by using the Jet OLE DB Provider, use a connection string that has the following syntax:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties="Excel 8.0;HDR=YES;"
In the connection string, specify the full path and file name for the workbook in the Data Source parameter. The Extended Properties parameter may contain two properties: a property for the ISAM version and a property to indicate whether or not the table(s) include headers.

With Excel workbooks, the first row in a range is the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If you specify HDR=NO in the connection string, the Jet OLE DB provider automatically names the fields for you (F1 represents the first field, F2 represents the second field, and so on).

back to the top
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans eight rows in a column to guess the data type for the field. You can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.

back to the top
Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:
Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner includes the whole used range of the worksheet.
Select * from [Sheet1$]
Use a range with a defined name (for example, [MyNamedRange]):
Select * from [MyNamedRange]
Use a range with a specific address (for example, [Sheet1$A1:B10]):
Select * from [Sheet1$A1:B10]
NOTE: The dollar sign following the worksheet name is an indication that the table exists. If you are creating a new table, as discussed in the Create New Workbooks and Tables section of this article, do not use the dollar sign.

back to the top
How to Use Excel Workbooks as ADO.NET Data Sources
Retrieve Records
You can retrieve records from a database by using one of two approaches in ADO.NET: with a Dataset or with a DataReader.

A Dataset is a cache of records retrieved from a data source. The data in the Dataset is usually a much-reduced version of what is in the database. However, you can work with it in the same way that you work with the actual data and remain disconnected from the actual database. Besides data retrieval, you can also use a Dataset to perform update operations on the underlying database.

Alternatively, you can use a DataReader to retrieve a read-only, forward-only stream of data from a database. When you use the DataReader program, performance increases and system overhead is decreases because only one row at a time is ever in memory. If you have a large quantity of data to retrieve and you do not intend to make changes to the underlying database, a DataReader is a better choice than a Dataset.

back to the top
Add and Update Records
With ADO.NET, you can insert and update records in a workbook in one of three ways:
Directly run a command to insert or update records one at a time. To do this, you can create an OLEDbCommand object on your connection and set its CommandText property to a valid command to insert records
INSERT INTO [Sheet1$] (F1, F2) values ('111', 'ABC')
or a command to update records
UPDATE [Sheet1$] SET F2 = 'XYZ' WHERE F1 = '111'
and then call the ExecuteNonQuery method.
Make changes to a DataSet that you have filled with a table/query from an Excel workbook and then call the Update method of the DataAdapter to resolve changes from the DataSet back to the workbook. However, to use the Update method for change resolution you must set parameterized commands for the DataAdapter's InsertCommand
INSERT INTO [Sheet1$] (F1, F2) values (?, ?)
and UpdateCommand:
UPDATE [Sheet1$] SET F2 = ? WHERE F1 = ?
Parameterized INSERT and UPDATE commands are required because the OleDbDataAdapter does not supply key/index information for Excel workbooks; without key/index fields, the CommandBuilder cannot automatically generate the commands for you.
Export data from another data source into an Excel workbook provided that the other data source can be used with the Jet OLE DB Provider. Data sources that you can use with the Jet OLE DB Provider in this manner include Text files, Microsoft Access databases, and, of course, other Excel workbooks. With a single INSERT INTO command, you can export data from another table/query into your workbook:
INSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' SELECT * FROM MyTable"
INSERT INTO requires that the target table (or worksheet) already exist; data is appended to the target table.

You may also use SELECT..INTO to export your table/query to a workbook:
SELECT * INTO [Excel 8.0;Database=C:\Book1.xls].[Sheet1] FROM [MyTable]
When you use SELECT..INTO, if the target table or workbook does not exist, it will be created for you. If the table already exists before the SELECT..INTO command is issued, you will receive an error.
The Sample Code section later in this article illustrates each of these approaches to add and update records in a workbook.

back to the top
Delete Records
Although the Jet OLE DB Provider allows you to insert and update records in an Excel workbook, it does not allow DELETE operations. If you try to perform a DELETE operation on one or more records, you receive the following error message:
Deleting data in a linked table is not supported by this ISAM.
This limitation is inherent in the treatment of Excel workbooks as databases.

[buzhubuxing]

Create Workbooks and Tables
To create a table in an Excel workbook, run the CREATE TABLE command:
CREATE TABLE Sheet1 (F1 char(255), F2 char(255))
When you run this command, a new worksheet is created with the name of the table you specify in the command. If the workbook for the connection does not exist, it too will be created.

The Sample Code section illustrates how you can use the CREATE TABLE command to create a new workbook and table.

back to the top
Step-by-Step
Sample Code
1. Start a new Visual Basic .NET Windows Application project.

Form1 is created by default.
2. Add six RadioButton controls and a Button control to Form1.
3. Select all of the RadioButton controls and set the Size property to 200,24.
4. On the View menu, click Code.
5. Add the following line to the very beginning of the code module:
Imports System.Data.OleDb
6. Insert the following code into the Form class:
7. Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
8. "Data Source=C:\ExcelData1.xls;" & _
9. "Extended Properties=""Excel 8.0;HDR=YES"""
10.
11. Private m_sConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
12. "Data Source=C:\ExcelData2.xls;" & _
13. "Extended Properties=""Excel 8.0;HDR=YES"""
14.
15. Private m_sNorthwind = _
16. "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
17.
18. Private m_sAction As String
19.
20. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
21. RadioButton1.Text = "Create_Workbook"
22. RadioButton2.Text = "Retrieve_Records"
23. RadioButton3.Text = "Add_Records"
24. RadioButton4.Text = "Update_Records"
25. RadioButton5.Text = "Update_Individual_Cells"
26. RadioButton6.Text = "Use_External_Source"
27. Button1.Text = "Go!"
28. End Sub
29.

[YII2er]

buzhubuxing(不注不行) ，谢谢你的回答，不过我是一个初学者，不大能看懂你贴出的代码，如果有与这些代码相应的源程序，能否提供？

[YII2er]

To buzhubuxing(不注不行):

兄台这两天没有上网吗？