Excel (in XML format) to a dataset

[sandeshmeda]

I have a XML file that is basically a Excel file saved as XML. I need
to be able to change the XML into a different format.

I was thinking one approach would be to:

1. Populate the dataset based on the XML file I get from excel
2. Use a WriteXML to create a XML file in the format I need.

Will this work? How do I get started on this? Are there any other
alternate ways of doing this?

TIA

 

[Darren Kopp]

I'm unfamiliar with the format that XML saves in, so if you could post
some it that would help. As for the WriteXML, it just writes out the
data in the data table (which may be the same format as what you
imported, I have not tried this).

You could try changing the schema or loading a new schema on the
dataset and then calling the WriteXML Method, but I don't know if that
would work. When I have needed to control the format of an XML
document, I have written up my own logic to parse and write the xml
file.

I answered another DataSet question about writing xml here
(http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/msg/ce816696cabf590e)
but I don't know if that will help you out.

I would model the format you want (with lists of CollectionBase) and
then parse the xml data and place it into the appropriate location,
then write out the data.

Really hope this helps you out somehow,
Darren Kopp
http://blog.secudocs.com/

 

[sloan]

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,firstname

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 XMLtoXSLTransformWrapper.
/// </summary>
public class XMLtoXSLTransformWrapper
{

string debugMsg=null;

public void DoTranslation(string xmlFile, string xslFile, string
outputFile)
{

try
{

//Create a new XslTransform object.
XslTransform xslt = new XslTransform();

//Load the stylesheet.
xslt.Load(xslFile);

//Create a new XPathDocument and load the XML data to be transformed.
XPathDocument mydata = new XPathDocument(xmlFile);

//Create an XmlTextWriter which outputs to the console.
//XmlWriter writer = new XmlTextWriter(Console.Out);

//Transform the data and send the output to the console.
//xslt.Transform(mydata,null,writer, null);
xslt.Transform (xmlFile, outputFile);
}
catch (Exception ex)
{
debugMsg = ex.Message;
Console.WriteLine (debugMsg);

}


}



public XMLtoXSLTransformWrapper()
{
//
// TODO: Add constructor logic here
//
}
}
}






//END C#

//START XSL









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



<xsl:template match="/">
<OneWellDefinedDS>
<!--<myRootPosition><xsl:value-of select="position()"/></myRootPosition>-->
<xsl:apply-templates select="ss:Workbook"/>
</OneWellDefinedDS>
</xsl:template>


<xsl:template match="ss:Workbook">
<!--<myWorkbookPosition><xsl:value-of
select="position()"/></myWorkbookPosition>-->

<xsl:apply-templates select="ss:Worksheet"/>

</xsl:template>


<xsl:template match="ss:Worksheet">
<!--<myWorksheetPosition><xsl:value-of
select="position()"/></myWorksheetPosition>-->


<xsl:choose>
<xsl:when test="position()=1">

<xsl:apply-templates select="ss:Table"/>

</xsl:when>


<xsltherwise>


</xsltherwise>

</xsl:choose>





</xsl:template>


<xsl:template match="ss:Table">
<!--<myTablePosition><xsl:value-of
select="position()"/></myTablePosition>-->
<xsl:apply-templates select="ss:Row"/>

</xsl:template>


<xsl:template match="ss:Row">
<!--<myRowPosition><xsl:value-of select="position()"/></myRowPosition>-->


<xsl:choose>
<xsl:when test="position()=1">

</xsl:when>


<xsltherwise>
<customerInfo>
<!--<myPosition><xsl:value-of select="position()"/></myPosition>-->

<xsl:apply-templates select="ss:Cell"/>
</customerInfo>

</xsltherwise>

</xsl:choose>


</xsl:template>


<xsl:template match="ss:Cell">




<!--<myCellPosition><xsl:value-of
select="$varCurrentAttributePosition"/></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="xfirstname" />
</xsl:when>

<xsltherwise>

</xsltherwise>

</xsl:choose>

</xsl:template>


<xsl:template name="xuid">
<uid><xsl:value-of select="."/></uid>
</xsl:template>

<xsl:template name="xlastname">
<lastname><xsl:value-of select="."/></lastname>
</xsl:template>

<xsl:template name="xfirstname">
<firstname><xsl:value-of select="."/></firstname>
</xsl:template>




</xsl:stylesheet>

 

[kahtava]

What format would you like to change your XML file into?

Are you converting from XML to Excel file?
If so; just open your XML file in Excel, Excel will display the XML
file as an Excel sheet.

Your approach sounds feasible; the dataset will allow you to loop
through the tables, rows and columns. You can then generate a file
(StreamWriter) with your desired format / extension.