Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

XSL Taking XML transforming it into Excel

Tags:

People also ask

Can XML data be converted to Excel?

If you already have an XML file (either downloaded on your system or a link to it on the web), you can easily convert it into data in an Excel file.

How does XSL work with XML?

XSLT is used to transform XML document from one form to another form. XSLT uses Xpath to perform matching of nodes to perform these transformation . The result of applying XSLT to XML document could be an another XML document, HTML, text or any another document from technology perspective.

Can XSLT transform XML to CSV?

This post shows you how to convert a simple XML file to CSV using XSLT. The following XSL Style Sheet (compatible with XSLT 1.0) can be used to transform the XML into CSV. It is quite generic and can easily be configured to handle different xml elements by changing the list of fields defined ar the beginning.


New guy here so bear with me. Ive got a basic XSL file that will read my xml data. Im trying to put xml into Excel. Heres my issue. With a small XML file it seems to convert it easly, BUT with this XML file that had several nodes ( I think they are called), when I call up the data, its not right. I want to only show info from the check portion of XML and then show it in Excel in a way that shows the 6 or 7 columns that I want, then show the data. Heres what I have so far:

XML:

<bdiData>
  <documentControlInfo>
    <documentInfo>
      <docDescription>Checks for Company X</docDescription>
      <docID>
        <ID>123456789</ID>
      </docID>
      <docModifier>My Company</docModifier>
      <docCreateDate>2010-08-23</docCreateDate>
      <docCreateTime>07:08:54-0700</docCreateTime>
      <standardVersion>1.0</standardVersion>
      <testIndicator>0</testIndicator>
      <resendIndicator>0</resendIndicator>
    </documentInfo>
    <sourceInfo>
      <sourceName>My Banking Name</sourceName>
      <sourceID>
        <idOther>ShortBankName</idOther>
      </sourceID>
    </sourceInfo>
    <destinationInfo>
      <destinationName>My Company</destinationName>
      <destinationID>
        <idOther>MYCO</idOther>
      </destinationID>
    </destinationInfo>
  </documentControlInfo>
  <checkItemCollection>
    <collectionInfo>
      <description>Items</description>
      <ID>654811650</ID>
      <Classification>
        <classification>Items</classification>
      </Classification>
    </collectionInfo>
    <checkItemBatch>
      <checkItemBatchInfo>
        <description>Paid Checks</description>
        <ID>1239668334710</ID>
        <Classification>
          <classification>Paid Checks</classification>
        </Classification>
      </checkItemBatchInfo>
      <checkItem>
        <checkItemType>check</checkItemType>
        <checkAmount>2960</checkAmount>
        <postingInfo>
          <date>2009-06-12</date>
          <RT>87654321</RT>
          <accountNumber>123465798</accountNumber>
          <seqNum>007725552898</seqNum>
          <trancode>001152</trancode>
          <amount>2960</amount>
          <serialNumber>55225410</serialNumber>
        </postingInfo>

XSL File:

<xsl:stylesheet version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:msxsl="urn:schemas-microsoft-com:xslt"
 xmlns:user="urn:my-scripts"
 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 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:apply-templates/>
    </Workbook>
  </xsl:template>


  <xsl:template match="/*">
    <Worksheet>
      <xsl:attribute name="ss:Name">
        <xsl:value-of select="local-name(/*/*)"/>
      </xsl:attribute>
      <Table x:FullColumns="1" x:FullRows="1">
        <Row>

          <xsl:for-each select="*[position() = 2]/*/checkItem/postingInfo/*">

            <Cell>
              <Data ss:Type="String">
                <xsl:value-of select="local-name()"/>
              </Data>
            </Cell>
          </xsl:for-each>
        </Row>
        <xsl:apply-templates/>
      </Table>
    </Worksheet>
  </xsl:template>


  <xsl:template match="/*/checkItem/postingInfo/*">
    <Row>
      <xsl:apply-templates/>
    </Row>
  </xsl:template>


  <xsl:template match="/*/checkItem/postingInfo/*">
    <Cell>
      <Data ss:Type="String">
        <xsl:value-of select="."/>
      </Data>
    </Cell>
  </xsl:template>


</xsl:stylesheet>

Does anyone have any Idea how I can get to JUSt the check portion f the XML file and have it format in an eay way??

Thanks

GabrielVA