0

I have a string of XML that I want to load directly into a spreadsheet with C#. I've found a few examples but haven't quite been able to fill in the gaps in my knowledge. 

Range r = Globals.ThisAddIn.Application.Worksheets["Sheet1"].Range["A1", Missing.Value];
Workbook w = Globals.ThisAddIn.Application.ActiveWorkbook;
w.XmlImportXml(xmlString, out map1, true, r);

It seems that the "map1" variable would be the mapping of the XML schema. How do I do this? Do I have to specify an .xsd file or can this be created programmatically? What is the preferred way to handle this?

My XML layout is similar to:

<root>
   <parent>
      <childOne>5</childOne>
      <childTwo>8</childTwo>
   </parent>
   <parent>
      <childOne>10</childOne>
      <childTwo>15</childTwo>
   </parent>
</root>
vincentvanjoe
  • 767
  • 1
  • 12
  • 23

1 Answers1

0

You could load the XML into a Datatable before like this:

DataTable table = new DataTable();
table.ReadXml(xmlFilePath); // new StringReader(xmlContent)

Excel.XmlMap map = w.XmlMaps.Add(table.GetXmlSchema(), "WhatEver");
w.XmlImportXml(table.GetXml(), out map, true, r);

I dont know from where you get the xml-content. If you got it as a string allready, use the ReadXML overload that accepts a textreader.

Also check those links Functionexample of XmlImportXml, DataTable from Xml

CSharpie
  • 9,195
  • 4
  • 44
  • 71
  • Is this System.Data.DataTable? It appears that that class does not have a GetXmlSchema() method – vincentvanjoe May 27 '14 at 01:00
  • Check the second link i provided – CSharpie May 27 '14 at 06:01
  • I think that even with that example I have the same problem. I need to be able to generate an XML schema for passing to XmlMaps.Add(). I posted a separate question for this: http://stackoverflow.com/questions/23898861/generate-xml-schema-from-xml-string – vincentvanjoe May 27 '14 at 21:03