将Excel的SUM（）添加到XLST结果页面？ [英] Add Excel's SUM() to XLST result page?

查看：260
本文介绍了将Excel的SUM（）添加到XLST结果页面？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何将SUM（）函数应用于每个小时节点？我将把这个转换的XML保存为Excel，我希望它嵌入SUM（）函数。
经过研究，似乎有这么多的方法，但没有一个真正适用于我的问题。

How to apply SUM() function to every hours node? I will be saving this transfomred XML as Excel where I would like it to have SUM() function embedded. After researching there seems to be so many ways to do it, but none really apply to my problem.

XSL：

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="/">
        <html>
            <body>
                <img src="../images/company_logo.png"></img>
                <p>Company: <xsl:value-of select="companies/company[1]/companyName"/></p>
                <p>Date: <xsl:value-of select="companies/company[1]/startDate"/> to <xsl:value-of select="companies/row[last()]/endDate"/></p>
                <table>
                    <xsl:for-each select="company/row">
                    <tr>
                        <td>ID:</td>
                        <td><xsl:value-of select="serviceID"/></td>
                        <td>Hours:</td>
                        <td><xsl:value-of select="hours"/></td>
                    </tr>
                    </xsl:for-each>
                </table>
            </body>
        </html>
    </xsl:template>
</xsl:stylesheet>

XML 

<company>
    <companyName>Cool Beans</companyName>
    <serviceID>1</serviceID>
    <startDate>01-01-2014 00:00:00</startDate>
    <endDate>01-02-2014 00:00:00</endDate>
    <hours>2</hours>
</company>


推荐答案

假设你有一个这样的文件： p>

Assuming you have a file like this one:

<companies name="My Companies">
    <company>
        <companyName>Cool Beans</companyName>
        <serviceID>1</serviceID>
        <startDate>01-01-2014 00:00:00</startDate>
        <endDate>01-02-2014 00:00:00</endDate>
        <hours>2</hours>
    </company>
    <company>
        <companyName>Hot Beans</companyName>
        <serviceID>2</serviceID>
        <startDate>01-01-2014 00:00:00</startDate>
        <endDate>01-02-2014 00:00:00</endDate>
        <hours>2</hours>
    </company>
    <company>
        <companyName>Evil Beans</companyName>
        <serviceID>3</serviceID>
        <startDate>01-03-2014 00:00:00</startDate>
        <endDate>01-04-2014 00:00:00</endDate>
        <hours>2</hours>
    </company>
</companies>

您必须生成有效的XLS或XSLX文件。我将使用[Office 2003]（ http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats）格式作为示例（XLS）。

You will have to generate a valid XLS or XSLX file. I will use [this Office 2003] (http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats) format as an example (XLS).

您的样式表必须声明您可能需要为电子表格中的属性和元素进行质量的所有前缀和命名空间。您可以简单地在XSLT中声明它们，并将它们复制到您的结果文件中：

Your stylesheet has to declare all the prefixes and namespaces you might need to quality the attributes and elements in the spreadsheet. You can simply declare them in XSLT and they will be copied to your result file:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> ... </xsl:stylesheet>

您需要生成一个 mso-application 应该出现在文档根元素之前的处理指令。我们可以创建一个模板：

You need to generate an mso-application processing instruction which should appear before the document root element. We can create a template for that:

<xsl:template match="/">
    <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
    <Workbook>
        <xsl:apply-templates select="companies" />
    </Workbook>
</xsl:template>

由于它与root匹配，因此此模板将被处理一次。它将调用将处理其余样式表的公司元素。这是一个最小的模板。您也可以将元数据标签，样式等放在那里。

This template will be processed once since it matches root. It will call the companies element which will process the rest of the stylesheet. This is a minimal template. You could place your meta-data tags, styles, etc. there too.

我们也可以将工作表代码放在根模板中。我决定分开它，以避免大模板。因为只有一个公司节点，这一个也将被处理一次。它将创建一个 WorkSheet ，一个表，并调用其他模板来处理单个行和单元格。

We could have placed the worksheet code in the root template as well. I decided to separate it, to avoid big templates. This one will also be processed only once, since there is only one companies node. It will create a WorkSheet, a Table and call some other templates to process the individual rows and cells.

<xsl:template match="companies">  
    <Worksheet ss:Name="{@name}">
        <Table x:FullColumns="1" x:FullRows="1">
            <Row><!-- Header Row -->
                <xsl:apply-templates select="company[1]/*" mode="headers"/>
            </Row>
            <xsl:apply-templates select="company" />
            <Row><!-- Last Row -->
                <Cell ss:Index="4"><Data ss:Type="String">Total:</Data></Cell>
                <Cell ss:Formula="=SUM(R[-{count(company)}]C:R[-1]C)">
                    <Data ss:Type="Number"></Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
</xsl:template>

第一个行将包含标题。由于XML源没有头名称，我们将使用第一个公司的子元素名称创建它们（在单独的模板中完成）。单独的行和单元格也将在单独的模板中处理，但在这里我们创建了最后一个行。我们在第4列中放置一个单元格以打印文本Total：，在 行中，我们插入一个Excel公式，它将先前的 n 行，其中 n 是总计公司节点（ R（-n）C：R（-1）C 将评估为 E2：E4 ，如下所示：从这行 - count（company）

The first Row will contain the headers. Since the XML source has no header names, we will create them using the child element names of the first company (that is done in a separate template). Individual rows and cells will also be processed in separate templates, but here we create the last row. We place a cell in the the 4th column to print the text "Total: ", and in the following row we insert an Excel formula which will sum the previous n rows, where n is the total company nodes (R(-n)C:R(-1)C will evaluate to E2:E4 and reads like: "from this row - count(company) to this row - 1").

其他模板使用为每行和数据单元格创建代码， ss：键入信息（您要求的字段必须为号码）。

The other templates create the code for each row and data cell, using ss:Type information (the fields you are going to sum must be of type Number).

这是完整的样式表：

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

    <xsl:output method="xml" indent="yes" />

    <xsl:template match="/">
        <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
        <Workbook>
            <xsl:apply-templates select="companies" />
        </Workbook>
    </xsl:template>

    <xsl:template match="companies">  
        <Worksheet ss:Name="{@name}">
            <Table x:FullColumns="1" x:FullRows="1">
                <Row><!-- Header Row -->
                    <xsl:apply-templates select="company[1]/*" mode="headers"/>
                </Row>
                <xsl:apply-templates select="company" />
                <Row><!-- Last Row -->
                    <Cell ss:Index="4"><Data ss:Type="String">Total:</Data></Cell>
                    <Cell ss:Formula="=SUM(R[-{count(company)}]C:R[-1]C)">
                        <Data ss:Type="Number"></Data>
                    </Cell>
                </Row>
            </Table>
        </Worksheet>
    </xsl:template>

    <xsl:template match="company[1]/*" mode="headers">
        <Cell>
            <Data ss:Type="String">
                <xsl:value-of select="name()" />
            </Data>
        </Cell>
    </xsl:template>

    <xsl:template match="company">
        <Row>
            <xsl:apply-templates select="*" />
        </Row>
    </xsl:template>

    <xsl:template match="companyName|serviceID|startDate|endDate">
        <Cell>
            <Data ss:Type="String">
                <xsl:value-of select="."/>
            </Data>
        </Cell>
    </xsl:template>

    <xsl:template match="hours">
        <Cell>
            <Data ss:Type="Number">
                <xsl:value-of select="."/>
            </Data>
        </Cell>
    </xsl:template>

</xsl:stylesheet>

将结果保存在带有 .xls extension（ not .xslx ），并在Excel中打开它。您将拥有一个包含名为我的公司的工作表的电子表格，表格的列中的每个字段以最后一行/列的总计时间计算为Excel公式。

Save the result in a file with an .xls extension (not .xslx), and open it in Excel. You will have a spreadsheet with a worksheet named "My Companies", each field in a column of the table and the total of hours in the last line/column calculated as an Excel formula.

这是一个 小提琴 ，其中包含应用于我提供的源的样式表在这个答案的开始（可能类似于你的来源）。以下是结果列表：

Here is a fiddle containing that stylesheet applied to the source I provided at the beginning of this answer (which might resemble your source). Here is a listing of the result:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:msxsl="urn:schemas-microsoft-com:xslt"
          xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
   <Worksheet ss:Name="My Companies">
      <Table x:FullColumns="1" x:FullRows="1">
         <Row>
            <Cell>
               <Data ss:Type="String">companyName</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">serviceID</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">startDate</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">endDate</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">hours</Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss:Type="String">Cool Beans</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">1</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">01-01-2014 00:00:00</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">01-02-2014 00:00:00</Data>
            </Cell>
            <Cell>
               <Data ss:Type="Number">2</Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss:Type="String">Hot Beans</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">2</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">01-01-2014 00:00:00</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">01-02-2014 00:00:00</Data>
            </Cell>
            <Cell>
               <Data ss:Type="Number">2</Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss:Type="String">Evil Beans</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">3</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">01-03-2014 00:00:00</Data>
            </Cell>
            <Cell>
               <Data ss:Type="String">01-04-2014 00:00:00</Data>
            </Cell>
            <Cell>
               <Data ss:Type="Number">2</Data>
            </Cell>
         </Row>
         <Row>
            <Cell ss:Index="4">
               <Data ss:Type="String">Total:</Data>
            </Cell>
            <Cell ss:Formula="=SUM(R[-3]C:R[-1]C)">
               <Data ss:Type="Number"/>
            </Cell>
         </Row>
      </Table>
   </Worksheet>
</Workbook>

这是在Excel 2011 for Mac 2011中加载后的结果文件的屏幕截图：

And this is a screenshot of the result file after loading in Excel for Mac 2011:

点击位置上的字段（5）Col（5）或 E5 ），它计算总结，你应该看到它存储Excel公式，它使用行（5-3）中的数据正确添加 3 Col（5）：行（5-1）Col（5）（ E2：E4 ）：

Clicking on the field at position Row(5)Col(5) or E5) which calculates the totals you should see that it stored the Excel formula which adds the 3 fields correctly using data from Row(5-3)Col(5):Row(5-1)Col(5) (E2:E4):

这篇关于将Excel的SUM（）添加到XLST结果页面？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆