Étiquette : integration_excel_xml

Exporting data from an Excel worksheet to an XML document is similar to importing, but the steps are performed in reverse.
It is also important to remember that exporting data to an XML document is not possible without the corresponding XML schema.

So, perform the following steps:

• Open the Microsoft Excel file that also contains an XML map (see file 8-File for export.xlsx on the CD).
• Go to the Developer tab on the ribbon and in the XML group select the Export command.
• In the Export XML dialog box that appears, choose the location for the exported data and, in the File name field, enter a name for the XML file to be saved. Click the Export button.
• Make sure that the XML file you created uses UTF-8 encoding for Russian characters and that it can be viewed in a browser.

If an XML document contains an XML schema, then when importing data from such a document, Excel can use the information from the schema linked to the XML document and store it in XML maps of the corresponding workbook into which the data from the original document is imported.
When the original document does not contain a schema, Excel attempts to create an XML map on its own — based on an analysis of the data contained in the source document.

Importing Data from an XML File Without an XML Schema

To import data from an XML file:

• Go to the File tab on the ribbon and choose Open.
• In the Open File dialog box, click the drop-down list for All Excel Files and choose XML Files (*.xml). After this, only files of this type will be displayed in the file list area.
• Select the required file in the file list area and click Open.
• In the Open XML dialog box that appears, select the XML Table option and click OK.

• If the file being opened does not contain a data schema, a warning will appear stating that Excel will create a schema based on the XML file.

• Click OK — the data from the XML file will be imported onto an Excel worksheet.

• To view the XML map created by Excel, go to the Developer tab on the ribbon and in the XML group click Source: in the task pane on the right side, the XML Source pane will open with the created XML map.

Creating an XML Map and Importing Data from an XML File

Suppose we already have an XML schema. To create an XML map in MS Excel, perform the following steps:

• Open an Excel file (or create a new one) into which you need to import data from the XML document.
• Go to the Developer tab on the ribbon and in the XML group click Source: in the task pane on the right side, the XML Source pane will open.

• Click the XML Maps button in the lower-right corner of the XML Source pane — the XML Maps dialog box will open.
• In the XML Maps dialog box, click Add to open the Select XML Source dialog box.

• Select the file to create the map from and click Open.
• If the selected source contains multiple root nodes, a dialog box for selecting a root node for the XML map will appear.
• Choose the appropriate option from the list and click OK. A line with the parameters of the added map will appear in the XML Maps dialog box.

• Highlight the name of the added schema in the XML Maps dialog box and click OK. The map will be added to the workbook and displayed in the XML Source pane in Excel’s working area.

To use the added XML map:

• Drag the required elements from the XML Source pane onto the worksheet to specify which fields to display on the worksheet.

• Go to the Developer tab on the ribbon, in the XML group click Import. Then, after specifying the required file, the data will be imported into the Excel worksheet.

As mentioned earlier, when working with XML documents, a data schema describing their structure is necessary.
In practice, the prolog usually contains an XML schema describing which elements the document using this schema may contain, which attributes correspond to which elements, and so on.

If we draw an analogy with a database, an XML schema resembles the description of attributes and data types for tables in a database.
A special language exists for describing schemas — XSD (XML Schema Definition Language).

The process of comparing the contents of an XML document against a certain XML schema is called validation.

The schema itself may be placed directly inside the document, but more often it is stored in a separate file with the .xsd extension, while the XML document itself contains a reference to this file.

NOTE: A schema may be completely absent from the document — neither inside it nor as a reference. In this case, validation is carried out either manually or programmatically.

Namespaces

Several schemas can be used in a single XML document. In this case, the problem of name conflicts arises: different schemas may define the same names, and if a document refers to two such schemas, each defining the same element name differently, the question arises: which definition applies?

To solve this problem, the concept of a namespace is introduced. By specifying a name, one can always determine the corresponding namespace. A namespace must also have a unique name (prefix). To specify a prefix, a URL (Uniform Resource Locator) can be used.

A namespace is defined inside the opening tag of an element:

<namespacePrefix:elementName xmlns:namespacePrefix = "URL">

The URL used does not necessarily have to point to a real file, since its main purpose is to ensure uniqueness.

A document may use several namespaces, one of which may remain unnamed. In this case, it is called the default namespace.

Using a Default Namespace

<?xml version="1.0" encoding="Windows-1251" ?>
<!-- Using a default namespace -->
<employee xmlns = "http://www.myorg.ru/staff">
  <name> Петров </name>
  <salary currency="р."> 100000 </salary>
</employee>

Requirements for an XML Schema

Note that a schema is also an XML document and must satisfy the following requirements:

• all schemas must have a top-level element named schema;
• all schemas must use the same base namespace, whose URL is:
  http://www.w3.org/2001/XMLSchema.
  In addition to the base namespace, additional namespaces may also be used in the schema.

For example, an XML schema with the base namespace bn can be defined as shown:

Example of an XML Schema

<?xml version="1.0" encoding="Windows-1251" ?>
<bn:schema xmlns:bn="http://www.w3.org/2001/XMLSchema">
  <bn:element name="employee">
    <bn:complexType>
      <bn:sequence>
        <bn:element name="name" type="bn:string"/>
        <bn:element name="salary" type="bn:integer"/>
      </bn:sequence>
    </bn:complexType>
  </bn:element>
</bn:schema>

Schema Embedded in an XML Document

The XML schema given in Listing 10.6 can be directly inserted into an XML document (Listing 10.7).

Example of Using a Schema Inside an XML Document

<?xml version="1.0" encoding="Windows-1251" ?>
<employees>
  <!-- Beginning of schema -->
  <bn:schema xmlns:bn="http://www.w3.org/2001/XMLSchema">
    <bn:element name="employee">
      <bn:complexType>
        <bn:sequence>
          <bn:element name="name" type="bn:string"/>
          <bn:element name="salary" type="bn:integer"/>
        </bn:sequence>
      </bn:complexType>
    </bn:element>
  </bn:schema>
  <!-- End of schema -->
  <employee>
    <name> Петров </name>
    <salary>10000</salary>
  </employee>
  <employee>
    <name> Сидоров </name>
    <salary>15000</salary>
  </employee>
</employees>

External XML Schema

In the previous section, we considered using a schema inside an XML document.
However, the most optimal approach is to use an external schema stored in a separate file.

Enter the code from Listing 10.6 in a text editor (e.g., Notepad) and save it under the name 5-Schema.xsd.

To specify in the document that it should be validated using the schema stored in 5-Schema.xsd, it is necessary to reference this file in a special attribute (from the namespace http://www.w3.org/2001/XMLSchema-instance).

• If the document refers to any additional namespaces (besides the one above), the schemaLocation attribute is used.
• Otherwise, the noNamespaceSchemaLocation attribute is applied.

XML Document Referring to Schema 5-Schema.xsd

<?xml version="1.0" encoding="Windows-1251" ?>
<!-- Using an external XML schema -->
<employee xmlns:bni="http://www.w3.org/2001/XMLSchema-instance"
          bni:schemaLocation="employee 5-Schema.xsd">
  <name> Петров </name>
  <salary>10000</salary>
</employee>

Another Example of an XML Schema

Let us now consider another XML schema, to be saved as 6-Schema.xsd, which allows the use of a list of multiple records.
The corresponding XML document for this schema is shown:

Example of an XML Schema for Verifying a List of Multiple Records

<?xml version="1.0" encoding="Windows-1251" ?>
<bn:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <bn:element name="employees">
    <bn:complexType>
      <bn:sequence>
        <bn:element ref="employee" maxOccurs="unbounded"/>
      </bn:sequence>
    </bn:complexType>
  </bn:element>
  <bn:element name="employee">
    <bn:complexType>
      <bn:sequence>
        <bn:element name="firstname" type="bn:string"/>
        <bn:element name="lastname" type="bn:string"/>
        <bn:element name="salary" type="bn:integer"/>
      </bn:sequence>
    </bn:complexType>
  </bn:element>
</bn:schema>

XML Document with a List of Multiple Employees

<?xml version="1.0" encoding="Windows-1251" ?>
<!-- Example of using an XML schema -->
<employees xmlns:bni="http://www.w3.org/2001/XMLSchema-instance">
  bni:schemaLocation="employee 6-Schema.xsd"
  <employee>
    <firstname> Иван </firstname>
    <lastname> Петров </lastname>
    <salary> 10000 </salary>
  </employee>
  <employee>
    <firstname> Дмитрий </firstname>
    <lastname> Федоров </lastname>
    <salary> 9000 </salary>
  </employee>
  <employee>
    <firstname> Анна </firstname>
    <lastname> Котова </lastname>
    <salary> 15000 </salary>
  </employee>
</employees>

 

An XML document consists of a prolog and a root element that includes all other elements.
The prolog contains information about the version of XML used in the document and, as a rule, information about character encoding.
Often, the prolog also contains information about the declaration of a standalone document and whether there are references to an external markup file, which may directly affect the editable XML file.

Thus, the value « yes » in the standalone document declaration indicates the absence of external markup declarations that would affect the information passed by the XML processor to the application. For example, a prolog with a standalone document declaration looks like this:

<?xml version="1.0" encoding="Windows-1251" standalone="yes"?>

Accordingly, the simplest XML document may look like the one shown :

Example of a simple XML document

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<!-- Example of an XML document -->
<employee>
  <person lastname="Walkenbach" firstname="John"
          email="johnw@yandex.com"/>
  <person lastname="Wiley" firstname="Gary"
          email="gwiley@yandex.com"/>
</employee>

As a rule, XML code can be typed in a text editor and saved with the .xml extension. For example can be typed in Notepad and then opened in Internet Explorer.

It should be noted that an XML document opened in a browser can be viewed element by element.
For example, if you click the minus sign to the left of the <employee> element, the nested elements will be hidden.

Similar to an HTML document, an XML document also contains tags.
The main components of an XML document are elements, attributes, and comments.

Elements are used to mark up parts (sections) of an XML document and have the following syntax:

<Element> Content </Element>

Here <Element> is the start tag, </Element> is the end tag, and Content is the value of the element.
For example:

<name> Walkenbach </name>

The content refers to character data, while the elements belong to the markup of the document. In turn, character data is divided into Parsed Character Data (PCDATA) and Unparsed Character Data.

Elements may have no content. For example:

<cellphone></cellphone>

In this case, they can be combined into one tag:

<cellphone/>

Elements may also be nested inside other elements:

<employee>
  <name> Walkenbach </name>
  <salary> 10000 </salary>
</employee>

Attributes can be assigned to elements to provide additional information and to shorten the code.
An attribute is a name=value pair placed inside the opening tag.
For example, currency is an attribute of the <salary> tag:

<salary currency="USD"> 10000 </salary>

Or the <person> element could be written using attributes as follows.

Using attributes in XML code

<employee>
  <person lastname="Garnaev" firstname="Andrej" email="garnaev@yandex.ru"/>
  <person lastname="Rudikova" firstname="Lada" email="rudikowa@gmail.com"/>
</employee>

In addition, attributes allow elements to be divided into categories.
For example, in the following code (Listing 10.2, see also file 1-Example.xml on the CD), depending on the value of the type attribute in the <person> element, the information is either confidential or public.

Categorizing elements using attributes

<?xml version="1.0" standalone="yes" ?>
<employee>
  <person type="work">
    <lastname>Bond</lastname>
    <firstname>James</firstname>
    <email>bond007@yandex.com</email>
  </person>
  <person type="work">
    <lastname>Cooper</lastname>
    <firstname>Gary</firstname>
    <email>gcooper@yandex.com</email>
  </person>
  <person type="personal">
    <lastname>Cooper</lastname>
    <firstname>Gary</firstname>
    <marriedstatus>new married</marriedstatus>
    <homephone>354-56-56</homephone>
  </person>
</employee>

Comments in XML are written as follows:

<!-- Example of a comment -->

XML (Extensible Markup Language) is an extensible markup language developed on the basis of SGML (Standard Generalized Markup Language — approved by the International Standards Organization (ISO) as standard ISO 8879:1986 in 1986) as a universal data representation format. This format allows completely different applications to exchange data over the Internet. The basis of XML documents, similar to SGML or, for example, HTML documents, consists of tags (or markers) — marks in the document that can be identified by angle brackets, for example, <p>, <h1>. Tags are codes that are used to define the structure of a document, its visual presentation, and the meaning of the data. Thus, in HTML documents tags serve to define data formatting, whereas in XML documents they serve to define the structure and meaning of the data.

It should be noted that XML provides more advanced capabilities compared to, for example, HTML, since it allows the creation of additional elements with which new data, objects, and their properties can be described and defined, separating the data from their presentation in HTML. This makes it possible to overcome the limitations of HTML in describing non-standard objects. XML has been officially adopted by the W3C (World Wide Web Consortium), which is responsible for standards related to the World Wide Web.

The XML language is platform-independent: any program that is designed to use XML can read and process XML data regardless of the operating system or hardware. Thanks to its universal representation format, XML data can be used in many Microsoft Office 2007 applications.

When working with documents in XML format, the following files may be specified:

• a web page for viewing the resulting document (e.g., in HTML format);
• an XSL file containing a description of the structure of the external representation of the document;
• an XML file including the data that serves as the source for filling in the document;
• an XSD file containing a description of the data structure, usually called a data schema. The data schema may not be separated into a distinct file but instead may be embedded directly into the XML file containing the data.

Such a structure is dictated by the logical separation of an XML document into separate parts: data (XML), data structure (XSD), and data presentation (XSL, for example, transformation into HTML format). Conceptually, all these files together form a single web document, which can be viewed using Internet Explorer 5 or higher. However, an XML file (possibly together with an XSD file) can be used by applications that recognize this data format, regardless of other parts of the document. For example, an HTML file may contain only a script that is activated when the page loads and then loads data onto the page from an XML source.

Separating the data themselves from their presentation (in HTML format) and placing them in a separate XML file enables other applications that understand this specially developed universal format to access and process data from such a document independently of its presentation. Separating data from their presentation also allows applications to apply different methods of displaying the same XML data using several different presentation schemas.

The XSD file is called an XML schema. Its content complies with the XML Schema Definition (XSD) standard, officially adopted by the W3C consortium. The XML schema file describes the structure of the data in a universal way, including information about element names, data types, element combinations, as well as element attributes. The XML schema defines the data model for XML format: it sets the rules for tags and text. The use of an XML schema ensures that data in XML format is correctly understood by other applications and properly transformed into other data formats.

The XML schema contains a description of the data but does not include a description of how they should be displayed in a viewer. Previously, CSS files were used to display data in HTML, containing the relevant information on presenting data in the Cascading Style Sheets language. However, this was not very convenient, as the developer also had to study CSS in addition to XML, and CSS does not provide sufficient tools for controlling data output. In modern applications, a more flexible tool is often used for describing the external representation of data: XSL (Extensible Stylesheet Language). It allows one to select exactly which data to display, specify the order of data elements, modify them, and add additional information. Moreover, this language is similar to XML: it uses tags similar to XML tags and HTML constructs to create a style template for data output. Note that to display XML data in Internet Explorer 5 or higher it is not necessary to attach CSS or XSL files, since this browser has its own default style description. Use your own stylesheet files to ensure a consistent appearance of your web pages based on XML data.

Within XSL, the following specification parts can be distinguished:

• XSLT (XSL for Transformation) — the language for transforming styles, and
• XSL-FO (XSL Formatting Objects) — a unified formatting language that preserves all document data within itself.