XSLT to transform Excel XML spreadsheet to CSV or HTML table

jemodurn

3.72/5 (15 votes)

Jul 7, 2003

172393

1843

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.