当前位置：文江博客话题详情

如何以编程方式将 XML 映射添加到 Excel 2010 电子表格中？

发布于 2024-09-25 12:03:09 字数 113 浏览 5 评论 0原文

我有一个基本的 XML 文件，需要将其作为 XML 映射添加到 Excel 2010 工作表中。

我如何以编程方式执行此操作？我更喜欢使用 Microsoft OpenXML SDK 的解决方案。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淡淡離愁欲言轉身 2024-10-02 12:03:09

您需要将 CustomXmlMappingsPart 添加到 WorkbookPart 中，并使用 XML 架构填充它。然后，您需要添加链接到 xml 文件的 ConnectionsPart。

以下 xml 位于我的桌面上的文件 note.xml 中：

<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>

这是生成 xmlmappings 的代码

CustomXmlMappingsPart part = workbookPart.AddNewPart<CustomXmlMappingsPart>("rId8");

CustomXmlMappingsPart part = new 
MapInfo mapInfo1 = new MapInfo(){ SelectionNamespaces = "" };

Schema schema1 = new Schema(){ Id = "Schema1" };

OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<xsd:schema xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"><xsd:element nillable=\"true\" name=\"note\"><xsd:complexType><xsd:sequence minOccurs=\"0\"><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"to\" form=\"unqualified\" /><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"from\" form=\"unqualified\" /><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"heading\" form=\"unqualified\" /><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"body\" form=\"unqualified\" /></xsd:sequence></xsd:complexType></xsd:element></xsd:schema>");

schema1.Append(openXmlUnknownElement1);

Map map1 = new Map(){ ID = (UInt32Value)1U, Name = "note_Map", RootElement = "note", SchemaId = "Schema1", ShowImportExportErrors = false, AutoFit = true, AppendData = false, PreserveAutoFilterState = true, PreserveFormat = true };
DataBinding dataBinding1 = new DataBinding(){ FileBinding = true, ConnectionId = (UInt32Value)1U, DataBindingLoadMode = (UInt32Value)1U };

map1.Append(dataBinding1);

mapInfo1.Append(schema1);
mapInfo1.Append(map1);

part.MapInfo = mapInfo1;

此代码生成连接并将其添加到 WorkbookPart：

ConnectionsPart part = wookbookPart.AddNewPart<ConnectionsPart>("rId5");

Connections connections1 = new Connections();

Connection connection1 = new Connection(){ Id = (UInt32Value)1U, Name = "note", Type = (UInt32Value)4U, RefreshedVersion = 0, Background = true };
WebQueryProperties webQueryProperties1 = new WebQueryProperties(){ XmlSource = true, SourceData = true, Url = "C:\\Users\\Thomas\\Desktop\\note.xml", HtmlTables = true, HtmlFormat = HtmlFormattingValues.All };

connection1.Append(webQueryProperties1);

connections1.Append(connection1);

part.Connections = connections1;

我使用 Open XML SDK 生成了此代码2.0 生产力工具。我将纯 xlsx 文件与添加了简单 XML 映射的文件进行了比较。希望这有帮助。

You will need to add a CustomXmlMappingsPart to your WorkbookPart and populate it with the schema for your XML. Then you will need to add a ConnectionsPart that is linked to your xml file.

The below xml lives on my desktop in the file note.xml:

<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>

This is the code to generate the xmlmappings

CustomXmlMappingsPart part = workbookPart.AddNewPart<CustomXmlMappingsPart>("rId8");

CustomXmlMappingsPart part = new 
MapInfo mapInfo1 = new MapInfo(){ SelectionNamespaces = "" };

Schema schema1 = new Schema(){ Id = "Schema1" };

OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<xsd:schema xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"><xsd:element nillable=\"true\" name=\"note\"><xsd:complexType><xsd:sequence minOccurs=\"0\"><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"to\" form=\"unqualified\" /><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"from\" form=\"unqualified\" /><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"heading\" form=\"unqualified\" /><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"body\" form=\"unqualified\" /></xsd:sequence></xsd:complexType></xsd:element></xsd:schema>");

schema1.Append(openXmlUnknownElement1);

Map map1 = new Map(){ ID = (UInt32Value)1U, Name = "note_Map", RootElement = "note", SchemaId = "Schema1", ShowImportExportErrors = false, AutoFit = true, AppendData = false, PreserveAutoFilterState = true, PreserveFormat = true };
DataBinding dataBinding1 = new DataBinding(){ FileBinding = true, ConnectionId = (UInt32Value)1U, DataBindingLoadMode = (UInt32Value)1U };

map1.Append(dataBinding1);

mapInfo1.Append(schema1);
mapInfo1.Append(map1);

part.MapInfo = mapInfo1;

This code generates the connection and adds it to the WorkbookPart:

ConnectionsPart part = wookbookPart.AddNewPart<ConnectionsPart>("rId5");

Connections connections1 = new Connections();

Connection connection1 = new Connection(){ Id = (UInt32Value)1U, Name = "note", Type = (UInt32Value)4U, RefreshedVersion = 0, Background = true };
WebQueryProperties webQueryProperties1 = new WebQueryProperties(){ XmlSource = true, SourceData = true, Url = "C:\\Users\\Thomas\\Desktop\\note.xml", HtmlTables = true, HtmlFormat = HtmlFormattingValues.All };

connection1.Append(webQueryProperties1);

connections1.Append(connection1);

part.Connections = connections1;

I generated this code with the Open XML SDK 2.0 Productivity Tool. I compared a plain xlsx file with one that I added my simple XML map to. Hope this helps.

回复收藏 0 原文

~没有更多了~