将电子表格转换为xml格式的列表

I am trying to export a spreadsheet to a specific xml layout to be imported into a pdf form. I have gotten pretty far on my own but am stuck at what seems to be near the end. (Mind you, I am not very proficient with programing stuff, and my experience with XML/XSLT is limited to this project)

我正在尝试将一个电子表格导出到一个特定的xml布局中，以导入pdf格式。我已经靠自己走得很远了，但却被似乎即将结束的事情困住了。(请注意，我对编程不是很精通，我对XML/XSLT的经验仅限于这个项目)

First, I exported an example set of data from the pdf, which gave me an idea of what I am looking for. I then imported this into Excel and got a spreadsheet from the data. This let me edit the data easily, then theoretically export it back, but...Excel wouldn't export the XML map that it created on import due to a "lists of lists" problem. This led me to putting the speadsheet into OpenOffice (technically LibreOffice) and trying an XML export filter using XSLT.

首先，我从pdf中导出了一组示例数据，这使我了解了我要查找的内容。然后我将它导入到Excel中，并从数据中获取电子表格。这让我很容易地编辑数据，然后理论上导出它，但是…Excel不会导出由于“列表列表”问题而创建的XML映射。这导致我将speadsheet放入OpenOffice(技术上是LibreOffice)并尝试使用XSLT进行XML导出筛选。

My data in the spreadsheet looks like...

我在电子表格中的数据看起来像……

Type    Name    Compound    Weight  Material    Weight
AAA     BBB         X           5       s         2
AAA     BBB         X           5       t         3
AAA     BBB         Y           4       r         4

I need it to be exported as like...

我需要它像…

<?xml version="1.0" encoding="UTF-8" ?> 
- <MCD Type="AAA" Name="BBB">
  - <Product Compound="X">
     <Amount weight="5"/> 
   - <HM Material="s">
      <Amount weight="2" /> 
     </HM>
   - <HM Material="t">
      <Amount weight="3" />
     </HM>
    </Product>
  - <Product Compound="Y">
     <Amount weight="4"/> 
   - <HM Material="r">
      <Amount weight="4" /> 
     </HM>
    </Product>
   </MCD>

But with my current XSL, I get something more like...

但是通过我当前的XSL，我得到了更类似于……

<?xml version="1.0" encoding="UTF-8" ?> 
- <MCD Type="AAA" Name="BBB">
  - <Product Compound="X">
     <Amount weight="5"/> 
   - <HM Material="s">
      <Amount weight="2" /> 
     </HM>
    </Product>
   </MCD>
- <MCD Type="AAA" Name="BBB">
  - <Product Compound="X">
   - <HM Material="t">
      <Amount weight="3" />
     </HM>
    </Product>
   </MCD>
- <MCD Type="AAA" Name="BBB">
  - <Product Compound="Y">
     <Amount weight="4"/> 
   - <HM Material="r">
      <Amount weight="4" /> 
     </HM>
    </Product>
   </MCD>

My problem lies with each cell of the spreadsheet being in the output. I want to have the parent cell only be deiplayed when it changes, like in a folder tree. I hope this makes sense and someone can help me (either making it work in excel or openoffice, though from my search it seems the OpenOffice route is more likely). I have scoured for answers but nothing has quite answered my needs. Thank you in advance.

我的问题在于电子表格的每个单元格都在输出中。我想让父单元格只在它改变时被deiplay，比如在文件夹树中。我希望这是有道理的，有人可以帮助我(可以在excel或openoffice中使用，不过从我的搜索来看，openoffice路径更有可能)。我一直在寻找答案，但没有什么能完全满足我的需要。提前谢谢你。

1 个解决方案

#1

if you're using XSLT 2, starting from your last XML and using the following stylesheet:

如果您正在使用XSLT 2，请从上一个XML开始，并使用以下样式表:

    <?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:template match="/">
    <xsl:for-each-group select="//MCD" group-by="concat(@Type,'-',@Name)">
      <MCD Type="{@Type}" Name="{@Name}">
        <xsl:for-each select="//Product[(parent::MCD/@Type = substring-before(current-grouping-key(),'-')) and (parent::MCD/@Name = substring-after(current-grouping-key(),'-'))]"> 
           <xsl:copy-of select="." />
        </xsl:for-each>
      </MCD>
    </xsl:for-each-group>
  </xsl:template>

</xsl:stylesheet>

you ll get:

你会得到:

    <?xml version="1.0" encoding="UTF-8"?><MCD Type="AAA" Name="BBB"><Product Compound="X">
     <Amount weight="5"/> 
   <HM Material="s">
      <Amount weight="2"/> 
     </HM>
    </Product><Product Compound="X">
   <HM Material="t">
      <Amount weight="3"/>
     </HM>
    </Product><Product Compound="Y">
     <Amount weight="4"/> 
   <HM Material="r">
      <Amount weight="4"/> 
     </HM>
    </Product></MCD>

#1