Export DataSet to XML for MS Excel?

[jp2msft]

I've got a DataSet that I save as XML using the DataSet DataTable's WriteXml
method.

If I say XmlWriteMode.IgnoreSchema, it shows up great in Excel, but I can
not reopen the file in my application because there is no schema for it.

On the other hand, I can choose XmlWriteMode.WriteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet, ns1:IsDataSet=TRUE, etc. If I open this file in Notepad, my
data is there, bunched between a lot of this difficult to read other info.

Is there some common ground that I am missing?

I write the XML file using this:

using (XmlTextWriter xw = new XmlTextWriter(m_filename, Encoding.UTF8)) {
foreach (DataTable table in ds.Tables) {
table.WriteXml(xw, XmlWriteMode.WriteSchema);
//table.WriteXml(xw, XmlWriteMode.IgnoreSchema);
}
}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(openFileDlg1.FileName, XmlReadMode.ReadSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultView;
}

There is typically only one (1) table in the view, but my use of foreach
prevents errors while reminding me of the possibilities!

 

[Martin Honnen]

I've got a DataSet that I save as XML using the DataSet DataTable's WriteXml
method.

If I say XmlWriteMode.IgnoreSchema, it shows up great in Excel, but I can
not reopen the file in my application because there is no schema for it.

On the other hand, I can choose XmlWriteMode.WriteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet, ns1:IsDataSet=TRUE, etc. If I open this file in Notepad, my
data is there, bunched between a lot of this difficult to read other info.

Is there some common ground that I am missing?

I write the XML file using this:

using (XmlTextWriter xw = new XmlTextWriter(m_filename, Encoding.UTF8)) {
foreach (DataTable table in ds.Tables) {
table.WriteXml(xw, XmlWriteMode.WriteSchema);
//table.WriteXml(xw, XmlWriteMode.IgnoreSchema);
}
}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(openFileDlg1.FileName, XmlReadMode.ReadSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultView;
}

There is typically only one (1) table in the view, but my use of foreach
prevents errors while reminding me of the possibilities!

I am not sure why you iterate over the tables to write out the DataSet.
DataSet has its own WriteXml method and also a WriteXmlSchema method.
And if you use
ds.WriteXml("file.xml");
then you should be able to read that in with
ds.ReadXml("file.xml");
too.
If the schema does make problems with Excel then you could write it out
as a separate document with
ds.WriteXmlSchema("schema.xsd");
ds.WriteXml("file.xml");
and read it back in with e.g.
ds.ReadXmlSchema("schema.xsd");
ds.ReadXml("file.xml");

 

[Ignacio Machin ( .NET/ C# MVP )]

I've got a DataSet that I save as XML using the DataSet DataTable's WriteXml
method.

If I say XmlWriteMode.IgnoreSchema, it shows up great in Excel, but I can
not reopen the file in my application because there is no schema for it.

On the other hand, I can choose XmlWriteMode.WriteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet, ns1:IsDataSet=TRUE, etc. If I open this file in Notepad, my
data is there, bunched between a lot of this difficult to read other info..

Is there some common ground that I am missing?

I write the XML file using this:

using (XmlTextWriter xw = new XmlTextWriter(m_filename, Encoding.UTF8)){
  foreach (DataTable table in ds.Tables) {
    table.WriteXml(xw, XmlWriteMode.WriteSchema);
    //table.WriteXml(xw, XmlWriteMode.IgnoreSchema);
  }

}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(openFileDlg1.FileName, XmlReadMode.ReadSchema);
foreach (DataTable table in ds.Tables) {
  dgv.DataSource = table.DefaultView;

}

There is typically only one (1) table in the view, but my use of foreach
prevents errors while reminding me of the possibilities!

Hi,

Have you tried to export it to CSV instead?
CSV is readable by Excel (most of the cases Excel is teh default
program for .CSV files)

 

[sloan]

Here is a food for thought post:
http://sholliday.spaces.live.com/blog/cns!A68482B9628A842A!148.entry

The xml generated by the WriteXml method...can be transformed to anything
you like.

.......

 

[jp2msft]

A CSV option is included in the SaveAs Dialog Box for the Clients to select
from.

I'm just trying to figure out a little of this XML stuff.