Check my blog:
http://spaces.msn.com/sholliday/
9/22/2005
You need to do an XML to XML transformation.
The format of the Excel XML is .... unique. It also has namespaces. So
there isn't a super trivial example.
I have some code below. I think it will work, but it will definately get
you going.
It took me about 2 weeks to figure this out, so post a 'thank you' please,
(and any issues if you run into them)
Ok..
Create an excel file.
3 columns, the first row is the header.
uid,lastname,fi rstname
Add like 4 more rows of real data ( "123","Smith"," John" .. like that)
Save as XML.
I have the C# code, which will show how to apply the XSL to the XML.
There is xsl also. Hopefully, it won't get screwed up
///////////////// START C# code
/// <summary>
/// Summary description for XMLtoXSLTransfo rmWrapper.
/// </summary>
public class XMLtoXSLTransfo rmWrapper
{
string debugMsg=null;
public void DoTranslation(s tring xmlFile, string xslFile, string
outputFile)
{
try
{
//Create a new XslTransform object.
XslTransform xslt = new XslTransform();
//Load the stylesheet.
xslt.Load(xslFi le);
//Create a new XPathDocument and load the XML data to be transformed.
XPathDocument mydata = new XPathDocument(x mlFile);
//Create an XmlTextWriter which outputs to the console.
//XmlWriter writer = new XmlTextWriter(C onsole.Out);
//Transform the data and send the output to the console.
//xslt.Transform( mydata,null,wri ter, null);
xslt.Transform (xmlFile, outputFile);
}
catch (Exception ex)
{
debugMsg = ex.Message;
Console.WriteLi ne (debugMsg);
}
}
public XMLtoXSLTransfo rmWrapper()
{
//
// TODO: Add constructor logic here
//
}
}
}
//END C#
//START XSL
<xsl:styleshe et version="1.0"
xmlns:xsl="http ://www.w3.org/1999/XSL/Transform"
xmlns:o="urn:sc hemas-microsoft-com:office:offi ce"
xmlns:x="urn:sc hemas-microsoft-com:office:exce l"
xmlns:ss="urn:s chemas-microsoft-com:office:spre adsheet" >
<xsl:template match="/">
<OneWellDefined DS>
<!--<myRootPosition ><xsl:value-of select="positio n()"/></myRootPosition>-->
<xsl:apply-templates select="ss:Work book"/>
</OneWellDefinedD S>
</xsl:template>
<xsl:template match="ss:Workb ook">
<!--<myWorkbookPosi tion><xsl:value-of
select="positio n()"/></myWorkbookPosit ion>-->
<xsl:apply-templates select="ss:Work sheet"/>
</xsl:template>
<xsl:template match="ss:Works heet">
<!--<myWorksheetPos ition><xsl:valu e-of
select="positio n()"/></myWorksheetPosi tion>-->
<xsl:choose>
<xsl:when test="position( )=1">
<xsl:apply-templates select="ss:Tabl e"/>
</xsl:when>
<xsl:otherwis e>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template match="ss:Table ">
<!--<myTablePositio n><xsl:value-of
select="positio n()"/></myTablePosition >-->
<xsl:apply-templates select="ss:Row"/>
</xsl:template>
<xsl:template match="ss:Row">
<!--<myRowPosition> <xsl:value-of select="positio n()"/></myRowPosition>-->
<xsl:choose>
<xsl:when test="position( )=1">
</xsl:when>
<xsl:otherwis e>
<customerInfo >
<!--<myPosition><xs l:value-of select="positio n()"/></myPosition>-->
<xsl:apply-templates select="ss:Cell "/>
</customerInfo>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template match="ss:Cell" >
<!--<myCellPosition ><xsl:value-of
select="$varCur rentAttributePo sition"/></myCellPosition>-->
<xsl:choose>
<xsl:when test="position( )=1">
<xsl:call-template name="xuid" />
</xsl:when>
<xsl:when test="position( )=2">
<xsl:call-template name="xlastname " />
</xsl:when>
<xsl:when test="position( )=3">
<xsl:call-template name="xfirstnam e" />
</xsl:when>
<xsl:otherwis e>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="xuid">
<uid><xsl:val ue-of select="."/></uid>
</xsl:template>
<xsl:template name="xlastname ">
<lastname><xsl: value-of select="."/></lastname>
</xsl:template>
<xsl:template name="xfirstnam e">
<firstname><xsl :value-of select="."/></firstname>
</xsl:template>
</xsl:stylesheet>
