XmlSerializer, C# , Excel, XML and SpreadsheetML

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am increasingly frustrated and exasperated.

I am trying to generate SpreadsheetML using C#. CSV won't do because I need
formatting.

I have the Office 2003 XSD files but xsd.exe doesn't want to know about
them. So I am trying to hand construct a basic "hello world" spreadsheet
using XmlSerializer.

I have a number of problems:

1) XmlSerializer's handling of ICollection derived classes is horrible:
I need to generate something like this (some things omitted for clarity):
<Table>
<Row>
<Cell>blah blah blah</Cell>
<Cell>blah blah blah</Cell>
</Row>
<Row>
<Cell>blah blah blah</Cell>
<Cell>blah blah blah</Cell>
</Row>
</Table>

Putting
class Table
{
public Row []Rows;
}

class Row
{
public int something;
public Cell []Cells;
}

doesn't work, because that produces unwanted <Rows> and <Cells> tags. Using
ICollection as a base for TableType and RowType almost works, but insists on
putting <ArrayOfCell>, won't allow any metadata on Row and refuses to
seriaize the other attributes of Row.


2) XmlSerializer does not allow me to put xmlns="something" and
xmlns:ss="something"

3) XmlSerializer does not like the Style tag. Excel seems to insist that the
XML be <Style ss:ID="something">
rather than
<ss:Style ID="something">
which is what XmlSerializer wants to produce. There is no way (unless I am
mistaken) of forcing the ss: onto the ID.

4) It is unclear how to perform the following
<Cell>
<Data ss:Type="String">world</Data>
</Cell>

I can put
class Cell
{
public DataType Data;
}

class DataType
{
[XmlAttribute]
public string Type;

}

but then how do I get the actual data into the XML (in this case the word
"world")

5) The help built into Visual Studio 2003 is not up to Microsoft's usual
standard:
Example code of Xml Serialization should include the resulting Xml in the
help file. Duh!

6) It should be easy to work with Microsoft XML format using Microsoft
development tools. The fact that it isn't is very frustrating.

7) There is example code in C++, but none in C#.
 
Hi,

The XmlSerializer is more flexible than most thing, it just takes a bit of
exploring to discover its little tricks. I have not looked at the Office xml
format so the following is based on what I understood from your post.

1) To get the arrays to emit the correct xml tags you can add the
XmlElementAttribute to the array and give the approriate name
2) For this you need to create an XmlSerializerNamespaces and add the
namespace and pass this instance to the call to Serialize
3) To force the namespace prefix on the attribute add the
XmlAttributeAttribute to the class member and set the Namespace property of
the XmlAttributeAttribute to be the namespace of the required prefix. Note
you must use the full namespace not the prefix.

The following demonstrates these principals:

Call to perform the serialization, this assumes the classes have been
populated and the root class Table instance is called 'tbl'.

XmlSerializerNamespaces xns = new XmlSerializerNamespaces();
xns.Add("ss", "urn:schemas-microsoft-com:office:spreadsheet");

XmlSerializer xs = new XmlSerializer(typeof(Table));
StringWriter wr = new StringWriter();
xs.Serialize(wr, tbl, xns);

Class Definitions to obtain required XML structure, this is the bare
minimum!

public class Table
{
[XmlElement("Row")] // Treat array as a flat list of elements called
Row
public Row[] Rows;
}

public class Row
{
[XmlElement("Cell")] // Treat array as a flat list of elements called
Cell
public Cell[] Cells;
}

public class Cell
{
public Data Data;
}

public class Data
{
[XmlAttribute(Namespace="urn:schemas-microsoft-com:office:spreadsheet")]
// Write as attribute with namespace prefix
public string Type;
[XmlText] // Write as a value rather than an element or attribute
public string Value;
}


Hope this helps
--
Chris Taylor
http://dotnetjunkies.com/weblog/chris.taylor


Chispa said:
I am increasingly frustrated and exasperated.

I am trying to generate SpreadsheetML using C#. CSV won't do because I need
formatting.

I have the Office 2003 XSD files but xsd.exe doesn't want to know about
them. So I am trying to hand construct a basic "hello world" spreadsheet
using XmlSerializer.

I have a number of problems:

1) XmlSerializer's handling of ICollection derived classes is horrible:
I need to generate something like this (some things omitted for clarity):
<Table>
<Row>
<Cell>blah blah blah</Cell>
<Cell>blah blah blah</Cell>
</Row>
<Row>
<Cell>blah blah blah</Cell>
<Cell>blah blah blah</Cell>
</Row>
</Table>

Putting
class Table
{
public Row []Rows;
}

class Row
{
public int something;
public Cell []Cells;
}

doesn't work, because that produces unwanted <Rows> and <Cells> tags. Using
ICollection as a base for TableType and RowType almost works, but insists on
putting <ArrayOfCell>, won't allow any metadata on Row and refuses to
seriaize the other attributes of Row.


2) XmlSerializer does not allow me to put xmlns="something" and
xmlns:ss="something"

3) XmlSerializer does not like the Style tag. Excel seems to insist that the
XML be <Style ss:ID="something">
rather than
<ss:Style ID="something">
which is what XmlSerializer wants to produce. There is no way (unless I am
mistaken) of forcing the ss: onto the ID.

4) It is unclear how to perform the following
<Cell>
<Data ss:Type="String">world</Data>
</Cell>

I can put
class Cell
{
public DataType Data;
}

class DataType
{
[XmlAttribute]
public string Type;

}

but then how do I get the actual data into the XML (in this case the word
"world")

5) The help built into Visual Studio 2003 is not up to Microsoft's usual
standard:
Example code of Xml Serialization should include the resulting Xml in the
help file. Duh!

6) It should be easy to work with Microsoft XML format using Microsoft
development tools. The fact that it isn't is very frustrating.

7) There is example code in C++, but none in C#.
Click to expand...
 
Hi


If your having problems with XSD.exe, then I suggest you look at Liquid

XML

http://www.liquid-technologies.com/


It supports C#, java, C++ & Visual Basic. It also supports more of the
XSD standard than any of the other tools out there - including all the
complex namespaces in the office standard.


Regards


Simon
 
Back
Top