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:
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).
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:
You need to generate an
mso-application
processing instruction which should appear before the document root element. We can create a template for that: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.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 aWorkSheet
, aTable
and call some other templates to process the individual rows and cells.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 firstcompany
(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 totalcompany
nodes (R(-n)C:R(-1)C
will evaluate toE2:E4
and reads like: "from this row - count(company) to this row - 1").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 typeNumber
).Here is the full stylesheet:
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:
And this is a screenshot of the result file after loading in Excel for Mac 2011:
Clicking on the field at position
Row(5)Col(5)
orE5
) which calculates the totals you should see that it stored the Excel formula which adds the3
fields correctly using data fromRow(5-3)Col(5):Row(5-1)Col(5)
(E2:E4
):