Automation Adding XmlMaps & SaveAsXMLData in Excel

[James E]

I am trying to achieve the following. We have a third-party application that
exports data into six columns (multiple rows - the number of rows is
unknown). I have created an XML schema, which, when I add it as a map in
Excel, I can saveAs XML Data and I get the correct data in the format of my
XML Schema - perfect. Now, I want to automate this process. 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?

Cheers

James E

 

[Steve Culhane [MS]]

James,

From the sounds of your request you want to place the elements from your
mapping on your workbook. You may want to use a workbook with your mapping
elements already laid out, but no data in the workbook. Then from your C#
or VB app automate importing the data into the map, then all the data will
be filled in where you put the elements. Here's a quick sample that
Imports the data into the workbook, with the elements already laid out.

ActiveWorkbook.XmlMaps("Customers_Map").Import URL:=
"C:\XMLCustomers.xml"

If you're working with a lot of different data, you can now save the
workbook out under a new name, close it, and open the one you were using as
a template again, and start the process over. In the end you'll have all
the workbooks filled in with the different sets of XML Data.

Let me know if this does what you were looking for.

Stephen Culhane
(e-mail address removed)
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "James E" <[email protected]>
| Subject: Automation Adding XmlMaps & SaveAsXMLData in Excel
| Date: Mon, 16 Feb 2004 12:38:03 -0000
| Lines: 16
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <#[email protected]>
| Newsgroups: microsoft.public.office.developer.automation
| NNTP-Posting-Host: 193.128.116.98
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.
phx.gbl
| Xref: cpmsftngxa07.phx.gbl
microsoft.public.office.developer.automation:8413
| X-Tomcat-NG: microsoft.public.office.developer.automation
|
| I am trying to achieve the following. We have a third-party application
that
| exports data into six columns (multiple rows - the number of rows is
| unknown). I have created an XML schema, which, when I add it as a map in
| Excel, I can saveAs XML Data and I get the correct data in the format of
my
| XML Schema - perfect. Now, I want to automate this process. 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?
|
| Cheers
|
| James E
|
|
|

 

[James E]

Thanks for your advice Steve. I have actually achieved what I hoped to using
the example below. This is still test code and is not yet complete. I am
still trying to work out how to locate the last row with data in it
(equivalent of doing CTRL+SHIFT+END in Excel).

Cheers

James E

public void ProcessFile(string filPath, string destPath)

{

Excel.Application xlApp;

Excel.Workbook xlBook;

try

{

object o = Type.Missing;

xlApp = new Excel.ApplicationClass();

//Open up the workbook exported from 3rd Party application

xlBook = xlApp.Workbooks.Open(filPath,o.o.o.o.o.o.o.o.o.o.o.o.o.o);

//Add our schema to the XmlMaps

xlBook.XmlMaps.Add(@"http://localhost/schemas/TestSchema.xsd","costs");

Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];

string strXPath;

Excel.XmlMap myMap;

myMap = xlBook.XmlMaps[1];

Excel.Worksheet mySheet;



mySheet = (Excel.Worksheet)xlBook.Worksheets[1];

//select the six columns - still trying to work out how to locate the
last row without going all the way to the bottom of the worksheet

mySheet.get_Range("A1","F65536").Select();

//Add a ListObject to the ListObjects collection

Excel.ListObject myList =
(Excel.ListObject)mySheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrc
Range,o,o,Excel.XlYesNoGuess.xlYes,o);

strXPath = "/ns1:costs/ns1:costItem/ns1:description";

//Map the first column to the corresponding element in my schema

myList.ListColumns[1].XPath.SetValue(myMap,
strXPath,Type.Missing,Type.Missing);

myList.ListColumns[1].Name = "Description";

strXPath = "/ns1:costs/ns1:costItem/ns1:costcode";

myList.ListColumns[2].XPath.SetValue(myMap, strXPath,o,o);

myList.ListColumns[2].Name = "Cost Code";

strXPath = "/ns1:costs/ns1:costItem/ns1:unit";

myList.ListColumns[3].XPath.SetValue(myMap, strXPath,o,o);

myList.ListColumns[3].Name = "Unit";

strXPath = "/ns1:costs/ns1:costItem/ns1:rate";

myList.ListColumns[4].XPath.SetValue(myMap, strXPath,o,o);

myList.ListColumns[4].Name = "rate";

strXPath = "/ns1:costs/ns1:costItem/ns1:quantity";

myList.ListColumns[5].XPath.SetValue(myMap, strXPath,o,o);

myList.ListColumns[5].Name = "Quantity";

strXPath = "/ns1:costs/ns1:costItem/ns1:cost";

myList.ListColumns[6].XPath.SetValue(myMap, strXPath,o,o);

myList.ListColumns[6].Name = "Cost";





//All the mappings have been added, save the XML Data...

xlBook.SaveAsXMLData(destPath,myMap);


xlBook.Close(false,o,o);

xlApp.Quit();

}

catch(Exception ex)

{

//Exception handling

}

finally

{

xlBook = null;

xlApp = null;

}

}