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

Posted on 2007-04-10
Last Modified: 2013-11-18
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).

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

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.

Question by:Manjesh
  • 5
  • 4
LVL 60

Accepted Solution

Geert Bormans earned 50 total points
ID: 18887672
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="" 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:value-of select="$tab"/>
        <xsl:value-of select="$tab"/>
        <xsl:value-of select="$newline"/>
        <xsl:apply-templates select="//userdata"/>
    <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"/>


LVL 60

Expert Comment

by:Geert Bormans
ID: 18887720
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"/>

Author Comment

ID: 18887870
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 ?

LVL 60

Expert Comment

by:Geert Bormans
ID: 18887907
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.


Author Comment

ID: 18888270
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.


<?xml version="1.0"?>
<xsl:stylesheet version = "1.0"      xmlns:xsl = "">
<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" >


<?xml version = "1.0"?>
    <county>West Midlands</county>


  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
  Set file = Nothing
  Set fso = Nothing
  Set xmlSource = Nothing
  Set xmlXForm = Nothing

Thanks for your earliest attention.


Manjesh Gowda S H
LVL 60

Expert Comment

by:Geert Bormans
ID: 18888291
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



Author Comment

ID: 18888345
Hi Gertone,
  Thanks for your quick reply and handy solution, I was able to get it done through VB Script.

Thanks a lot again.
LVL 60

Expert Comment

by:Geert Bormans
ID: 18888459

Author Comment

ID: 18896594
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.


Manjesh Gowda S H

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Scraping specific data within an XML document 19 45
crm development 2 37
Extract XML Data from using TSQL 5 31
XML & .net 5 20
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now