Excel 2007 XML源地图 - 刷新模式 [英] Excel 2007 XML Source Maps - Refreshing Schemas

问题描述

我有一个电子表格报告应用程序生成的xml文档。我使用的功能允许您通过将XML源窗格中的元素拖放到工作表中来创建电子表格的格式。

我没有一个模式（目前） - Excel只是从提供的XML文档中推断出来。

由于规范更改，XML格式已经改变 - 现在有一些额外的元素在几个地方。

我看不到如何让Excel刷新XML源窗格中可用的字段（即推断的模式），基于更新的XML文档。如果XML获得额外的字段，似乎我必须从头开始使用新的电子表格。

有没有办法这样做？

解决方案

我处于类似的情况，在我的情况下，从头开始更容易，即手动添加每个字段。在我这样做之前，我看看Excel如何管理这些连接，并在以下示例中放置了相关的（和我找到selfexplanitory）方法（代码假定有一个名为Tabelle1的工作表，具有excel-list和两个xml链接在单元格A1和B1中提交，并且在模式中XPath到/ dataroot / Projekte / ID是有效的 - 也就是说，相应地进行更改以尝试它）。

所以我假设使用这些方法/函数，你应该能够

• 添加你的第二个（新）模式，然后


• 读出所有相关信息，如下所示，即读出旧模式上的字段的XPath，


• 删除旧的XPath引用和


• 使用新模式再次为该单元添加。

我自己尝试过（因为我在我的例子中只说9个字段/单元格和2个表）

  Sub mainExcelSchemaExamples ）
 
 View Inferred（或显式插入）Schema 
 Dim myxmlmap As XmlMap 
 Dim myXMLSchemaString As String 
 
设置myxmlmap = ActiveWorkbook.XmlMaps（1）
 
 myXMLSchemaString = ActiveWorkbook.XmlMaps（1）.Schemas（1）.XML 
 Debug.Print myXMLSchemaString 
 
'显示XPath 
将mySheet作为工作表
设置mySheet = ActiveWorkbook。表单（Tabelle1）
 
'从列表
 Debug.Print mySheet.ListObjects（1）.ListColumns（2）.XPath.Map，_ 
 mySheet.ListObjects（ 1）.ListColumns（2）.XPath 
 
'单字段
 Debug.Print mySheet.Range（A1）。XPath.Map，mySheet.Range（A1）。XPath 
 Debug.Print mySheet.Range（B1）。XPath.Map，mySheet.Range（B1）。XPath 
 
'添加XPath（和XML-Map）
 mySheet.Range（A2）。XPath.SetValue Map：= myxmlmap，XPath：=/ dataroot / Projekte / ID
 
 End Sub 
  

我希望这有助于作为起点。

问候
Andreas

I have a spreadsheet that is reporting on xml docs generated by an application. I'm using the functionality that allows you to create the format of the spreadsheet by dragging and dropping elements from the XML Source pane into your sheet.

I don't have a schema (at the moment) - Excel is just inferring from a supplied XML document.

Due to a spec change the XML format has changed - there are now some extra elements at a couple of places.

I can't see how to get Excel to refresh the fields available in the XML Source pane (ie in the inferred schema) based on an updated XML doc. It seems like I have to start from scratch with a new spreadsheet if the XML gets extra fields.

Is there a way around having to do this?

解决方案

I was in a similar situation and in my case it was easier to just start from scratch, i.e. add every field manually. Before I did that I had a look at how Excel manages these connections and have put the relevant (and I find selfexplanitory) methods in the following example (the code assumes there is a worksheet called Tabelle1 with an excel-list and two xml-linked filed in cells A1 and B1 and further in the schema the XPath to /dataroot/Projekte/ID is valid -- that said, change accordingly to try it out).

So I assume with these methods/ functions you should be able to

• add your second (new) schema, then
• read out all the relevant information as shown below i.e. read out the XPath of the field on the old schema,
• delete the old XPath reference and
• add it again for that cell using the new schema.

BUT I haven't tried this myself (because as I said in my example it was just 9 fields/ cells and 2 tables)

Sub mainExcelSchemaExamples()

    ' View Inferred (or explicitly inserted) Schema
    Dim myxmlmap As XmlMap
    Dim myXMLSchemaString As String

    Set myxmlmap = ActiveWorkbook.XmlMaps(1)

    myXMLSchemaString = ActiveWorkbook.XmlMaps(1).Schemas(1).XML
    Debug.Print myXMLSchemaString

    ' Show XPath
    Dim mySheet As Worksheet
    Set mySheet = ActiveWorkbook.Sheets("Tabelle1")

    ' From a List
    Debug.Print mySheet.ListObjects(1).ListColumns(2).XPath.Map, _
            mySheet.ListObjects(1).ListColumns(2).XPath

    ' Single field
    Debug.Print mySheet.Range("A1").XPath.Map, mySheet.Range("A1").XPath
    Debug.Print mySheet.Range("B1").XPath.Map, mySheet.Range("B1").XPath

    ' Add XPath (and XML-Map)
    mySheet.Range("A2").XPath.SetValue Map:=myxmlmap, XPath:="/dataroot/Projekte/ID"

End Sub

I hope this helps as starting point.

Regards Andreas

这篇关于Excel 2007 XML源地图 - 刷新模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！