Solved

Convert XML/XSL to Excel

Posted on 2004-08-31
12
20,933 Views
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.

thanks
0
Comment
Question by:sunilramu
12 Comments
 
LVL 19

Expert Comment

by:ramazanyich
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.
0
 
LVL 15

Accepted Solution

by:
dualsoul earned 500 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.
0
 
LVL 4

Expert Comment

by:ChenChen
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.

0
 
LVL 21

Expert Comment

by:MogalManic
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.
0
 
LVL 2

Expert Comment

by:stevenbaker
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:

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
0
 
LVL 3

Expert Comment

by:Sleepyhead_NO
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"
 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>
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 2

Expert Comment

by:stevenbaker
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
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.
0
 
LVL 3

Expert Comment

by:Sleepyhead_NO
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..
0
 
LVL 2

Expert Comment

by:stevenbaker
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.
0
 

Author Comment

by:sunilramu
ID: 12001208
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
0
 
LVL 2

Expert Comment

by:stevenbaker
ID: 12004656
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.
0
 
LVL 3

Expert Comment

by:wadalhag
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  
http://www.experts-exchange.com/Web/Web_Languages/XML/Q_21098399.html

Let me know if you need more assistance
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
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)

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now