nested xml data not getting in excel as output while using xslt

Question

I'm trying to translate xml data into excel using xslt template. But i'm getting only headers, not data. Please check the below xml data and xslt template and my C# code for your reference.

example.xml

<?xml version="1.0"?>
<projects>
  <project>
    <name>Shock</name>
    <language>Ruby</language>
    <owner>Brian May</owner>
    <state>New</state>
    <Date>31/10/2008 0:00:00</Date>
  </project>
  <project>
    <name>Shock1</name>
    <language>Ruby1</language>
    <owner>Brian May1</owner>
    <state>New1</state>
    <Date>30/10/2008 0:00:00</Date>
  </project>
</projects>

test.xsl

<?xml version="1.0" encoding="utf-8"?>
<?mso-application progid="Excel.Sheet"?>
<xsl:stylesheet version="1.0"
xmlns:html="http://www.w3.org/TR/REC-html40"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
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">
  <xsl:template match="/">
    <Workbook>
      <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
          <Alignment ss:Vertical="Bottom" />
          <Borders />
          <Font />
          <Interior />
          <NumberFormat />
          <Protection />
        </Style>
        <Style ss:ID="s21">
          <Font ss:Size="22" ss:Bold="1" />
        </Style>
        <Style ss:ID="s22">
          <Font ss:Size="14" ss:Bold="1" />
        </Style>
        <Style ss:ID="s23">
          <Font ss:Size="12" ss:Bold="1" />
        </Style>
        <Style ss:ID="s24">
          <Font ss:Size="10" ss:Bold="1" />
        </Style>
      </Styles>
      <Worksheet ss:Name="Page1">
        <Table>
          <xsl:call-template name="XMLToXSL" />
        </Table>
      </Worksheet>
    </Workbook>
  </xsl:template>
  <xsl:template name="XMLToXSL">
    <Row ss:Index="1" >
      <Cell>
        <Data ss:Type="String">name</Data>
      </Cell>
      <Cell>
        <Data ss:Type="String">language</Data>
      </Cell>
      <Cell>
        <Data ss:Type="String">owner</Data>
      </Cell>
      <Cell>
        <Data ss:Type="String">state</Data>
      </Cell>
      <Cell>
        <Data ss:Type="String">Date</Data>
      </Cell>
    </Row>
    <xsl:for-each select="/projects">
      <xsl:for-each select="/project">
        <Row ss:Index="1">
          <Cell ss:Index="2">
            <Data ss:Type="String">
              <xsl:value-of select="name" />
            </Data>
          </Cell>
          <Cell ss:Index="2">
            <Data ss:Type="String">
              <xsl:value-of select="language" />
            </Data>
          </Cell>
          <Cell ss:Index="2">
            <Data ss:Type="String">
              <xsl:value-of select="owner" />
            </Data>
          </Cell>
          <Cell ss:Index="2">
            <Data ss:Type="String">
              <xsl:value-of select="state" />
            </Data>
          </Cell>
          <Cell ss:Index="2">
            <Data ss:Type="String">
              <xsl:value-of select="Date" />
            </Data>
          </Cell>
        </Row>
      </xsl:for-each>
    </xsl:for-each>
  </xsl:template>
  <xsl:template match="DataList">
  </xsl:template>
</xsl:stylesheet>

C# code:

XmlDocument xdoc = new XmlDocument();
xdoc.Load(@"C:\Xmls\example.xml");
XslCompiledTransform xct = new XslCompiledTransform();
xct.Load(@"C:\Templates\Test.xsl");
XmlTextWriter writer = new XmlTextWriter(@"C:\Outputs\output.xls", null);
writer.WriteProcessingInstruction("xml", "version='1.0'");
xct.Transform(xdoc, null, writer);
writer.Close();

Excel output.xls:

|  name  |  language  |  owner  |  state  |  Date   |
-----------------------------------------------------

But my expected output should be as,

|  name  |  language  |  owner    |  state  |        Date        |
------------------------------------------------------------------
| Shock  | Ruby       | Brian May | New     | 31/10/2008 0:00:00 |
------------------------------------------------------------------
| Shock1 | Ruby1      | Brian May1| New1    | 30/10/2008 0:00:00 |
------------------------------------------------------------------

Currently it is printing headers alone in excel, How to get the data into excel row-wise.

As well as Martin's answer, you are also not setting ss:Index correctly on any of the rows or cells. All your rows are currently numbered 1, but the should be numbered 1, 2, 3... and similarly all you cells are numbered 2, but these also need to be numbered 1, 2, 3.... Having said that, if you remove all instances of ss:index you should still be OK as then Excel will treat everything as consecutive. You only need ss:index if you want to skip over some rows/cells to leave them blank. — Tim C
– Tim C, Commented May 14, 2018 at 7:50
Thank you Tim :) I've followed Martin's replace code and removed Index things. It got worked. :) — cgsabari
– cgsabari, Commented May 14, 2018 at 8:02

Martin Honnen · Accepted Answer · 2018-05-14 07:25:51Z

1

Replace the xsl:for-each select="/project" with xsl:for-each select="project".

I would also simplify the C# to use

XslCompiledTransform xct = new XslCompiledTransform();
xct.Load(@"C:\Templates\Test.xsl");
xct.Transform(@"C:\Xmls\example.xml", @"C:\Outputs\output.xls");

although I don't think the code you have causes the problem, it is the XSLT that has the wrong XPath.

answered May 14, 2018 at 7:25

Martin Honnen

169k6 gold badges100 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cgsabari Over a year ago

Thank you for your suggestion. I've tried this. not working. Excel itself not opening if i replace as you suggested.

Collectives™ on Stack Overflow

nested xml data not getting in excel as output while using xslt

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related