Haven't done it myself yet, have a look at this:

http://articles.techrepublic.com.com/5100-10878_11-6163451.html

It shouldn't be that difficult I guess, the link above outlines the target Xml structure of the Excel file.

Hope that helps,

Sascha

Thanks for that Sascha,

I am running through the article and through the example and all I get when I test via VS2008 is this output via an XMLDATASOURCE :

 My solution needs to be downloadable XML that can import into Excel.

I can't believe that this problem hasn't been addressed before.

Thanks again.

Try this:

logs.xml

<?xml version="1.0" encoding="utf-8" ?>
<?xml-stylesheet type="text/xsl" href="logs.xsl"?>
<logs>
  <log time="16:08:30" date="09/09/2010">
    <field alias="name">Dave Smith</field>
    <field alias="position">Managing Director</field>
    <field alias="company">Ford Motors</field>
    <field alias="email">[email protected]</field>
  </log>
  <log time="16:56:46" date="09/09/2010">
    <field alias="name">Terry Prachett</field>
    <field alias="position">Author</field>
    <field alias="company">Canterbury Book</field>
    <field alias="email">[email protected]</field>
  </log>
  <log time="16:59:16" date="09/09/2010">
    <field alias="name">Winston Churchil</field>
    <field alias="position">PM</field>
    <field alias="company">No 10 Downing Street</field>
    <field alias="email">[email protected]</field>
  </log>
</logs>

logs.xsl

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output omit-xml-declaration="no"/>
    <xsl:template match="/">
        <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
        <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
            <Styles>
                <Style ss:ID="header" ss:Name="Normal">
                    <Font ss:FontName="Verdana" ss:Bold="1" />
                </Style>
            </Styles>
            <Worksheet ss:Name="Sheet1">
                <Table>
                    <Row ss:Index="1">
                        <Cell ss:Index="1" ss:StyleID="header">
                            <Data ss:Type="String">Date</Data>
                        </Cell>
                        <Cell ss:Index="2" ss:StyleID="header">
                            <Data ss:Type="String">Time</Data>
                        </Cell>
                        <Cell ss:Index="3" ss:StyleID="header">
                            <Data ss:Type="String">Name</Data>
                        </Cell>
                        <Cell ss:Index="4" ss:StyleID="header">
                            <Data ss:Type="String">Position</Data>
                        </Cell>
                        <Cell ss:Index="5" ss:StyleID="header">
                            <Data ss:Type="String">Company</Data>
                        </Cell>
                        <Cell ss:Index="6" ss:StyleID="header">
                            <Data ss:Type="String">Email</Data>
                        </Cell>
                    </Row>
                     <xsl:for-each select="//log">
                         <Row ss:Index="{position() + 1}">
                             <Cell ss:Index="1">
                                 <Data ss:Type="String"><xsl:value-of select="@date"/></Data>
                             </Cell>
                             <Cell ss:Index="2">
                                 <Data ss:Type="String"><xsl:value-of select="@time"/></Data>
                             </Cell>
                             <Cell ss:Index="3">
                                 <Data ss:Type="String"><xsl:value-of select="field[@alias = 'name']"/></Data>
                             </Cell>
                             <Cell ss:Index="4">
                                 <Data ss:Type="String"><xsl:value-of select="field[@alias = 'position']"/></Data>
                             </Cell>
                             <Cell ss:Index="5">
                                 <Data ss:Type="String"><xsl:value-of select="field[@alias = 'company']"/></Data>
                             </Cell>
                             <Cell ss:Index="6">
                                 <Data ss:Type="String"><xsl:value-of select="field[@alias = 'email']"/></Data>
                             </Cell>
                         </Row>
                     </xsl:for-each>
                </Table>
            </Worksheet>
            </Workbook>
    </xsl:template>
</xsl:stylesheet>

Must be doing something wrong here.

Created a test page and added a gridview and xml datasource. Added the XML and XSL and got:

The data source for GridView with id 'GridView1' did not have any properties or attributes from which to generate columns.  Ensure that your data source has content.

So I simplified the structure getting rid of the aliases (in order to see the problem:

XML File:

<?xml version="1.0" encoding="utf-8" ?>
<?xml-stylesheet type="text/xsl" href="logs.xsl"?>
<logs>
  <log time="16:08:30" date="09/09/2010">
    <Name>Dave Smith</Name>
    <Position>Managing Director</Position>
    <Company>Ford Motors</Company>
    <Email>[email protected]</Email>
  </log>
  <log time="16:56:46" date="09/09/2010">
    <Name>Terry Prachett</Name>
    <Position>Author</Position>
    <Company>Canterbury Book</Company>
    <Email>[email protected]</Email>
  </log>
  <log time="16:59:16" date="09/09/2010">
    <Name>Winston Churchil</Name>
    <Position>PM</Position>
    <Company>No 10 Downing Street</Company>
    <Email>[email protected]</Email>
  </log>
</logs>

XSL File:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output omit-xml-declaration="no"/>
  <xsl:template match="/">
    <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
      <Styles>
        <Style ss:ID="header" ss:Name="Normal">
          <Font ss:FontName="Verdana" ss:Bold="1" />
        </Style>
      </Styles>
      <Worksheet ss:Name="Sheet1">
        <Table>
          <Row ss:Index="1">
            <Cell ss:Index="1" ss:StyleID="header">
              <Data ss:Type="String">Date</Data>
            </Cell>
            <Cell ss:Index="2" ss:StyleID="header">
              <Data ss:Type="String">Time</Data>
            </Cell>
            <Cell ss:Index="3" ss:StyleID="header">
              <Data ss:Type="String">Name</Data>
            </Cell>
            <Cell ss:Index="4" ss:StyleID="header">
              <Data ss:Type="String">Position</Data>
            </Cell>
            <Cell ss:Index="5" ss:StyleID="header">
              <Data ss:Type="String">Company</Data>
            </Cell>
            <Cell ss:Index="6" ss:StyleID="header">
              <Data ss:Type="String">Email</Data>
            </Cell>
          </Row>
          <xsl:for-each select="//log">
            <Row ss:Index="{position() + 1}">
              <Cell ss:Index="1">
                <Data ss:Type="String">
                  <xsl:value-of select="@date"/>
                </Data>
              </Cell>
              <Cell ss:Index="2">
                <Data ss:Type="String">
                  <xsl:value-of select="@time"/>
                </Data>
              </Cell>
              <Cell ss:Index="3">
                <Data ss:Type="String">
                  <xsl:value-of select="@Name"/>
                </Data>
              </Cell>
              <Cell ss:Index="4">
                <Data ss:Type="String">
                  <xsl:value-of select="@Position"/>
                </Data>
              </Cell>
              <Cell ss:Index="5">
                <Data ss:Type="String">
                  <xsl:value-of select="@Company"/>
                </Data>
              </Cell>
              <Cell ss:Index="6">
                <Data ss:Type="String">
                  <xsl:value-of select="@Email"/>
                </Data>
              </Cell>
            </Row>
          </xsl:for-each>
        </Table>
      </Worksheet>
    </Workbook>
  </xsl:template>
</xsl:stylesheet>

Still no luck>

This editor is pants, had to hand code this html.

I've never tried binding XML to a GridView, so not sure how to go about that. The logs.xsl transforms directly to SpreadsheetML so I don't think it will work with the GridView. If you open logs.xml in VS2008 and click the "Show XSLT Output" button, you should get the final XML ready for Excel.

If you want to do it inside a user control, it could work like this:

XPathDocument xml = new XPathDocument(@"C:\logs.xml");

XslCompiledTransform xsl = new XslCompiledTransform();

xsl.Load(@"C:\logs.xsl");

XmlWriterSettings settings = new XmlWriterSettings();

settings.OmitXmlDeclaration = false;

XmlWriter writer = XmlWriter.Create("output.xml", settings);

xsl.Transform(xml, null, writer) ;

writer.Close();

Thanks I have a go and get back to you.

It would be great to hear if the above works. Here's an alternative:

<msxml:script language="CSharp" implements-prefix="gecko">
    <msxml:assembly name="System.Web" />
    <msxml:using namespace="System.Web" />

    <![CDATA[
public void changeOutPut(String ContentType, String FileName) {
        HttpContext.Current.Response.ContentType = ContentType;
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + FileName);
}
]]>
</msxml:script>


<xsl:template match="/">
    <xsl:variable name="fileName" select="concat('filename-',$someVar,'.xls')" />
    <xsl:value-of select="gecko:changeOutPut('application/msexcel',$fileName)" /> <!-- Issue description of Excel warning... http://forums.asp.net/p/1070490/1560113.aspx -->
    <table>
        <tr>
            <td></td>
            <td></td>
        </tr>
        ...
    </table>
</xsl:template>

You can simply output your data as a table and set the content type of the download to application/msexcel like in the above code. What you're trying above defintely seems less hacky, but this could be an option if that doesn't work for ya.

This uses code developed by Dan and is available as a download here.

Cheers,
Nik

Sorry guys.

I'm struggling with this. I just don't know the syntax of XSLT and I am getting silly errors. Any chance of a completed file so that I can see it in action.

Thanks again.

Try this. You'll need to change the path to your log file:

1. Create a new, empty Document Type to output your spreadsheet (also choose to create a new template)

2. In the new template, add this

<%@ Master Language="C#" MasterPageFile="~/umbraco/masterpages/default.master" AutoEventWireup="true" %>
<asp:Content ContentPlaceHolderID="ContentPlaceHolderDefault" runat="server"><umbraco:Macro Alias="CreateLog" runat="server" /></asp:Content>

3. Create a new XSLT (choose to create macro as well) called CreateLog with an alias of CreateLog. Remember to change the path to your log file. (Thanks to dandrayne for the code to set content-disposition to force download)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xsl:stylesheet [ <!ENTITY nbsp "&#x00A0;"> ]>
<xsl:stylesheet
    version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxml="urn:schemas-microsoft-com:xslt"
    xmlns:ss="schemas-microsoft-com:office:spreadsheet"
    xmlns:gecko="urn:gecko-com:xslt"
    xmlns:umbraco.library="urn:umbraco.library"
    exclude-result-prefixes="umbraco.library msxml">
    
    <xsl:output method="xml" omit-xml-declaration="yes"/>
    
    <msxml:script language="CSharp" implements-prefix="gecko">
        <msxml:assembly name="System.Web" />
        <msxml:using namespace="System.Web" />
        <![CDATA[
            public void changeOutPut(String ContentType, String FileName) {
                HttpContext.Current.Response.ContentType = ContentType;
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + FileName);
            }
        ]]>
    </msxml:script>
    
    
    <xsl:param name="currentPage"/>
    <xsl:variable name="logFile" select="document('C:\Websites\Umbraco\data\contact_form_log.xml')/logs" />
    
    <!-- Template: / -->
    <xsl:template match="/">
      <xsl:value-of select="gecko:changeOutPut('text/xml','log.xml')" />
        <xsl:text disable-output-escaping="yes">&lt;?xml version="1.0" encoding="UTF-8"?&gt;</xsl:text>
        <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
        
        <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
            <Styles>
                <Style ss:ID="header" ss:Name="Normal">
                    <Font ss:FontName="Verdana" ss:Bold="1" />
                </Style>
            </Styles>
            <Worksheet ss:Name="Sheet1">
                <Table>
                    <Row ss:Index="1">
                        <Cell ss:Index="1" ss:StyleID="header">
                            <Data ss:Type="String">Date</Data>
                        </Cell>
                        <Cell ss:Index="2" ss:StyleID="header">
                            <Data ss:Type="String">Time</Data>
                        </Cell>
                        <Cell ss:Index="3" ss:StyleID="header">
                            <Data ss:Type="String">Name</Data>
                        </Cell>
                        <Cell ss:Index="4" ss:StyleID="header">
                            <Data ss:Type="String">Position</Data>
                        </Cell>
                        <Cell ss:Index="5" ss:StyleID="header">
                            <Data ss:Type="String">Company</Data>
                        </Cell>
                        <Cell ss:Index="6" ss:StyleID="header">
                            <Data ss:Type="String">Email</Data>
                        </Cell>
                    </Row>
                    <xsl:for-each select="$logFile/log">
                        <Row ss:Index="{position() + 1}">
                            <Cell ss:Index="1">
                                <Data ss:Type="String"><xsl:value-of select="@date"/></Data>
                            </Cell>
                            <Cell ss:Index="2">
                                <Data ss:Type="String"><xsl:value-of select="@time"/></Data>
                            </Cell>
                            <Cell ss:Index="3">
                                <Data ss:Type="String"><xsl:value-of select="field[@alias = 'name']"/></Data>
                            </Cell>
                            <Cell ss:Index="4">
                                <Data ss:Type="String"><xsl:value-of select="field[@alias = 'position']"/></Data>
                            </Cell>
                            <Cell ss:Index="5">
                                <Data ss:Type="String"><xsl:value-of select="field[@alias = 'company']"/></Data>
                            </Cell>
                            <Cell ss:Index="6">
                                <Data ss:Type="String"><xsl:value-of select="field[@alias = 'email']"/></Data>
                            </Cell>
                        </Row>
                    </xsl:for-each>
                </Table>
            </Worksheet>
        </Workbook>
    </xsl:template>
</xsl:stylesheet>

4. Create a page with your new document type and browse to it. It will save an XML file that should open in Excel 2007-2010.

There's also a package on CodePlex called EPPlus for generating Excel docs if you're not working in XSLT

http://epplus.codeplex.com/

Thanks Guys.

Will try this at the weekend and report back.

Hi Jc & Streety,

         Thanks for this posting, it helped me a lot i was wondering a lot finally i got ur post and succeeded my project thanx once again to u both............

Regards,

Gopinath V.

is working on a reply...