‘xslt_select_xml’ is a helper function. It reads XML and returns a subset of the data, based on a supplied XPATH expression. Used to pre-filter data passed to ‘xslt_transform_xml’, ‘xslt_select_xml’ allows for more generic, reuseable stylesheets.
[xslt_select_csv/] is a helper function for converting CSV data to XML. Once reformatted, the result can be output directly to the page as an HTML <table>, or the result can be passed to [xslt_transform_xml/] for further processing.
There are two (2) options for specifying the source CSV when using the csv attribute:
| local file: | csv="/path/to/local/spreadsheet.csv" |
| remote file: | csv="http://abc.de/remote/spreadsheet.csv" |
Source data can also be input directly in the body of the shortcode. This results in two (2) usage patterns:
[xslt_select_csv csv="{file|url}" /][xslt_select_csv]{my,csv,data}[/xslt_select_csv]
Of course, either pattern may be used in combination with [xslt_transform_xml/].
[xslt_transform_xml][xslt_select_csv/][/xslt_transform_xml]
When no additional parameters are given, [xslt_select_csv/] returns a <table class="table"> element containing every row –<tr>– and every column –<td>– from the CSV. If you are using the output directly as HTML, styles can be applied by setting the table’s CSS class name(s) in the class shortcode attribute:
[xslt_select_csv class="{css-classnames}" /]
CSV parameters
Three parameters control the process of reading CSV data: separator, enclosure, and escape. The default values describe a standard comma-separated file, with fields enclosed by quotes, and with quotes optionally escaped by backslash. To read a standard tab-separated file, setting separator="\t" is the only required change.
- CSV:
[xslt_select_csv separator="," enclosure="\"" escape="\\" /] - TSV:
[xslt_select_csv separator="\t" enclosure="\"" escape="\\" /]
For more info on these options, see https://www.php.net/manual/en/function.fgetcsv.php
Column parameters
The col shortcode attribute is used to specify which columns are returned by [xslt_select_csv/]. By itself, col accepts either column numbers or column letters as values, and it accepts multiple values separated by commas. Alternatively, if key_row is specified, column labels matching that row can be used in col.
[xslt_select_csv col="{num|letter}+" /][xslt_select_csv key_row="{num}" col="{label}+" /]
The following (3) shortcodes are equivalent when the output is used as HTML. If key_row is defined, as in the third example, then label attributes are added in the XML to each <td> element.
[xslt_select_csv csv="case-study-gsheets/Sheet1.csv" col="1,2,3" /]
[xslt_select_csv csv="case-study-gsheets/Sheet1.csv" col="A,B,C" /]
[xslt_select_csv csv="case-study-gsheets/Sheet1.csv" key_row="1" col="ID,NAME,TITLE" /]Output:
| ID | NAME | TITLE |
| 1001 | name-one | Name One |
| 1002 | name-two | Name Two |
| 1003 | name-three | Name Three |
| 1004 | name-four | Name Four |
| 1005 | name-five | Name Five |
Output - XML source
<table class="table" rows="6" cols="6">
<tr row="1" cols="6">
<td row="1" col="1" label="ID">ID</td>
<td row="1" col="2" label="NAME">NAME</td>
<td row="1" col="3" label="TITLE">TITLE</td>
</tr>
<tr row="2" cols="6">
<td row="2" col="1" label="ID">1001</td>
<td row="2" col="2" label="NAME">name-one</td>
<td row="2" col="3" label="TITLE">Name One</td>
</tr>
<tr row="3" cols="6">
<td row="3" col="1" label="ID">1002</td>
<td row="3" col="2" label="NAME">name-two</td>
<td row="3" col="3" label="TITLE">Name Two</td>
</tr>
<tr row="4" cols="6">
<td row="4" col="1" label="ID">1003</td>
<td row="4" col="2" label="NAME">name-three</td>
<td row="4" col="3" label="TITLE">Name Three</td>
</tr>
<tr row="5" cols="6">
<td row="5" col="1" label="ID">1004</td>
<td row="5" col="2" label="NAME">name-four</td>
<td row="5" col="3" label="TITLE">Name Four</td>
</tr>
<tr row="6" cols="6">
<td row="6" col="1" label="ID">1005</td>
<td row="6" col="2" label="NAME">name-five</td>
<td row="6" col="3" label="TITLE">Name Five</td>
</tr>
</table>
Row parameters
The row shortcode attribute is used to include specific rows in the result. Only numbers are accepted as values, and multiple values should be separated by commas.
The second, dynamic method of adding rows to the result uses a combination of two (2) attributes: key_col and key. The first specifies a column to search, and the second specifies a value to match in that column. The key_col attribute accepts numbers, letters, or a label - like colabove - but only one value is allowed. The key attribute accepts one or more comma-separated search values.
[xslt_select_csv row="{num}+" /][xslt_select_csv key_col="{num|letter}" key="{val}+" /][xslt_select_csv key_row="{num}" key_col="{label}" key="{val}+" /]
The following (3) shortcodes are equivalent when output as HTML. As before, when key_row is defined, label attributes are added in the XML to each <td> element.
[xslt_select_csv csv="case-study-gsheets/Sheet1.csv" row="1,5,6" /]
[xslt_select_csv csv="case-study-gsheets/Sheet1.csv" row="1" key_col="1" key="1004,1005" /]
[xslt_select_csv csv="case-study-gsheets/Sheet1.csv" row="1" key_row="1" key_col="ID" key="1004,1005" /]Output:
| ID | NAME | TITLE | MONEY | DATE | DESCRIPTION |
| 1004 | name-four | Name Four | $444.00 | 4/4/2004 | this is some longer content for name four |
| 1005 | name-five | Name Five | $555.00 | 5/5/2005 | this is some longer content, for name five |
Output - XML source
<table class="table" rows="6" cols="6">
<tr row="1" cols="6">
<td row="1" col="1" label="ID">ID</td>
<td row="1" col="2" label="NAME">NAME</td>
<td row="1" col="3" label="TITLE">TITLE</td>
<td row="1" col="4" label="MONEY">MONEY</td>
<td row="1" col="5" label="DATE">DATE</td>
<td row="1" col="6" label="DESCRIPTION">DESCRIPTION</td>
</tr>
<tr row="5" cols="6">
<td row="5" col="1" label="ID">1004</td>
<td row="5" col="2" label="NAME">name-four</td>
<td row="5" col="3" label="TITLE">Name Four</td>
<td row="5" col="4" label="MONEY">$444.00</td>
<td row="5" col="5" label="DATE">4/4/2004</td>
<td row="5" col="6" label="DESCRIPTION">this is some longer content for name four</td>
</tr>
<tr row="6" cols="6">
<td row="6" col="1" label="ID">1005</td>
<td row="6" col="2" label="NAME">name-five</td>
<td row="6" col="3" label="TITLE">Name Five</td>
<td row="6" col="4" label="MONEY">$555.00</td>
<td row="6" col="5" label="DATE">5/5/2005</td>
<td row="6" col="6" label="DESCRIPTION">this is some longer content, for name five</td>
</tr>
</table>
Pivot sample
The previous examples, for column and row selection, have shown the output of [xslt_select_csv/] when used directly, without further changes. Here, as an example of combined usage with [xslt_transform_xml/], we'll apply an XSL stylesheet to "pivot" the previous XML result, and layout the data in a new table.
For convenience, we're passing key_row to the pivot stylesheet so we can easily identify and modify label values in the output. A more specialized version of the stylesheet might use the label attributes to vary the output of other cells, reformatting DATE values, for instance, or converting newlines to breaks in the DESCRIPTION column.
[xslt_transform_xml xsl="csv-pivot-xsl" key_row="1"]
[xslt_select_csv csv="case-study-gsheets/Sheet1.csv" row="1" key_row="1" key_col="ID" key="1004,1005" /]
[/xslt_transform_xml]Output:
| ID | 1004 | 1005 |
| NAME | name-four | name-five |
| TITLE | Name Four | Name Five |
| MONEY | $444.00 | $555.00 |
| DATE | 4/4/2004 | 5/5/2005 |
| DESCRIPTION | this is some longer content for name four | this is some longer content, for name five |
csv-pivot-xsl
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html" version="1.0" encoding="UTF-8" indent="yes"/>
<!-- params -->
<xsl:param name="key_row" select="0"/>
<!-- compound key for data -->
<xsl:key name="csv-col" match="//td" use="concat(@row,'|',@col)"/>
<!-- main -->
<xsl:template match="/">
<xsl:element name="table">
<xsl:attribute name="class"><xsl:value-of select="//table/@class"/></xsl:attribute>
<xsl:for-each select="//tr[1]/td/@col">
<xsl:variable name="col"><xsl:value-of select="."/></xsl:variable>
<xsl:element name="tr">
<xsl:for-each select="//tr/@row">
<xsl:variable name="row"><xsl:value-of select="."/></xsl:variable>
<xsl:variable name="col-label">
<xsl:value-of select="key('csv-col', concat($row,'|',$col))/@label"/>
</xsl:variable>
<xsl:variable name="col-value">
<xsl:value-of select="key('csv-col', concat($row,'|',$col))/text()"/>
</xsl:variable>
<xsl:variable name="col-class">
<xsl:text>align-top</xsl:text>
<xsl:if test="$row = $key_row"> <!-- xtra classes for labels -->
<xsl:text> text-end fw-bold</xsl:text>
</xsl:if>
</xsl:variable>
<xsl:element name="td">
<xsl:attribute name="class">
<xsl:value-of select="$col-class"/>
</xsl:attribute>
<xsl:value-of select="$col-value" disable-output-escaping="yes"/>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>
</xsl:stylesheet>