Convert XML/XSL to Excel

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.

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.

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.

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:

ContentType="application/octet-stream"
Content-transfer-encoding="8bit"
Content-Disposition="attachment;filename=workbook1.xls"

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:
http://www.microsoft.com/office/xml/default.mspx

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

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"
 xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:msxsl="urn:schemas-microsoft-com:xslt"
 xmlns:user="urn:my-scripts"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:rs="urn:schemas-microsoft-com:rowset"
 xmlns:z="#RowsetSchema">

<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"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40"
    encoding="ISO-8859-1">

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



      <Worksheet>


      <xsl:attribute name="ss:Name">Report</xsl:attribute>
      <Table x:FullColumns="1" x:FullRows="1" ID="Table1">
      <Row>
                  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
      next
      %>
      </Row>
      <%
      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
      next
      response.Write "</Row>" & vbNewLine
      response.Write "</xsl:for-each>" & vbNewLine
      response.Write vbNewLine
      %>
    </Table>
  </Worksheet>
  </Workbook>
</xsl:template>


</xsl:stylesheet>

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
Response.CharSet="WINDOWS-1252"
Response.ContentType="application/vnd.ms-excel"
call Response.AddHeader("Content-Disposition","attachment;filename=workbook1.xml")
'get your results from db or somewhere else as xml
xmlStr=myResults
xslFile="http://yourSite/yourXSLFile.xsl"
%>
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="<%=xslFile%>" ?><?mso-application progid="Excel.Sheet"?>
<%=xmlStr%>

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.

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..

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.

hey

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.

thanks
sunil

Yep,

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
Response.ContentType="application/vnd.ms-excel"
'get your XML and XSL and do the conversion
htmlStr=xmlObj.transform(xslObj)
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.

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  
https://www.experts-exchange.com/questions/21098399/Convert-XML-to-CVS.html

Let me know if you need more assistance