Re: Automation Adding XmlMaps & SaveAsXMLData in Excel

[Peter Torr \(MS\)]

Using C#
preferably, or VB.Net, I would like to add my schema as a map and map the
relevant elements to their corresponding columns. Adding the XmlMap to the
workbook is ok, but actually mapping the elements of my schema to individual
columns is proving a problem. Documentation on this subject is very scarce
and I was wondering whether anyone is doing this already?

Hmmm, I tried the macro recorder and it doesn't seem to help. Perhaps you
can't do this through automation?

Adding the Excel group.

Peter

 

[Andrew Whitechapel]

Here's some sample code to get you started:

Excel.XmlMap supplierMap = null;

try
{
// If we already have a Suppliers map, or if the
// Maps collection is null, this will throw.
supplierMap = book.XmlMaps["Suppliers"];
}
catch (System.Runtime.InteropServices.COMException) {}

// We don't yet have a Suppliers map, so add it now.
if (supplierMap == null)
{
string currentLocation = System.Environment.CurrentDirectory;
string currentPath = currentLocation.Substring(
0, currentLocation.IndexOf(@"bin\Debug"));
string schemaPath = Path.Combine(currentPath, "Suppliers.xsd");
object missing = Type.Missing;
supplierMap = book.XmlMaps.Add(schemaPath, missing);
supplierMap.Name = "Suppliers";
book.Save();
}

// Make the XML taskpane visible.
book.Application.DisplayXMLSourcePane(supplierMap);
Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
int row = 2;
int col = 1;
for (int i = 0; i < 12; i++)
{
// Fill in the field name in Column 1.
Excel.Range label = (Excel.Range)sheet.Cells[row+i,col];
label.Value2 = fields;

// Setup an XML node alongside, in Column 2.
Excel.Range data = (Excel.Range)sheet.Cells[row+i, col+1];
string elementXPath =
String.Format("/ns1ocument/Suppliers/{0}", fields);
object missing = Type.Missing;
data.XPath.SetValue(supplierMap, elementXPath, missing, false);
}

The fields array looks like this:
private string[] fields =
new string[12]
{
"SupplierID", "CompanyName", "ContactName",
"ContactTitle", "Address", "City", "Region",
"PostalCode", "Country", "Phone", "Fax", "HomePage"
};

The schema is here:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="Suppliers"
targetNamespace="http://tempuri.org/Suppliers.xsd"
elementFormDefault="unqualified"
xmlns="http://tempuri.org/Suppliers.xsd"
xmlns:mstns="http://tempuri.org/Suppliers.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Document" msdata:Locale="en-GB">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Suppliers">
<xs:complexType>
<xs:sequence>
<xs:element name="SupplierID" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="CompanyName" type="xs:string" />
<xs:element name="ContactName" type="xs:string" minOccurs="0"
/>
<xs:element name="ContactTitle" type="xs:string" minOccurs="0"
/>
<xs:element name="Address" type="xs:string" minOccurs="0" />
<xs:element name="City" type="xs:string" minOccurs="0" />
<xs:element name="Region" type="xs:string" minOccurs="0" />
<xs:element name="PostalCode" type="xs:string" minOccurs="0" />
<xs:element name="Country" type="xs:string" minOccurs="0" />
<xs:element name="Phone" type="xs:string" minOccurs="0" />
<xs:element name="Fax" type="xs:string" minOccurs="0" />
<xs:element name="HomePage" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="DocumentKey1" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:Suppliers" />
<xs:field xpath="mstns:SupplierID" />
</xs:unique>
</xs:element>
</xs:schema>

Regards
Andrew