Excel XML Import Problem

See more:

C#

XML

VSTO

DataTable

Hey folks!

I stuck on this problem now for a few days and i don't get the error.

I have to datasets. Each of the dataset hold one datatable. So i have two datatable which holds very simple data.

Now i want to import table1 on sheet1 and table2 on sheet2, of my excel solution. But it always import table1 and table2 on sheet1 and merge them.

Can anybody give me a tip or can tell me how to import two tables on two sheets?!?!?

Thanks in advance.

Greets James

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
namespace TestExcelWorkbook
{
    public partial class ThisWorkbook
    {
        private DataSet Set1 = new DataSet("Set1");
        public System.Data.DataTable Table1 { get; set; }
        private DataSet Set2 = new DataSet("Set2");
        public System.Data.DataTable Table2 { get; set; }
        private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
            Table1 = Set1.Tables.Add("StreamUsageData");
            Table1.Columns.Add(new DataColumn("Field1", typeof(string)));
            Table1.Columns.Add(new DataColumn("Field2", typeof(string)));
            Table2 = Set2.Tables.Add("StreamsPerClient");
            Table2.Columns.Add(new DataColumn("Field1", typeof(string)));
            Table2.Columns.Add(new DataColumn("Field2", typeof(string)));
            DataRow dr = null;
            for (int i = 0; i < 100; i++)
            {
                dr = Table1.NewRow();
                dr[0] = "- " + i.ToString();
                dr[1] = "Table 1 - " + i.ToString();
                Table1.Rows.Add(dr);
            }
            for (int i = 0; i < 100; i++)
            {
                dr = Table1.NewRow();
                dr[0] = "-- " + i.ToString();
                dr[1] = "Table 2 - " + i.ToString();
                Table1.Rows.Add(dr);
            }
            Excel.XmlMap map1 = this.XmlMaps.Add(Set1.GetXmlSchema(), Set1.DataSetName);
            Excel.XmlMap map2 = this.XmlMaps.Add(Set2.GetXmlSchema(), Set2.DataSetName);
            //define range for stream usage
            Excel.Range range1 = Globals.Tabelle1.Range["A1", missing];
            //define range for streams per client
            Excel.Range range2 = Globals.Tabelle2.Range["A1", missing];
            this.XmlImportXml(Set1.GetXml(), out map1, true, range1);
            this.XmlImportXml(Set2.GetXml(), out map2, true, range2);
        }
        private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
        {
        }
        #region VSTO Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisWorkbook_Startup);
            this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown);
        }
        #endregion
    }
}