exporting xml data to excel file

[kate_cha]

HI,

I need to export some xml data to an excel file. I've found a few ways of doing this on the web, but none of them quite fit what I want, and my knowledge of xml manipulation is not fantastic. The problem is:

I have some existing code in a web application which used to work but which now redirects you to a new page (as it should) with the title for the excel and the excel cells on it (as it should), but no data (as it shouldn't).

Two constraints are that
1. I need to open up the excel file in a web browser instead of the excel application itself
2. The xml input comes from a custom object, which returns an xml string. Because I'm working with an existing application, this can't be changed. So as I understand it, I need to work with xml data that is in memory rather than in a file.

I'm happy to consider changing the way the code works, but I need to stay within the contraints I just mentioned.....

Any help would be greatly appreciated.

The vb code is:
Response.Clear()
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"

xmlTransform.TransformSource = "excel.xslt"
xmlTransform.DocumentContent = [custom object returning valid xml string]
xmlTransform.DataBind()

And excel.xslt is:

<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:user="urn:my-scripts"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet" >

<xsl:template match="/">
<Workbook xmlns="urn:schemas-microsoft-comffice:spreadsheet"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<xsl:apply-templates/>
</Workbook>
</xsl:template>

<xsl:template match="/*">
<Worksheet>
<xsl:attribute name="ss:Name">
<xsl:value-of select="local-name(/*/*)"/>
</xsl:attribute>
<Table x:FullColumns="1" x:FullRows="1">
<Row>
<xsl:for-each select="*[position() = 1]/*">
<Cell><Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</Data></Cell>
</xsl:for-each>
</Row>
<xsl:apply-templates/>
</Table>
</Worksheet>
</xsl:template>

<xsl:template match="/*/*">
<Row>
<xsl:apply-templates/>
</Row>
</xsl:template>

<xsl:template match="/*/*/*">
<Cell><Data ss:Type="String">
<xsl:value-of select="."/>
</Data></Cell>
</xsl:template>

</xsl:stylesheet>


thanks!!

 

[kate_cha]

solution #1

In case anyone out there is looking here is one solution I found:

Dim doc AsNew XmlDocument
Dim sInPath as String=[valid xml string]
doc.LoadXml(sInPath)
Dim trans As Xsl.XslTransform = New Xsl.XslTransform
trans.Load(Server.MapPath("excel.xsl"))
trans.Transform(doc, Nothing, Response.OutputStream, New System.Xml.XmlUrlResolver)

Response.ContentType = "application/vnd.ms-excel"
Response.Flush()
Response.End()

the key to is is the response.contentType, which is pretty cool - you can open up web forms in different formats - word, powerpoint, excel - the default is html. The link below lists the different values you can pass to the contentType.

http://www.topxml.com/asp/response_object_property_contenttype.asp

Still haven't found a way to display labels in the webpage, but getting there.