Add Excel's SUM() to XLST result page?

Question

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>

The question is not clear: your stylesheet creates an HTML table, not an Excel document. AFAIK, you cannot have an Excel function embedded in an HTML document - though you certainly can have the stylesheet compute the sum using the existing input values and place it in the result as a static value. — michael.hor257k, May 27 '14 at 20:23
Then you should create a native Excel document such as SpreadsheetML. See if this helps to get you started: http://stackoverflow.com/questions/2160159/create-excel-speadsheetml-output-with-xslt — michael.hor257k, May 27 '14 at 21:21
You *can* generate an HTML file containing a table, change its extension to *.xsl* and open it as an Excel spreadsheet, but you aren't really creating a true XLSX file. Excel will generate one. And you won't be able to *embed* Excel formulas. — helderdarocha, May 27 '14 at 23:16

helderdarocha · Accepted Answer · 2014-05-28T17:26:18.500

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>

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:

<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>

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>

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 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>

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").

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).

Here is the full stylesheet:

<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>

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>

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

Result after loading into Excel for Mac 2011

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):

Showing that the Excel Formula was correctly generated

Hmm, I saved the resulting XML file as XLS, but whenever I open it in Excel I get: `Problems came up in the following areas during load: Workbook setting`. It also points to a log file in `C:/Users/admin/AppData/Local/Mircosof/Windows/Temporary Internet Files/Content.MSO/25287435.log`, but I was unable to located Temporary Internet Files folder. — user3681280, May 28 '14 at 16:36
See if you can load the Excel XML file from this site: http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats. I used it as a reference to generate your example (but I removed some meta-data which I considered that might not be relevant for this example, and left it out since it worked). — helderdarocha, May 28 '14 at 17:09
I should mention that I did not use your actual source, since you did not provided (you only provided an excerpt). I used the XML source I provided at the beginning of this answer. Try to generate the spreadsheet with it. If your actual example is different, you will certainly need to make adjustments to it in order to generate the spreadsheet correctly. — helderdarocha, May 28 '14 at 17:11
Strange. I have the same data in XML, except I don't have the `name` attribute for the root node. Also I have few more nodes inside each `company` node. Do I need to list them all in XLS file? — user3681280, May 28 '14 at 19:47
I'm using the name for the Worksheet name, which might be required. I am not sure. You can add a static name to the worksheet: replace `` with `` — helderdarocha, May 28 '14 at 19:49
Yeap. Worksheet name was a required setting. Now the `xls` file opens fine. Though 1st row lists all node names even though I only needed 4 out of 10, and the formula missed the column of value by +1 column giving me a 0 total, but I'll figure the rest out. Thank you. — user3681280, May 28 '14 at 20:53
I got the column names copying the tag names from the first group of elements. It would be best to have a column headers section in your XML source, and the stylesheet could read them from there. — helderdarocha, May 28 '14 at 20:56

Add Excel's SUM() to XLST result page?

1 Answers1

Linked