XSLT to transform Excel XML spreadsheet to CSV or HTML table

Download sourcecode - 3.27 Kb

Introduction

One day, I needed to create a conversion of MS Excel saved XML spreadsheet to CSV file. I believe the MS Office Web Component (OWC) ver. 10 spreadsheet component can also expose XML data.

Practical use

Here is an example web page showing how to use OWC spreadsheet with it.

The Script

<script language="JavaScript">

    function action()
    {
        //get the xmldata
        var strXML = Spreadsheet1.XMLData
    
        // create xml object
        var xml = new ActiveXObject("Microsoft.XMLDOM")
        xml.async = false
        xml.load(strXML)

        // Load XSL
        var xsl = new ActiveXObject("Microsoft.XMLDOM")
        xsl.async = false
        xsl.load("book.xsl")

        // Transform
        form1.csvValue.value = xml.transformNode(xsl)
    
        // csv submit to backend 
        // alert(form1.xmlValue.value) 
        form1.submit()

    }
</script>

The Body

<html><BODY>
  <h3> Payroll Input Form </h3>
  <P>
   <OBJECT id="Spreadsheet1" style="WIDTH: 100%; HEIGHT: 80%" 
           classid="clsid:0002E551-0000-0000-C000-000000000046" >
    <PARAM NAME="DataType" VALUE="XMLDATA">
    <PARAM NAME="xmlURL" VALUE="book_no_xsl.xml">
   </OBJECT>
  </P>
  <form id="form1" method="post" runat="server">
   <P>
    <input type="button" onclick="action()">
   </P>
   <input id="csvValue" type="hidden" name="csvValue">
  </form>
</BODY></html>

How to Use it

Simply unzip and look at book.xml.

ASP.NET

Here is a piece of code I use to transform Excel XML to CSV.

'Create a new XslTransform object.
Dim xslt As New XslTransform
'Load the stylesheet.
xslt.Load(Server.MapPath(".") & "excel2csv.xsl")

Dim doc As New XmlDocument
'xmldata is string, use doc.Load(fileName) for file.
doc.LoadXml(xmlData)

'Create an XmlTextWriter which outputs to a file.
Dim fileName As String
fileName = Server.MapPath(".") & "book.csv"

Dim writer As XmlWriter = New XmlTextWriter(fileName, Nothing)
'Transform the data and send the output to the console.

xslt.Transform(doc, Nothing, writer, Nothing)
writer.Close()

Reference

-- May the code be with you.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

jemodurn

Web Developer

United States

Member

Frank is a Sr. UI Software Engineer specialized in MFC and Business Applications.

He has worked in BROADBASE, KANA, Macromeda and Siebel Systems.

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

Transformation Not Always Correct

CyberKnet

6:45 28 Mar '05

xml to csv

Pablo Nuñez

5:51 15 Dec '03

Not a lot of credibility

Dirk Vandenheuvel

22:55 16 Jul '03

Re: Not a lot of credibility

John M. Drescher

3:38 17 Jul '03

Re: Not a lot of credibility

folderMonkey

6:13 17 Jul '03

Re: Not a lot of credibility

jemodurn

6:30 17 Jul '03

Re: Not a lot of credibility

John M. Drescher

10:54 17 Jul '03

Re: Not a lot of credibility

zakriah

8:39 12 Mar '07

I don't think that's being very fair. It's just a simple example which true programmers wouldn't just cut and paste but get an idea of what needs to be done.
Sign In·View Thread·Permalink