Creating Tests

Tests are written from a separate worksheet interface. Figure 8.11 shows the Create_Edit_Tests worksheet with problems from an existing test imported into its data list.

Command Button control

The problem

Test results

Create List From Data Excel Macro

Excel Lists mapped^^ to XML files V

Command Buttons^

Create_Edit_ Tests worksheet used to write or edit tests.

Excel Lists mapped^^ to XML files V

Command Buttons^

Excel Xml Denormalize

-XML Map Name

-XML Source Task Pane

- Element Type Icons

Create_Edit_ Tests worksheet used to write or edit tests.

-XML Map Name

-XML Source Task Pane

- Element Type Icons

The data in the worksheet is formatted as an Excel list and is mapped to two XML files. The range A2:C2 is mapped to an XML file with the following structure:

<?xml version="1.0" encoding="UTF-8"?> <test_properties fileID=""> <level></level> <time></time> </test_properties>

The elements <level> and <time> are mapped to cells B2 and C2 respectively, and the filelD attribute of the <test_properties> element is mapped to cell A2. I initially created the map by importing the file as an XML list when it was void of data; although, it doesn't matter if there is data in the XML file because it's the document structure that's important. The name of the map is test_properties_Map. Excel creates the initial value for a map's name by concatenating the root element name with the word Map. You can change it by selecting Data, XML, XML Map Properties in the Excel application window.

The <level> and <time> elements are non-repeating child elements of <test_properties> so each test will have an associated test properties file. These files are named by concatenating the filelD attribute in cell A2 with the character p followed by the xml file extension.

When opening an XML file in Excel that does not reference a schema, Excel automatically creates a schema based on the XML source data. If you resave the data from Excel and examine the resulting XML source code in a text editor, you will notice two new declarations. The first new declaration is referred to as the standalone document declaration and can be found in the XML declaration at the beginning of the document.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

Excel adds the standalone document declaration so that it knows the XML document has external markup declarations (the schema created by Excel), but these external declarations do not affect the document's content.

In addition to the standalone document declaration, Excel adds a reference to the location of the schema reserved for an Excel workbook. The reference is added as an attribute to the root element of the XML document.

<root_element_name xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

The <level> and <time> elements are non-repeating child elements of <test_properties> so each test will have an associated test properties file. These files are named by concatenating the filelD attribute in cell A2 with the character p followed by the xml file extension.

To create a test, the user simply enters values for the number, operands, operator, and answer into the corresponding columns in the worksheet. In order to save time, the user may use formulas (if desired) to calculate answers or generate operands. The problems contained in the data list (cells D2:H22 in Figure 8.11) are mapped to a second XML file. The data list can be extended to any number of rows. The name of the XML map is test_Map (see Figure 8.11) and the XML document structure is listed again in the following:

<?xml version="1.0" encoding="UTF-8"?> <test>

<problem>

<number></number> <left_operand></left_operand> <operator></operator> <right_operand></right_operand> <answer></answer> </problem>

<!--repeating <problem> elements-> </test>

The structure of the XML test file contains the root element <test> with a series of child elements (<problem>) that represent the test problems. Each <problem> element contains the child elements that define a problem (<number>, <left_operand>, <operator>, <right_operand>, and <answer>).

When this file is opened as an XML list, the data elements are loaded into adjacent columns in the worksheet. Element types (child, parent, attribute, and so on) can be identified from the icon displayed in the XML Source Task Pane. In order for Excel to recognize a repeating parent element such as <problem>, I had to include at least two of these elements in the original file that I opened with Excel when creating the XML list and map.

I will use the fileID attribute of the <test_properties> element to specify the file name of a test file; therefore, each test is associated with two XML files (for example, test7p.xml and test7.xml). The program only needs one of these files to open an existing test because a test file's name is stored in the fileID attribute of the test properties file; thus, when the user chooses to open a test file, they must be shown a selection of test property files and not the test files themselves.

You may be wondering why I used two XML files to describe a single test. An easier approach might combine the two structures into a single XML document

<?xml version="1.0" encoding="UTF-8"?> <test fileID="">

<problem>

<number></number> <left_operand></left_operand> <operator></operator> <right_operand></right_operand> <answer></answer> </problem>

<!--repeating <problem> elements--> </test>

The problem with this structure is that the <level> and <time> elements, and the fileID attribute are associated with every <problem> element in the file; so when Excel imports the data into a worksheet, it will repeat the values for the fileID attribute, and <level> and <time> elements. This causes a data redundancy and the resulting map is said to be denormalized. Excel cannot export data from a denormalized map to an XML file.

similar to the following:

similar to the following:

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment