473,372 Members | 1,246 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Export DataSet to XML for MS Excel?

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! :)
Aug 25 '08 #1
4 9578
jp2msft wrote:
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");

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Aug 25 '08 #2
On Aug 25, 12:52*pm, jp2msft <jp2m...@discussions.microsoft.com>
wrote:
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)
Aug 25 '08 #3

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" <jp*****@discussions.microsoft.comwrote in message
news:CC**********************************@microsof t.com...
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! :)

Aug 25 '08 #4
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. :)

"Ignacio Machin ( .NET/ C# MVP )" wrote:
On Aug 25, 12:52 pm, jp2msft <jp2m...@discussions.microsoft.com>
wrote:
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)
Aug 25 '08 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Maria L. | last post by:
Hi, I need to export the content of a DataGrid (in Windows application in C#), into an Excel spreadsheet. Anyone knows how to do this? Any code snippets would help! thanks a lot, Maria
4
by: Hans [DiaGraphIT] | last post by:
Hi! I want to export a dataset to an excel file. I found following code on the net... ( http://www.codeproject.com/csharp/Export.asp ) Excel.ApplicationClass excel = new ApplicationClass();...
1
by: Kevin Blakeley | last post by:
I know this was just posted but I did not want this message to get lost in the other thread as it's slightly different. Yes I want to export my dataset to excel for my clients, but I don't want...
1
by: ad | last post by:
I used the code below to export a table in a dataset to Excel. It can export only on table at a time. Can export more than on table in a dataset to Excel public static void...
4
by: John Z. | last post by:
I have read numerous articles and postings on various approaches to exporting a DataSet to excel while avoiding the use of automation. I found the most performant approach to be assigning a...
3
by: Scott M. Lyon | last post by:
I'm trying to figure out a way to export data (actually the result of a Stored Procedure call from SQL Server) into a specified Excel spreadsheet format. Currently, I have the data read into a...
3
by: Sachin Salgarkar | last post by:
I have a DataSet that I need to export to Excel. The dataset has multiple tables. I need a way to export the complete dataset to a single Excel Workbook with sheets for each table in the dataset....
1
by: DennisBetten | last post by:
First of all, I need to give some credit to Mahesh Chand for providing me with an excellent basis to export data to excel. What does this code do: As the title says, this code is capable of...
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.