OFC305 Advanced XML Programming In Excel 2003

1. OFC305Advanced XML Programming In Excel 2003 Joseph Chirilov Program Manager

2. Resumes Joseph Chirilov Program Manager Excel Data Services demo

3. 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: XmlMap.IsExportable

4. Schema Support • W3C XML Schema 1.0 spec, except: • Abstract elements • Substitution groups • xsd:any • xsd:anyAttribute • Recursive Structures • Mixed Content • Excel can derive XML schema from sample data

5. 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

6. XmlMap Object • Represents a schema attached to the workbook. • Methods: • .Import / .ImportXml • .Export / .ExportXml • .Delete • Properties: • .Schemas • .DataBinding • Many others…

7. Adding an XML map • Workbook.XmlMaps.Add( Schema As String, [RootElementName] As String) As XmlMap • Example:Set myMap = ActiveWorkbook.XmlMaps.Add( “C:\expense.xsd” )

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

9. Mapping Non-Rep Elements • Range.XPath.SetValue (Map As XmlMap,XPath As String,[SelectionNamespace] As String,[Repeating] As Boolean ) • Example:ActiveCell.XPath.SetValue( ActiveWorkbook.XmlMaps(1), “/Expense/Meta/FirstName”, , False )

10. Selection Namespace • Example:If the XPath you want to map is:“/ns0:Expense/Meta/ns1:Firstname”then the selection namespace should be:“xmlns:ns0=http://urnexpense xmlns:ns1=http://urnempinfo”

11. ListObject Object • Represents a List • Methods: • .Delete • Many others… • Properties: • .DataBodyRange • .ListColumns • .Range • .XPath • .ListRows • .Range • .Name • .XmlMap

12. Mapping Rep Elements • ListColumn.XPath.SetValue (Map As XmlMap,XPath As String,[SelectionNamespace] As String,[Repeating] As Boolean ) • Example:Set myList = ActiveSheet.ListObjects.Add( xlSrcRange, Range(“A1:D10”))myList.ListColumns(1).XPath.SetValue( ActiveWorkbook.XmlMaps(1), “/Expense/Meta/FirstName”, , False )

13. OM: Mapping Joseph Chirilov Program Manager Excel Data Services demo

14. Importing XML Data • XmlMap.Import( Url As String, [Overwrite] As Boolean )As XlXmlImportResult • XmlMap.ImportXml( XmlData As String,[Overwrite] As Boolean )As XlXmlImportResult

15. Importing XML Data • Example:result = ActiveWorkbook.XmlMaps(1).Import( “\\expense\empinfo.xml” )result = ActiveWorkbook.XmlMaps(1).ImportXml( sWSData )

16. XlXmlImportResult • 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

17. Exporting XML Data • XmlMap.Export( Url As String, [Overwrite] As Boolean )As XlXmlExportResult • XmlMap.ExportXml( Data As String )As XlXmlExportResult (Note: ‘Data’ is an IN/OUT variable)

18. Exporting XML Data • Example:If ActiveWorkbook.XmlMaps(1).IsExportable Then…result = ActiveWorkbook.XmlMaps(1).Export( “C:\expenseData\JDoe.xml” )Dim sData As Stringresult = ActiveWorkbook.XmlMaps(1).ExportXml( sData ) (‘sData’ now contains exported data)

19. XlXmlExportResult • 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!

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

21. Auto Map and Import • Quick & easy way to import data without the need to map elements before hand • Workbook.XmlImport( Url As String,ImportMap As XmlMap,[Overwrite],[Destination] ) As XlXmlImportResult • Example:ActiveWorkbook.XmlImport “C:\expense.xml”, Nothing, , Range(“A1”) • Workbook.XmlImportXml for in-memory strings

22. Query Methods • Use XmlMapQuery to discover mappings, and XmlDataQuery to extract data from those mappings • Worksheet.XmlMapQuery( XPath As String,[SelectionNamespaces] As String,[Map] As XmlMap ) As Range • Worksheet.XmlDataQuery(XPath As String,[SelectionNamespaces] As String,[Map] As XmlMap ) As Range

23. Query Methods • sXPath = “/Expense/Items/Amount”If Not XmlMapQuery(sXPath) Is Nothing Then Set myRange = XmlDataQuery(sXPath) … perform normal range manipulation …End If

24. Import Events • Hang off Workbook and Application Object • BeforeXmlImport( Map As XmlMap,Url As String,IsRefresh As Boolean,Cancel As Boolean ) • AfterXmlImport(Map As XmlMap,IsRefresh As Boolean,Result As XlXmlImportResult )

25. Export Events • Hang off Workbook and Application Object • BeforeXmlExport( Map As XmlMap,Url As String,Cancel As Boolean ) • AfterXmlExport(Map As XmlMap,Url As String,Result As XlXmlExportResult )

26. 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

27. Web Services • Getting data from web services is easier with Excel. • Methods: • Existing COM objects • OfficeXP Web Services Toolkit 2.0 • Visual Studio

28. Web Services Joseph Chirilov Program Manager Excel Data Services demo

29. Community Resources • 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

30. evaluations

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