Kaitis
Reputation: 638
XSLT v1.0 transform grouped with multiple values to transform Filemaker Pro XML export to Excel spreadsheet
I am new to xslt transforms and I am having some difficulty getting the result I want. I am exporting a set of records from Filemaker Pro as XML with xslt transform to make a formatted ".xls" file.
This is the export order I am using from Filemaker Pro:
GOALS:
1. I want each distinct SupplierName to have its own spreadsheet
- Group the records within each spreadsheet by the
InvoiceNo
EDIT: Using Martin's suggestions
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:fmp="http://www.filemaker.com/fmpxmlresult" version="1.0"
exclude-result-prefixes="fmp set"
xmlns:set="http://exslt.org/sets"
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">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:key name="group" match="fmp:ROW" use="fmp:COL[4]/fmp:DATA"/>
<xsl:key name="nested-group" match="fmp:ROW" use="concat(fmp:COL[4]/fmp:DATA,'+',fmp:COL[2]/fmp:DATA)"/>
<xsl:template match="fmp:FMPXMLRESULT">
<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">
<LastAuthor/>
<Created/>
<Version>11.9999</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>8190</WindowHeight>
<WindowWidth>16380</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>0</WindowTopY>
<TabRatio>505</TabRatio>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font x:Family="Swiss"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="Header">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="0"/>
<Borders/>
<Interior ss:Color="#666699" ss:Pattern="Solid"/>
<Font ss:Color="#FFFFFF" ss:Size="26" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1" ss:Underline="Single"/>
</Style>
<Style ss:ID="ColNames">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Font ss:Size="12" ss:Color="#FFFFFF" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#6C4E91" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="Data">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>
<Borders />
<Font ss:Size="14" ss:Color="#090A0E" ss:FontName="Verdana" x:Family="Swiss"/>
<Interior ss:Color="#D8E2E1" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="DataRight">
<Alignment ss:Horizontal="Right" ss:Vertical="Bottom" ss:WrapText="1"/>
<Borders />
<Font ss:Size="14" ss:Color="#090A0E" ss:FontName="Verdana" x:Family="Swiss"/>
<Interior ss:Color="#D8E2E1" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="DataLeft">
<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
<Borders />
<Font ss:Size="14" ss:Color="#090A0E" ss:FontName="Verdana" x:Family="Swiss"/>
<Interior ss:Color="#D8E2E1" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="DCurrency">
<Alignment ss:Horizontal="Right" ss:Vertical="Bottom" ss:WrapText="1"/>
<Borders />
<Font ss:Color="#090A0E" ss:FontName="Verdana" x:Family="Swiss"/>
<Interior ss:Color="#D8E2E1" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="Group">
<Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="0"/>
<Borders />
<Font ss:Size="16" ss:Color="#FFFFFF" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1" ss:Underline="Single"/>
<Interior ss:Color="#FFA500" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="GroupRight">
<Alignment ss:Horizontal="Right" ss:Vertical="Center" ss:WrapText="0"/>
<Borders />
<Font ss:Size="16" ss:Color="#FFFFFF" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#FFA500" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="GroupLeft">
<Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="0"/>
<Borders />
<Font ss:Size="16" ss:Color="#FFFFFF" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#FFA500" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="GroupData">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="0"/>
<Borders />
<Font ss:Size="16" ss:Color="#FFFFFF" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#FFA500" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="Group2Right">
<Alignment ss:Horizontal="Right" ss:Vertical="Center" ss:WrapText="0"/>
<Borders />
<Font ss:Size="16" ss:Color="#FFFFFF" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1" ss:Italic="1"/>
<Interior ss:Color="#666699" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="Group2Data">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="0"/>
<Borders />
<Font ss:Size="16" ss:Color="#FFFFFF" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1" ss:Italic="1"/>
<Interior ss:Color="#666699" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="Group2">
<Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="0"/>
<Borders />
<Font ss:Size="14" ss:Color="#FFFFFF" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1" ss:Italic="1"/>
<Interior ss:Color="#666699" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="GroupSubTotalRight">
<Alignment ss:Horizontal="Right" ss:Vertical="Center" ss:WrapText="0"/>
<Borders />
<Font ss:Size="12" ss:Color="#000000" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#64A4E5" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="GroupSubTotal">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="0"/>
<Borders />
<Font ss:Size="12" ss:Color="#000000" ss:FontName="Verdana" x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#64A4E5" ss:Pattern="Solid"/>
</Style>
</Styles>
<xsl:for-each select="fmp:RESULTSET/fmp:ROW[generate-id() = generate-id(key('group', fmp:COL[4]/fmp:DATA)[1])]">
<xsl:variable name="sheetName" select="fmp:COL[4]/fmp:DATA"/>
<Worksheet ss:Name="{$sheetName}">
<Table ss:ExpandedColumnCount="15" x:FullColumns="1" x:FullRows="1" >
<Column ss:AutoFitWidth="0" ss:Width="300"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="120"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Row ss:AutoFitHeight="0" ss:Height="60">
<Cell ss:StyleID="Header">
</Cell>
<Cell ss:StyleID="Header">
</Cell>
<Cell ss:StyleID="Header">
<Data ss:Type="String">Purchases</Data>
</Cell>
<Cell ss:StyleID="Header">
</Cell>
<Cell ss:StyleID="Header">
</Cell>
<Cell ss:StyleID="Header">
</Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="40" >
<Cell ss:StyleID="ColNames">
<Data ss:Type="String">ITEM</Data>
</Cell>
<Cell ss:StyleID="ColNames">
<Data ss:Type="String">BARCODE</Data>
</Cell>
<Cell ss:StyleID="ColNames">
<Data ss:Type="String">Qty</Data>
</Cell>
<Cell ss:StyleID="ColNames">
<Data ss:Type="String">DISCOUNT</Data>
</Cell>
<Cell ss:StyleID="ColNames">
<Data ss:Type="String">VAT</Data>
</Cell>
<Cell ss:StyleID="ColNames">
<Data ss:Type="String">TOTAL COST</Data>
</Cell>
</Row>
<xsl:for-each select="key('group', fmp:COL[4]/fmp:DATA)[generate-id() = generate-id(key('nested-group', concat(fmp:COL[4]/fmp:DATA,'+',fmp:COL[2]/fmp:DATA))[1])]">
<xsl:variable name="invoice" select="fmp:COL[2]/fmp:DATA"/>
<xsl:variable name="type" select="fmp:COL[3]/fmp:DATA"/>
<xsl:variable name="receivedDate" select="fmp:COL[3]/fmp:DATA"/>
<Row ss:AutoFitHeight="0" ss:Height="26" >
<Cell ss:StyleID="GroupLeft">
<Data ss:Type="String">
<xsl:value-of select="concat('Reveived On:',$receivedDate)"/>
</Data>
</Cell>
<Cell ss:StyleID="GroupLeft">
<Data ss:Type="String">
<xsl:value-of select="concat('Invoice #:',$invoice)"/>
</Data>
</Cell>
<Cell ss:StyleID="GroupLeft">
</Cell>
<Cell ss:StyleID="GroupLeft">
</Cell>
<Cell ss:StyleID="GroupRight">
<Data ss:Type="String">Type:</Data>
</Cell>
<Cell ss:StyleID="GroupLeft">
<Data ss:Type="String">
<xsl:value-of select="$type"/>
</Data>
</Cell>
</Row>
<xsl:for-each select="key('group', fmp:COL[4]/fmp:DATA)[generate-id() = generate-id(key('nested-group', concat(fmp:COL[4]/fmp:DATA,'+',fmp:COL[2]/fmp:DATA))[1])]">
<xsl:variable name="item" select="fmp:COL[10]/fmp:DATA"/>
<xsl:variable name="qty" select="fmp:COL[11]/fmp:DATA"/>
<xsl:variable name="cost" select="fmp:COL[12]/fmp:DATA"/>
<xsl:variable name="discount" select="fmp:COL[13]/fmp:DATA"/>
<xsl:variable name="VAT" select="fmp:COL[14]/fmp:DATA"/>
<xsl:variable name="itemTotal" select="fmp:COL[15]/fmp:DATA"/>
<Row ss:AutoFitHeight="0" ss:Height="22">
<Cell ss:StyleID="Data">
<Data ss:Type="String">
<xsl:value-of select="$item"/>
</Data>
</Cell>
<Cell ss:StyleID="Data">
<Data ss:Type="String">
<xsl:value-of select="$qty"/>
</Data>
</Cell>
<Cell ss:StyleID="Data">
<Data ss:Type="String">
<xsl:value-of select="$cost"/>
</Data>
</Cell>
<Cell ss:StyleID="Data">
<Data ss:Type="String" >
<xsl:value-of select="$discount"/>
</Data>
</Cell>
<Cell ss:StyleID="Data">
<Data ss:Type="String">
<xsl:value-of select="$VAT"/>
</Data>
</Cell>
<Cell ss:StyleID="Data">
<Data ss:Type="String">
<xsl:value-of select="$itemTotal"/>
</Data>
</Cell>
</Row>
</xsl:for-each>
<xsl:variable name="Subtotal" select="fmp:COL[5]/fmp:DATA"/>
<xsl:variable name="Discount" select="fmp:COL[6]/fmp:DATA"/>
<xsl:variable name="NetCost" select="fmp:COL[7]/fmp:DATA"/>
<xsl:variable name="TotalVAT" select="fmp:COL[8]/fmp:DATA"/>
<xsl:variable name="InvoiceTotal" select="fmp:COL[9]/fmp:DATA"/>
<Row ss:AutoFitHeight="0" ss:Height="26">
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell ss:StyleID="GroupSubTotalRight">
<Data ss:Type="String">Subtotal:</Data>
</Cell>
<Cell ss:StyleID="GroupSubTotal">
<Data ss:Type="String" >
<xsl:value-of select="$Subtotal"/>
</Data>
</Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="26">
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell ss:StyleID="GroupSubTotalRight">
<Data ss:Type="String">Discount:</Data>
</Cell>
<Cell ss:StyleID="GroupSubTotal">
<Data ss:Type="String" >
<xsl:value-of select="$Discount"/>
</Data>
</Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="26">
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell ss:StyleID="GroupSubTotalRight">
<Data ss:Type="String">Net Cost:</Data>
</Cell>
<Cell ss:StyleID="GroupSubTotal">
<Data ss:Type="String" >
<xsl:value-of select="$NetCost"/>
</Data>
</Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="26">
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell ss:StyleID="GroupSubTotalRight">
<Data ss:Type="String">VAT:</Data>
</Cell>
<Cell ss:StyleID="GroupSubTotal">
<Data ss:Type="String" >
<xsl:value-of select="$TotalVAT"/>
</Data>
</Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="26">
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell> </Cell>
<Cell ss:StyleID="GroupSubTotalRight">
<Data ss:Type="String">Total:</Data>
</Cell>
<Cell ss:StyleID="GroupSubTotal">
<Data ss:Type="String" >
<xsl:value-of select="$InvoiceTotal"/>
</Data>
</Cell>
</Row>
</xsl:for-each>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Layout x:Orientation="Landscape"/>
<Header x:Margin="0.1701388888888889"/>
<Footer x:Margin="0.15972222222222221" x:Data=" "/>
<PageMargins x:Bottom="0.16944444444444443" x:Left="0.2" x:Right="0.19027777777777777" x:Top="0.4604166666666667"/>
</PageSetup>
<FitToPage/>
<Print>
<FitHeight>0</FitHeight>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>300</HorizontalResolution>
<VerticalResolution>300</VerticalResolution>
</Print>
<Zoom>85</Zoom>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</xsl:for-each>
</Workbook>
</xsl:template>
EDIT: Here is the result in Excel
I would greatly appreciate any assistance!
Upvotes: 0
Views: 177
Answers (1)
Martin Honnen
Reputation: 167696
Try with two keys
<xsl:key name="group" match="fmp:ROW" use="fmp:COL[4]/fmp:DATA"/>
<xsl:key name="nested-group" match="fmp:ROW" use="concat(fmp:COL[4]/fmp:DATA,'+',fmp:COL[2]/fmp:DATA)"/>
and then change
<xsl:for-each select="set:distinct(fmp:RESULTSET/fmp:ROW/fmp:COL[4]/fmp:DATA)">
to
<xsl:for-each select="fmp:RESULTSET/fmp:ROW[generate-id() = generate-id(key('group', fmp:COL[4]/fmp:DATA)[1])]">
and the inner
<xsl:for-each select="key('group', .)">
to
<xsl:for-each select="key('group', fmp:COL[4]/fmp:DATA)[generate-id() = generate-id(key('nested-group', concat(fmp:COL[4]/fmp:DATA,'+',fmp:COL[2]/fmp:DATA))[1])]">
You will have to adapt some paths as the context nodes are fmp:ROW elements.
Upvotes: 1