How can I use XSLT and XML to compose Excel , which has dynamic fields?

Hi All,
  My XML file has few static and few dynamic elements, like name is static, grap points is dynamic means its computed by average of "input" ( This I would do it from my application).

<userdata>
  <name>Manjesh</name>                  // Static
  <profession>Eng</profession>            // Static      
  <gradpoints>12</gradpoints>  // Dynamic field calculated by computing the average of input
  <acadrecord>      
    <input>10</input>            // Static
    <input>12</input>            // Static
    <input>14</input>            // Static
  </acadrecord>
</userdata>


Excel Sheet
Name            Profession            GradPoints
Manjesh            Engg            12

Once its done I would like to show only few relavent fields in my excel as mentioned above. Unfortunatley I dont have enough time to experiment XSLT and mapping to Excel. So if any expert can give the XSLT and steps for importing the same to excel .

Thanks for your earliest attention.



LVL 1
ManjeshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert BormansInformation ArchitectCommented:
There are several ways to get to Excel from XML.
Depending on your needs you could use an XML schema, bind it to a table and import the XML directly. Or you could use special tools dediacted to writing Excel files.

If you want to use XSLT, again you have some options (though you can't write binary Excel)
You could write the Excel XML format. You then have most of the bells and whistles, but it is a complex format.
You can also write simple HTML tables, in order to pass some formatting.
In simple cases, you can write tab seperated text, which you then import in Excel.

That is what this example does

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:param name="newline" select="'&#13;&#10;'"/>
    <xsl:param name="tab" select="'&#9;'"/>
    <xsl:output method="text"/>
    <xsl:strip-space elements="*"/>
    <xsl:template match="/">
        <xsl:text>name</xsl:text>
        <xsl:value-of select="$tab"/>
        <xsl:text>profession</xsl:text>
        <xsl:value-of select="$tab"/>
        <xsl:text>gradpoints</xsl:text>
        <xsl:value-of select="$newline"/>
        <xsl:apply-templates select="//userdata"/>
    </xsl:template>
    <xsl:template match="userdata">
        <xsl:value-of select="name"/>
        <xsl:value-of select="$tab"/>
        <xsl:value-of select="profession"/>
        <xsl:value-of select="$tab"/>
        <xsl:value-of select="gradpoints"/>
        <xsl:value-of select="$newline"/>
    </xsl:template>
</xsl:stylesheet>

cheers

Geert
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert BormansInformation ArchitectCommented:
I don't know how much effort you do for calculating the mean,
but this is straightforward in XSLT

   <xsl:template match="userdata">
        <xsl:value-of select="name"/>
        <xsl:value-of select="$tab"/>
        <xsl:value-of select="profession"/>
        <xsl:value-of select="$tab"/>
        <xsl:value-of select="sum(acadrecord/input) div count(acadrecord/input)"/>
        <xsl:value-of select="$newline"/>
    </xsl:template>
0
ManjeshAuthor Commented:
Thanks for your quik reply.

Assuming I have the XML  and XSLT as mentioned above , How can I show the data in Excel?

Like how can I import XSLT in excel and get the view of my data ?

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Geert BormansInformation ArchitectCommented:
use a commandline XSLT processor, or set the proces up in ASP
save the result file as *.xls and double-click
or serve the result out with a ms-application-excel mime-type
0
ManjeshAuthor Commented:
Finally I found a VB Script which can convert XML( XSL ) to CSV file. But instead of being comma separeated it separated by ;

When I run the script its seperated by ";(Semicolon)" instead od Comma. Experts any clue how to tweak the code , to make it comma seperated.

C:\Test\testme.xsl

<?xml version="1.0"?>
<xsl:stylesheet version = "1.0"      xmlns:xsl = "http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:strip-space elements = "gazetteer"/>
<xsl:template match="place">
<xsl:text/><xsl:value-of select="normalize-space(name)"/>,<xsl:text/>
<xsl:value-of select="normalize-space(county)"/>,<xsl:text/>
<xsl:value-of select="normalize-space(ngr/easting)"/>,<xsl:text/>
<xsl:value-of select="normalize-space(ngr/northing)"/>,<xsl:text/>
<xsl:value-of select="normalize-space(geographical/latitude)"/>,<xsl:text/>
<xsl:value-of select="normalize-space(geographical/longitude)"/><xsl:text/>          
<xsl:text disable-output-escaping = "yes" >
</xsl:text>
</xsl:template>
</xsl:stylesheet>

C:\Test\testme.xml

<?xml version = "1.0"?>
<gazetteer>
  <place>
    <name>Wolverhampton</name>
    <county>West Midlands</county>
    <ngr>
          <easting>390</easting>
            <northing>298</northing>
    </ngr>
    <geographical>
          <latitude>52.583</latitude>
            <longitude>-2.133</longitude>
    </geographical>
  </place>
</gazetteer>

C:\Test\testme.vbs


  Dim xmlSource
  Dim xmlXForm
  Dim strErr
  Dim strResult
 
  Dim fso , file
  Dim strPath
  Const ForReading = 1
  Const ForWriting = 2
  Const ForAppending = 8
 
  Set xmlSource = CreateObject("MSXML.DOMDocument")
  Set xmlXForm = CreateObject("MSXML.DOMDocument")
 
  xmlSource.validateOnParse = True
  xmlXForm.validateOnParse = True
  xmlSource.async = False
  xmlXForm.async = False
 
  xmlSource.Load "c:\test\testme.xml"  ' This loads the text that I want to transform
  xmlXForm.Load "c:\test\testme.xsl" ' This loads the XSLT transform
 
  strResult = xmlSource.transformNode(xmlXForm) ' This transforms the data in xmlSource
 
  Set fso = CreateObject("Scripting.FileSystemObject")
  strPath = "c:\test\testme.csv"
 
  Set file = fso.opentextfile(strPath, ForWriting, True)

  file.write strResult
 
  file.Close
 
  Set file = Nothing
  Set fso = Nothing
  Set xmlSource = Nothing
  Set xmlXForm = Nothing


Thanks for your earliest attention.

Regards,

Manjesh Gowda S H
0
Geert BormansInformation ArchitectCommented:
If I look at the XSLT, I would expect the seperator being ','
because that is what is in the code

Have you tried using your own XML, with my XSLT in combination with this VB script?
It should give you what you need

cheers

Geert
0
ManjeshAuthor Commented:
Hi Gertone,
  Thanks for your quick reply and handy solution, I was able to get it done through VB Script.

Thanks a lot again.
0
Geert BormansInformation ArchitectCommented:
welcome
0
ManjeshAuthor Commented:
Hi Greet,
  I got the thing workign fine...but a small hitch. I have the XSL and XML file and I want to open it excel, I dont want to create a CSV file using VB Script. I want to directly import in Excel. Any suggestions?

Thanks for your earliest response.

Regards,

Manjesh Gowda S H
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.