XmlImportXml on Excel template

[nickbr]

Hi All

 

I've bing'd this to death and have reached a dead end, so any help would be appreciated, I'm not an excel expert at all.

 

The situation

 

I have created an application in VSTS that exports an XML file with a single root node, and a number of child nodes. Most are themselves arrays (serialised IEnumerables<T>) but one is not. I have created a template in Excel 2007 that properly formats the XML. If I open the template (as in, create a workbook from the template) I can right click somewhere that is mapped and select XML | Import XML. This lets me select the file output from my application, and imports it and formats it a treat. The import takes place across worksheets (one for each child of the root node). Now, I would like to have an "export to XML" button that automatically opens a copy of the template, imports the XML and then saves the file. This is where the problem happens.

 

The Problem

 

When I try to import the XML, I am getting an error that seems to be saying I can't import the XML as a mapping already exists at this point. If I try to import the XML onto a cell that is not mapped, it imports but the formatting is default, which is not what I need - hence my creating a template.

 

The Error

 

"The operation cannot be completed because the result would overlap an existing XML mapping"

 

The Code (snipped as appropriate)

 

Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel._Workbook oWB;

Microsoft.Office.Interop.Excel._Worksheet oSheet;

 

oXL.Visible = true;

 

 

oWB = oXL.Workbooks.Open(@"C:\Template.xltx", false, true,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing);

 

Microsoft.Office.Interop.Excel.XmlMap xmlMap1 = oWB.XmlMaps[1];

 

oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.Sheets[1];

Microsoft.Office.Interop.Excel.Range variablesRange = oSheet.get_Range("B3", Type.Missing);

oWB.XmlImportXml(xmlString, out xmlMap1, true, variablesRange);

 

 

More...

 

The last line is the line that throws the error stated above. If I step through, the xmlMap1 seems to be populated with the correct map (the root node is correct). Cell B3 is the first node on sheet 1 that is mapped - this is where I can right click and select XML | Import etc. As stated, this is done in VS2008 and I ref MS.Office.Core and MS.Office.Excel.Interop. I have tried putting the full range in ("B3", "B33"), and tried a bunch of other things but with no success so far other than getting unformatted data in.

 

Finally

 

Apologies if I have miss posted this question to the wrong forum, if that's the case please can a mod point me at the right one. If not, has anyone got any thoughts on what I am doing wrong here.

 

Thanks and very best regards

Nick

(cross post from http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/1fefef4f-2e8e-4e99-8106-33e00f89da8a?prof=required)

[PlausiblyDamp]

As far as I can tell the error seems to indicate you are trying to import two elements into a single cell, perhaps it thinks there is already a value in the B3 cell?

 

could you try programatically clearing the b3 cell and then importing the data to see what happens? Would it be possible to import the xml and then apply the format?

[nickbr]

Hi PD

 

Possibly. I think the error is stating that in the template I have mapped that field to an XML element and I can't apply another one to it (even though it is actually the same one)

 

If I clear the cell, is there a way that I can impose the correct formatting onto the imported data?

 

thanks for the response

Nick

[David80]

Hello,

I solved this problem deleting all the XmlMaps in the ActiveWorkbook before importing the XML:

foreach (Excel.XmlMap xmlMap in ((Excel.Application)application).ActiveWorkbook.XmlMaps) {

xmlMap.Delete();

}

 

but now I'm having a similar problem when I try to do a second import in the same range of the Excel sheet (despite I do the import with overwrite = true).

The error message when doing the second import in Excel is:

"The operation cannot be completed because the result would overlap an existing table."

So I've changed the problem from XmlMap to table :)

Does anybody know how to solve this?

 

Regards,

David.

[wrigracap]

I solved this problem deleting all the XmlMaps in the ActiveWorkbook before importing the XML:

foreach (Excel.XmlMap xmlMap in ((Excel.Application)application).ActiveWorkbook.XmlMaps) {

xmlMap.Delete();