Writing Columns of data versus Rows in Excel from XML with XSLT

Question

I'm trying to take the XML structure below (scrubbed so please forgive any inconsistency) and transform it via XSLT 1.0 to a grouped column of two columns per channel, one for start time and one for show title. The XML looks like:

<days>
<DAY>
    <channel>
        <CHANNEL name="A"/>
    </channel>
    <transmissions>
        <TRANSMISSION title="Show 1">
            <starttime>
                <TIME hours="6" minutes="00" seconds="00" timeinseconds="21600"/>
            </starttime>
        </TRANSMISSION>
        <TRANSMISSION title="Show 2">
            <starttime>
                <TIME hours="7" minutes="45" seconds="00" timeinseconds="27900"/>
            </starttime>
        </TRANSMISSION>
        <TRANSMISSION title="Show 3">
            <starttime>
                <TIME hours="8" minutes="00" seconds="00" timeinseconds="28800"/>
            </starttime>
        </TRANSMISSION>
    </transmissions>
    <date>
        <DATE year="2015" month="3" day="2" dayname="Monday" monthname="March" dateindays="41698"/>
    </date>
</DAY>
<DAY>
    <channel>
        <CHANNEL name="B"/>
    </channel>
    <transmissions>
        <TRANSMISSION title="Show 1">
            <starttime>
                <TIME hours="6" minutes="00" seconds="00" timeinseconds="21600"/>
            </starttime>
        </TRANSMISSION>
        <TRANSMISSION title="Show 4">
            <starttime>
                <TIME hours="7" minutes="45" seconds="00" timeinseconds="27900"/>
            </starttime>
        </TRANSMISSION>
        <TRANSMISSION title="Show 2">
            <starttime>
                <TIME hours="8" minutes="00" seconds="00" timeinseconds="28800"/>
            </starttime>
        </TRANSMISSION>
    </transmissions>
    <date>
        <DATE year="2015" month="3" day="2" dayname="Monday" monthname="March" dateindays="41698"/>
    </date>
</DAY>
</days>

From this code I need to create an Excel document that has two columns per channel with one column being the start time and the other being the title of the transmission. These need to be side-by-side (I would post an image, but I don't have enough reputation).

I have tried to build a for-each against the TRANSMISSION, but of course that means the second channel is ignored until the first channel transmission is done and this creates data in row 2 for channel A and row 3 for channel B instead of the data being in row 2 for both channels.

<xsl:for-each select="DAY/transmissions/TRANSMISSION">
    <xsl:sort select="starttime/TIME/@durationinseconds" data-type="number" order="ascending"/>
        <Row ss:AutoFitHeight="0" ss:Height="29.25">
            <xsl:if test="../../channel/CHANNEL/@name = 'A'">
                <xsl:variable name="time">
                    <xsl:value-of select="starttime/TIME/@timeinseconds"/>
                </xsl:variable>
                <Cell>
                    <Data ss:Type="String">
                        <xsl:apply-templates select="starttime/TIME" mode="time"/>
                    </Data>
                </Cell>
                <Cell ss:StyleID="s17">
                    <Data ss:Type="String">
                        <xsl:apply-templates select="../TRANSMISSION[starttime/TIME/@timeinseconds = $time]"/>
                    </Data>
                </Cell>
            </xsl:if>
            <xsl:if test="../../channel/CHANNEL/@name = 'B'"> (repeat of above)

I realize this code has multiple issues, but the core issue is the one I am trying to resolve before I deal with the other smaller issues in this code.

In effect I need to write a column at a time instead of a row, but this is of course not possible. How can I get the data to appear as two grouped columns of data where every channel has a list of start times and associated show titles?

I have considered creating a "master" sheet and then using referential formulas to pull the information, but the amount of data and the variability of the data per channel that the full report will pull creates issues with how to build the "child" sheets. Not to mention the end user doesn't like this solution.

I have also tried building the for-each against the DAY node, but that exacerbates the issue by building the entire channel A data before switching to channel B.

Answer 1

This stylesheet could be a starting point for what you are trying to do:

XSLT 1.0

<!-- (I needed a starting point, you will probably have something different) -->
<xsl:template match="/">
    <wrapperElement>
        <!-- create the rows -->
        <xsl:apply-templates select="//DAY[1]//TRANSMISSION" mode="createRow"/>
    </wrapperElement>
</xsl:template>

<!-- template to create the row -->
<xsl:template match="TRANSMISSION" mode="createRow">
    <Row ss:AutoFitHeight="0" ss:Height="29.25">
        <xsl:apply-templates select="." mode="createCells"/>
    </Row>
</xsl:template>

<!-- template to create two cells for each channel -->
<xsl:template match="TRANSMISSION" mode="createCells">
    <Cell>
        <Data ss:Type="String">
            <xsl:apply-templates select="starttime/TIME" mode="time"/>
        </Data>
    </Cell>
    <Cell ss:StyleID="s17">
        <Data ss:Type="String">
            <xsl:apply-templates select="@title"/>
        </Data>
    </Cell>
    <!-- cells for next channel -->
    <xsl:apply-templates select="ancestor::DAY/following-sibling::DAY[1]//TRANSMISSION[count(preceding-sibling::TRANSMISSION) = count(current()/preceding-sibling::TRANSMISSION)]" mode="createCells"/>
</xsl:template>

Notable points:

• each TRANSMISSION element of the first channel is matched to create a Row
• the same element is processed by another template (in a different mode) to produce the two Cells with time and title
• then the TRANSMISSION element of the next channel with the same position is processed to create other Cells in the same Row