如何使用简单的样式表转换 ms excel xml? [英] how to convert ms excel xml using a simple stylesheet?

问题描述

我需要使用样式表转换以 xml 格式保存的 excel 电子表格...但我一直对 ms 使用的许多命名空间感到困惑.我需要一个 XSLT 样式表，它允许传递输入 xml 的每个元素(当我使用样式表时，默认似乎传递所有文本，否则该样式表应该只传递我正在编写的元素模板).>

这是输入的xml:

http://apriority.dyndns.biz/im_cache/u_1/j_2390/2_9/tf/testexcel.xml

:我需要简单地从列中的两个工作表中提取数据，例如:

输出:

表 11) blah, blue , burn, baste, 肚皮, belie, 赐予, 订婚, 哀叹2) quack, quagmire, quick,quantum, quant3) 赡养费、灰烬、琥珀、绝对、占星术、雪花石膏、愤怒4)成本，咖喱，坦率，客舱，能力，蓖麻，加拿大第 2 页1) 32) 323) 322....等等.

我的样式表有问题...与 MS 在输入 xml 中使用的命名空间有关...我似乎正在输出所有数据，即使我只有一个元素模板(例如用于仅提取第一张表的行)...什么样式表代码将获得上面的输出???

这是我目前的样式表:

http://apriority.dyndns.biz/im_cache/u_1/j_2390/2_9/tf/learningmap.xsl

文件放在非 https url 上，所以没有病毒风险而且我用的是我的真名，谷歌我！

这是使用 Mads 建议的代码后编辑的样式表！

http://apriority.dyndns.biz/im_cache/u_1/j_2390/2_9/tf/learningmap_mod.xsl

我仍然有一个问题，即在后续代码中没有提取实际的文本数据，为什么我无法获取文本数据.我可以输出第一个变量snid"，但所有文本变量都不会出现在输出中，即使我正在选择它们并且原始源 xml 在这些条目中有内容.对这个新问题的任何帮助将不胜感激！

2 月 9 日更新:

我解决了变量映射失败的问题.这是一个简单的 xpath 错误，我正在处理不存在的节点.单元格和它们的行之间存在一对一的映射，因此变量应该提取为 Cell[k].Row[1]...Cell[k+1].Row[1]...等.

输出转换在我需要时发生，感谢您对答案的贡献.这个答案很难判断哪个答案被接受，因为这两个提交都是有帮助的，但这次我必须把它交给 Mads Hanson.谢谢！

解决方案

• 命名空间继承自父级，并且 元素使用它而没有命名空间前缀，因此 Worksheet、 元素可能并不明显>Data、Row 和 Cell 都绑定到相同的命名空间，并且每个匹配条件都需要 ss:.
  • 您已正确声明 urn:schemas-microsoft-com:office:spreadsheet 命名空间，但没有使用它来匹配所有内容.
• 在 ss:Worksheet 的模板中，您没有应用模板，因此处理停止.我为 ss:Table/ss:Row
添加了一个 apply-templates
• 看起来好像是在创建文本输出
  • 我将输出 method="xml" 更改为 method="text"
  • 我将 <br/> 替换为 
(换行符)

我已修改您的样式表以生成您显示的输出.

<xsl:stylesheet version="1.0"xmlns:xsl="http://www.w3.org/1999/XSL/Transform"xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ><xsl:output method="text"/><xsl:template match="ss:Workbook"><xsl:apply-templates select="ss:Worksheet"/></xsl:模板><xsl:template match="ss:Worksheet"><xsl:value-of select="@ss:Name"/><xsl:text>:&#xA;</xsl:text><xsl:apply-templates select="ss:Table/ss:Row"/></xsl:模板><xsl:template match="ss:Row"><xsl:apply-templates select="ss:Cell"/></xsl:模板><xsl:template match="ss:Cell"><xsl:apply-templates select="ss:Data"/></xsl:模板><xsl:template match="ss:Data"><xsl:value-of select="count(../preceding-sibling::ss:Cell) + 1"/><xsl:text>)</xsl:text><xsl:value-of select="."/><xsl:text>&#xA;</xsl:text></xsl:模板></xsl:stylesheet>

I need to convert a excel spreadsheet saved in xml format using a stylesheet...but I keep getting hung up on the many namespaces ms uses. I need an XSLT stylesheet that allows per element passing (the default seems to pass ALL the text when I use a stylesheet that should otherwise only pass the element templates I am writing) of the input xml.

Here's the input xml:

http://apriority.dyndns.biz/im_cache/u_1/j_2390/2_9/tf/testexcel.xml

:I need to simply extract data from both sheets in columns, like:

Output:

Sheet 1
1) blah, blue , burn, baste, belly, belie, bestow, betrothed, bemoan
2) quack, quagmire, quick, quantum, quant
3) alimony, ashy, amber, absolute, astrology, alabaster, angry
4) cost, curry, candor, cabin, capability, castor, canada

Sheet 2
1) 3
2) 32
3) 322
....etc.

I have trouble with the stylesheet...something to do with the namespaces that MS uses in the input xml ...I seem to be getting in my output ALL of the data even if i only have one element template (say for extracting the rows of only the first sheet)...what stylesheet code will get the output above???

Here is the stylesheet I have so far:

http://apriority.dyndns.biz/im_cache/u_1/j_2390/2_9/tf/learningmap.xsl

File put on non https url so no virus risk plus I am using my real name, google me!

Here is the edited stylesheet after use of suggested code by Mads!

http://apriority.dyndns.biz/im_cache/u_1/j_2390/2_9/tf/learningmap_mod.xsl

I still have a problem where the actual text data is not being extracted in that follow up code, why am I not able to get the text data. I can output the first variable "snid" but all the text variables don't appear in the output even though I am selecting them and the original source xml has stuff in those entries. Any help on this new problem would be greatly appreciated!

Update Feb. 9:

I solved the issue with the mapping failure to the variables. It was a simple xpath mistake I was addressing nodes that didn't exist. There is a one to one mapping between the Cells and their rows so the variables should have extracted as Cell[k].Row[1]...Cell[k+1].Row[1]...etc.

The output transformation occurs as I need it to, thanks for the contributions toward the answer. This one is hard to judge which answer is accepted as both submissions were of aid but I'll have to give it to Mads Hanson this time. Thanks!

解决方案

• Namespaces are inherited from the parents, and the <Worksheet> element uses it without a namespace prefix, so it may not have been apparent that Worksheet, Data, Row, and Cell were all bound to the same namespace and needed ss: for each of the match criteria.
  • You had correctly declared the urn:schemas-microsoft-com:office:spreadsheet namespace, but were not using it to match all of the content.
• In the template for ss:Worksheet you were not applying templates, so processing was stopping. I added an apply-templates for ss:Table/ss:Row
• It looked as if you were creating text output
  • I changed the output method="xml" to method="text"
  • I replaced <br/> with 
 (a newline)

I have modified your stylesheet to produce the output that you had shown.

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" 
         xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
         xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >
    <xsl:output method="text"/>

    <xsl:template match="ss:Workbook">
        <xsl:apply-templates select="ss:Worksheet"/>
    </xsl:template>

    <xsl:template match="ss:Worksheet">
        <xsl:value-of select="@ss:Name"/>
        <xsl:text>:&#xA;</xsl:text>
        <xsl:apply-templates select="ss:Table/ss:Row" />
    </xsl:template>

    <xsl:template match="ss:Row">
        <xsl:apply-templates select="ss:Cell"/>
    </xsl:template>

    <xsl:template match="ss:Cell">
        <xsl:apply-templates select="ss:Data"/>
    </xsl:template>

    <xsl:template match="ss:Data">
        <xsl:value-of select="count(../preceding-sibling::ss:Cell) + 1"/>
        <xsl:text>)</xsl:text>
        <xsl:value-of select="."/>
        <xsl:text>&#xA;</xsl:text>
    </xsl:template>
</xsl:stylesheet>

这篇关于如何使用简单的样式表转换 ms excel xml?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！