Click here to Skip to main content
Click here to Skip to main content
Go to top

XSLT to transform Excel XML spreadsheet to CSV or HTML table

, 15 Jul 2003
Rate this:
Please Sign up or sign in to vote.
XSLT to transform Excel XML spreadsheet to CSV or HTML table.

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

Share

About the Author

jemodurn
Web Developer
United States United States
Frank is a Sr. UI Software Engineer specialized in MFC and Business Applications.
 
He has worked in BROADBASE, KANA, Macromeda and Siebel Systems.

Comments and Discussions

 
NewsVariation: Reformatting Excel XML to "usable" XML with proper element names. PinmemberAnamera2-Jun-14 3:22 
GeneralTransformation Not Always Correct PinmemberCyberKnet28-Mar-05 6:45 
Generalxml to csv PinsussPablo Nuñez15-Dec-03 5:51 
GeneralNot a lot of credibility PinmemberDirk Vandenheuvel16-Jul-03 22:55 
GeneralRe: Not a lot of credibility PinmemberJohn M. Drescher17-Jul-03 3:38 
GeneralRe: Not a lot of credibility PinmemberfolderMonkey17-Jul-03 6:13 
GeneralRe: Not a lot of credibility Pinmemberjemodurn17-Jul-03 6:30 
GeneralRe: Not a lot of credibility PinmemberJohn M. Drescher17-Jul-03 10:54 
GeneralRe: Not a lot of credibility Pinmemberzakriah12-Mar-07 8:39 
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.
QuestionCan not download the source zip file PinmemberChemutury16-Jul-03 8:26 
AnswerRe: Can not download the source zip file Pinmemberjemodurn16-Jul-03 15:33 
GeneralCannot download PinmemberJesterka9-Jul-03 23:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 16 Jul 2003
Article Copyright 2003 by jemodurn
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid