XML and Microsoft Office Excel 2003: Creating an Expense Report Template

  • Article

 

Frank C. Rice
Microsoft Corporation

June 2003

Applies to:**
**    Microsoft Office Excel 2003

Summary: Learn how to reuse legacy forms and documents by taking advantage of some of the new features of Excel. This document discusses repeating and non-repeating XML data, creating Data Maps, and exporting data as XML. (11 printed pages)

Download odc_xlexpen.exe.

Contents

Introduction
The Contoso Expense Report
The XML Data
The Schema File
Creating an XML Map
Populating the Data Map
Exporting the Data
Conclusion

Introduction

One concern of managers and Information Technology personnel when considering a move to updated or new technology is what about the existing legacy processes and forms? For example, a payroll department will have certain processes in place that allow employees to record and submit their hours worked, vacation hours used, and sick time taken. This process will usually entail one or more forms including various templates. Templates are forms that are used as the basis for the working forms used every day in an organization. One example of these are the timesheets distributed to employees on a weekly or monthly basis. Additionally, the payroll department will also have other forms and form templates related to adding dependents and implementing direct deposits. Likewise, the accounting department will utilize a number of forms for such things as reporting travel expenses, managing accounts receivable, and so forth.

Because of the difficulty and expense in redesigning and replacing these forms, organizations will sometimes delay or forego moving to new technology. And even for those organizations that do make the move to such technologies as storing their data as XML, this move doesn't necessarily translate well to legacy forms.

The Contoso Expense Report

Fortunately with the new XML mapping features in Microsoft® Office Excel 2003, recreating spreadsheet templates can usually be accomplished by simple drag-and-drop. The following scenario demonstrates one example of using the XML mapping features in Excel to 'retro fit' an existing spreadsheet. An example of an expense report template appears in the following figure.

Figure 1. Expense Report Template

In this template, the light blue cells represent mapped elements that occur only once in the form. For example, the Name and Purpose cells are only filled out once per form. Whereas, the cells in the lower portion of the template (the white grid area) represent repeating data and may be filled out zero or more times as the user records multiple expenses. These columns include the Date, Description of Expense, Mileage, and so forth. This template is typical of an expense report used by an organization.

The XML Data

We’ll begin by examining the XML data that will be used later in this scenario to populate the form. Only a portion of the file is displayed here. The complete file can be found as part of the accompanying download:

...
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root>
    <Meta>
        <Name>Nancy Davolio</Name>
        <Email>nancyd</Email>
        <EmployeeNumber>1</EmployeeNumber>
        <CompanyCode>1001</CompanyCode>
        <CostCenter>10101</CostCenter>
        <StartDate>2003-05-01</StartDate>
        <EndDate>2003-05-05</EndDate>
        <Purpose>Research for Tablet PCs</Purpose>
    </Meta>
    <Summary>
        <TravelTotal>0</TravelTotal>
        <MealsTotal>0</MealsTotal>
        <ConferenceTotal>0</ConferenceTotal>
....</Summary>
....<ExpenseItem>
........<Date>2003-05-01</Date>
........<Description>Pacer Tablet PCs</Description>
........<Miles/>
........<Mileage>0</Mileage>
........<AirFare/>
........<Other/>
........<Meals/>
........<Conference/>
........<Misc>2000</Misc>
........<MiscCode>2</MiscCode>
........<Amount>2000</Amount>
....</ExpenseItem>
....<ExpenseItem>
........<Date>2003-05-02</Date>
........<Description>Space Tablet PCs</Description>
........<Miles/>
........<Mileage>0</Mileage>
........<AirFare/>
........<Other/>
........<Meals/>
........<Conference/>
........<Misc>1200</Misc>
........<MiscCode>2</MiscCode>
........<Amount>1200</Amount>
....</ExpenseItem>
....
</Root>

After the XML declaration and <Root> tag, comes the group of elements between the <Meta> and <Summary> tags that represent information about the employee. As you might expect, these items, for example the employees name and e-mail alias, will occur only once in the expense report. Following are the repeating elements that represent expense line items in the form. These, too, will be identified in the schema with an attribute designator.

The Schema File

Next, we will look at the contents of the MSExpenseSchema.xsd file which is the schema file for the expense report. A schema file allows you to specify the syntax of the XML document as well as specify the data type of each element, create simple and complex types, specify the minimum and maximum times that an element can occur, restrict the ranges of values that elements can hold, and much more.:

...
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <xsd:element name="Root">
         <xsd:complexType mixed="false">
            <xsd:sequence minOccurs="0" maxOccurs="1">
               <xsd:element minOccurs="1" maxOccurs="1" name="Meta">
                  <xsd:complexType mixed="false">
                     <xsd:all>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="Name"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="Email"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="EmployeeNumber"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="CompanyCode"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="CostCenter"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:date" name="StartDate"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:date" name="EndDate"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="Purpose"/>
                     </xsd:all>
                  </xsd:complexType>
               </xsd:element>
               <xsd:element minOccurs="1" maxOccurs="1" name="Summary">
                  <xsd:complexType mixed="false">
                     <xsd:all>
.........................<xsd:element type="xsd:decimal" name="TravelTotal"/>
.........................<xsd:element type="xsd:decimal" name="MealsTotal"/>
.........................<xsd:element type="xsd:decimal" name="ConferenceTotal"/>
                     </xsd:all>
                  </xsd:complexType>
               </xsd:element>
               <xsd:element minOccurs="0" maxOccurs="unbounded" name="ExpenseItem">
                  <xsd:complexType mixed="false">
 ................<xsd:sequence minOccurs="0" maxOccurs="1">
.................<xsd:element type="xsd:date" name="Date"/>
.................<xsd:element type="xsd:string" name="Description"/>
.................<xsd:element type="xsd:integer" name="Miles"/>
.................<xsd:element type="xsd:decimal" name="Mileage"/>
.................<xsd:element type="xsd:decimal" name="AirFare"/>
.................<xsd:element type="xsd:decimal" name="Other"/>
.................<xsd:element type="xsd:decimal" name="Meals"/>
.................<xsd:element type="xsd:decimal" name="Conference"/>
.................<xsd:element type="xsd:decimal" name="Misc"/>
.................<xsd:element type="xsd:int" name="MiscCode"/>
.................<xsd:element type="xsd:decimal" name="Amount"/>
.................</xsd:sequence>
                  </xsd:complexType>
               </xsd:element>
            </xsd:sequence>
         </xsd:complexType>
      </xsd:element>
   </xsd:schema>
...

Note the use of a namespace in this schema file—http://www.w3.org/2001/XMLSchema and that the elements used in the schema such as <xsd:element> are part of that namespace. A namespace is utilized to make sure that items referenced are unique. For example, an XML document may contain different <Language> tags; one referring to a language such as English or Spanish, and one referring to the a computer programming such as Microsoft Visual C#® or Microsoft Visual Basic®. By prefacing one of the tags with the namespace attribute, you (and any application that uses the file) can differentiate which tag is being used. Continuing with our examination of the schema file, look at the sequence of elements in the following segment:

...
<xsd:element name="Root">
<xsd:complexType mixed="false">
<xsd:sequence minOccurs="0" maxOccurs="1">
...

The first two statements define an element named <Root> as a complex-type. A complex-type is an element that consists of multiple simple-type elements and attributes. For example, a complex type might consist of String and Integer data types. The mixed=false attribute specifies that the content of the Root element can't be a mix of text and elements.

Note   Excel doesn't support mixed mode content.

Returning to the schema, the <Root> element is the outermost element in our structure (although it doesn't have to be named Root-it could be any valid element name). The attributes <sequence minOccurs="0" maxOccurs="1"> specify that the child elements of the <Root> element appear zero or one time.

The element <xsd:element minOccurs="1" maxOccurs="1" name="Meta"> identifies an element name Meta which must appear one time. The <xsd:All> element encloses a group of elements that must appear one time as part of the Meta element.

The next series of elements defines another group of single occurrence subelements for the <Summary> element. These elements contain similar attributes that we discussed for the <Meta> element.

Next, we see the <ExpenseItem> element and its subelements. These are the repeating elements that will repeat for each line item in the expense report. Look at the following segment from the file:

...
<xsd:element minOccurs="0" maxOccurs="unbounded" name="ExpenseItem">
<xsd:complexType mixed="false">
<xsd:sequence minOccurs="0" maxOccurs="1">
<xsd:element type="xsd:date" name="Date"/>
...

The first two statements define the <ExpenseItem> element as a complex type that can occur for zero or for an unlimited number of times. The next statement specifies that the subelements of the <ExpenseItem> element can occur zero or one time and only in the sequence that they appear in the schema. The remaining elements in the group define the other expense fields in the template. Now let’s see how we can use Excel to help us combine the XML data file and the schema for to create an expense report template.

Creating an XML Map

In the following series of procedures, we’ll take an unmapped expense report template (representing a legacy form) and map it to a schema. This will then allow any data that is imported to appear in the correct locations in the form automatically. Instead of being an expense report, this form could just as easily be any form that exists as a spreadsheet. It could also represent any other type of form that an organization wants to use as a template. We’ll start by opening the workbook containing the unmapped template.

To Create the Map:

  1. Start Excel, locate, and then open ExpenseReportUnmapped.xls.

  2. Next, point to XML on the Data menu, and then click XML Source. The XML Source task pane is displayed.

  3. In the XML Source task pane, click Workbook Maps.

  4. In the XML Maps dialog box, click Add.

  5. Locate the file MSExpenseSchema.xsd, and click Open. The Root_Map designator is displayed in the XML Maps dialog box.

  6. Click OK. The elements of the schema are displayed in the XML Source task pane.

    Figure 2. Expense report schema in the XML Source task pane

    Next, map the individual elements of the schema to the cells in the worksheet.

  7. Drag-and-drop the named elements from the XML Source task pane onto like-named cells in the worksheet. For example, drag the Name element from the XML Source task pane to the single-mapped area (blue cells) by the Name label. Likewise, drag the Date element from the task pane to the repeating cell area (white cells) on the Date column.

In addition to having single instance cells and repeating columns of data mapped in a worksheet, you can also have columns in a list of repeating elements that aren’t mapped to XML, such as those used for calculations. These unmapped cells would be part of the template but not part of any data exported into the worksheet. To insert unmapped columns into a list, you must select a cell in the list, right-click and then click Column on the Insert menu, OR drag the gray handle on the bottom-right of the list to the right.

Instead of completing the mapping of cells in the unmapped workbook, there is already a fully mapped workbook that you can use by opening the ExpenseReportMapped.xls file. You can see that some of the cells are already filled in with data as seen in Figure 1.

Populating the Data Map

Now that you've seen how to create a data map, we'll import some data into the map. This data will overwrite any existing data in the map.

  1. While still in the ExpenseReportMapped.xls file, point to XML on the Data menu, and then click Import.
  2. In the Import XML dialog box, click XML Files in the Files of type drop-down box, and then locate the XML file ExpenseReport.xml. Click Import.

The map should now appear as seen in Figure 1. Because we mapped the data in advance, the XML data in our file appears in the correct locations.

Exporting the Data

Now we will export the data from the expense report to see the options available in Excel for creating external XML files.

To Export the Data

  1. Click on the Data menu, point to XML and then click Export.
  2. Click XML Files in the Save as type drop-down list. The XML data is saved without any presentation or formatting information.

To Save the XML Data or the XML Presentation Information

  1. Click to Save As on the File menu.
  2. Click XML Data in the Save as type drop-down list and then click Save. This saves just the data from this worksheet as an XML file.
  3. Click to Save As on the File menu.
  4. Click XML Spreadsheet in the Save as type drop-down list and then click Save. This option saves the entire workbook as XML; with formatting, layout, calculations, etc. By opening just this file into a blank worksheet, you can recreate the worksheet, including the data.

The value behind exporting as XML File or saving as XML Data is that it separates the data from the presentation—giving you just the relevant data I XML with none of the extra formatting you get if you save as an XML spreadsheet.

In addition to exporting through the user interface, you can also export by using the Excel object model. To demonstrate:

To Export Data Using the Export Method

  • Click the Submit button. A message box is displayed with selected data range from the worksheet and then the data from cells B15 through B19 is exported to "C:\expense_sample.xml".

The following listing is the code that is executed when the Submit button is clicked:

Sub SubmitClick()
    Dim strRange As String
    
    Application.ActiveWorkbook.XmlMaps(1).ExportXml strRange
    Application.DisplayAlerts = False
    
    ' Review the XML that will be exported.
    MsgBox strRange
    
    ' Create the external XML file.
    Application.ActiveWorkbook.XmlMaps(1).Export "C:\expense_sample.xml", True

End Sub

In this code, the value of the range element representing the XML data map first displayed in a message box so that the developer can see a sample of what will be exported (see Figure 3), and then the data for the entire map is exported to the XML file. If there were more than one map in the workbook, you could choose to export a different map by changing the ordinal number of the XmlMaps object.

Figure 3. Sample XML data

Also notice in the listing the use of ExportXML and the Export methods. The ExportXML method exports XML data to a String variable whereas Export exports data to a file.

In this example, the data was written to a file. However, you could just as easily submit it to an XML Web service or to another application using the new methods added to Excel.

Conclusion

In this article, we discussed some of the difficulties of organizations moving to new technologies. We also demonstrated how you can reuse legacy forms by converting them to XML data maps in Excel through the use of XML schemas. Once the data map has been created then any data imported matching the schema definitions will automatically land in the correct location on import.