Microsoft Office Excel 2003 Training

1
Microsoft Office Excel 2003 Training

• Using XML in Excel

2
Course 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.)

3
Course contents, contd.

• Lesson 3 Creating XML maps with the XML Source
  task pane
• Lesson 4 Understand the XML Source task pane

Each lesson includes a list of suggested tasks
and a set of test questions.
4
Overview XML in Excel 2003

• Two new tools in Excel 2003 XML lists and the
  XML Source task pane make it easier than ever
  to use XML.

Learn the concepts behind these new tools, and
how to use them to process XML data in Excel 2003.
5
Course 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
  Source task pane.

6
Lesson 1

• The Excel approach to XML

7
The 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.
8
Getting 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.
9
Getting started using XML with Excel

• If 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.
10
XML 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

• 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 structure

Maps link elements in a schema to cells in a
worksheet.
11
Ways 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 Source task pane is the best option for
putting XML to work in Excel 2003.
12
Ways to create XML maps

• Create 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 Source task pane is the best option for
putting XML to work in Excel 2003.
13
Entering, 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
  Source task pane, you either enter data manually
  or import it from an existing file.

Excel provides several ways to save and
distribute your XML data.
14
Entering, saving, and exporting XML data

• Keep 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.
15
Entering, saving, and exporting XML data

• When you're ready to save your data, Excel gives
  you several choices. You'll use these options the
  most

• Save your data as an Excel workbook You do this
  by saving the file using any of the standard
  methods that Excel provides.

Excel provides several ways to save and
distribute your XML data.
16
Entering, saving, and exporting XML data

• Save your data as a separate XML file You do
  this by using the Save As command and selecting
  the XML Data file 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.

Excel provides several ways to save and
distribute your XML data.
17
Suggestions for practice

• List the ways you can create an XML map.
• List the ways you can save XML data in Excel.

18
Test 1, question 1

• You create an XML map when you need to (Pick one
  answer.)

• Enter an array function (also called a CSE
  function).
• Link cells in a worksheet with items in a schema
  and make use of XML data.
• Use the XML Spreadsheet file format.

19
Test 1, question 1 Answer

• Link 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.
20
Test 1, question 2

• You must create a map before you can save a
  spreadsheet as an XML data file. (Pick one
  answer.)

• True.
• False.

21
Test 1, question 2 Answer

• True.

You can create two types of maps, and the next
lessons explain how.
22
Lesson 2

• Opening XML files as XML lists

23
Opening 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.
24
Opening 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.
25
Opening files as XML lists

• When 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.
26
Important points about XML lists

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

XML lists have the same look and feel as Excel
lists, but they use XML files as their data
source.
27
Suggestions for practice

• Name the only real difference between XML lists
  and Excel lists.
• Describe when you would use an XML list.
• Describe at least two important points about XML
  lists.

Online practice (requires Excel 2003)
28
Test 2, question 1

• Excel creates an XML map for you when you open a
  data file as an XML list. (Pick one answer.)

• True.
• False.

29
Test 2, question 1 Answer

• True.

Remember that Excel does all the work for you,
you have no control over the map, and the map
becomes part of the workbook.
30
Test 2, question 2

• You open an XML data file as an XML list when you
  need to (Pick one answer.)

• Apply a transform to it.
• Quickly browse, sort, or filter data.
• Export it for use by others.

31
Test 2, question 2 Answer

• Quickly browse, sort, or filter data.

You can also add totals, averages, and other
common calculations to the list.
32
Lesson 3

• Creating XML maps with the XML Source task pane

33
Using the XML Source task pane

• Using the XML Source task 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 Source task pane is how you really put
XML to work in Excel.
34
Why create XML maps with the task pane?

• Creating an XML map with the XML Source task pane
  takes a bit of time and effort, but the results
  are much more powerful. You can

• Display only the data you want to see by dragging
  the elements you need onto your worksheet.

Maps link the items in a schema with the cells in
a worksheet.
35
Why create XML maps with the task pane?

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

Maps link the items in a schema with the cells in
a worksheet.
36
An 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.
37
An example of a map at work

• Your 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.
38
An example of a map at work

• In 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.
39
Suggestions for practice

• List a few results you can achieve by creating an
  XML map with the XML Source task pane.
• Describe a scenario in which you might use an XML
  map.

Online practice (requires Excel 2003)
40
Test 3, question 1

• You export data when you need to (Pick one
  answer.)

• Empty the workbook to make room for new data.
• Make the data available to other users or
  systems.
• Create tables in a relational database.

41
Test 3, question 1 Answer

• Make the data available to other users or systems.

You export data to an XML file so that other
systems or users can process it.
42
Test 3, question 2

• When you first create a map, the mapped cells
  remain empty because (Pick one answer.)

• Networks aren't engineered to provide data while
  you create a map.
• Excel requires you to filter the data first.
• Schemas don't contain data.

43
Test 3, question 2 Answer

• Schemas 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.
44
Lesson 4

• Understand the XML Source task pane

45
Understand the XML Source task pane

• Now that you've had a chance to use the XML
  Source task pane, take a deeper look at the
  controls it provides.
• Youll see what the icons in the element list
  mean, and how to use the various XML Source
  options.

Learn the ins and outs of the XML Source task
pane.
46
The controls in the XML Source task pane

• You can open the task pane in several ways

• Press SHIFT F1.
• From the Data menu (XML submenu, XML Source
  command).
• With the Use the XML Source task pane command in
  the Open XML dialog box (File menu, Open command).

Controls and other elements in the XML Source
task pane
47
The controls in the XML Source task pane
Controls and elements in the XML Source task
pane

• List of XML maps that you or others have added to
  the workbook. Use the list to select a given map.
• Element list. A hierarchical list of the elements
  in an XML schema.

Controls and other elements in the XML Source
task pane
48
The controls in the XML Source task pane

• Set options for using the XML Source task pane.
• Open the XML Maps dialog box.
• 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 Source
task pane
49
What do all those icons mean?

• There are a number of icons in the XML Source
  task 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 Source task pane.

The icons in the element list indicate the type
of element you're adding to a map.
50
Icons in the elements list
51
Understand the task pane options
To see these options, click the Options button in
the XML Source task pane.

• Preview Data in Task Pane When checked, displays
  sample data in the element list.
• Hide Help Text in the Task Pane When checked,
  hides the Help text that appears below the
  element list in the task pane.

Options for the XML Source task pane
52
Understand the task pane options

• Automatically Merge Elements When Mapping When
  checked, Excel creates an XML list when you place
  repeating elements next to each other on a row.
• My Data Has Headings When checked, Excel uses the
  existing column headings in a worksheet when you
  map repeating elements.

Options for the XML Source task pane
53
Understand the task pane options

• Hide Border of Inactive Lists When 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.

Options for the XML Source task pane
54
Suggestions for practice

• Name both ways that you can open the XML Source
  task pane.
• Describe how you can view the options for the XML
  Source task pane.
• List a few of the options for the task pane, and
  what they do.

55
Test 4, question 1

• This icon represents a required child element
  (Pick one answer.)

• True.
• False.

56
Test 4, question 1 Answer

• False.

This is a basic child element icon. (If it were
required, there would be a red star in one
corner.) Chances are, youll map child elements
more than any others.
57
Test 4, question 2

• You can add any number of XML maps to a
  worksheet. (Pick one answer.)

• True.
• False.

58
Test 4, question 2 Answer

• True.

Keep in mind that adding dozens of maps can make
the workbook hard for others to use.
59
Quick Reference Card

• For a summary of the tasks covered in this
  course, view the Quick Reference Card.

60
USING THIS TEMPLATE

• See the notes pane or view the full notes page
  (View menu) for detailed help on this template.