XML to excel (.xls) using XSLTRSS

• • Reply
  meygnanam

  Member

  2 Points

  48 Posts

  XML to excel (.xls) using XSLT

  Nov 05, 2016 03:08 AM|meygnanam|LINK

  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.

• • Reply
  Brando ZWZ

  Star

  9301 Points

  2868 Posts

  Re: XML to excel (.xls) using XSLT

  Nov 07, 2016 07:57 AM|Brando ZWZ|LINK

  Hi meygnanam,

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  meygnanam

  Member

  2 Points

  48 Posts

  Re: XML to excel (.xls) using XSLT

  Nov 08, 2016 09:55 AM|meygnanam|LINK

  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.

• • Reply
  Brando ZWZ

  Star

  9301 Points

  2868 Posts

  Re: XML to excel (.xls) using XSLT

  Nov 10, 2016 11:34 AM|Brando ZWZ|LINK

  Hi meygnanam,

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.