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

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

问题描述

如何将 SUM() 函数应用于每个 hours 节点?我将把这个转换后的 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>

推荐答案

假设你有一个这样的文件:

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_formatsa>) 格式作为示例(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 匹配.它将调用 companies 元素，该元素将处理样式表的其余部分.这是一个最小的模板.您也可以将元数据标签、样式等放在那里.

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.

我们也可以将工作表代码放在根模板中.我决定将它分开，以避免大模板.由于只有一个 companys 节点，因此该节点也将只处理一次.它将创建一个 WorkSheet、一个 Table 并调用一些其他模板来处理各个行和单元格.

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>

第一个 Row 将包含标题.由于 XML 源没有标题名称，我们将使用第一个 company(在单独的模板中完成)的子元素 names 创建它们.单独的行和单元格也将在单独的模板中处理，但在这里我们创建最后行.我们在第 4 列中放置一个单元格以打印文本总计:"，并在 以下 行中插入一个 Excel 公式，该公式将对前 n 行求和，其中 n 是 company 节点的总数(R(-n)C:R(-1)C 将计算为 E2:E4 并读作:从 这一行 - count(company) 到 这一行 - 1").

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:Type 信息(您要求和的字段必须是Number 类型)为每一行和数据单元格创建代码.

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 扩展名的文件中(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.

这是一个 fiddle 包含应用于我提供的源代码的样式表此答案的开头(可能类似于您的来源).以下是结果列表:

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 for Mac 2011 中加载后结果文件的屏幕截图:

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

单击位置 Row(5)Col(5) 或 E5) 处的字段，计算总数，您应该看到它存储了 Excel 公式，该公式添加了3 字段正确使用来自 Row(5-3)Col(5):Row(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屋！

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