xml to microsoft excel

Streety 358 posts 568 karma points

Sep 09, 2010 @ 18:40

XSLT

Hi, Am a newbie to XSLT and the like and I would like to know how to take an XML file and convert it to say XLS for import into MS Excel.

I have been using the Warren Buckly CWS and would like to use the contact_form_log.xml to capture email addresses and names for ultimate import into a Outlook Distribution List.

The XML file looks like this:

<?xml version="1.0" encoding="utf-8" ?>

- <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="company">No 10 Downing Street</field>

<field alias="email">[email protected]</field>

</log>

</logs>

Would appreciate any pointers.

Copy Link

Sascha Wolter 615 posts 1101 karma points

Sep 09, 2010 @ 20:49

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

Copy Link

Streety 358 posts 568 karma points

Sep 10, 2010 @ 12:22

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 :

xmlns
urn:schemas-microsoft-com:office:office
urn:schemas-microsoft-com:office:office
urn:schemas-microsoft-com:office:excel

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.

Copy Link

jc 64 posts 101 karma points

Sep 10, 2010 @ 23:22

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>

Copy Link

Streety 358 posts 568 karma points

Sep 13, 2010 @ 11:21

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.

Copy Link

jc 64 posts 101 karma points

Sep 13, 2010 @ 18:54

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();

Copy Link

Streety 358 posts 568 karma points

Sep 16, 2010 @ 19:12

Thanks I have a go and get back to you.

Copy Link

Nik Wahlberg 639 posts 1237 karma points MVP

Sep 16, 2010 @ 19:32

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

Copy Link

Streety 358 posts 568 karma points

Sep 20, 2010 @ 09:47

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.

Copy Link

jc 64 posts 101 karma points

Sep 20, 2010 @ 16:26

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.

Copy Link

jc 64 posts 101 karma points

Sep 20, 2010 @ 16:35

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

http://epplus.codeplex.com/

Copy Link

Streety 358 posts 568 karma points

Sep 22, 2010 @ 14:47

Thanks Guys.

Will try this at the weekend and report back.

Copy Link

Gopinath 1 post 21 karma points

Feb 21, 2012 @ 09:11

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.

Copy Link

is working on a reply...

Flag this post as spam?

XML to Microsoft Excel