Click here to Skip to main content
11,581,852 members (74,543 online)
Click here to Skip to main content

XSLT to transform Excel XML spreadsheet to CSV or HTML table

, 15 Jul 2003 146.3K 1.6K 34
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.

You may also be interested in...

Comments and Discussions

 
NewsVariation: Reformatting Excel XML to "usable" XML with proper element names. Pin
Anamera2-Jun-14 3:22
memberAnamera2-Jun-14 3:22 
GeneralTransformation Not Always Correct Pin
CyberKnet28-Mar-05 6:45
memberCyberKnet28-Mar-05 6:45 
Generalxml to csv Pin
Pablo Nuñez15-Dec-03 5:51
sussPablo Nuñez15-Dec-03 5:51 
GeneralNot a lot of credibility Pin
Dirk Vandenheuvel16-Jul-03 22:55
memberDirk Vandenheuvel16-Jul-03 22:55 
GeneralRe: Not a lot of credibility Pin
John M. Drescher17-Jul-03 3:38
memberJohn M. Drescher17-Jul-03 3:38 
GeneralRe: Not a lot of credibility Pin
folderMonkey17-Jul-03 6:13
memberfolderMonkey17-Jul-03 6:13 
GeneralRe: Not a lot of credibility Pin
jemodurn17-Jul-03 6:30
memberjemodurn17-Jul-03 6:30 
GeneralRe: Not a lot of credibility Pin
John M. Drescher17-Jul-03 10:54
memberJohn M. Drescher17-Jul-03 10:54 
jemodurn wrote:
I should have put "#define M$ Microsoft"

Laugh | :laugh:

John
GeneralRe: Not a lot of credibility Pin
zakriah12-Mar-07 8:39
memberzakriah12-Mar-07 8:39 
QuestionCan not download the source zip file Pin
Chemutury16-Jul-03 8:26
memberChemutury16-Jul-03 8:26 
AnswerRe: Can not download the source zip file Pin
jemodurn16-Jul-03 15:33
memberjemodurn16-Jul-03 15:33 
GeneralCannot download Pin
Jesterka9-Jul-03 23:32
memberJesterka9-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 | Terms of Use | Mobile
Web04 | 2.8.150603.1 | Last Updated 16 Jul 2003
Article Copyright 2003 by jemodurn
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid