Training Presentation Excel 2003-Using Xml In Excel

Microsoft®Office Excel 2003 TrainingUsing XML in Excel[Your company name] presents:Using XML in ExcelCourse contents•Overview: XML in Excel 2003•Lesson 1: The Excel approach to XML•Lesson 2: Opening XML files as XML lists(Continued on next slide.)Using XML in ExcelCourse contents, cont’d.•Lesson 3: Creating XML maps with the XML Source task pane•Lesson 4: Understand the XML Source task paneEach lesson includes a list of suggested tasks and a set of test questions.Using XML in ExcelTwo new tools in Excel 2003 —XML lists and the XML Sourcetask pane —make it easier than ever to use XML.Overview: XML in Excel 2003Learn the concepts behind these new tools, and how to use them to process XML data in Excel 2003.Using XML in ExcelCourse goals•Create an XML list.•Create an XML map. •Import and export XML data to and from mapped data cells.•Use the icons, controls, and options in the XML Sourcetask pane. Lesson 1The Excel approach to XMLUsing XML in ExcelThe Excel approach to XML An XML map is a set of links that you create between an XML schema and the cells in a worksheet. If you don't have an XML schema, all you need is an XML data file. Excel will infer a schema from the structure of the tags in your data file.When you work with XML in Excel, you use maps.Using XML in ExcelGetting started using XML with Excel To work with XML in Excel, you use an XML schema to create what Excel calls an XML map. XML maps link the cells in a worksheet to the items (also called elements) in a schema. You then enter or import data into the mapped cells, work with that data, and then either save or export the data to an XML file.Don't try to go anywhere in XML without a map.Using XML in ExcelGetting started using XML with ExcelIf you don't have a schema, don't worry. All you need is an XML data file. Excel infers a schema from the structure of the tags in the file and allows you to create a map. You'll see that process in detail later in this course.Don't try to go anywhere in XML without a map.Using XML in ExcelXML maps, schemas, and data files This illustration shows how a map links the items in a schema to cells in a worksheet. You can create a map by using any of these three sources:Maps link elements in a schema to cells in a worksheet.•A separate schema file (uses the .xsd extension) •A schema embedded in an XML data file (uses the .xml extension) •An XML data file with no schema other than its own tag structureUsing XML in ExcelWays to create XML maps Excel 2003 gives you two ways to get started with an XML map:Open an XML data file as an XML list.When you use this option, Excel creates the map for you automatically, and then imports all the data from the file into the list.The XML Sourcetask pane is the best option for putting XML to work in Excel 2003.Using XML in ExcelWays to create XML mapsCreate a map by using the XML Source task pane.This is a much more powerful and flexible option because you can create maps from schemas or data files. (When you create an XML list, you can only use a data file.) You can also select just the items from the schema or data file that you want to see.The XML Sourcetask pane is the best option for putting XML to work in Excel 2003.Using XML in ExcelEntering, saving, and exporting XML data Excel automatically loads all the data from the source file into your worksheet for you.However, when you create a map by using the XML Sourcetask pane, you either enter data manually or import it from an existing file.Excel provides several ways to save and distribute your XML data.Using XML in ExcelEntering, saving, and exporting XML dataKeep in mind that the underlying schema specifies the type of data you can enter into a mapped cell.For example, if a schema specifies numeric values for a given cell and you enter text, Excel displays an alert.Excel provides several ways to save and distribute your XML data.Using XML in ExcelEntering, saving, and exporting XML dataWhen you're ready to save your data, Excel gives you several choices. You'll use these options the most:Excel provides several ways to save and distribute your XML data.•Save your data as an Excel workbookYou do this by saving the file using any of the standard methods that Excel provides.Using XML in ExcelEntering, saving, and exporting XML dataExcel provides several ways to save and distribute your XML data.•Save your data as a separate XML fileYou do this by using the Save Ascommand and selecting the XML Datafile format.•Export the data Exporting is another way to create a standard XML data file. The export process also removes any formatting and objects such as images.Using XML in ExcelSuggestions for practice1.List the ways you can create an XML map.2.List the ways you can save XML data in Excel.Using XML in ExcelTest 1, question 1You create an XML map when you need to:(Pick one answer.)1.Enter an array function (also called a CSE function).2.Link cells in a worksheet with items in a schema and make use of XML data.3.Use the XML Spreadsheet file format.Using XML in ExcelTest 1, question 1: AnswerLink cells in a worksheet with items in a schema and make use of XML data.And to create the map, all you need is a schema or a data file.Using XML in ExcelTest 1, question 2You must create a map before you can save a spreadsheet as an XML data file.(Pick one answer.)1.True.2.False.Using XML in ExcelTest 1, question 2: AnswerTrue.You can create two types of maps, and the next lessons explain how.Lesson 2Opening XML files as XML listsUsing XML in ExcelOpening XML files as XML lists XML lists are new to Excel 2003. Opening an XML data file as an XML list is a quick way to browse the data in that file.XML lists also make it easy for you to sort and filter data and to insert common calculations, such as totals and averages.Find facts and answers quickly with an XML list.Using XML in ExcelOpening files as XML lists What is an XML list?An XML list has the same look, feel, and features as an Excel list. You can sort, filter, and add common calculations like sums and averages to your data. The only real difference between XML lists and Excel lists is the data source.XML lists have the same look and feel as Excel lists, but they use XML files as their data source.Using XML in ExcelOpening files as XML listsWhen do you use an XML list?You open an XML data file as an XML list when you want to view or work with the data in that file quickly, or when the file contains a small, straightforward data structure.XML lists have the same look and feel as Excel lists, but they use XML files as their data source.Using XML in ExcelImportant points about XML listsXML lists have the same look and feel as Excel lists, but they use XML files as their data source.•You don't see the process, but Excel creates a map for you automatically when you open the data file as a list. •The map becomes part of the workbook, and Excel saves any changes or new data to the workbook in the standard Excel file format (.xls).•You can't export the data from the list, but you can import new or changed data into the list.Using XML in ExcelSuggestions for practice1.Name the only real difference between XML lists and Excel lists. 2.Describe when you would use an XML list.3.Describe at least two important points about XML lists.Online practice(requires Excel 2003)Using XML in ExcelTest 2, question 1Excel creates an XML map for you when you open a data file as an XML list.(Pick one answer.)1.True.2.False.Using XML in ExcelTest 2, question 1: AnswerTrue.Remember that Excel does all the work for you, you have no control over the map, and the map becomes part of the workbook.Using XML in ExcelTest 2, question 2You open an XML data file as an XML list when you need to: (Pick one answer.)1.Apply a transform to it.2.Quickly browse, sort, or filter data.3.Export it for use by others.Using XML in ExcelTest 2, question 2: AnswerQuickly browse, sort, or filter data.You can also add totals, averages, and other common calculations to the list.Lesson 3Creating XML maps with the XML Source task paneUsing XML in ExcelUsing the XML Source task pane Using the XML Sourcetask pane to create a map is the most powerful and flexible way to use XML in Excel 2003. The options in this task pane enable you to be selective about which data you view. The XML Sourcetask pane is how you really put XML to work in Excel.Using XML in ExcelWhy create XML maps with the task pane? Creating an XML map with the XML Sourcetask pane takes a bit of time and effort, but the results are much more powerful. You can:Maps link the items in a schema with the cells in a worksheet.•Display only the data you want to see by dragging the elements you need onto your worksheet.Using XML in ExcelWhy create XML maps with the task pane? Maps link the items in a schema with the cells in a worksheet.•Save or export the mapped data as standard XML, which means that other users or systems can process it. •Refresh the data in mapped cells as needed. Using XML in ExcelAn example of a map at work Here's a common example of how you might use a map. Say that one of your customers sends purchase orders in the form of XML files created in Microsoft Office Word 2003.Maps can help you use XML data from any number of sources.Using XML in ExcelAn example of a map at workYour shipping department appreciates the use of XML, but it uses Excel to track all orders and shipments. So, you create a map to make the data usable in Excel.Maps can help you use XML data from any number of sources.Using XML in ExcelAn example of a map at workIn this particular case, the items in the file created with Word are mapped to specific cells in a worksheet. The employees in the shipping department then put the worksheets to use as needed to retrieve ordered items and track shipments.Maps can help you use XML data from any number of sources.Using XML in ExcelSuggestions for practice1.List a few results you can achieve by creating an XML map with the XML Sourcetask pane.2.Describe a scenario in which you might use an XML map.Online practice(requires Excel 2003)Using XML in ExcelTest 3, question 1You export data when you need to:(Pick one answer.)1.Empty the workbook to make room for new data.2.Make the data available to other users or systems.3.Create tables in a relational database.Using XML in ExcelTest 3, question 1: AnswerMake the data available to other users or systems.You export data to an XML file so that other systems or users can process it.Using XML in ExcelTest 3, question 2When you first create a map, the mapped cells remain empty because:(Pick one answer.)1.Networks aren't engineered to provide data while you create a map.2.Excel requires you to filter the data first.3.Schemas don't contain data.Using XML in ExcelTest 3, question 2: AnswerSchemas don't contain data.Schemas contain rules for what can and can't reside in an XML data file. That's why you have to import data into the mapped cells after you create the map.Lesson 4Understand the XML Source task paneUsing XML in ExcelUnderstand the XML Source task pane Now that you've had a chance to use the XML Sourcetask pane, take a deeper look at the controls it provides.You’ll see what the icons in the element list mean, and how to use the various XML Sourceoptions.Learn the ins and outs of the XML Sourcetask pane.Using XML in ExcelThe controls in the XML Source task pane You can open the task pane in several ways:Controls and other elements in the XML Sourcetask pane•Press SHIFT + F1.•From the Datamenu (XMLsubmenu, XML Sourcecommand). •With the Use the XML Source task panecommand in the Open XMLdialog box (Filemenu, Opencommand).Using XML in ExcelThe controls in the XML Source task pane1.List of XML maps that you or others have added to the workbook. Use the list to select a given map.2.Element list. A hierarchical list of the elements in an XML schema.Controls and other elements in the XML Sourcetask paneControls and elements in the XML Sourcetask pane:Using XML in ExcelThe controls in the XML Source task pane3.Set options for using the XML Sourcetask pane.4.Open the XML Mapsdialog box.5.Before you export data from mapped cells, this option validates your data against the schema used to create the map.Controls and other elements in the XML Sourcetask paneUsing XML in ExcelWhat do all those icons mean? There are a number of icons in the XML Sourcetask pane. What icons you see in the task pane depends on the schemas that you use to create maps. The following table lists and describes all of the possible icons in the XML Sourcetask pane.The icons in the element list indicate the type of element you're adding to a map.Using XML in ExcelIcons in the elements listIconElement typeIconElement typeParent elementRepeating child elementRequired parent elementRequired repeating child elementRepeating parent elementAttributeRequired repeating parent elementRequired attributeChild elementSimple content in a complex structureRequired child elementRequired simple content in a complex structureUsing XML in ExcelUnderstand the task pane optionsOptions for the XML Sourcetask pane1.Preview Data in Task PaneWhen checked, displays sample data in the element list.2.Hide Help Text in the Task PaneWhen checked, hides the Help text that appears below the element list in the task pane.To see these options, click the Optionsbutton in the XML Sourcetask pane.Using XML in ExcelUnderstand the task pane optionsOptions for the XML Sourcetask pane3.Automatically Merge Elements When MappingWhen checked, Excel creates an XML list when you place repeating elements next to each other on a row.4.My Data Has HeadingsWhen checked, Excel uses the existing column headings in a worksheet when you map repeating elements.Using XML in ExcelUnderstand the task pane optionsOptions for the XML Sourcetask pane5.Hide Border of Inactive ListsWhen checked, this option hides the border of a list or a single-mapped cell when you select a cell outside of the list or the single-mapped cell.Using XML in ExcelSuggestions for practice1.Name both ways that you can open the XML Sourcetask pane.2.Describe how you can view the options for the XML Sourcetask pane.3.List a few of the options for the task pane, and what they do. Using XML in ExcelTest 4, question 1This icon represents a required child element: (Pick one answer.)1.True.2.False.Using XML in ExcelTest 4, question 1: AnswerFalse.This is a basic child element icon. (If it were required, there would be a red star in one corner.) Chances are, you’ll map child elements more than any others. Using XML in ExcelTest 4, question 2You can add any number of XML maps to a worksheet.(Pick one answer.)1.True.2.False.Using XML in ExcelTest 4, question 2: AnswerTrue.Keep in mind that adding dozens of maps can make the workbook hard for others to use.Using XML in ExcelQuick Reference CardFor a summary of the tasks covered in this course, view the Quick Reference Card.