使用 Excel 2003 对象模型添加 XML 数据集成 (From MSDN)

使用 Excel 2003 对象模型添加 XML 数据集成

发布日期 : 8/11/2004 | 更新日期 : 8/11/2004

Peter Vogel

PH&V Information Services

适用范围：

Microsoft® Office Excel 2003

Microsoft Office System

摘要 ： 将工作簿连接到数据源，以便电子表格可以操作并更新数据，这是基于 Office 的解决方案的共同目标。Microsoft Office Excel 2003 通过添加更多的 XML 数据集成功能，来使该目标实现更加容易，在其功能方面更加全面。本文说明了使用 Excel 对象模型来利用 Excel 中的 XML 支持，包括命名空间、XML 列表、XML 映射、数据绑定，等等。

本页内容

引言
加载映射
映射区域
创建列表
加载文档
访问数据
创建 XML 文档
Excel XML 事件
结论

引言

将数据检索到电子表格中并使用在电子表格中完成的工作结果来更新数据源或应用程序，这个功能对于使电子表格有用起着决定性的作用。如果没有集成来自各种源的数据的功能，则要求电子表格用户将数据从数据源复制到电子表格中，并将电子表格中的数据添加回任何需要该数据的系统。

Microsoft® Office Excel 2003 添加该功能来通过增强的 XML 数据互操作性处理任何 XML 文档。进行此改进之后，Excel 要集成到任何应用程序中必须满足一个要求：该应用程序必须可以生成 XML，或接受 XML。

以下是一个应用程序中使用的 XML 文档的示例，该 XML 文档保存销售订单信息，包括用于客户信息的元素和用于产品信息的重复元素：

<so id="A1024" type="Rush" >
   <Customer>
      <Name>Pat Smith</Name>
   </Customer>
   <Products>
      <Line>
         <ProductId>AX142</ProductId>
         <Quantity>12</Quantity>
      </Line>
      <Line>
         <ProductId>BZ739</ProductId>
         <Quantity>1</Quantity>
      </Line>
   </Products>
</so>

Excel 并不要求 XML 文档具有说明此文档格式的相应架构。Excel 会为其导入的任何文档生成架构。但是，对于本文中显示的该示例，假定该文档的架构位于具有以下路径的一个文件中：c:/schemas/salesorder.xsd。

返回页首

加载映射

在 Excel 中，XmlMap 对象代表一个或多个架构及其到电子表格的映射。要加载新的 XML 映射，则必须将一个新的架构添加到 XmlMaps 集合中。以下代码将新的架构文件添加到 Workbook 对象，并返回对此映射的引用：

Dim xmp As XmlMap
Set xmp =  _ 
    Application.Workbooks(1).XmlMaps.Add("c:/schemas/salesorder.xsd")

Add 方法接受第二个参数，使您可以指定要在电子表格中使用的根元素。当 Excel 无法从多个顶级元素中确定哪个为根元素时，该根元素参数则为基本的。

加载映射后，可以按它在 XmlMaps 集合中的位置或名称引用它。Excel 通过将 _Map 追加到根元素名称，来构成映射的初始名称。可以从 XmlMap 的 Name 属性中检索映射名称。

前面示例中的代码所加载的销售订单文档中的根元素名为“so”；也可以使用以下代码从 XMLMaps 集合中查找该架构的引用。请注意，映射名称区分大小写：

Set xmp = Application.Workbooks(1).XmlMaps("so_Map")

提示 ：一个好的做法是，为映射分配一个名称并在加载架构之后立即控制此名称：

Dim xmp As XmlMap
Set xmp = Application.Workbooks(1).XmlMaps.Add("c:/schemas/salesorder.xsd")
xmp.Name = "SalesOrder"

要从 Workbook 中删除架构及其相关映射，只需使用 XmlMap 的 Delete 方法即可。电子表格的单元格中的数据不受影响。

Application.Workbooks(1).XmlMaps("SalesOrder").Delete

一个架构可能引用其他架构。如果它引用其他架构，则 XmlMap 的 Add 方法也将所有这些架构添加到映射中。可以通过 XmlMap 的 Schemas 集合访问映射中的架构。例如，以下代码检索 XML 以查找映射中的第一个架构（即示例中的 so 架构）：

Dim strSchemaXML As String
strSchemaXML = Set xmp = Application.Workbooks(1).XmlMaps("SalesOrder").Schemas(1).XML

返回页首

映射区域

使 用在架构中指定元素或属性的 XPath 语句，可以将元素和属性从该架构映射到电子表格的单元格。Excel 将 XPath 语句（连同对架构映射的引用）传递到一个 Range 对象的 XPath 对象的 SetValue 方法。例如，以下示例将单元格 A1 映射到 so 根元素下的 Customer 元素的 Name 子元素。

Dim cll As Range
Dim xp As XPath

Set xp = ActiveSheet.Range("A1").XPath
xp.SetValue ActiveWorkbook.XmlMaps(1), "/so/Customer/Name"

Excel 使用同样的方法映射属性，该方法为在属性的名称前面添加符号 @ 以指示此路径引用一个属性：

ActiveSheet.Range("A2").XPath.SetValue _
ActiveWorkbook.XmlMaps(1), "/so/@id"

可以通过 XPath 对象的 Value 属性来检索单元格的映射。以下代码显示为单元格 A1 建立的映射：

Msgbox cll.XPath.Value

如果所用 XPath 语句的格式是错误的，Excel 会返回错误“The XPath is not valid because either the XPath syntax is incorrect or not supported by Excel”（XPath 无效，因为 XPath 语法不正确或不受 Excel 的支持）。但是，如果您违反了下一部分中说明的 Excel XPath 语句的任何规则，也会收到该错误消息。

一次只能将一个单元格映射到一个元素或属性。可以随时通过调用 SetValue 方法并传递新的路径，更改为映射到一个区域。可以通过调用 XPath 对象的 Clear 方法，来断开区域与元素之间的映射，如下所示：

cll.XPath.Clear

XPath 谓词

可以在 XPath 语句中将谓词用作指定要映射到电子表格的元素的部分。Excel 支持 Xpath，但具有以下限制：

• XPath 是简单内容元素和属性的绝对路径

  示例：支持“/ns1:root/ns1:row/ns1:column1”（如果“column1”最多为一个子节点），但是不支持同一个文档中的“/ns1:root/ns1:row”，因为“row”不是子节点。

• XPath 并不指明轴，但是使用默认的子轴

  示例：支持“/ns1:root/ns1:row”，但不支持“/ns1:root/child::ns1:row”

• 可选的筛选器可以在 xpath 的末尾指明

  示例：支持“/ns1:root/ns1:row/ns1:column1[@foo='abc']”，但是不支持“/ns1:root/ns1:row[@foo='abc']/ns1:column1”

• 筛选器只能包含一个将命名属性与指定值进行比较的表达式

• 筛选器仅受解析为简单内容元素（而不是属性）的 XPath 的支持

• 命名属性必须定义为简单内容元素的属性

• 属性名称的前面必须有表示轴“属性”的 @ 符号

  示例：支持“/ns1:root/ns1:row/ns1:column1[@foo='abc']”，但不支持“/ns1:root/ns1:row/ns1:column1[attribute::foo='abc']”

• 可以在筛选器令牌之间嵌入任意量的空白

  示例：“/ns1:root/ns1:row/ns1:column1[ @ foo='abc']”是有效的

命名空间

必须将 XPath 语句中的元素或属性与架构中所用的命名空间相关联。SetValue 方法的第三个参数使用与 XML 文档中相同的语法，接受命名空间定义。在以下示例中，命名空间 http://phvis.com/exSalesOrder 使用前缀 so ，与文档中的元素相关联。以下代码传递映射、一个带有命名空间前缀的 XPath 语句和 SetValue 方法的命名空间定义：

xp.SetValue xmp, "/so:so/so:Products/so:Line/so:Quantity",  _
         "xmlns:so='http://phvis.com/exSalesOrder'"

可以从 XmlMap 的 Schemas 集合的 Schema 对象中，检索与架构相关联的命名空间。以下代码是为了利用该属性而对前面的代码进行的重新编写：

xp.SetValue xmp, "/so:so/so:Products/so:Line/so:Quantity",  _
         "xmlns:so='" & xmp.Schemas(1).Namespace & "'"

Excel 在架构被加载时自动为架构创建命名空间和前缀。前缀使用的格式是 ns1、ns2，等等。Workbook 对象的 Namespaces 集合使您可以在加载架构时检索 Excel 为命名空间创建的前缀。以下代码使用 Namespaces 集合建立命名空间定义：

Dim strNSDefinition As String
strNSDefinition = "xmlns: " & _
     ActiveWorkbook.XmlNamespaces(1).Prefix & _
   "=" & ActiveWorkbook.XmlNamespace(1).URI
xp.SetValue xmp, "/so:so/so:Products/so:Line/so:Quantity",  _
strNSDefinition

如果使用 Excel 的前缀，调用 SetValue 方法时则无须提供命名空间/前缀定义。以下示例使用 Excel 用于加载的第一个架构的 ns1 前缀：

xp.SetValue xmp, "/ns1:so/ns1:Products/ns1:Line/ns1:Quantity"

返回页首

创建列表

在销售订单示例中，ProductId 和 Quantity 元素可以重复多次。只要元素在架构中重复，就可以将该元素分配给一列单元格。只需将 True 作为 SetValue 方法的第四个参数进行传递即可：

Dim xmp As XmlMap
Dim cll As Range
Dim xp As XPath

Set xp = ActiveSheet.Range("B1 ").XPath
xp.SetValue xmp, _
    "/ns1:so/ns1:Products/ns1:Line/ns1:ProductId", , True
Set xp = ActiveSheet.Range("C1").XPath
xp.SetValue xmp, "/ns1:so/ns1:Products/ns1:Line/ns1:Quantity", , True

将重复元素映射到一个单元格区域的结果是，在 Excel 中创建一个列表（请参阅 图 1）。自动创建的、与 XML 相关的列表会显示为单个列，其标题（基于元素或属性名称）位于顶部，而且列表底部有一个用于添加数据的单元格。当您向最后一个单元格添加数据时，该列表会 自动扩展到下一行。

图 1. 通过将元素映射到一列单元格而在 Excel 中创建的列表

应该总是映射文档的层次结构中的最低级别数据。尽管可以映射容器元素（如此示例中的 Products）而不会产生错误，但是得到的映射并不生成 XML 文档。例如，在销售订单示例中，这意味着在文档中可以映射的组件包括：

• ID 和类型属性

• Name、ProductId 和 Quantity 元素

映射容器元素（Customer、Products 和 Line）会产生“XPath syntax is incorrect”（XPath 语法不正确）消息。

列表

可以从 Worksheet 对象的 ListObjects 集合中检索对列表的引用，以便配置该列表。

例如，以下代码检索对电子表格中第一个列表的引用，并为该列表分配名称：

Dim lst As ListObject
Set lst = ActiveSheet.ListObjects(1)
lst.Name = "ProductId"

可以使用 ListObject 对象的名称来从 ListObjects 集合中检索该对象（Excel 所分配名称的格式为 list1、list2，等等）。以下代码使用刚分配的名称检索该列表：

Set lst = ActiveSheet.ListObjects("ProductId")

从 ListObject 对象的 Range 属性中，可以检索列表的 Range 对象。从 Range 对象可以访问列表的 XPath 对象来清除或更改列表的映射。一个列表还具有三个其他 Range 对象：

• HeaderRowRange ：位于列表顶部、包含标题的单元格

• DataBodyRange ：列表中包含数据的单元格区域

• InsertRowRange ：位于列表底部、可以在其中添加数据的单元格。仅当列表中的一个单元格当前处于活动状态时，此区域才可用。

许多元素和属性的名称不便于用户使用，所以 ListObject 的典型应用是在列表顶部设置名称：

lst.HeaderRowRange.Value = "Product Number"

可以将列表再转换为一组普通的单元格，方法是调用其 Unlink 方法。

当列表中的一个单元格当前处于活动状态时，ListObject 的 Active 属性为 True。以下代码将新值添加到列表的底部：

If lst.Active Then
   lst.InsertRowRange.Value = "BZ734"
End If

返回页首

加载文档

除了加载说明 XML 文档的架构，还可以将数据从 XML 文档加载到 Excel 电子表格中。如果所加载的文档使用当前加载的架构，则将文档中的数据插入映射到文档元素的单元格。

导入到 XMLMap

将 XML 文档加载到映射工作簿是使用 XmlMap 对象的 Import 方法完成的。以下代码将销售订单文档加载到一个工作簿中，该工作簿已包含映射到工作簿中单元格的“so”架构：

ActiveWorkbook.XmlMaps("SalesOrder").Import _
"C:/Documents/SalesOrder.XML", False

将 Import 方法的第二个参数设置为 False，可以防止已导入文档中的数据覆盖已位于工作簿的列表中的数据。Excel 将列表数据追加到已存在数据（在第二个参数设置为 False 时，如果网格中唯一的元素为非重复元素，则 Excel 不导入文件）。也可以通过将 XmlMap 的 AppendOnImport 属性设置为 True，来控制 Excel 是否在加载时追加数据。

Import 方法返回一个指示导入是否成功的整数值。将返回以下三个值之一：

• xlXmlImportSuccess：已成功导入

• xlXmlImportElementsTruncated：已成功导入，但并不是所有数据都适合工作表

• xlXmlImportValidationFailed：导入失败，因为在与映射的架构进行比较时文档是无效的

可以通过检索与当前单元格关联的映射的名称，使导入过程的交互性更强。Range 对象的 XPath 对象的 Map 属性包含对此单元格中所用映射的引用。以下代码使用 Map 对象的 Name 属性，来确定要使用的映射、加载文档并检查返回的结果：

Dim res As XlXmlImportResult
res = ActiveWorkbook.XmlMaps(ActiveCell.XPath.Map.Name).Import( _
"C:/Documents/SalesOrder.XML", False)
Select Case res
   Case xlXmlImportElementsTruncated
      MsgBox "并不是所有的数据都适合电子表格。 "
   Case xlXmlImportValidationFailed
      MsgBox "文档无效。 "
End Select

Map 对象还包括一个用于导入 XML 字符串的 ImportXML 方法。以下示例导入仅包括根元素的销售订单文档：

ActiveWorkbook.XmlMaps("SalesOrder").ImportXml  _
  "<so:so xmlns:so='http://phvis.com/xSalesOrder'/>", True

将数据绑定到映射

XMLMap 对象的 DataBinding 属性提供了另一种将 XML 文档加载到电子表格中的方法。DataBinding 对象的 LoadSettings 方法接受对 UDC 文件和说明 Web 服务的 WSDL 文件的各种类型的输入（包括 URL 或路径名）。唯一的要求是由参数指定的数据源必须返回一个 XML 文档。在以下示例中，数据绑定设置为 XML 文档的 URL：

ActiveWorkbook.XmlMaps("SalesOrder").DataBinding.LoadSettings  _
"http://Customers/so.xml"

完成设置之后，可以通过调用 DataBinding 对象的 Refresh 方法，将 XML 数据加载到电子表格中：

ActiveWorkbook.XmlMaps("SalesOrder").DataBinding.Refresh

可以通过调用该对象的 ClearSettings 方法，断开 DataBinding 对象与数据源的连接。也可以通过读取 DataBinding 对象的 SourceURL 属性，来检索数据源的 URL：

MsgBox "Breaking Connection to " & _
ActiveWorkbook.XmlMaps("SalesOrder").DataBinding.SourceURL
ActiveWorkbook.XmlMaps("SalesOrder").DataBinding.ClearSettings

从工作表中导入

另一种导入数据的方法是使用 Workbook 对象的 XMLImport 方法，它导入 XML 文档并为此文档生成 XMLMap 。XMLImport 方法将 XML 文档的名称接受为它的第一个参数。第二个参数必须是一个 XMLMap 类型的变量，它在 Excel 导入文档之后包含该导入创建的 XMLMap 引用。如果此文档并不包含对架构的引用，则 Excel 为此文档生成一个架构。XMLImport 方法的第三个参数使您可以指示是覆盖已在区域中的数据 (True)，还是将新数据追加到现有数据 (False)。最后，第四个参数是 Excel 应将数据导入到其中的区域。Excel 将自动为任何重复元素创建列表。

以下代码将销售订单文档导入到从单元格 A1 开始的区域。Excel 覆盖任何现有数据：

Dim xmp As XmlMap
ActiveWorkbook.Import "c:/documents/MyBook.XML", xmp, True,  _
ActiveWorkBook.Range("A1")

也可以使用 Workbook 对象的 XMLImportXML 来加载数据，它导入 XML 字符串的方式与 XMLMap 对象的 ImportXML 方法导入 XML 字符串的方式相同。

返回页首

访问数据

可以检索使用 XMLDataQuery 方法分配给元素的单元格中的数据。XMLDataQuery 方法接受一个 XPath 语句，该语句说明了单元格映射并返回一个 Excel Range 对象。例如，以下代码检索映射到 Name 元素的 Excel Range 对象：

Dim rng As Range
ActiveWorkbook.Worksheets(1).XmlDataQuery( _
"/ns1:so/ns1:Customer/ns1:Name").value

其中的 XPath 表达式必须与用于将单元格映射到元素或属性的 XPath 表达式匹配。如果将谓词用作映射的一部分，而且该谓词是 XPath 语句的最后一部分，则可以忽略该谓词。

一次只能映射一个元素。因此，必须确定是否已映射了一个元素。XMLMapQuery 提供了一种方法，它可用于测试是否已将一个元素映射到另一个元素。如果 XMLMapQuery 方法未返回任何 Range 对象，则不存在该元素的映射。以下示例通过检查是否映射到 ProductQuantity 元素说明了此方法：

Dim rng As Range
Set rng = ActiveWorkBook.WorkSheets(1). _
  XmlMapQuery("/ns1:so/ns1:Products/ns1:Line/ns1: Quantity")
If rng Is Nothing Then
xp.SetValue xmp, _
 "/ns1:so/ns1:Products/ns1:Line/ns1:Quantity", , True
End If

返回页首

创建 XML 文档

使用 Excel 更新并创建 XML 文档过程中的一个重要部分是保存文档。可以使用 Workbook 对象的 SaveAsXMLData 方法，为任何加载的架构创建文档。必须向方法传递已保存文件的路径名，并添加对要写出其元素的映射的引用。以下示例从集合中的第一个映射创建一个名为 ASalesOrder.XML 的文件：

ActiveWorkbook.SaveAsXMLData "C:/Documents/ASalesOrder.XML",  _
   ActiveWordbook.XmlMaps(1)

也可以从 XMLMap 对象创建文档。XMLMap 对象既包括 ExportXML 方法，也包括 Export 方法。ExportXML 将 XML 文档保存到字符串变量中，并且 Export 将映射数据导出到文件。这两种方法都返回指示导出是否成功的两个值之一：

xlXmlExportSuccess: 已成功导出。
xlXmlExportValidationFailed: 导出未成功，因为电子表格中的数据
对于架构而言无效。

检查映射的 IsExportable 属性以确保已成功导出。例如，如果不提供必需字段，或者为元素提供了无效值，则映射是无法导出的。以下两个示例说明这两种方法：

Dim strXML As String
If ActiveWorkbook.XmlMaps(1).IsExportable Then
ActiveWorkbook.XmlMaps(1).ExportXML strXML
End If

Dim xmp As XmlMap
Set xmp = ActiveWorkbook.XmlMaps(1)
If  xmp.IsExportable Then
ActiveWorkbook.XmlMaps(1).Export "c:/Documents/ASalesOrder.XML"
End If

返回页首

Excel XML 事件

Excel 中的 Application 和 Workbook 对象在导入和导出 XML 文档的之前和之后激发事件。要使用这些事件，请创建全局变量并将其分配给相应的对象。以下代码执行该操作，作为类模块的 Initialize 事件的一部分：

Dim WithEvents exc As Application
Dim WithEvents sheet As WorkSheet

Private Sub Class_Initialize()
Set sheet = ActiveSheet
Set exc = Application
End Sub

在某些其他事件（如 Excel 中的 AutoOpen 例程）中，则需要创建类模块。完成之后，类模块中的代码可以捕获 XML 事件。

Excel 向“之前”事件（WorkbookBeforeXMLImport 和 WorkbookBeforeXMLExport ） 传递对工作簿的引用、要使用的映射以及布尔变量（如果该变量在事件代码中设置为 True，则取消导入或导出）。导入和导出事件之间有一个参数是不同的：Excel 向导出事件传递文件的路径名；Excel 向导入事件传递一个布尔值，此布尔值指示工作簿数据刷新过程中是否发生此导入。

Excel 向“之后”事件（WorkbookAfterXMLExport 和 WorkbookAfterXMLImport ） 传递对工作簿的引用和在导出中使用的映射。Excel 向这两种事件都传递指示导出或导入是否成功的结果代码。类似于“之前”事件，这些事件的其余参数也因导入事件和导出事件而不同：Excel 向导出事件传递要创建文件的路径名；Excel 向导入事件传递一个布尔值，此布尔值指示工作簿数据刷新过程中是否应发生导入。

Workbook 对象也在 XML 导入和导出的之前和之后激发事件。传递给这些事件的参数与等效的 Application 事件基本相同，不同之处是 Excel 不传递对工作簿的引用。例如，Excel 向 AfterXMLExport 事件传递对要使用的 Map 对象的引用、要创建的文件以及该导出的结果代码。

以下面的代码为例，它使用 Workbook 对象的“之前”事件，来检查是否尝试在目录 c:/documents/ 中创建文件。如果不是，则取消导出：

Private Sub sheet_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url _
As String, Cancel As Boolean)
If Instr(Url, "c:/documents/") = 0 Then
   Cancel = True
   MsgBox "文档必须保存到 C:/Documents 目录。"
End If
End Sub

返回页首

结论

Excel 的最新版本通过与 XML 的集成获得了一组丰富的功能。Excel 的对象模型提供了可用于创建将 Excel 集成到基于 XML 的工作流的应用程序的工具。通过使用自定义代码扩展 Excel 的基本功能，您可以允许用户使用任何 XML 词汇创建和更新 XML 文档。

关于作者

Peter Vogel 是一名 Office 和 .NET 开发方面的顾问。他是《Visual Basic Object and Component Handbook》（Prentice Hall PTR，2000，ISBN：0-13023-0731）的作者，也是《Smart Access》时事通讯的编辑。

 

返回页首