locked
XML to excel (.xls) using XSLT RRS feed

  • Question

  • User402957518 posted

    I have the following XML document:

     

    <projects>
      <project>
       <name>Shock</name>
    <Details> <language>Ruby</language> <owner>Brian May</owner> <state>New</state> <Date>31/10/2008 0:00:00</Date>
    </Details> </project> </projects>

    And I'd like to get this from the transformation (XSLT) result:

    name     : Shock
    language : Ruby
    owner    : Brian May
    state    : New
    Date     : 31/10/2008 0:00:00

    Does anyone know the XSLT to achieve this? I'm using .net in case that matters. I don't have knowledge on XSLT. Can you help me to prepare XSLT to excel.

    Saturday, November 5, 2016 3:08 AM

All replies

  • User283571144 posted

    Hi meygnanam,

    Does anyone know the XSLT to achieve this? I'm using .net in case that matters. I don't have knowledge on XSLT. Can you help me to prepare XSLT to excel.

    According to your description, I couldn't understand your requirement clearly.

    Do you mean you want to change your xml file to excel like below format:

    Or below format:

    Besides, I suggest you could refer to follow link to know how to using xslt format your xml and show in the excel:

    https://blogs.msdn.microsoft.com/brian_jones/2005/06/30/intro-to-excel-xml-part-2-displaying-your-data/

    https://blogs.msdn.microsoft.com/brian_jones/2005/06/27/introduction-to-excel-xml-part-1-creating-a-simple-table/

    Best Regards,

    Brando

    Monday, November 7, 2016 7:57 AM
  • User402957518 posted

    Hi,

    Thanks for prompt reply. Second one is the Correct. If the Project is repeated once then we need to generate in another excel file. Not in another excel sheet.

    Tuesday, November 8, 2016 9:55 AM
  • User283571144 posted

    Hi meygnanam,

    Thanks for prompt reply. Second one is the Correct. If the Project is repeated once then we need to generate in another excel file. Not in another excel sheet.

    As far as I know, xslt couldn't generate two excel file.

    In my opinion, one xml file means one file.

    We couldn't generate the two excel file.

    I suggest you could use C# select the different nodes and load different xml.

    Then you could use XslCompiledTransform.Transform method to format the xml by using xslt file.

    More details, you could refer to follow codes:

    XML:

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

    XSLT:

    <?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>
       <xsl:for-each select="//project">
        <Cell ss:Index="2">
          <Data ss:Type="String">
            <xsl:value-of select="name" />
          </Data>
        </Cell>
        </xsl:for-each>
        </Row>
       <Row ss:Index="2" >
      <Cell>
        <Data ss:Type="String">language</Data>
      </Cell>
      <xsl:for-each
     select="//project">
        <Cell ss:Index="2">
          <Data ss:Type="String">
            <xsl:value-of select="Details/language" />
          </Data>
        </Cell>
      </xsl:for-each>
    </Row>
        <Row ss:Index="3" >
          <Cell>
            <Data ss:Type="String">owner</Data>
          </Cell>
          <xsl:for-each
         select="//project">
            <Cell ss:Index="2">
              <Data ss:Type="String">
                <xsl:value-of select="Details/owner" />
              </Data>
            </Cell>
          </xsl:for-each>
        </Row>
        <Row ss:Index="4" >
          <Cell>
            <Data ss:Type="String">state</Data>
          </Cell>
          <xsl:for-each
         select="//project">
            <Cell ss:Index="2">
              <Data ss:Type="String">
                <xsl:value-of select="Details/state" />
              </Data>
            </Cell>
          </xsl:for-each>
        </Row>
        <Row ss:Index="5" >
          <Cell>
            <Data ss:Type="String">Date</Data>
          </Cell>
          <xsl:for-each
         select="//project">
            <Cell ss:Index="2">
              <Data ss:Type="String">
                <xsl:value-of select="Details/Date" />
              </Data>
            </Cell>
          </xsl:for-each>
        </Row>
    </xsl:template>
    <xsl:template match="DataList">
    </xsl:template>
    </xsl:stylesheet>

    C# codes:

        XmlDocument xdoc = new XmlDocument();
                xdoc.Load(@"c:\users\v-xxxx\documents\visual studio 2015\Projects\Error\Error\example.xml");
                XmlNodeList nodes = xdoc.SelectNodes("/projects/project");
                int i = 0;
                foreach (XmlNode node in nodes)
                {
                    i++;
                    XmlDocument a = new XmlDocument();
                    a.LoadXml("<?xml version='1.0' encoding='UTF-8'?>" + node.OuterXml);
                    //XmlDeclaration declaration =a.CreateXmlDeclaration("1.0", "UTF-8", null);
                    //a.AppendChild(declaration);
                    XslCompiledTransform xct = new XslCompiledTransform();
                    xct.Load(@"c:\users\v-xxxx\documents\visual studio 2015\Projects\Error\Error\exap.xslt");
                    XmlTextWriter writer = new XmlTextWriter(@"c:\users\vxxxx\documents\visual studio 2015\Projects\Error\Error\output" + i + ".xls", null);
                    writer.WriteProcessingInstruction("xml", "version='1.0'");
                    xct.Transform(a, null, writer);
                    writer.Close();
    }

    Result:

    Best Regards,

    Brando

    Thursday, November 10, 2016 11:34 AM