|
|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
ADO是什么
Excel 工作表中的行和列与数据库中的行和列非常相似。只要用户记住 Microsoft Excel 不是关系型数据库管理系统，并认识到这一事实所带来的限制，在许多情况下都可以利用 Excel 及其工具来存储和分析数据。
使用 ADO 连接到 Excel
ADO 使用 MDAC 中以下两个 OLE DB 提供程序中的任何一个都可以连接到 Excel 数据文件。
Jet 提供程序只需要两条信息就可以连接到 Excel 数据源：路径（包括文件名），和 Excel 文件版本。
Jet 提供程序使用连接字符串
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
默认情况下，系统认为 Excel 数据源的第一行包含可用作字段名的列标题。如果不是这种情况，则必须将该设置关闭，否则，第一行数据将会“消失”，而被用作字段名称。这可通过向连接字符串的扩展属性添加可选的 HDR= 设置来完成。默认情况下（无需指定）是 HDR=Yes。如果没有列标题，则需要指定 HDR=No；提供程序将字段命名为 F1、F2 等等。因为扩展属性字符串现在包含了多个值，所以各值必须用双引号括起来，再加一对双引号，让 Visual Basic 将第一层引号中的内容当作字面值，如下例所示（为便于看清楚，添加了额外的空格）。
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=" " Excel 8.0; HDR=No;" " "
使用 Microsoft OLE DB Provider for ODBC Drivers
ODBC 驱动程序的提供程序（本文为简便起见将其称为“ODBC 提供程序”）也只需要两条信息就可以连接到 Excel 数据源：驱动程序名，以及工作簿的路径和文件名。
使用没有 DSN 的连接字符串的 ODBC 提供程序
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"
.Open
End With
使用带有 DSN 的连接字符串的 ODBC 提供程序
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "DSN=MyExcelDSN;"
.Open
End With
其他 ODBC 提供程序连接设置
默认情况下，系统认为 Excel 数据源的第一行包含可用作字段名的列标题。如果不是这种情况，则必须将该设置关闭，否则，第一行数据将会“消失”，而被用作字段名称。这可通过向连接字符串添加可选的 FirstRowHasNames= 设置来完成。默认情况下（无需指定）为 FirstRowHasNames=1，其中 1 = True。如果没有列标题，则需要指定 FirstRowHasNames=0，其中 0 = False；驱动程序将字段命名为 F1、F2 等等。
指定未命名区域
若要指定未命名的单元格区域作为记录源，请在工作表名的后面加上用标准 Excel 行/列表示法表示的区域，并用方括号将其括起。例如：
strQuery = "SELECT * FROM [Sheet1$A1:B10]"
如何更改 Excel 数据：编辑、添加和删除
编辑
可以使用普通 ADO 方法来编辑 Excel 数据。对应于 Excel 工作表中包含 Excel 公式（以“=”开始）的单元格的记录集字段是只读的，不能对其进行编辑。记住 Excel 的 ODBC 连接默认是只读的，除非在连接设置中另行指定。
添加
如果有可用空间，可以将记录添加到 Excel 记录源中。但是，如果将新记录添加到了原来指定的区域之外，那么在对原来指定的区域重新进行查询时，将看不到这些记录。
删除
删除 Excel 数据时，受到的限制要比从关系数据源中删除数据时更多。在关系数据库中，“行”除了表示一条“记录”外没有其他意义；但在 Excel 工作表中却不同。可以删除字段（单元格）中的值。但不能：
1. 一次删除一整条记录，否则将出现以下错误信息：
Deleting data in a linked table is not supported by this ISAM.
只能通过分别清空各个字段的内容来删除一条记录。
2. 删除包含 Excel 公式的单元格中的值，否则将出现以下错误信息：
Operation is not allowed in this context.
3. 虽然电子表格中已被删除的数据原来所在的行现在是空行，但无法将其删除，而且记录集将继续显示对应于这些空行的空记录。
从 Excel 检索数据源结构（元数据）
使用 ADO 可以检索有关 Excel 数据源（表和字段）的结构的数据。虽然使用两种 OLE DB 提供程序时至少都返回相同数量（很少）字段的有用信息，但结果在两者之间仍有细微差别。使用 ADO Connection 对象的 OpenSchema 方法可检索此元数据，该方法返回一个 ADO Recordset 对象。也可以使用更强大的 Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) 库来检索元数据。然而，对于 Excel 数据源，“表”既可以是工作表也可以是命名区域，而“字段”则是几种有限的一般数据类型之一，所以这一附加的功能没有什么用处。
查询表信息
在关系数据库提供的各种对象中（表、视图、存储过程，等等），Excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为“表”，而工作表被视为“系统表”。除“table_type”属性外，检索不到太多有用的表信息。使用以下代码可以检索工作簿中可用表的列表：
Set rs = cn.OpenSchema(adSchemaTables)
查询字段信息Excel 数据源中的每个字段（列）都属于以下数据类型之一：
◊ 数字（ADO 数据类型 5，adDouble）
◊ 货币（ADO 数据类型 6，adCurrency）
◊ 逻辑或布尔值（ADO 数据类型 11，adBoolean）
◊ 日期（使用 Jet 时，为 ADO 数据类型 7，adDate；使用 ODBC 时为数据类型 135，adDBTimestamp）
◊ 文本（一种 ADO ad...Char 类型，例如，202，adVarChar；200，adVarWChar，或相似类型）
对于数字列，返回的 numeric_precision 始终为 15（是 Excel 中的最大精度）；对于文本列，返回的 character_maximum_length 始终为 255（是 Excel 列中文本的最大显示宽度，但不是最大长度）。除了 data_type 属性之外，得不到多少有用的字段信息。使用以下代码可以检索表中可用字段的列表：
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, "TableName", Empty))
枚举表和字段及其属性可以使用 Visual Basic 代码（如下例所示）来枚举 Excel 数据源中的表和列，以及有关各个表和列的可用信息。本示例将其结果输出到同一窗体上的一个列表框 List1。
Dim cn As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & App.Path & "\ExcelSrc.xls;Extended Properties=Excel 8.0;"
'.Provider = "MSDASQL"
'.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & App.Path & "\ExcelSrc.xls; "
.CursorLocation = adUseClient
.Open
End With
Set rsT = cn.OpenSchema(adSchemaTables)
intTblCnt = rsT.RecordCount
intTblFlds = rsT.Fields.Count
List1.AddItem "Tables: " & intTblCnt
List1.AddItem " "
For t = 1 To intTblCnt
strTbl = rsT.Fields("TABLE_NAME").Value
List1.AddItem vbTab & "Table #" & t & ": " & strTbl
List1.AddItem vbTab & " "
For f = 0 To intTblFlds - 1
List1.AddItem vbTab & rsT.Fields(f).Name & _
vbTab & rsT.Fields(f).Value
Next
List1.AddItem " "
Set rsC = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, strTbl, Empty))
intColCnt = rsC.RecordCount
intColFlds = rsC.Fields.Count
For c = 1 To intColCnt
strCol = rsC.Fields("COLUMN_NAME").Value
List1.AddItem vbTab & vbTab & "Column #" & c & ": " & strCol
List1.AddItem vbTab & vbTab & " "
For f = 0 To intColFlds - 1
List1.AddItem vbTab & vbTab & rsC.Fields(f).Name & _
vbTab & rsC.Fields(f).Value
Next
List1.AddItem vbTab & vbTab & " "
rsC.MoveNext
Next
rsC.Close
List1.AddItem " "
rsT.MoveNext
Next
rsT.Close
cn.Close
代码格式自己整理，谢谢
在附个update的例子，比较简短，很实用
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sPath & "; Extended_ Properties=""Excel 8.0;HDR=NO;""" 'HDR=NO表示字段列名没有 sPath 路径
oConn.Execute "update [" & sheet名& "$] set F" & iFieldNumber & "=" & 字段1 & " WHERE F1='" & 要搜索的内容 & "'"
oConn.Close
Set oConn = Nothing'循环用
'在excel中，缺省的把第一行作为header行（也就是第一行各单元格的内容作为字段名），如果第一行没有内容，要在连接串中把HDR=NO,这样Jet OLE DB provider 就会把字段名缺省定义为：F1,F2…
[ 本帖最后由 vasa 于 2009-3-24 09:39 编辑 ] |
评分
-
2
查看全部评分
-
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-3-16 14:21
楼主
