Importing XML into Excel RSS

• • Reply
  

  billcrawley

  Member

  341 Points

  286 Posts

  Importing XML into Excel

  Apr 18, 2013 11:55 PM|LINK

  Hi All,

  I'm using Excel 2010 and I need to open and import XML files using C# It seems that the key to performing this is to use XmlImportXml on the workbook object. Trouble is I'm not sure how to use it.

  I have the datasource which is a string value of the XML streamed in. I know I want to set overwrite to true, and I set my importmap to null since I do not have one of these. The bit thats throwing me is that there is an out parameter of type XmlMap, the documentation seems to state I can leave this if I have a destination range. I have a Destination of (Range("$A$1")) to use. can anybody give an example of using this in C#

• • Reply
  

  DarrellNorto...

  All-Star

  86555 Points

  9624 Posts

  Moderator

  MVP

  Re: Importing XML into Excel

  Apr 19, 2013 10:09 AM|LINK

  There is an example on MSDN here.

  Here is the example which I've updated with your info - you need to specify the XML since I don't know how you are getting that:

  private void WorkbookXmlImportEvents()
  {
  	// Just a little debugging - remove if you don't need it
      this.BeforeXmlImport +=
          new Excel.WorkbookEvents_BeforeXmlImportEventHandler(
          ThisWorkbook_BeforeXmlImport);
  
      this.AfterXmlImport += new
          Excel.WorkbookEvents_AfterXmlImportEventHandler(
          ThisWorkbook_AfterXmlImport);
  
      // Add a new XML map to the collection.
      Excel.XmlMap xmlMap1 = this.XmlMaps.Add(xmlSchemaAsStringOrFileLocation,
          "rootNodeName");
  
      // Import the data stream if the XmlMap was successfully created.
      if (xmlMap1 != null)
      {
          // Get objects that can be used while the ExcelLocale1033
          // attribute is true (the default).
          Excel.Workbook workbook1 = (Excel.Workbook)Microsoft.Office.Tools.
              Excel.ExcelLocale1033Proxy.Unwrap(this.InnerObject);
          Excel.Range range1 = Globals.Sheet1.Range["A1", missing];
          range1 = (Excel.Range)Microsoft.Office.Tools.Excel.
              ExcelLocale1033Proxy.Unwrap(range1);
          xmlMap1 = (Excel.XmlMap)Microsoft.Office.Tools.Excel.
              ExcelLocale1033Proxy.Unwrap(xmlMap1);
  
          // This will raise the BeforeXmlImport and AfterXmlImport events.
          workbook1.XmlImportXml(xmlAsString, out xmlMap1, true,
              range1);
      }
      else
      {
          MessageBox.Show("The XmlMap could not be created");
      }
  }
  
  void ThisWorkbook_BeforeXmlImport(Excel.XmlMap Map,
      string Url, bool IsRefresh, ref bool Cancel)
  {
      if (DialogResult.No == MessageBox.Show("Microsoft Excel is about" +
          " to import XML into the workbook. Continue with importing?",
          "Custom XML Import Dialog", MessageBoxButtons.YesNo))
      {
          Cancel = true;
      }
  }
  
  void ThisWorkbook_AfterXmlImport(Excel.XmlMap Map, bool IsRefresh,
      Excel.XlXmlImportResult Result)
  {
      if (Result == Excel.XlXmlImportResult.xlXmlImportSuccess)
      {
          MessageBox.Show("XML import succeeded.");
      }
      else
      {
          MessageBox.Show("XML import failed.");
      }
  }

  
  
  

  Darrell Norton, MVP
  Darrell Norton's Blog
  Please click "Mark as Answer" if this helped you.
  • Marked as answer by Angie xu - MSFT on Apr 29, 2013 07:16 AM