OFC 305 Advanced XML Programming In Excel 2003

OFC 305 Advanced XML Programming In Excel 2003 Joseph Chirilov Program Manager

demo Resumes Joseph Chirilov Program Manager Excel Data Services

Exportability Data not exportable when you have: Fill down List of lists Sparse layout Data is exportable when required elements are not mapped Taskpane: Verify Map for Export OM: Xml. Map. Is. Exportable

Schema Support W 3 C XML Schema 1. 0 spec, except: Abstract elements Substitution groups xsd: any. Attribute Recursive Structures Mixed Content Excel can derive XML schema from sample data

Object Model XML Scenarios Adding a schema / map Mapping non-repeating elements Mapping repeating elements Importing Exporting Querying for map structure & data Catching Import / Export Events

Xml. Map Object Represents a schema attached to the workbook. Methods: . Import /. Import. Xml. Export /. Export. Xml. Delete Properties: . Schemas. Data. Binding Many others…

Adding an XML map Workbook. Xml. Maps. Add( Schema As String, [Root. Element. Name] As String) As Xml. Map Example: Set my. Map = Active. Workbook. Xml. Maps. Add( “C: expense. xsd” )

XPath Object Representation of an element mapping in the spreadsheet Methods: . Set. Value. Clear Properties: . Map. Repeating. Value

Mapping Non-Rep Elements Range. XPath. Set. Value ( Map As Xml. Map, XPath As String, [Selection. Namespace] As String, [Repeating] As Boolean ) Example: Active. Cell. XPath. Set. Value( Active. Workbook. Xml. Maps(1), “/Expense/Meta/First. Name”, , False )

Selection Namespace Example: If the XPath you want to map is: “/ns 0: Expense/Meta/ns 1: Firstname” then the selection namespace should be: “xmlns: ns 0=http: //urnexpense xmlns: ns 1=http: //urnempinfo”

List. Object Represents a List Methods: . Delete Many others… Properties: . Data. Body. Range. List. Columns. Range. XPath . List. Rows. Range . Name. Xml. Map

Mapping Rep Elements List. Column. XPath. Set. Value ( Map As Xml. Map, XPath As String, [Selection. Namespace] As String, [Repeating] As Boolean ) Example: Set my. List = Active. Sheet. List. Objects. Add( xl. Src. Range, Range(“A 1: D 10”)) my. List. Columns(1). XPath. Set. Value( Active. Workbook. Xml. Maps(1), “/Expense/Meta/First. Name”, , False )

demo OM: Mapping Joseph Chirilov Program Manager Excel Data Services

Importing XML Data Xml. Map. Import( Url As String, [Overwrite] As Boolean ) As Xl. Xml. Import. Result Xml. Map. Import. Xml( Xml. Data As String, [Overwrite] As Boolean ) As Xl. Xml. Import. Result

Importing XML Data Example: result = Active. Workbook. Xml. Maps(1). Import( “\expenseempinfo. xml” ) result = Active. Workbook. Xml. Maps(1). Import. Xml( s. WSData )

Xl. Xml. Import. Result Import results are for non-failing imports only Only most severe condition is returned Possible results (in order of increasing severity): Success Schema validation failed Some data truncated to fit cell Failing imports will result in an run-time error

Exporting XML Data Xml. Map. Export( Url As String, [Overwrite] As Boolean ) As Xl. Xml. Export. Result Xml. Map. Export. Xml( Data As String ) As Xl. Xml. Export. Result (Note: ‘Data’ is an IN/OUT variable)

Exporting XML Data Example: If Active. Workbook. Xml. Maps(1). Is. Exportable Then… result = Active. Workbook. Xml. Maps(1). Export( “C: expense. DataJDoe. xml” ) Dim s. Data As String result = Active. Workbook. Xml. Maps(1). Export. Xml( s. Data ) (‘s. Data’ now contains exported data)

Xl. Xml. Export. Result Export results are for non-failing exports only Only most severe condition is returned Possible results (in order of increasing severity): Success Schema validation failed Failing exports will result in an run-time error Don’t forget to check exportability first!

demo OM: Import / Export Joseph Chirilov Program Manager Excel Data Services

Auto Map and Import Quick & easy way to import data without the need to map elements before hand Workbook. Xml. Import( Url As String, Import. Map As Xml. Map, [Overwrite], [Destination] ) As Xl. Xml. Import. Result Example: Active. Workbook. Xml. Import “C: expense. xml”, Nothing, , Range(“A 1”) Workbook. Xml. Import. Xml for in-memory strings

Query Methods Use Xml. Map. Query to discover mappings, and Xml. Data. Query to extract data from those mappings Worksheet. Xml. Map. Query( XPath As String, [Selection. Namespaces] As String, [Map] As Xml. Map ) As Range Worksheet. Xml. Data. Query( XPath As String, [Selection. Namespaces] As String, [Map] As Xml. Map ) As Range

Query Methods s. XPath = “/Expense/Items/Amount” If Not Xml. Map. Query(s. XPath) Is Nothing Then Set my. Range = Xml. Data. Query(s. XPath) … perform normal range manipulation … End If

Import Events Hang off Workbook and Application Object Before. Xml. Import( Map As Xml. Map, Url As String, Is. Refresh As Boolean, Cancel As Boolean ) After. Xml. Import( Map As Xml. Map, Is. Refresh As Boolean, Result As Xl. Xml. Import. Result )

Export Events Hang off Workbook and Application Object Before. Xml. Export( Map As Xml. Map, Url As String, Cancel As Boolean ) After. Xml. Export( Map As Xml. Map, Url As String, Result As Xl. Xml. Export. Result )

Event Examples Before Export: Validate sheet data according to business rules before export and cancel if rules are broken. Before Import: warn user that data will be discarded upon subsequent import / refresh After Import: Examine data that was imported (according to business rules, etc. ) Enforce that a map is Import-only or Export-only

Web Services Getting data from web services is easier with Excel. Methods: Existing COM objects Office. XP Web Services Toolkit 2. 0 Visual Studio

demo Web Services Joseph Chirilov Program Manager Excel Data Services

Community Resources http: //www. microsoft. com/communities/default. mspx Most Valuable Professional (MVP) http: //www. mvp. support. microsoft. com/ Newsgroups Converse online with Microsoft Newsgroups, including Worldwide http: //www. microsoft. com/communities/newsgroups/default. mspx User Groups Meet and learn with your peers http: //www. microsoft. com/communities/usergroups/default. mspx

evaluations

© 2003 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.