Go Premium for a chance to win a PS4. Enter to Win


Convert XML/XSL to Excel

Posted on 2004-08-31
Medium Priority
Last Modified: 2013-11-19
I am looking for a code or a reference about how to convert an XML file with XSL to MS-Excel.

Question by:sunilramu
LVL 19

Expert Comment

ID: 11944103
You can't convert to MS-Excel format as it is proprietary Microsoft format.
But you can prepare CSV file and import it to MSExcel.
LVL 15

Accepted Solution

dualsoul earned 1500 total points
ID: 11945605
hi, sunilramu

you have 2 possibilites:

1. Convert XML to CSV as  ramazanyich have suggested, but not: going this way you can't apply any style to your spreadsheet - no colors, no formating and so on.
   You can use some VB scripting to pretty and format this row data.

2. MSOffice 2003 and therefore Excel 2003 supports saving spreasheets in it's own ExcelML - XML format - this is fully support all formating and posibilites Excel can suggest you, but this is tied to 2003 and higher version.

So, you can choose one of this, and we can try help you with it.

Expert Comment

ID: 11959606
if you are familar with DOM and VB, it would be quite easy.
with DOM, you can traverse your xml document, read any node or attribute you want.
And then populate the value (of the nodes or the attributes) into excel in the way you like.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 21

Expert Comment

ID: 11959708
Where/how are you doing the XSL processing?  

If you are doing the processing on the Server side (ie as part of your Java Servlet), then you can set the content type to applicaction/ms-excel and Excel will open the HTML and import it into the Excel.

If you are doing the processing on the client side(using MSXML), then ChenChen's option might work.  In VBScript or JavaScript, you can open the Excel application as a ActiveX and write the results into a new document.

Expert Comment

ID: 11962662
You have several options, depending on whether you are transforming server or client side.  Here are the server side options:

1)  Produce HTML output froma file that is a .xls file (but processed by your script language - easy to do using IIS, and assume easy in java etc) - this will work in excel 95+
You need the following response headers:


However, you dont have much control over the look & feel, cant set cell data types (so everything is treated as text) and have to be carefull about ytour HTML.

2) If you have excel xp or excel 2003 you can use excelML:

You have 2 options, transform on the server and push out excel XML as a .xml or .xls file, or just push out an xml doc with a referenced stylesheet and then your transform will be done client side.
A few security considerations with this method:
- CANT use script
- Excel XP: must have medium (not high) macro security and receive macro security prompt
ALso, your users need to select 'transform with the following stylesheet' which is NOT the defualt option, so only really acceptable in a highly controlled deployment environment.

The advantage of using excel XML is that you can control nearly everything, from freeze panes, page layout, zoom and also can have multiple worksheets + charts etc

Expert Comment

ID: 11977670
Ignore the firsts posts here - you can create Excel spreadsheets using XSLT. I am doing with a report generator i made which exports to XML, CSV, HTML and Excel - all using XSLT:

You have good control over the spreadsheet, but I am not sure how much you can specify using XSLT - i have only made a simple spreadsheet.

Make sure you set content type to: application/vnd.ms-excel

This is a cut/paste from my code (with minor modifications - it might not work, haven't testet it). This is due to the code using our database, and i removed irrelevant script lines. What I do is:

Main script:
1. Set content type to Excel

2. Executes sql query and saves ADO recordset to ADO stream as XML.

3. Loads XSLT below.

4. Transform XML using the XSLT.

5. Output transformed document to response.

Let me know if you have any questions.

I found this - http://aspalliance.com/articleviewer.aspx?aId=471 - link in my bookmarks. The title says 'Convert XML to Excel', but it seems like the server is done. ANother article (just as proof of concept in regards to MS Office) is http://www.tkachenko.com/blog/archives/000024.html - which generates Word document using XSLT.

I am not sure if this method works in all versions of Excel. I assume it only works in newer versions with XML support. If that is a problem, then the next solution would be generating a CSV which you can load up in Excel - but you will only get data then, nothing visual.

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"

<xsl:output method="xml" media-type="application/vnd.ms-excel" encoding="ISO-8859-1" indent="yes"/>

<xsl:template match="/">
  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

    <Style ss:ID="header">
      <Font x:Family="Swiss" ss:Bold="1"/>


      <xsl:attribute name="ss:Name">Report</xsl:attribute>
      <Table x:FullColumns="1" x:FullRows="1" ID="Table1">
                  response.Write "<Cell ss:StyleID=""header""><Data ss:Type=""String"">Header"
                  response.Write "</Data></Cell>" & vbNewLine
                  response.Write "<xsl:text>&#10;</xsl:text>"
            end if
      response.Write "<xsl:for-each select=""//rs:data/z:row"">" & vbNewLine
      response.Write vbNewLine
      response.Write "<Row>"
                  response.Write "<Cell><Data ss:Type=""String"">"
                  response.Write "<xsl:value-of select=""normalize-space(@XML_ELEMENT"
                  response.Write ")""/>"
                  response.Write "</Data></Cell>" & vbNewLine
                  response.Write "<xsl:text>&#10;</xsl:text>"
            end if
      response.Write "</Row>" & vbNewLine
      response.Write "</xsl:for-each>" & vbNewLine
      response.Write vbNewLine


Expert Comment

ID: 11989474
Sleepyhead_no - you just posted exactly what dualsoul and I both suggested, ie producing ExcelML

your example includes the stylesheet inline using ASP, which is one of the many ways that you could do this

I personally would not use response.writes to create the stylesheet, but would produce an xml stream which referenced an external stylesheet as in:

<%@ LANGUAGE=vbscript enablesessionstate=false %>
Response.Buffer = True
call Response.AddHeader("Content-Disposition","attachment;filename=workbook1.xml")
'get your results from db or somewhere else as xml
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="<%=xslFile%>" ?><?mso-application progid="Excel.Sheet"?>

As I said before though, this does the transform on the client (good as uses less of you servers resource) BUT there are secuirty issues you should be aware of as mentioned above.

Expert Comment

ID: 11989881
Ok, sorry. I wasn't aware of the ExcelML name. I was mainly referreing to the first comment here though.

The reason I have used response.write and transformed on the server is because that's what I do in my code. Didn't have time to write a script so I just did a copy/paste.

Doing on the client is a good idea when thinking of server performance, but then you require the client to have MS XML Parser installed. If this is for a intranet with Windows XP client, then that is no problem, but for a internet site with many different clients, then doing on the client will be a problem. But if this only works in Excel 2003, then it wont be an issue anyway though..

Expert Comment

ID: 11989986
Excel XP can also handle excel XML files, although it acts slightly differently from Excel 2003 when doing client side transformations - they run under macro security mode, so a setting of high will stop them running and a medium setting will produce a warning asking if they want to run macros.  If they say NO (the default) you just see the XML as a spreadsheet.

Author Comment

ID: 12001208

thanks for all your responses, here is what i have to do, I am doing xsl procesing on the server side.
I have an html generated using XSLT i was wondering if it was possible to send the data in html format and open it as Excel at the client end.


Expert Comment

ID: 12004656

call your file excelDownload.xsl (or whatever) and set IIS to process .xsl files using the ASP processor - you may need to place this in a seperate folder and make this an application so that you dopnt start processing normal xls files.  Doing this depends on the version of IIS you have.

You can then use the following code

<%@ LANGUAGE=vbscript enablesessionstate=false %>
Response.Buffer = True
'get your XML and XSL and do the conversion
response.write htmlStr

Excel will then download the file and translate the html.
Cant use images etc though
I would recommend changing your transform to produce excel xml but as said earlier, that'll only work with excel xp or 2003.

Expert Comment

ID: 12040046
Hi sunilramu
I have an accepted answer for the same question using Saxon transformation engine
This will enable you to produce a *.csv or *.xls files  in an easy and fast way  

Let me know if you need more assistance

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. 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.: (CODE)
Suggested Courses

916 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