Skip to content

Instantly share code, notes, and snippets.

@grmartin

grmartin/1-convert-trello.xsl

Last active April 22, 2019 13:06
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save grmartin/6bc120135f0e6237e600226826963a8e to your computer and use it in GitHub Desktop.
Save grmartin/6bc120135f0e6237e600226826963a8e to your computer and use it in GitHub Desktop.
Download ZIP
Convert Trello JSON to Excel Document via XSLT
Raw
1-convert-trello.xsl
<?xml version="1.0" encoding="UTF-8"?>
<!-- The output of this XSLT is the MS Excel 2003-2004 XML File Format, simply open in Excel to utilize. -->
<!-- This script was built in Oxygen and was tested on Saxon PE 9.8 -->
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:array="http://www.w3.org/2005/xpath-functions/array"
xmlns:fn="http://www.w3.org/2005/xpath-functions"
xmlns:local="urn:grmartin:locally-scoped-functions:01D92EAAG3HKHKARK5B3E0Z04Z"
version="3.0"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40"
exclude-result-prefixes="array local fn">
<xsl:output indent="true" media-type="text/xml" encoding="UTF-8"/>
<xsl:param name="input" select="'url to json file'"/>
<xsl:variable name="jsonData" select="json-doc($input)"/>
<xsl:template name="xsl:initial-template">
<xsl:variable name="numRows" select="array:size($jsonData?cards)+1"/>
<xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Title>Trello Conversion for <xsl:value-of select="$input"/></Title>
<Author>Glenn Martin</Author>
<LastAuthor>Glenn Martin</LastAuthor>
<Created><xsl:value-of select="fn:current-dateTime()"/></Created>
<HyperlinkBase>https://trello.com/c/</HyperlinkBase>
<Version>16.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>17440</WindowHeight>
<WindowWidth>28040</WindowWidth>
<WindowTopX>5180</WindowTopX>
<WindowTopY>1860</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s67" ss:Name="Hyperlink">
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#0563C1"
ss:Underline="Single"/>
</Style>
<Style ss:ID="s65">
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="14" ss:Color="#FFFFFF"
ss:Bold="1"/>
<Interior ss:Color="#000000" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s66">
<NumberFormat ss:Format="Short Date"/>
</Style>
<Style ss:ID="s70">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="14" ss:Color="#FFFFFF"
ss:Bold="1"/>
<Interior ss:Color="#000000" ss:Pattern="Solid"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="8" ss:ExpandedRowCount="{$numRows}" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="65" ss:DefaultRowHeight="16">
<Column ss:Index="2" ss:AutoFitWidth="0" ss:Width="270"/>
<Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="270"/>
<Column ss:Width="86"/>
<Column ss:Width="124"/>
<Row ss:Height="19" ss:StyleID="s65">
<Cell><Data ss:Type="String">ID</Data></Cell>
<Cell><Data ss:Type="String">Name</Data></Cell>
<Cell><Data ss:Type="String">Status</Data></Cell>
<Cell><Data ss:Type="String">Description</Data></Cell>
<Cell><Data ss:Type="String">Last Updated</Data></Cell>
<Cell><Data ss:Type="String">Link</Data></Cell>
<Cell ss:MergeAcross="1" ss:StyleID="s70"><Data ss:Type="String">Estimate</Data></Cell>
</Row>
<xsl:apply-templates select="$jsonData?cards"/>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
<ActiveCol>5</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>
<xsl:template match=".[. instance of map(*)]">
<xsl:variable name="listId" select="?idList"/>
<xsl:variable name="listName" select="array:head(array:filter($jsonData?lists, function($x) {$x instance of map(*) and $x?id eq $listId}))?name"/>
<Row>
<Cell><Data ss:Type="String"><xsl:value-of select="?id"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="?name"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:comment select="$listId"/><xsl:value-of select="$listName" disable-output-escaping="false"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="local:cdataIt(?desc)" disable-output-escaping="true"/></Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="DateTime"><xsl:value-of select="?dateLastActivity"/></Data></Cell>
<Cell ss:StyleID="s67" ss:HRef="{?url}"
x:HRefScreenTip="URL for {?id}"><Data ss:Type="String">Ticket Link</Data></Cell>
<Cell><Data ss:Type="Number"><!-- To be filled by developers after xform -->0</Data></Cell>
<Cell><Data ss:Type="Number"><!-- To be filled by developers after xform -->0</Data></Cell>
</Row>
</xsl:template>
<xsl:function name="local:cdataIt">
<xsl:param name="inner"/>
<xsl:text disable-output-escaping="true">&lt;</xsl:text>
<xsl:value-of select="concat('![','CDATA[', $inner, ']]')" disable-output-escaping="true" />
<xsl:text disable-output-escaping="true">&gt;</xsl:text>
</xsl:function>
</xsl:stylesheet>
Raw
2-README.md

Convert Trello JSON to Excel Document via XSLT

The intent of this script is to convert a Trello export JSON in to a valid Excel (2003/2004 'Plain' XML Format) document for later review and estimation amongst developers.

LICENSE

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

In jurisdictions that recognize copyright laws, the author or authors
of this software dedicate any and all copyright interest in the
software to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and
successors. We intend this dedication to be an overt act of
relinquishment in perpetuity of all present and future rights to this
software under copyright law.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
OTHER DEALINGS IN THE SOFTWARE.

For more information, please refer to <http://unlicense.org/>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment