Note: this is NOT the recommended method for using Google Sheets data with the XSLT Processor plugin. Export your sheets as .csv files and use [xslt_select_csv/] instead. For comparison, the code samples here use the same data as presented in the csv shortcode documentation, but exported as Sheet1.html rather than Sheet1.csv.
Sometimes, HTML is the closest you can get to a structured version of some dataset. Sometimes, as a developer, you need to ‘scrape’ data. Unfortunately, HTML is a display format, rather than an exchange format like XML or CSV. Although it looks and acts like XML much of the time, HTML also allows for <script> and <style> tags, for instance. Browsers seamlessly switch between these conflicting code syntaxes; the general-purpose xsltproc, which powers the XSLT Processor plugin, does not.
In this How To, the page exported from Google is valid HTML, of course, but it is invalid XML. We’ll see how to get around that problem, and then develop a stylesheet to dynamically pull row and column information from the page’s main <table>.
Getting Started
- Open or create a new Google Sheet document
- Export using
File >> Download >> Web Page (.html) - Unzip the archive and locate the file
Sheet1.html - Upload the file to a known location on your WP site
Table data – case-study-gsheets/Sheet1.html
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
|
1
|
ID | NAME | TITLE | MONEY | DATE | DESCRIPTION |
|
2
|
1001 | name-one | Name One | $111.00 | 1/1/2001 | this is some longer content for “Name One” |
|
3
|
1002 | name-two | Name Two | $222.00 | 2/2/1002 | this is some longer content for name two with multiple lines |
|
4
|
1003 | name-three | Name Three | $333.00 | 3/3/2003 | this is some html content for <b>name three</b> |
|
5
|
1004 | name-four | Name Four | $444.00 | 4/4/2004 | this is some bolded content for name four |
|
6
|
1005 | name-five | Name Five | $555.00 | 5/5/2005 | this is some longer content, for name five |
Version 0
To confirm the validity of the input, before applying stylesheets, we can pass just the input file location to [xslt_transform_xml/]. The default XSL stylesheet reads the data and, if there are no errors, prints the source using HTML entities.
As shown in the first output sample below, Sheet1.html is not valid as XML. As supplied, the <style> info creates "Opening and ending tag mismatch..." errors.
The second sample, on the other hand, shows the result after adding the tidy attribute to the shortcode. This option ‘repairs’ the HTML if needed, and then returns just the page’s <body> content as XHTML.
[xslt_transform_xml tidy xml="case-study-gsheets/Sheet1.html" /]Output v0 – tidy=”no”
ERROR : DomDocument->load(xml):
----------------------------------------
<style type="text/css">.ritz .waffle a { color: inherit; }.ritz .waffle .s0{background-color:#ffffff;text-align:left;font-weight:bold;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s3{background-color:#ffffff;text-align:left;color:#1f1f1f;font-family:'docs-Google Sans',Arial;font-size:9pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s1{background-color:#ffffff;text-align:right;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s2{background-color:#ffffff;text-align:left;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}</style><div class="ritz grid-container" dir="ltr"><table class="waffle" cellspacing="0" cellpadding="0"><thead><tr><th class="row-header freezebar-origin-ltr"></th><th id="0C0" style="width:100px;" class="column-headers-background">A</th><th id="0C1" style="width:100px;" class="column-headers-background">B</th><th id="0C2" style="width:100px;" class="column-headers-background">C</th><th id="0C3" style="width:100px;" class="column-headers-background">D</th><th id="0C4" style="width:100px;" class="column-headers-background">E</th><th id="0C5" style="width:344px;" class="column-headers-background">F</th></tr></thead><tbody><tr style="height: 20px"><th id="0R0" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">1</div></th><td class="s0" dir="ltr">ID</td><td class="s0" dir="ltr">NAME</td><td class="s0" dir="ltr">TITLE</td><td class="s0" dir="ltr">MONEY</td><td class="s0" dir="ltr">DATE</td><td class="s0" dir="ltr">DESCRIPTION</td></tr><tr style="height: 20px"><th id="0R1" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">2</div></th><td class="s1" dir="ltr">1001</td><td class="s2" dir="ltr">name-one</td><td class="s2" dir="ltr">Name One</td><td class="s1" dir="ltr">$111.00</td><td class="s1" dir="ltr">1/1/2001</td><td class="s2" dir="ltr">this is some longer content for "Name One"</td></tr><tr style="height: 20px"><th id="0R2" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">3</div></th><td class="s1" dir="ltr">1002</td><td class="s2" dir="ltr">name-two</td><td class="s2" dir="ltr">Name Two</td><td class="s1" dir="ltr">$222.00</td><td class="s1" dir="ltr">2/2/1002</td><td class="s2" dir="ltr">this is some longer content for name two<br>with multiple lines</td></tr><tr style="height: 20px"><th id="0R3" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">4</div></th><td class="s1" dir="ltr">1003</td><td class="s2" dir="ltr">name-three</td><td class="s2" dir="ltr">Name Three</td><td class="s1" dir="ltr">$333.00</td><td class="s1" dir="ltr">3/3/2003</td><td class="s3" dir="ltr">this is some html content for <b>name three</b></td></tr><tr style="height: 20px"><th id="0R4" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">5</div></th><td class="s1" dir="ltr">1004</td><td class="s2" dir="ltr">name-four</td><td class="s2" dir="ltr">Name Four</td><td class="s1" dir="ltr">$444.00</td><td class="s1" dir="ltr">4/4/2004</td><td class="s2" dir="ltr">this is some bolded content for <span style="font-weight:bold;">name four</span></td></tr><tr style="height: 20px"><th id="0R5" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">6</div></th><td class="s1" dir="ltr">1005</td><td class="s2" dir="ltr">name-five</td><td class="s2" dir="ltr">Name Five</td><td class="s1" dir="ltr">$555.00</td><td class="s1" dir="ltr">5/5/2005</td><td class="s3" dir="ltr">this is some longer content, for name five</td></tr></tbody></table></div>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
Fatal Error 76: Opening and ending tag mismatch: br line 2 and td
Line: 2
Column: 2723
File: /srv/plugins.tenandtwo.com/htdocs/wp-content/uploads/case-study-gsheets/Sheet1.html
----------------------------------------
----------------------------------------
<style type="text/css">.ritz .waffle a { color: inherit; }.ritz .waffle .s0{background-color:#ffffff;text-align:left;font-weight:bold;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s3{background-color:#ffffff;text-align:left;color:#1f1f1f;font-family:'docs-Google Sans',Arial;font-size:9pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s1{background-color:#ffffff;text-align:right;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s2{background-color:#ffffff;text-align:left;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}</style><div class="ritz grid-container" dir="ltr"><table class="waffle" cellspacing="0" cellpadding="0"><thead><tr><th class="row-header freezebar-origin-ltr"></th><th id="0C0" style="width:100px;" class="column-headers-background">A</th><th id="0C1" style="width:100px;" class="column-headers-background">B</th><th id="0C2" style="width:100px;" class="column-headers-background">C</th><th id="0C3" style="width:100px;" class="column-headers-background">D</th><th id="0C4" style="width:100px;" class="column-headers-background">E</th><th id="0C5" style="width:344px;" class="column-headers-background">F</th></tr></thead><tbody><tr style="height: 20px"><th id="0R0" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">1</div></th><td class="s0" dir="ltr">ID</td><td class="s0" dir="ltr">NAME</td><td class="s0" dir="ltr">TITLE</td><td class="s0" dir="ltr">MONEY</td><td class="s0" dir="ltr">DATE</td><td class="s0" dir="ltr">DESCRIPTION</td></tr><tr style="height: 20px"><th id="0R1" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">2</div></th><td class="s1" dir="ltr">1001</td><td class="s2" dir="ltr">name-one</td><td class="s2" dir="ltr">Name One</td><td class="s1" dir="ltr">$111.00</td><td class="s1" dir="ltr">1/1/2001</td><td class="s2" dir="ltr">this is some longer content for "Name One"</td></tr><tr style="height: 20px"><th id="0R2" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">3</div></th><td class="s1" dir="ltr">1002</td><td class="s2" dir="ltr">name-two</td><td class="s2" dir="ltr">Name Two</td><td class="s1" dir="ltr">$222.00</td><td class="s1" dir="ltr">2/2/1002</td><td class="s2" dir="ltr">this is some longer content for name two<br>with multiple lines</td></tr><tr style="height: 20px"><th id="0R3" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">4</div></th><td class="s1" dir="ltr">1003</td><td class="s2" dir="ltr">name-three</td><td class="s2" dir="ltr">Name Three</td><td class="s1" dir="ltr">$333.00</td><td class="s1" dir="ltr">3/3/2003</td><td class="s3" dir="ltr">this is some html content for <b>name three</b></td></tr><tr style="height: 20px"><th id="0R4" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">5</div></th><td class="s1" dir="ltr">1004</td><td class="s2" dir="ltr">name-four</td><td class="s2" dir="ltr">Name Four</td><td class="s1" dir="ltr">$444.00</td><td class="s1" dir="ltr">4/4/2004</td><td class="s2" dir="ltr">this is some bolded content for <span style="font-weight:bold;">name four</span></td></tr><tr style="height: 20px"><th id="0R5" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">6</div></th><td class="s1" dir="ltr">1005</td><td class="s2" dir="ltr">name-five</td><td class="s2" dir="ltr">Name Five</td><td class="s1" dir="ltr">$555.00</td><td class="s1" dir="ltr">5/5/2005</td><td class="s3" dir="ltr">this is some longer content, for name five</td></tr></tbody></table></div>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
Fatal Error 76: Opening and ending tag mismatch: td line 2 and tr
Line: 2
Column: 2729
File: /srv/plugins.tenandtwo.com/htdocs/wp-content/uploads/case-study-gsheets/Sheet1.html
----------------------------------------
----------------------------------------
<style type="text/css">.ritz .waffle a { color: inherit; }.ritz .waffle .s0{background-color:#ffffff;text-align:left;font-weight:bold;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s3{background-color:#ffffff;text-align:left;color:#1f1f1f;font-family:'docs-Google Sans',Arial;font-size:9pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s1{background-color:#ffffff;text-align:right;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s2{background-color:#ffffff;text-align:left;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}</style><div class="ritz grid-container" dir="ltr"><table class="waffle" cellspacing="0" cellpadding="0"><thead><tr><th class="row-header freezebar-origin-ltr"></th><th id="0C0" style="width:100px;" class="column-headers-background">A</th><th id="0C1" style="width:100px;" class="column-headers-background">B</th><th id="0C2" style="width:100px;" class="column-headers-background">C</th><th id="0C3" style="width:100px;" class="column-headers-background">D</th><th id="0C4" style="width:100px;" class="column-headers-background">E</th><th id="0C5" style="width:344px;" class="column-headers-background">F</th></tr></thead><tbody><tr style="height: 20px"><th id="0R0" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">1</div></th><td class="s0" dir="ltr">ID</td><td class="s0" dir="ltr">NAME</td><td class="s0" dir="ltr">TITLE</td><td class="s0" dir="ltr">MONEY</td><td class="s0" dir="ltr">DATE</td><td class="s0" dir="ltr">DESCRIPTION</td></tr><tr style="height: 20px"><th id="0R1" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">2</div></th><td class="s1" dir="ltr">1001</td><td class="s2" dir="ltr">name-one</td><td class="s2" dir="ltr">Name One</td><td class="s1" dir="ltr">$111.00</td><td class="s1" dir="ltr">1/1/2001</td><td class="s2" dir="ltr">this is some longer content for "Name One"</td></tr><tr style="height: 20px"><th id="0R2" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">3</div></th><td class="s1" dir="ltr">1002</td><td class="s2" dir="ltr">name-two</td><td class="s2" dir="ltr">Name Two</td><td class="s1" dir="ltr">$222.00</td><td class="s1" dir="ltr">2/2/1002</td><td class="s2" dir="ltr">this is some longer content for name two<br>with multiple lines</td></tr><tr style="height: 20px"><th id="0R3" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">4</div></th><td class="s1" dir="ltr">1003</td><td class="s2" dir="ltr">name-three</td><td class="s2" dir="ltr">Name Three</td><td class="s1" dir="ltr">$333.00</td><td class="s1" dir="ltr">3/3/2003</td><td class="s3" dir="ltr">this is some html content for <b>name three</b></td></tr><tr style="height: 20px"><th id="0R4" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">5</div></th><td class="s1" dir="ltr">1004</td><td class="s2" dir="ltr">name-four</td><td class="s2" dir="ltr">Name Four</td><td class="s1" dir="ltr">$444.00</td><td class="s1" dir="ltr">4/4/2004</td><td class="s2" dir="ltr">this is some bolded content for <span style="font-weight:bold;">name four</span></td></tr><tr style="height: 20px"><th id="0R5" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">6</div></th><td class="s1" dir="ltr">1005</td><td class="s2" dir="ltr">name-five</td><td class="s2" dir="ltr">Name Five</td><td class="s1" dir="ltr">$555.00</td><td class="s1" dir="ltr">5/5/2005</td><td class="s3" dir="ltr">this is some longer content, for name five</td></tr></tbody></table></div>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
Fatal Error 76: Opening and ending tag mismatch: tr line 2 and tbody
Line: 2
Column: 4072
File: /srv/plugins.tenandtwo.com/htdocs/wp-content/uploads/case-study-gsheets/Sheet1.html
----------------------------------------
----------------------------------------
<style type="text/css">.ritz .waffle a { color: inherit; }.ritz .waffle .s0{background-color:#ffffff;text-align:left;font-weight:bold;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s3{background-color:#ffffff;text-align:left;color:#1f1f1f;font-family:'docs-Google Sans',Arial;font-size:9pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s1{background-color:#ffffff;text-align:right;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s2{background-color:#ffffff;text-align:left;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}</style><div class="ritz grid-container" dir="ltr"><table class="waffle" cellspacing="0" cellpadding="0"><thead><tr><th class="row-header freezebar-origin-ltr"></th><th id="0C0" style="width:100px;" class="column-headers-background">A</th><th id="0C1" style="width:100px;" class="column-headers-background">B</th><th id="0C2" style="width:100px;" class="column-headers-background">C</th><th id="0C3" style="width:100px;" class="column-headers-background">D</th><th id="0C4" style="width:100px;" class="column-headers-background">E</th><th id="0C5" style="width:344px;" class="column-headers-background">F</th></tr></thead><tbody><tr style="height: 20px"><th id="0R0" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">1</div></th><td class="s0" dir="ltr">ID</td><td class="s0" dir="ltr">NAME</td><td class="s0" dir="ltr">TITLE</td><td class="s0" dir="ltr">MONEY</td><td class="s0" dir="ltr">DATE</td><td class="s0" dir="ltr">DESCRIPTION</td></tr><tr style="height: 20px"><th id="0R1" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">2</div></th><td class="s1" dir="ltr">1001</td><td class="s2" dir="ltr">name-one</td><td class="s2" dir="ltr">Name One</td><td class="s1" dir="ltr">$111.00</td><td class="s1" dir="ltr">1/1/2001</td><td class="s2" dir="ltr">this is some longer content for "Name One"</td></tr><tr style="height: 20px"><th id="0R2" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">3</div></th><td class="s1" dir="ltr">1002</td><td class="s2" dir="ltr">name-two</td><td class="s2" dir="ltr">Name Two</td><td class="s1" dir="ltr">$222.00</td><td class="s1" dir="ltr">2/2/1002</td><td class="s2" dir="ltr">this is some longer content for name two<br>with multiple lines</td></tr><tr style="height: 20px"><th id="0R3" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">4</div></th><td class="s1" dir="ltr">1003</td><td class="s2" dir="ltr">name-three</td><td class="s2" dir="ltr">Name Three</td><td class="s1" dir="ltr">$333.00</td><td class="s1" dir="ltr">3/3/2003</td><td class="s3" dir="ltr">this is some html content for <b>name three</b></td></tr><tr style="height: 20px"><th id="0R4" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">5</div></th><td class="s1" dir="ltr">1004</td><td class="s2" dir="ltr">name-four</td><td class="s2" dir="ltr">Name Four</td><td class="s1" dir="ltr">$444.00</td><td class="s1" dir="ltr">4/4/2004</td><td class="s2" dir="ltr">this is some bolded content for <span style="font-weight:bold;">name four</span></td></tr><tr style="height: 20px"><th id="0R5" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">6</div></th><td class="s1" dir="ltr">1005</td><td class="s2" dir="ltr">name-five</td><td class="s2" dir="ltr">Name Five</td><td class="s1" dir="ltr">$555.00</td><td class="s1" dir="ltr">5/5/2005</td><td class="s3" dir="ltr">this is some longer content, for name five</td></tr></tbody></table></div>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
Fatal Error 76: Opening and ending tag mismatch: tbody line 2 and table
Line: 2
Column: 4081
File: /srv/plugins.tenandtwo.com/htdocs/wp-content/uploads/case-study-gsheets/Sheet1.html
----------------------------------------
----------------------------------------
<style type="text/css">.ritz .waffle a { color: inherit; }.ritz .waffle .s0{background-color:#ffffff;text-align:left;font-weight:bold;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s3{background-color:#ffffff;text-align:left;color:#1f1f1f;font-family:'docs-Google Sans',Arial;font-size:9pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s1{background-color:#ffffff;text-align:right;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}.ritz .waffle .s2{background-color:#ffffff;text-align:left;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}</style><div class="ritz grid-container" dir="ltr"><table class="waffle" cellspacing="0" cellpadding="0"><thead><tr><th class="row-header freezebar-origin-ltr"></th><th id="0C0" style="width:100px;" class="column-headers-background">A</th><th id="0C1" style="width:100px;" class="column-headers-background">B</th><th id="0C2" style="width:100px;" class="column-headers-background">C</th><th id="0C3" style="width:100px;" class="column-headers-background">D</th><th id="0C4" style="width:100px;" class="column-headers-background">E</th><th id="0C5" style="width:344px;" class="column-headers-background">F</th></tr></thead><tbody><tr style="height: 20px"><th id="0R0" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">1</div></th><td class="s0" dir="ltr">ID</td><td class="s0" dir="ltr">NAME</td><td class="s0" dir="ltr">TITLE</td><td class="s0" dir="ltr">MONEY</td><td class="s0" dir="ltr">DATE</td><td class="s0" dir="ltr">DESCRIPTION</td></tr><tr style="height: 20px"><th id="0R1" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">2</div></th><td class="s1" dir="ltr">1001</td><td class="s2" dir="ltr">name-one</td><td class="s2" dir="ltr">Name One</td><td class="s1" dir="ltr">$111.00</td><td class="s1" dir="ltr">1/1/2001</td><td class="s2" dir="ltr">this is some longer content for "Name One"</td></tr><tr style="height: 20px"><th id="0R2" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">3</div></th><td class="s1" dir="ltr">1002</td><td class="s2" dir="ltr">name-two</td><td class="s2" dir="ltr">Name Two</td><td class="s1" dir="ltr">$222.00</td><td class="s1" dir="ltr">2/2/1002</td><td class="s2" dir="ltr">this is some longer content for name two<br>with multiple lines</td></tr><tr style="height: 20px"><th id="0R3" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">4</div></th><td class="s1" dir="ltr">1003</td><td class="s2" dir="ltr">name-three</td><td class="s2" dir="ltr">Name Three</td><td class="s1" dir="ltr">$333.00</td><td class="s1" dir="ltr">3/3/2003</td><td class="s3" dir="ltr">this is some html content for <b>name three</b></td></tr><tr style="height: 20px"><th id="0R4" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">5</div></th><td class="s1" dir="ltr">1004</td><td class="s2" dir="ltr">name-four</td><td class="s2" dir="ltr">Name Four</td><td class="s1" dir="ltr">$444.00</td><td class="s1" dir="ltr">4/4/2004</td><td class="s2" dir="ltr">this is some bolded content for <span style="font-weight:bold;">name four</span></td></tr><tr style="height: 20px"><th id="0R5" style="height: 20px;" class="row-headers-background"><div class="row-header-wrapper" style="line-height: 20px">6</div></th><td class="s1" dir="ltr">1005</td><td class="s2" dir="ltr">name-five</td><td class="s2" dir="ltr">Name Five</td><td class="s1" dir="ltr">$555.00</td><td class="s1" dir="ltr">5/5/2005</td><td class="s3" dir="ltr">this is some longer content, for name five</td></tr></tbody></table></div>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
Fatal Error 76: Opening and ending tag mismatch: table line 2 and div
Line: 2
Column: 4087
File: /srv/plugins.tenandtwo.com/htdocs/wp-content/uploads/case-study-gsheets/Sheet1.html
----------------------------------------
----------------------------------------
Fatal Error 77: Premature end of data in tag div line 2
Line: 3
Column: 1
File: /srv/plugins.tenandtwo.com/htdocs/wp-content/uploads/case-study-gsheets/Sheet1.html
----------------------------------------
----------------------------------------
Fatal Error 77: Premature end of data in tag link line 1
Line: 3
Column: 1
File: /srv/plugins.tenandtwo.com/htdocs/wp-content/uploads/case-study-gsheets/Sheet1.html
----------------------------------------
----------------------------------------
Fatal Error 77: Premature end of data in tag meta line 1
Line: 3
Column: 1
File: /srv/plugins.tenandtwo.com/htdocs/wp-content/uploads/case-study-gsheets/Sheet1.html
----------------------------------------
Output v0 – tidy=”yes” or tidy=”html”
<div class="ritz grid-container" dir="ltr">
<table class="waffle" cellspacing="0" cellpadding="0">
<thead>
<tr>
<th class="row-header freezebar-origin-ltr"/>
<th id="0C0" style="width:100px;" class="column-headers-background">A</th>
<th id="0C1" style="width:100px;" class="column-headers-background">B</th>
<th id="0C2" style="width:100px;" class="column-headers-background">C</th>
<th id="0C3" style="width:100px;" class="column-headers-background">D</th>
<th id="0C4" style="width:100px;" class="column-headers-background">E</th>
<th id="0C5" style="width:344px;" class="column-headers-background">F</th>
</tr>
</thead>
<tbody>
<tr style="height: 20px">
<th id="0R0" style="height: 20px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 20px">1</div>
</th>
<td class="s0" dir="ltr">ID</td>
<td class="s0" dir="ltr">NAME</td>
<td class="s0" dir="ltr">TITLE</td>
<td class="s0" dir="ltr">MONEY</td>
<td class="s0" dir="ltr">DATE</td>
<td class="s0" dir="ltr">DESCRIPTION</td>
</tr>
<tr style="height: 20px">
<th id="0R1" style="height: 20px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 20px">2</div>
</th>
<td class="s1" dir="ltr">1001</td>
<td class="s2" dir="ltr">name-one</td>
<td class="s2" dir="ltr">Name One</td>
<td class="s1" dir="ltr">$111.00</td>
<td class="s1" dir="ltr">1/1/2001</td>
<td class="s2" dir="ltr">this is some longer content for "Name One"</td>
</tr>
<tr style="height: 20px">
<th id="0R2" style="height: 20px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 20px">3</div>
</th>
<td class="s1" dir="ltr">1002</td>
<td class="s2" dir="ltr">name-two</td>
<td class="s2" dir="ltr">Name Two</td>
<td class="s1" dir="ltr">$222.00</td>
<td class="s1" dir="ltr">2/2/1002</td>
<td class="s2" dir="ltr">
this is some longer content for name two
<br/>
with multiple lines
</td>
</tr>
<tr style="height: 20px">
<th id="0R3" style="height: 20px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 20px">4</div>
</th>
<td class="s1" dir="ltr">1003</td>
<td class="s2" dir="ltr">name-three</td>
<td class="s2" dir="ltr">Name Three</td>
<td class="s1" dir="ltr">$333.00</td>
<td class="s1" dir="ltr">3/3/2003</td>
<td class="s3" dir="ltr">this is some html content for <b>name three</b></td>
</tr>
<tr style="height: 20px">
<th id="0R4" style="height: 20px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 20px">5</div>
</th>
<td class="s1" dir="ltr">1004</td>
<td class="s2" dir="ltr">name-four</td>
<td class="s2" dir="ltr">Name Four</td>
<td class="s1" dir="ltr">$444.00</td>
<td class="s1" dir="ltr">4/4/2004</td>
<td class="s2" dir="ltr">
this is some bolded content for
<span style="font-weight:bold;">name four</span>
</td>
</tr>
<tr style="height: 20px">
<th id="0R5" style="height: 20px;" class="row-headers-background">
<div class="row-header-wrapper" style="line-height: 20px">6</div>
</th>
<td class="s1" dir="ltr">1005</td>
<td class="s2" dir="ltr">name-five</td>
<td class="s2" dir="ltr">Name Five</td>
<td class="s1" dir="ltr">$555.00</td>
<td class="s1" dir="ltr">5/5/2005</td>
<td class="s3" dir="ltr">this is some longer content, for name five</td>
</tr>
</tbody>
</table>
</div>
Version 1
Now we’re ready to add our own XSL stylesheet to read and transform the data. Let’s start by adding two parameters: row and col. These ‘extra’ shortcode attributes are passed to the stylesheet, so we can use them to select cells within the spreadsheet.
This first iteration of the stylesheet accepts one or more, comma-separated numbers for each parameter. An empty or zero (0) value matches all rows and/or columns, while positive values match rows and/or columns at the requested positions.
Note the xsl:include of the XSLT Processor’s string.xsl stylesheet. This allows the string-to-nodeset template to be used in the main template – match="/" – to split the incoming col and row values. This creates two nodeset variables –columns and rows– which are passed to the row and column templates – match="tr" and match="td" – where they are used to match requested cell positions.
[xslt_transform_xml tidy xml="case-study-gsheets/Sheet1.html" xsl="case-study-gsheets/gsheets-v1.xsl" row="1,3" col="1,2,6" /]Output v1:
| ID | NAME | DESCRIPTION |
| 1002 | name-two | this is some longer content for name two |
XSL file – “case-study-gsheets/gsheets-v1.xsl”
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:exslt="http://exslt.org/common" version="1.0" exclude-result-prefixes="exslt">
<xsl:output method="html" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:include href="../../plugins/tenandtwo-xslt-processor/xsl/string.xsl"/>
<!-- params
col : comma-separated numbers
row : comma-separated numbers
-->
<xsl:param name="col"/>
<xsl:param name="row"/>
<!-- main -->
<xsl:template match="/">
<!-- final column numbers (nodeset) from col (str) -->
<xsl:variable name="columns">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$col"/>
<xsl:with-param name="delimiter" select="','"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- final row numbers (nodeset) from row (str) -->
<xsl:variable name="rows">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$row"/>
<xsl:with-param name="delimiter" select="','"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- output -->
<xsl:element name="table">
<xsl:attribute name="class">table</xsl:attribute>
<xsl:apply-templates select="//tbody/tr">
<xsl:with-param name="columns" select="$columns"/>
<xsl:with-param name="rows" select="$rows"/>
</xsl:apply-templates>
</xsl:element>
<!-- debug -->
<!--
<br/> - col: <xsl:copy-of select="$col" />
<br/> - row: <xsl:copy-of select="$row" />
<br/> - columns: <xsl:copy-of select="$columns" />
<br/> - rows: <xsl:copy-of select="$rows" />
-->
</xsl:template>
<!-- output table rows
-->
<xsl:template match="tr">
<xsl:param name="columns"/>
<xsl:param name="rows"/>
<xsl:variable name="row_cnt"><xsl:value-of select="count(exslt:node-set($rows)//NODE[. > 0])"/></xsl:variable>
<xsl:variable name="row_num"><xsl:value-of select="position()"/></xsl:variable>
<xsl:if test="$row_cnt = 0 or exslt:node-set($rows)//NODE[. = $row_num]">
<xsl:element name="tr">
<xsl:attribute name="id"><xsl:value-of select="concat('row',$row_num)"/></xsl:attribute>
<xsl:apply-templates select="td">
<xsl:with-param name="columns" select="$columns"/>
</xsl:apply-templates>
</xsl:element>
</xsl:if>
</xsl:template>
<!-- output table columns
-->
<xsl:template match="td">
<xsl:param name="columns"/>
<xsl:variable name="col_cnt"><xsl:value-of select="count(exslt:node-set($columns)//NODE[. > 0])"/></xsl:variable>
<xsl:variable name="col_num"><xsl:value-of select="position()"/></xsl:variable>
<xsl:if test="$col_cnt = 0 or exslt:node-set($columns)//NODE[. = $col_num]">
<xsl:element name="td">
<xsl:attribute name="id"><xsl:value-of select="concat('col',$col_num)"/></xsl:attribute>
<xsl:value-of select="text()" disable-output-escaping="yes"/>
</xsl:element>
</xsl:if>
</xsl:template>
</xsl:stylesheet><!-- end gsheets-v2.xsl -->
Version 2
This second iteration of the stylesheet extends the col attribute to accept letters and labels as column specifiers. As in Version 1, the string-to-nodeset template is used to split the incoming parameter. Then, each of the col_values is validated and, if necessary, converted to a number. If the new key_row parameter is specified, the labels from that row can be used match columns. Alternately, if a column letter is specified – A,B…ZZ – then the new alpha-to-num template is used.
[xslt_transform_xml tidy xml="case-study-gsheets/Sheet1.html" xsl="case-study-gsheets/gsheets-v2.xsl" key_row="1" row="1,3" col="1,B,DESCRIPTION" /]Output v2:
| ID | NAME | DESCRIPTION |
| 1002 | name-two | this is some longer content for name two |
XSL file – “case-study-gsheets/gsheets-v2.xsl”
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:exslt="http://exslt.org/common" version="1.0" exclude-result-prefixes="exslt">
<xsl:output method="html" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:include href="../../plugins/tenandtwo-xslt-processor/xsl/string.xsl"/>
<!-- params
key_row : int, row number for column labels
col : comma-separated numbers, letters, or labels
row : comma-separated numbers
-->
<xsl:param name="key_row"/>
<xsl:param name="col"/>
<xsl:param name="row"/>
<xsl:variable name="alphabet">ABCDEFGHIJKLMNOPQRSTUVWXYZ</xsl:variable>
<!-- main -->
<xsl:template match="/">
<!-- key_row_values (nodeset) from key_row (int) -->
<xsl:variable name="key_row_values">
<xsl:if test="string(number($key_row)) != 'NaN' and $key_row > 0">
<xsl:copy-of select="//tbody/tr[position() = $key_row]/td"/>
</xsl:if>
</xsl:variable>
<!-- col_values (nodeset) from col (str) -->
<xsl:variable name="col_values">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$col"/>
<xsl:with-param name="delimiter" select="','"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- row_values (nodeset) from row (str) -->
<xsl:variable name="row_values">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$row"/>
<xsl:with-param name="delimiter" select="','"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- col_num (str) from col_values (nodeset) -->
<xsl:variable name="col_num">
<xsl:for-each select="exslt:node-set($col_values)//NODE[string-length(.) > 0]">
<xsl:variable name="col_val"><xsl:value-of select="text()"/></xsl:variable>
<xsl:choose>
<xsl:when test="string-length($col_val) > 0 and exslt:node-set($key_row_values)/td[. = $col_val]">
<xsl:for-each select="exslt:node-set($key_row_values)/td[. = $col_val]">
<xsl:text>|</xsl:text>
<xsl:value-of select="count(preceding-sibling::td) + 1"/>
</xsl:for-each>
</xsl:when>
<xsl:when test="contains($alphabet,substring($col_val,1,1))">
<xsl:text>|</xsl:text>
<xsl:call-template name="alpha-to-num">
<xsl:with-param name="alpha" select="$col_val"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="string(number($col_val)) != 'NaN' and $col_val > 0">
<xsl:value-of select="concat('|',$col_val)"/>
</xsl:when>
<!-- <xsl:otherwise>|0</xsl:otherwise> -->
</xsl:choose>
</xsl:for-each>
</xsl:variable>
<!-- row_num (str) from row_values (nodeset) -->
<xsl:variable name="row_num">
<xsl:for-each select="exslt:node-set($row_values)//NODE[string-length(.) > 0]">
<xsl:variable name="row_val"><xsl:value-of select="text()"/></xsl:variable>
<xsl:if test="string(number($row_val)) != 'NaN' and $row_val > 0">
<xsl:value-of select="concat('|',$row_val)"/>
</xsl:if>
</xsl:for-each>
</xsl:variable>
<!-- final column numbers (nodeset) from col_num (str) -->
<xsl:variable name="columns">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$col_num"/>
<xsl:with-param name="delimiter" select="'|'"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- final row numbers (nodeset) from row_num (str) -->
<xsl:variable name="rows">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$row_num"/>
<xsl:with-param name="delimiter" select="'|'"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- output -->
<xsl:element name="table">
<xsl:attribute name="class">table</xsl:attribute>
<xsl:apply-templates select="//tbody/tr">
<xsl:with-param name="columns" select="$columns"/>
<xsl:with-param name="rows" select="$rows"/>
</xsl:apply-templates>
</xsl:element>
<!-- debug -->
<!--
<br/> - key_row: <xsl:copy-of select="$key_row" />
<br/> - col: <xsl:copy-of select="$col" />
<br/> - row: <xsl:copy-of select="$row" />
<br/> - key_row_values: <xsl:copy-of select="$key_row_values" />
<br/> - col_values: <xsl:copy-of select="$col_values" />
<br/> - row_values: <xsl:copy-of select="$row_values" />
<br/> - col_num: <xsl:copy-of select="$col_num" />
<br/> - row_num: <xsl:copy-of select="$row_num" />
<br/> - columns: <xsl:copy-of select="$columns" />
<br/> - rows: <xsl:copy-of select="$rows" />
-->
</xsl:template>
<!-- output table rows
-->
<xsl:template match="tr">
<xsl:param name="columns"/>
<xsl:param name="rows"/>
<xsl:variable name="row_cnt"><xsl:value-of select="count(exslt:node-set($rows)//NODE[. > 0])"/></xsl:variable>
<xsl:variable name="row_num"><xsl:value-of select="position()"/></xsl:variable>
<xsl:if test="$row_cnt = 0 or exslt:node-set($rows)//NODE[. = $row_num]">
<xsl:element name="tr">
<xsl:attribute name="id"><xsl:value-of select="concat('row',$row_num)"/></xsl:attribute>
<xsl:apply-templates select="td">
<xsl:with-param name="columns" select="$columns"/>
</xsl:apply-templates>
</xsl:element>
</xsl:if>
</xsl:template>
<!-- output table columns
-->
<xsl:template match="td">
<xsl:param name="columns"/>
<xsl:variable name="col_cnt"><xsl:value-of select="count(exslt:node-set($columns)//NODE[. > 0])"/></xsl:variable>
<xsl:variable name="col_num"><xsl:value-of select="position()"/></xsl:variable>
<xsl:if test="$col_cnt = 0 or exslt:node-set($columns)//NODE[. = $col_num]">
<xsl:element name="td">
<xsl:attribute name="id"><xsl:value-of select="concat('col',$col_num)"/></xsl:attribute>
<xsl:value-of select="text()" disable-output-escaping="yes"/>
</xsl:element>
</xsl:if>
</xsl:template>
<!-- convert 'A'=1, 'B'=2 ... 'ZZ'=702
-->
<xsl:template name="alpha-to-num">
<xsl:param name="alpha"/>
<xsl:variable name="pos_one">
<xsl:value-of select="string-length(substring-before($alphabet,substring($alpha,1,1))) + 1"/>
</xsl:variable>
<xsl:choose>
<xsl:when test="string-length($alpha) > 1">
<xsl:variable name="pos_two">
<xsl:value-of select="string-length(substring-before($alphabet,substring($alpha,2,1))) + 1"/>
</xsl:variable>
<xsl:value-of select="($pos_one * 26) + $pos_two"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$pos_one"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet><!-- end gsheets-v4.xsl -->
Version 3
With a flexible method of choosing columns in place, this third iteration of the stylesheet implements a similar method for choosing rows by adding two (2) parameters – key_col and key. The first specifies a single column in the data and, similar to the col parameter, and accepts either a number, a letter, or a label. The second new parameter – key – specifies one or more values to match in key_col.
Note this new method of selecting rows does not replace the first. In this sample, the row attribute is used to include the spreadsheet’s labels in the result.
[xslt_transform_xml tidy xml="case-study-gsheets/Sheet1.html" xsl="case-study-gsheets/gsheets-v3.xsl" key_row="1" row="1" col="ID,NAME,DESCRIPTION" key_col="ID" key="1002,1005" /]Output v3:
| ID | NAME | DESCRIPTION |
| 1002 | name-two | this is some longer content for name two |
| 1005 | name-five | this is some longer content, for name five |
XSL file – “case-study-gsheets/gsheets-v3.xsl”
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:exslt="http://exslt.org/common" version="1.0" exclude-result-prefixes="exslt">
<xsl:output method="html" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:include href="../../plugins/tenandtwo-xslt-processor/xsl/string.xsl"/>
<!-- params
key_row : int, row number for column labels
col : comma-separated numbers, letters, or labels
key_col : number, letter or label
key : comma-separated search values
row : comma-separated numbers
-->
<xsl:param name="key_row"/>
<xsl:param name="col"/>
<xsl:param name="key_col"/>
<xsl:param name="key"/>
<xsl:param name="row"/>
<xsl:variable name="alphabet">ABCDEFGHIJKLMNOPQRSTUVWXYZ</xsl:variable>
<!-- main -->
<xsl:template match="/">
<!-- key_row_values (nodeset) from key_row (int) -->
<xsl:variable name="key_row_values">
<xsl:if test="string(number($key_row)) != 'NaN' and $key_row > 0">
<xsl:copy-of select="//tbody/tr[position() = $key_row]/td"/>
</xsl:if>
</xsl:variable>
<!-- col_values (nodeset) from col (str) -->
<xsl:variable name="col_values">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$col"/>
<xsl:with-param name="delimiter" select="','"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- row_values (nodeset) from row (str) -->
<xsl:variable name="row_values">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$row"/>
<xsl:with-param name="delimiter" select="','"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- key_values (nodeset) from key (str) -->
<xsl:variable name="key_values">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$key"/>
<xsl:with-param name="delimiter" select="','"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- key_col_num (int) from key_col (string) -->
<xsl:variable name="key_col_num">
<xsl:choose>
<xsl:when test="string-length($key_col) > 0 and exslt:node-set($key_row_values)/td[. = $key_col]">
<xsl:for-each select="exslt:node-set($key_row_values)/td[. = $key_col][1]">
<xsl:value-of select="count(preceding-sibling::td) + 1"/>
</xsl:for-each>
</xsl:when>
<xsl:when test="contains($alphabet,substring($key_col,1,1))">
<xsl:call-template name="alpha-to-num">
<xsl:with-param name="alpha" select="$key_col"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="string(number($key_col)) != 'NaN' and $key_col > 0">
<xsl:value-of select="$key_col"/>
</xsl:when>
<xsl:when test="string-length($key) > 0">
<xsl:text>1</xsl:text>
</xsl:when>
<xsl:otherwise>0</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<!-- key_col_values (nodeset) from key_col_num (int)-->
<xsl:variable name="key_col_values">
<xsl:if test="$key_col_num > 0">
<xsl:copy-of select="//tbody/tr/td[position() = $key_col_num]"/>
</xsl:if>
</xsl:variable>
<!-- col_num (str) from col_values (nodeset) -->
<xsl:variable name="col_num">
<xsl:for-each select="exslt:node-set($col_values)//NODE[string-length(.) > 0]">
<xsl:variable name="col_val"><xsl:value-of select="text()"/></xsl:variable>
<xsl:choose>
<xsl:when test="string-length($col_val) > 0 and exslt:node-set($key_row_values)/td[. = $col_val]">
<xsl:for-each select="exslt:node-set($key_row_values)/td[. = $col_val]">
<xsl:text>|</xsl:text>
<xsl:value-of select="count(preceding-sibling::td) + 1"/>
</xsl:for-each>
</xsl:when>
<xsl:when test="contains($alphabet,substring($col_val,1,1))">
<xsl:text>|</xsl:text>
<xsl:call-template name="alpha-to-num">
<xsl:with-param name="alpha" select="$col_val"/>
</xsl:call-template>
</xsl:when>
<xsl:when test="string(number($col_val)) != 'NaN' and $col_val > 0">
<xsl:value-of select="concat('|',$col_val)"/>
</xsl:when>
<!-- <xsl:otherwise>|0</xsl:otherwise> -->
</xsl:choose>
</xsl:for-each>
</xsl:variable>
<!-- row_num (str) from key_values (nodeset) or row_values (nodeset) -->
<xsl:variable name="row_num">
<xsl:if test="$key_col_num > 0 and exslt:node-set($key_values)//NODE[string-length(.) > 0]">
<xsl:text>|keyed-search</xsl:text>
<xsl:for-each select="exslt:node-set($key_values)//NODE[string-length(.) > 0]">
<xsl:variable name="key_val"><xsl:value-of select="text()"/></xsl:variable>
<xsl:choose>
<xsl:when test="$key_col_num > 0 and string-length($key_val) > 0">
<xsl:for-each select="exslt:node-set($key_col_values)/td[. = $key_val]">
<xsl:text>|</xsl:text>
<xsl:value-of select="count(preceding-sibling::td) + 1"/>
</xsl:for-each>
</xsl:when>
<xsl:when test="string(number($key_val)) != 'NaN' and $key_val > 0">
<xsl:value-of select="concat('|',$key_val)"/>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</xsl:if>
<xsl:for-each select="exslt:node-set($row_values)//NODE[string-length(.) > 0]">
<xsl:variable name="row_val"><xsl:value-of select="text()"/></xsl:variable>
<xsl:if test="string(number($row_val)) != 'NaN' and $row_val > 0">
<xsl:value-of select="concat('|',$row_val)"/>
</xsl:if>
</xsl:for-each>
</xsl:variable>
<!-- final column numbers (nodeset) from col_num (str) -->
<xsl:variable name="columns">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$col_num"/>
<xsl:with-param name="delimiter" select="'|'"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- final row numbers (nodeset) from row_num (str) -->
<xsl:variable name="rows">
<xsl:call-template name="string-to-nodeset">
<xsl:with-param name="value" select="$row_num"/>
<xsl:with-param name="delimiter" select="'|'"/>
<xsl:with-param name="nodename" select="'NODE'"/>
</xsl:call-template>
</xsl:variable>
<!-- output -->
<xsl:element name="table">
<xsl:attribute name="class">table</xsl:attribute>
<xsl:apply-templates select="//tbody/tr">
<xsl:with-param name="columns" select="$columns"/>
<xsl:with-param name="rows" select="$rows"/>
</xsl:apply-templates>
</xsl:element>
<!-- debug -->
<!--
<br/> - key_row: <xsl:copy-of select="$key_row" />
<br/> - col: <xsl:copy-of select="$col" />
<br/> - key_col: <xsl:copy-of select="$key_col" />
<br/> - key: <xsl:copy-of select="$key" />
<br/> - row: <xsl:copy-of select="$row" />
<br/> - key_row_values: <xsl:copy-of select="$key_row_values" />
<br/> - col_values: <xsl:copy-of select="$col_values" />
<br/> - row_values: <xsl:copy-of select="$row_values" />
<br/> - key_values: <xsl:copy-of select="$key_values" />
<br/> - key_col_num: <xsl:copy-of select="$key_col_num" />
<br/> - key_col_values: <xsl:copy-of select="$key_col_values" />
<br/> - col_num: <xsl:copy-of select="$col_num" />
<br/> - row_num: <xsl:copy-of select="$row_num" />
<br/> - columns: <xsl:copy-of select="$columns" />
<br/> - rows: <xsl:copy-of select="$rows" />
-->
</xsl:template>
<!-- output table rows
-->
<xsl:template match="tr">
<xsl:param name="columns"/>
<xsl:param name="rows"/>
<xsl:variable name="keyed"><xsl:value-of select="count(exslt:node-set($rows)//NODE[. = 'keyed-search'])"/></xsl:variable>
<xsl:variable name="row_cnt"><xsl:value-of select="count(exslt:node-set($rows)//NODE[. > 0])"/></xsl:variable>
<xsl:variable name="row_num"><xsl:value-of select="position()"/></xsl:variable>
<xsl:if test="($keyed = 0 and $row_cnt = 0) or exslt:node-set($rows)//NODE[. = $row_num]">
<xsl:element name="tr">
<xsl:attribute name="id"><xsl:value-of select="concat('row',$row_num)"/></xsl:attribute>
<xsl:apply-templates select="td">
<xsl:with-param name="columns" select="$columns"/>
</xsl:apply-templates>
</xsl:element>
</xsl:if>
</xsl:template>
<!-- output table columns
-->
<xsl:template match="td">
<xsl:param name="columns"/>
<xsl:variable name="col_cnt"><xsl:value-of select="count(exslt:node-set($columns)//NODE[. > 0])"/></xsl:variable>
<xsl:variable name="col_num"><xsl:value-of select="position()"/></xsl:variable>
<xsl:if test="$col_cnt = 0 or exslt:node-set($columns)//NODE[. = $col_num]">
<xsl:element name="td">
<xsl:attribute name="id"><xsl:value-of select="concat('col',$col_num)"/></xsl:attribute>
<xsl:value-of select="text()" disable-output-escaping="yes"/>
</xsl:element>
</xsl:if>
</xsl:template>
<!-- convert 'A'=1, 'B'=2 ... 'ZZ'=702
-->
<xsl:template name="alpha-to-num">
<xsl:param name="alpha"/>
<xsl:variable name="pos_one">
<xsl:value-of select="string-length(substring-before($alphabet,substring($alpha,1,1))) + 1"/>
</xsl:variable>
<xsl:choose>
<xsl:when test="string-length($alpha) > 1">
<xsl:variable name="pos_two">
<xsl:value-of select="string-length(substring-before($alphabet,substring($alpha,2,1))) + 1"/>
</xsl:variable>
<xsl:value-of select="($pos_one * 26) + $pos_two"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$pos_one"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet><!-- end gsheets-v5.xsl -->