Ofc305 advanced xml programming in excel 2003
Download
1 / 31

OFC305 Advanced XML Programming In Excel 2003 - PowerPoint PPT Presentation


  • 264 Views
  • Uploaded on

OFC305 Advanced XML Programming In Excel 2003. Joseph Chirilov Program Manager. Resumes. Joseph Chirilov Program Manager Excel Data Services. demo. Exportability. Data not exportable when you have: Fill down List of lists Sparse layout

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'OFC305 Advanced XML Programming In Excel 2003' - iona


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Ofc305 advanced xml programming in excel 2003

OFC305Advanced XML Programming In Excel 2003

Joseph Chirilov

Program Manager


Resumes

Resumes

Joseph Chirilov

Program Manager

Excel Data Services

demo


Exportability
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


Schema support
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


Object model
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


Xmlmap object
XmlMap Object

  • Represents a schema attached to the workbook.

  • Methods:

    • .Import / .ImportXml

    • .Export / .ExportXml

    • .Delete

  • Properties:

    • .Schemas

    • .DataBinding

    • Many others…


Adding an xml map
Adding an XML map

  • Workbook.XmlMaps.Add( Schema As String, [RootElementName] As String) As XmlMap

  • Example:Set myMap = ActiveWorkbook.XmlMaps.Add( “C:\expense.xsd” )


Xpath object
XPath Object

  • Representation of an element mapping in the spreadsheet

  • Methods:

    • .SetValue

    • .Clear

  • Properties:

    • .Map

    • .Repeating

    • .Value


Mapping non rep elements
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 )


Selection namespace
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”


Listobject object
ListObject Object

  • Represents a List

  • Methods:

    • .Delete

    • Many others…

  • Properties:

    • .DataBodyRange

    • .ListColumns

      • .Range

      • .XPath

    • .ListRows

      • .Range

    • .Name

    • .XmlMap


Mapping rep elements
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 )


Om mapping

OM: Mapping

Joseph Chirilov

Program Manager

Excel Data Services

demo


Importing xml data
Importing XML Data

  • XmlMap.Import( Url As String, [Overwrite] As Boolean )As XlXmlImportResult

  • XmlMap.ImportXml( XmlData As String,[Overwrite] As Boolean )As XlXmlImportResult


Importing xml data1
Importing XML Data

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


Xlxmlimportresult
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


Exporting xml data
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)


Exporting xml data1
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)


Xlxmlexportresult
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!


Om import export

OM: Import / Export

Joseph Chirilov

Program Manager

Excel Data Services

demo


Auto map and import
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


Query methods
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


Query methods1
Query Methods

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


Import events
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 )


Export events
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 )


Event examples
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
Web Services

  • Getting data from web services is easier with Excel.

  • Methods:

    • Existing COM objects

    • OfficeXP Web Services Toolkit 2.0

    • Visual Studio


Web services1

Web Services

Joseph Chirilov

Program Manager

Excel Data Services

demo


Community resources
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



© 2003 Microsoft Corporation. All rights reserved.

This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.


ad