none
Multiple XmlImport RRS feed

  • Question

  • Hey folks!

     

    I'm trying to import some XML data in my excel project.

    When I import only one XML data set, everything works fine. BUT!!! When I want to import a second XML data set, in a different range, it imports it in the same range as the first import.

    //creates the empty datasets and tables
     this.CreateDataTables();
    
     //fills the datatables
     this.FillDataTables(xe);
    
    //specify the XmlMaps
    Excel.XmlMap xmlmStreamUsage = this.XmlMaps.Add(StreamUsageDataSet.GetXmlSchema(), StreamUsageDataSet.DataSetName);
    Excel.XmlMap xmlmStreamsPerClient = this.XmlMaps.Add(StreamsPerClientDataSet.GetXmlSchema(), StreamsPerClientDataSet.DataSetName);
             
    //clean up all necessary sheets
    this.CleanSheets();
    
    //define range for stream usage            
    Excel.Range rangeStreamUsage = Globals.Tabelle1.Range["A1", missing];
    //define range for streams per client
    Excel.Range rangeStreamsPerClient = Globals.Tabelle3.Range["$A$1", missing];
                           
    //import the data
    this.XmlImportXml(StreamUsageDataSet.GetXml(), out xmlmStreamUsage, false, rangeStreamUsage);
    
    this.XmlImportXml(StreamsPerClientDataSet.GetXml(), out xmlmStreamsPerClient, false, rangeStreamsPerClient);
    
    //creates all pivot tables and graphs
    this.CreatePivotTablesAndGraphs();        
    

     

    Would be awesome if anybody can help me... i stuck on this problem now for a few days, i don't find a way to fix this problem...

    Thanks in advance!

     

    Monday, May 2, 2011 11:07 AM

Answers

  • Hey!

    Finally i found the solution! Just one single line of code!

    First you have to activate the sheet where you want to import the data...

    //define range for stream usage
    Globals.Tabelle1.Activate();
    Excel.Range rangeStreamUsage = Globals.Tabelle1.Range["A1", missing];
    this.XmlImportXml(StreamUsageDataSet.GetXml(), out xmlmStreamUsage, true, rangeStreamUsage);
    
    //define range for streams per client
    Globals.Tabelle3.Activate();
    Excel.Range rangeStreamsPerClient = Globals.Tabelle3.Range["A1", missing];
    this.XmlImportXml(StreamsPerClientDataSet.GetXml(), out xmlmStreamsPerClient, true, rangeStreamsPerClient
    That's it!!!

    Thursday, May 5, 2011 12:09 PM

All replies

  • Hi Jamesinger,

     

    Thanks for posting in the MSDN Forum.

     

    Would you please clarify some questions:

     

    1.      What the key word “this” refer to? It’s seems not a worksheet. Is it right?

    2.      Is your snippet in an automatic application or in a add-in?

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 4, 2011 2:32 AM
    Moderator
  • Hey Tom!

     

    Thanks for your reply!

     

    I'm collecting my data over a webservice. Then everthing happens in the ThisWorkbook-Class.

    So the keyword this refers to the workbook and not to a sheet.

    This snippet is a part of a Excel 2007 Workbook project.

     

    Greets

     

     

     

    Wednesday, May 4, 2011 7:16 AM
  • Hi Jamesinger,

     

    OK, I think in you work book CreateDataTables, FillDataTables and CleanSheets are customized methods. Is it right?  And what is the “xe”?

     

    It’s based on my experience that CleanSheets is a key method in your issue – you are able to insert one successfully but aren’t able to insert the second, I think the CleanSheets is the most option which will be occurred exception. Because when I look at the method name I think you use it to initialize the range which you will fill data. Would you please show this method’s snippet?

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 4, 2011 8:15 AM
    Moderator
  • CreateDataTables - just initializes my DataTable 

    FillDataTables - fills the DataTables 

    CleanSheets - just cleans up the used ranges on my sheets

    these 3 methods are customized methods, yes!

     

    xe is just an XElement which i get from my webservice.

    I just post the hole ThisWorkbbok.cs (the code is not very clean and has plenty space for improvements)

     

    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;
    using Microsoft.Office.Interop.Excel;
    using System.Xml;
    using System.ServiceModel;
    using Microsoft.Office.Tools.Ribbon;
    using PlanetDigital.ExcelReport;
    using LocalResources = PlanetDigital.ExcelReport.Properties.Resources;
    
    namespace PlanetDigital.ExcelReport
    {
     public partial class ThisWorkbook
     {
      #region Properties
      public string Host { get; set; }
      public string CMSUser { get; set; }
      public string CMSPassword { get; set; }
      public List<string> Streams { get; set; }
      public DateTime StartDate { get; set; }
      public DateTime EndDate { get; set; }
      public PlanetDigitalRibbon PDRibbon { get; set; }
    
      private DataSet StreamUsageDataSet = new DataSet("StreamUsageDataSet");
      public System.Data.DataTable StreamUsageDataTable { get; set; }
    
      private DataSet StreamsPerClientDataSet = new DataSet("StreamsPerClientDataSet");
      public System.Data.DataTable StreamsPerClientDataTable { get; set; }
      #endregion
    
      #region Enums
      public enum ServiceType
      {
       OrganisationService,
       UsageService
      }
      #endregion
    
      #region EventHandlers
      void ThisWorkbook_BeforeXmlImport(Excel.XmlMap Map, string Url, bool IsRefresh, ref bool Cancel)
      {
      }
      void ThisWorkbook_AfterXmlImport(Excel.XmlMap Map, bool IsRefresh, Excel.XlXmlImportResult Result)
      {
       if (Result == Excel.XlXmlImportResult.xlXmlImportSuccess)
       {
        // MessageBox.Show("XML Daten import war erfolgreich.");
       }
       else
        MessageBox.Show(LocalResources.Warning_XmlImportFailed);
      }
      private void ThisWorkbook_Startup(object sender, System.EventArgs e)
      {
      }
      private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
      {
      }
      #endregion
    
      #region Methods
      public void DataImportThread()
      {
       System.Threading.Thread t = new System.Threading.Thread(new System.Threading.ThreadStart(DataImport));
       t.Start();
      }
      public void DataImport()
      {
       try
       {
        PDRibbon._btnDataImport.Enabled = false;
        PDRibbon._btnDataImport.Image = PlanetDigital.ExcelReport.Properties.Resources.iconRefresh;
        this.BeforeXmlImport += new Excel.WorkbookEvents_BeforeXmlImportEventHandler(ThisWorkbook_BeforeXmlImport);
        this.AfterXmlImport += new Excel.WorkbookEvents_AfterXmlImportEventHandler(ThisWorkbook_AfterXmlImport);
    
        //call web service
        PlanetDigital.ExcelReport.StreamUsageService.StreamUsageServiceSoapClient serviceClient = new PlanetDigital.ExcelReport.StreamUsageService.StreamUsageServiceSoapClient(GetHttpBasicBinding(), GetEndpointAddress(Host, ServiceType.UsageService));
        PlanetDigital.ExcelReport.StreamUsageService.ArrayOfString aos = new PlanetDigital.ExcelReport.StreamUsageService.ArrayOfString();
        aos.AddRange(Streams);
        XElement xe = serviceClient.GetXmlData(StartDate, EndDate, PDRibbon._ddlOrganisations.SelectedItem.Tag.ToString(), aos);
    
        //check web service response
        if (xe != null)
        {
         //creates the empty datasets and tables
         this.CreateDataTables();
    
         //fills the datatables
         this.FillDataTables(xe);
    
         //import to sheets
         this.ImportData();
    
         //creates all pivot tables and graphs
         this.CreatePivotTablesAndGraphs();
        }
        else
        {
         MessageBox.Show(LocalResources.Warning_NoDataFound);
        }
       }
       catch (System.Threading.ThreadAbortException)
       {
       }
       catch (System.ServiceModel.EndpointNotFoundException)
       {
        MessageBox.Show(String.Format(LocalResources.Warning_EndpointNotFoundException, Host));
       }
       catch (Exception ex)
       {
        MessageBox.Show(String.Format(LocalResources.Warning_XmlImportInterrupted, ex));
       }
       finally
       {
        PDRibbon._btnDataImport.Enabled = true;
        PDRibbon._btnDataImport.Image = PlanetDigital.ExcelReport.Properties.Resources.iconImport;
    
        Globals.Tabelle1.Activate();    
        
       }
      }
    
      private void ImportData()
      {
       //specify the XmlMaps
       Excel.XmlMap xmlmStreamUsage = this.XmlMaps.Add(StreamUsageDataSet.GetXmlSchema(), StreamUsageDataSet.DataSetName);
       Excel.XmlMap xmlmStreamsPerClient = this.XmlMaps.Add(StreamsPerClientDataSet.GetXmlSchema(), StreamsPerClientDataSet.DataSetName);
    
       //clean up all necessary sheets
       this.CleanSheets();
    
       //define range for stream usage
       Excel.Range rangeStreamUsage = Globals.Tabelle1.Range["A1", missing];
       //define range for streams per client
       Excel.Range rangeStreamsPerClient = Globals.Tabelle3.Range["A1", missing];
    
       //import the data   
       this.XmlImportXml(StreamUsageDataSet.GetXml(), out xmlmStreamUsage, true, rangeStreamUsage);
       this.XmlImportXml(StreamsPerClientDataSet.GetXml(), out xmlmStreamsPerClient, true, rangeStreamsPerClient);   
       //Globals.Tabelle3.Import(StreamsPerClientDataSet, xmlmStreamsPerClient, rangeStreamsPerClient)
      }
    
      public void OrganisationImportThread()
      {
       System.Threading.Thread t = new System.Threading.Thread(new System.Threading.ThreadStart(OrganisationImport));
       t.Start();
      }
      public void OrganisationImport()
      {
       try
       {
        PDRibbon._btnOrgImport.Enabled = false;
        PDRibbon._btnOrgImport.Image = PlanetDigital.ExcelReport.Properties.Resources.iconRefresh;
        PlanetDigital.ExcelReport.OrganisationService.OrganisationServiceSoapClient serviceClient = new PlanetDigital.ExcelReport.OrganisationService.OrganisationServiceSoapClient(GetHttpBasicBinding(), GetEndpointAddress(Host, ServiceType.OrganisationService));
        PlanetDigital.ExcelReport.OrganisationService.Organisation[] organisations = serviceClient.SelectList(new PlanetDigital.ExcelReport.OrganisationService.AuthHeader() { UserName = CMSUser, Password = CMSPassword });
    
        if (organisations != null && organisations.Length > 0)
        {
         RibbonDropDownItem item = null;
         PDRibbon._ddlOrganisations.Items.Clear();
         for (int i = 0; i < organisations.Length; i++)
         {
          item = new RibbonDropDownItem();
          item.Tag = organisations[i].Id;
          item.Label = organisations[i].Company;
    
          PDRibbon._ddlOrganisations.Items.Add(item);
         }
    
         PDRibbon._btnDataImport.Enabled = true;
        }
        else
         MessageBox.Show(LocalResources.Warning_NoOrganisationsToImport);
       }
       catch (System.Threading.ThreadAbortException)
       {
       }
       catch (Exception ex)
       {
        MessageBox.Show(String.Format(LocalResources.Warning_OrganisationImportFailed, ex));
       }
       PDRibbon._btnOrgImport.Enabled = true;
       PDRibbon._btnOrgImport.Image = PlanetDigital.ExcelReport.Properties.Resources.iconImport;
      }
    
      private void FillDataTables(XElement xe)
      {
       #region StreamUsageDataTable
       foreach (XElement element in xe.Descendants())
       {
        DataRow drStreamUsage = StreamUsageDataTable.NewRow();
    
        foreach (XAttribute attribute in element.Attributes())
        {
         if (attribute.Name.LocalName.Equals("Client"))
          drStreamUsage["Client"] = attribute.Value;
    
         else if (attribute.Name.LocalName.Equals("Mode"))
          drStreamUsage["Mode"] = attribute.Value;
    
         else if (attribute.Name.LocalName.Equals("Stream"))
          drStreamUsage["Stream"] = attribute.Value;
    
         else if (attribute.Name.LocalName.Equals("Position"))
         {
          if (String.IsNullOrEmpty(attribute.Value))
           drStreamUsage["Position"] = 0;
          else
           drStreamUsage["Position"] = Int64.Parse(attribute.Value);
         }
    
         else if (attribute.Name.LocalName.Equals("DateStart"))
          drStreamUsage["DateStart"] = DateTime.Parse(attribute.Value).ToUniversalTime().ToString();
    
         else if (attribute.Name.LocalName.Equals("DateEnd"))
          drStreamUsage["DateEnd"] = DateTime.Parse(attribute.Value).ToUniversalTime().ToString();
    
         else if (attribute.Name.LocalName.Equals("Seconds"))
          drStreamUsage["Seconds"] = attribute.Value;
    
        }
    
        StreamUsageDataTable.Rows.Add(drStreamUsage);
       }
       #endregion
    
       #region StreamsPerClientDataTable
       var groupQuery = from table in this.StreamUsageDataTable.AsEnumerable()
            group table by new { Stream = table["Stream"] }
             into groupeStreamUsageDataTable
             select new
             {
              Stream = groupeStreamUsageDataTable.Key, // Each Key contains column1 and column2
              Client = groupeStreamUsageDataTable.Count()
             };
    
       foreach (var item in groupQuery)
       {
        DataRow drStreamsPerClient = StreamsPerClientDataTable.NewRow();
        drStreamsPerClient = StreamsPerClientDataTable.NewRow();
        drStreamsPerClient["Stream"] = item.Stream;
        drStreamsPerClient["ClientCount"] = item.Client;
    
        StreamsPerClientDataTable.Rows.Add(drStreamsPerClient);
       }
       #endregion
      }
      #endregion
    
      #region Helpers
      private void CreateDataTables()
      {
       // Create a new DataTable. 
       if (!StreamUsageDataSet.Tables.Contains("StreamUsageData"))
       {
        StreamUsageDataTable = StreamUsageDataSet.Tables.Add("StreamUsageData");
        StreamUsageDataTable.Columns.Add(new DataColumn("Client", typeof(string)));
        StreamUsageDataTable.Columns.Add(new DataColumn("Mode", typeof(string)));
        StreamUsageDataTable.Columns.Add(new DataColumn("Stream", typeof(string)));
        StreamUsageDataTable.Columns.Add(new DataColumn("Position", typeof(Int64)));
        StreamUsageDataTable.Columns.Add(new DataColumn("DateStart", typeof(string)));
        StreamUsageDataTable.Columns.Add(new DataColumn("DateEnd", typeof(string)));
        StreamUsageDataTable.Columns.Add(new DataColumn("Seconds", typeof(Int64)));
       }
       else
       {
        StreamUsageDataSet.Tables["StreamUsageData"].Clear();
       }
    
       // Create a new DataTable.
       if (!StreamsPerClientDataSet.Tables.Contains("StreamsPerClient"))
       {
        StreamsPerClientDataTable = StreamsPerClientDataSet.Tables.Add("StreamsPerClient");
        StreamsPerClientDataTable.Columns.Add(new DataColumn("Stream", typeof(string)));
        StreamsPerClientDataTable.Columns.Add(new DataColumn("ClientCount", typeof(int)));
       }
       else
       {
        StreamsPerClientDataSet.Tables["StreamsPerClient"].Clear();
       }
      }
      private void CreatePivotTablesAndGraphs()
      {
       Globals.Tabelle2.CreatePivot();
       //Globals.Tabelle3.CreatePivot();
      }
      private void CleanSheets()
      {   
       Globals.Tabelle2.Cells.Clear();
       Globals.Tabelle2.Rows.Clear();
    
       Globals.Tabelle3.Cells.Clear();
       Globals.Tabelle3.Rows.Clear();
    
       //delete master sheet 
       Globals.Tabelle1.Cells.Clear();
       Globals.Tabelle1.Rows.Clear();
      }
      private EndpointAddress GetEndpointAddress(string host, ServiceType type)
      {
       EndpointAddress epa = null;
       switch (type)
       {
        case ServiceType.OrganisationService:
         epa = new EndpointAddress(String.Format("http://{0}/PlanetDigital/Service/Organisation.asmx", host));
         break;
        case ServiceType.UsageService:
         epa = new EndpointAddress(String.Format("http://{0}/PlanetDigital/Service/StreamUsageService.asmx", host));
         break;
       }
       return epa;
      }
      private static BasicHttpBinding GetHttpBasicBinding()
      {
       BasicHttpBinding basicHttpBinding = new BasicHttpBinding();
       basicHttpBinding.MaxReceivedMessageSize = 214783647;
       basicHttpBinding.MaxBufferSize = 214783647;
       basicHttpBinding.ReceiveTimeout = new TimeSpan(0, 0, 15, 0, 0);
       basicHttpBinding.SendTimeout = new TimeSpan(0, 0, 15, 0, 0);
       return basicHttpBinding;
      }
      #endregion
    
      #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
    
     }
    }
    
    

     


    Wednesday, May 4, 2011 8:20 AM
  • Hi Jamesinger,

     

    This reply is based on my assume that your data source will never take mistakes.

     

    In the snippet I can see that you will clean all of the cells of the worksheet which you will use (Tabelle1, Tabelle2, Tabelle3). It’s no problem.

     

    I’m not very familiar with Web Services and don’t know whether it’s necessary to use a thread to handle it. On my side I wouldn’t recommend you do that. Please try to handle you issue in the main thread.

     

    And another question is CreatePivot method. I think it’s a customized method. Please check it and see whether it will affect your issue.

     

    On my point I think your thread is not safe, so you can import a xml data but can’t do it again. I can see you open the thread but I can’t find where you close it. I’m wounding that there have some resource are locked and aren’t release when you try to import second xml data.

     

    I hope it can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 5, 2011 6:46 AM
    Moderator
  • Hey Tom!

     

    The webservice takes very long so i wanted to this in seperated thread.

    The CreatePivot() isn't implemented now, so nothing happens in there.

     

    I did everything in the main thread but still the same problem.

     

    Maybe you can post a simple sample for me where you import to simple datasets to two different sheets!?!?! This would be awesome!

    Thanks!

    Thursday, May 5, 2011 7:20 AM
  • I did a very simple one, but the same magic happens. It merge the to DataTable together in sheet1. Can u see an error???

    I just want to import Table1 on sheet1 and Table2 on sheet2.

     

    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 { getset; }

            private DataSet Set2 = new DataSet("Set2");
            public System.Data.DataTable Table2 { getset; }

            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();

                    Table2.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

        }
    }

     


    Thursday, May 5, 2011 8:04 AM
  • Hey!

    Finally i found the solution! Just one single line of code!

    First you have to activate the sheet where you want to import the data...

    //define range for stream usage
    Globals.Tabelle1.Activate();
    Excel.Range rangeStreamUsage = Globals.Tabelle1.Range["A1", missing];
    this.XmlImportXml(StreamUsageDataSet.GetXml(), out xmlmStreamUsage, true, rangeStreamUsage);
    
    //define range for streams per client
    Globals.Tabelle3.Activate();
    Excel.Range rangeStreamsPerClient = Globals.Tabelle3.Range["A1", missing];
    this.XmlImportXml(StreamsPerClientDataSet.GetXml(), out xmlmStreamsPerClient, true, rangeStreamsPerClient
    That's it!!!

    Thursday, May 5, 2011 12:09 PM