在 Excel 2003 中创建 XML 映射

• 关 键 词：
• microsoft
• internet
• office
• excel
• 解决方案

XML 实例

<?xml version="1.0" encoding="UTF-8" standalone="no" ?> <Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Customer> <Id>1</Id> <Name>Janet Q. Leverling</Name> <Address> <AddressLine>123 Elm</AddressLine> <City>Pleasantville</City> <State>WA</State> <zip>12345</zip> </Address> <Order> <Id>1</Id> <Date>3/2/01</Date> <Product> <Name>Widget</Name> <Quantity>3</Quantity> </Product> <Product> <Name>Sprocket</Name> <Quantity>7</Quantity> </Product> </Order> <Order> <Id>2</Id> <Date>4/7/01</Date> <Product> <Name>Widget</Name> <Quantity>1</Quantity> </Product> </Order> </Customer> <Customer> <Id>2</Id> <Name>Nancy Davolio</Name> <Address> <AddressLine>123 Main</AddressLine> <City>Mayberry</City> <State>WA</State> <zip>12345</zip> </Address> <Order> <Id>3</Id> <Date>5/1/01</Date> <Product> <Name>Widget</Name> <Quantity>5</Quantity> </Product> <Product> <Name>Sprocket</Name> <Quantity>2</Quantity> </Product> </Order> <Order> <Id>4</Id> <Date>5/8/01</Date> <Product> <Name>Widget</Name> <Quantity>4</Quantity> </Product> </Order> </Customer> </Root>

XSD

<?xml version="1.0" encoding="UTF-8" standalone="no" ?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="Root"> <xsd:complexType> <xsd:sequence> <xsd:element minOccurs="0" maxOccurs="unbounded" name="Customer"> <xsd:complexType> <xsd:sequence> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Id" /> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Name" /> <xsd:element minOccurs="0" maxOccurs="1" name="Address"> <xsd:complexType> <xsd:sequence> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="AddressLine" /> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="City" /> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="State" /> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="zip" /> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element minOccurs="0" maxOccurs="unbounded" name="Order"> <xsd:complexType> <xsd:sequence> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Id" /> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Date" /> <xsd:element minOccurs="0" maxOccurs="unbounded" name="Product"> <xsd:complexType> <xsd:sequence> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Name" /> <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Quantity" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema> 创建多维表

用户界面

当您将 FlightReport 或Root 元素放到单元格 A3 中时，该单元格将如下所示：

 

图 8. 多维映射

XML 实例

<?xml version="1.0"?> <Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <FlightReport altitude="1000" weight="10000"> <Measurement> <Airspeed>350</Airspeed> <Vibration>125.6</Vibration> <Noise>62</Noise> <Drag>0.12</Drag> </Measurement> <Measurement> <Airspeed>400</Airspeed> <Vibration>99</Vibration> <Noise>75</Noise> <Drag>0.10</Drag> </Measurement> </FlightReport> <FlightReport altitude="2000" weight="10000"> <Measurement> <Airspeed>350</Airspeed> <Vibration>125.6</Vibration> <Noise>62</Noise> <Drag>0.12</Drag> </Measurement> <Measurement> <Airspeed>400</Airspeed> <Vibration>99</Vibration> <Noise>75</Noise> <Drag>0.10</Drag> </Measurement> </FlightReport> <FlightReport altitude="1000" weight="15000"> <Measurement> <Airspeed>350</Airspeed> <Vibration>125.6</Vibration> <Noise>62</Noise> <Drag>0.12</Drag> </Measurement> <Measurement> <Airspeed>400</Airspeed> <Vibration>99</Vibration> <Noise>75</Noise> <Drag>0.10</Drag> </Measurement> </FlightReport> <FlightReport altitude="2000" weight="15000"> <Measurement> <Airspeed>350</Airspeed> <Vibration>125.6</Vibration> <Noise>62</Noise> <Drag>0.12</Drag> </Measurement> <Measurement> <Airspeed>400</Airspeed> <Vibration>99</Vibration> <Noise>75</Noise> <Drag>0.10</Drag> </Measurement> </FlightReport> </Root>

XSD

创建一个以属性为中心的映射

用户界面

当您将 FlightReport 或Root 元素放到单元格 A3 中时，该单元格将如下所示：

 

图 9. 以属性为中心的映射

XML 实例

<?xml version="1.0"?> <Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Employees EmployeeId="1" FirstName="Janet" LastName="Leverling" Title="Sales Representative"/> <Employees EmployeeId="2" FirstName="Nancy" LastName="Davolio" Title="Technical Advisor"/> <Employees EmployeeId="3" FirstName="Robert" LastName="Fuller" Title="Accountant"/> </Root>

XSD

<?xml version="1.0"?> <schema xmlns="http://www.w3.org/2001/XMLSchema"> <element name="Root"> <complexType> <sequence> <element name="Employees" maxOccurs="unbounded"> <complexType> <attribute name="EmployeeId" type="positiveInteger"/> <attribute name="FirstName" type="string"/> <attribute name="LastName" type="string"/> <attribute name="Title"> <simpleType> <restriction base="string"> <enumeration value="Sales Representative"/> <enumeration value="Technical Advisor"/> <enumeration value="Accountant"/> </restriction> </simpleType> </attribute> </complexType> </element> </sequence> </complexType> </element> </schema>

显然，有像架构一样尽可能多的格式来创建它们。这里介绍这组格式化可能性的目的是，为您开发和精细调整您自己的架构提供一个起点。小结

架构使您能够指定哪些元素、属性、数据类型和层次结构允许在 XML 数据文件中出现。您可以使用架构文件验证 XML 数据，并在交换数据时

确保可预见性。由于该可预见性，您可以调整您的架构来确保数据可以正确显示。在 Excel 中，您使用 XML Source 任务网格将元素映射到

单元格。然后，将 XML 数据导入到一个最适合您需要的窗体是相对较容易的。

【深 度 阅 读】 相 关 文 章

• 用VB创建Excel报表
• C＃来创建和读取XML文档
• XMLHttpRequest对象创建数据请求处理
• 创建快捷方式去掉小箭头妙招