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#
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
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#
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's Blog
Please click "Mark as Answer" if this helped you.