将非规范化数据从Excel导出到XML [英] Export denormalized data from excel to xml

问题描述

我们正尝试将具有非规范化数据"的excel表导出到xml.表标题如下:

We are trying to export an excel table with "Denormalized Data" to xml. The table headers are as follows:

| AssetManager Code | AssetManager Date | Portfolio Code | Portfolio Name | MarketValue | NetCashFlow | Field | Field Code | Field Name |

AssetManager代码和AssetManager日期始终相同，其余各列包含变量数据.

The AssetManager Code and AssetManager Date are the same throughout, the rest of the columns contain variable data.

这是我们想要的xml输出的示例:

Here is an example of the xml output we want:

<AssetManager Code="PFM" Date="20130117">                   
    <Portfolios>            
        <Portfolio Code="CC PSP" Name="Consilium Capital">      
            <MarketValue>5548056.51</MarketValue>   
            <NetCashFlow>0</NetCashFlow>    
            <UserFields>    
                <Field Code="AM UCGT" Name="AM daily Unrealised CG">4375</Field>
            </UserFields>   
        </Portfolio>        
        <Portfolio Code="MM (FC)" Name="Money Market UT (FC)">      
            <MarketValue>28975149.6500735</MarketValue> 
            <NetCashFlow>0</NetCashFlow>    
            <UserFields>    
                <Field Code="UCGT" Name="AM daily Unrealised CG">0</Field>
            </UserFields>   
        </Portfolio>        
    </Portfolios>           
</AssetManager> 

还有包含映射的xsd文件:

And our xsd file containing the mappings:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="AssetManager">
    <xs:complexType>
        <xs:sequence>
                    <xs:element ref="Portfolios" />
            </xs:sequence>
        <xs:attribute name="Code" type="xs:string"/>
            <xs:attribute name="Date" type="xs:string"/>
    </xs:complexType>
</xs:element>
<xs:complexType name="FieldType">
    <xs:simpleContent>
        <xs:extension base="xs:decimal">
            <xs:attribute name="Code" type="xs:string"/>
                <xs:attribute name="Name" type="xs:string"/>
        </xs:extension>
    </xs:simpleContent>
</xs:complexType>
<xs:element name="Portfolios">
  <xs:complexType>
    <xs:sequence>
      <xs:element name="Portfolio">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="MarketValue" type="xs:decimal"/>
        <xs:element name="NetCashFlow" type="xs:decimal"/>
        <xs:element name="UserFields">
          <xs:complexType>
            <xs:sequence>
                    <xs:element name="Field" type="FieldType"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="Code" type="xs:string"/>
      <xs:attribute name="Name" type="xs:string"/>
    </xs:complexType>
              </xs:element>
            </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

至少我们想知道为什么excel认为数据被非规范化了吗?

At the very least we'd like to know why excel considers data denormalised?

任何帮助将不胜感激.

推荐答案

首先，发布的XSD出现问题.投资组合应将maxOccurs设置为大于1的值-否则，您将不匹配示例XML，并且在Excel中验证地图时不会出现非规范化数据"错误.

First of all, you have a problem with the posted XSD. The Portfolio should have the maxOccurs set to a value greater than 1 - otherwise you're not matching the sample XML and you wouldn't get the "denormalized data" error when verifying your map in Excel.

本文应该说明您使用Excel映射时遇到的常见错误-包括您的错误.

This article should explain common errors you get with Excel maps - yours included.

我猜您所做的是拖放根-这不适用于重复元素.

I guess what you did was to drag-drop the root - this will not work with repeating elements.

您可能会喜欢我在下面所做的事情；它可能不适用于您的具体示例，但应该可以为您提供一个思路.

You may get around with what I did below; it may not work for your concrete example, but it should give you an idea.

修改了XSD以解决重复粒子的问题:

Modified your XSD to account for repeating particles:

<?xml version="1.0" encoding="UTF-8"?>
<!-- XML Schema generated by QTAssistant/XSD Module (http://www.paschidev.com) -->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="AssetManager">
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="Portfolios"/>
            </xs:sequence>
            <xs:attribute name="Code" type="xs:string"/>
            <xs:attribute name="Date" type="xs:string"/>
        </xs:complexType>
    </xs:element>
    <xs:complexType name="FieldType">
        <xs:simpleContent>
            <xs:extension base="xs:decimal">
                <xs:attribute name="Code" type="xs:string"/>
                <xs:attribute name="Name" type="xs:string"/>
            </xs:extension>
        </xs:simpleContent>
    </xs:complexType>
    <xs:element name="Portfolios">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="Portfolio" minOccurs="0" maxOccurs="unbounded">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="MarketValue" type="xs:decimal"/>
                            <xs:element name="NetCashFlow" type="xs:decimal"/>
                            <xs:element name="UserFields">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element name="Field" type="FieldType"/>
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                        <xs:attribute name="Code" type="xs:string"/>
                        <xs:attribute name="Name" type="xs:string"/>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

仅在第一页上拖动代码和日期；如果需要，可以将其重命名为其他名称.

Drag the Code and Date only on the first sheet; rename that to something else if you want.

将投资组合"拖到另一个工作表.

Drag Portfolios to another sheet.

填写一些数据并导出；这就是我得到的:

Fill in some data and Export; this is what I got:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<AssetManager Code="a" Date="b">
    <Portfolios>
        <Portfolio Code="aa" Name="bb">
            <MarketValue>10</MarketValue>
            <NetCashFlow>100</NetCashFlow>
            <UserFields>
                <Field/>
            </UserFields>
        </Portfolio>
        <Portfolio Code="aa" Name="bb">
            <MarketValue>10</MarketValue>
            <NetCashFlow>100</NetCashFlow>
            <UserFields>
                <Field/>
            </UserFields>
        </Portfolio>
    </Portfolios>
</AssetManager>

看起来很近.如果没有解决方案本身，那么它可以帮助您前进，然后再进行调查.

It looks pretty close. It should help you move forward if not with the solution itself, then with your investigations.

这篇关于将非规范化数据从Excel导出到XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！