Microsoft Excel – handy tool for organizing and structuring a wide variety of data. It allows you to process information using different methods and edit data sets.

Let's consider the possibilities of using it to generate and process web application files. Using a specific example, we will study the basics of working with XML in Excel.

How to create an XML file from Excel

XML is a file standard for transmitting data on the Web. Excel supports its export and import.

Let's look at creating an XML file using the example of a production calendar.

1. Let's make a table from which you need to create an XML file in Excel and fill it with data.
2. Let's create and insert an XML map with the required document structure.
3. Export table data to XML format.

We save the file as XML.

Other ways to get XML data (schema):

1. Download from a database, specialized business application. Schemes can be provided by commercial sites and services. Simple options are publicly available.
2. Use ready-made samples to test XML maps. The samples contain the main elements and XML structure. Copy and paste into Notepad and save with the desired extension.

How to save an Excel file in XML format

One of the options:

1. Click the Office button. Select “Save as” - “Other formats”.
2. We assign a name. Select the save location and file type – XML.

More options:

1. Download XLC to XML converter. Or find a service that allows you to export the file online.
2. Download the add-on from the official Microsoft website XML Tools Add-in. It is freely available.
3. Opening a new book. Office button – “Open”.

How to open an XML file in Excel

Click OK. You can work with the resulting table as with any Excel file.

How to Convert XML File to Excel

We edit the created table and save it in Excel format.

How to collect data from XML files in Excel

The principle of collecting information from multiple XML files is the same as the principle of transformation. When we import data into Excel, the XML map is transferred at the same time. Other data can be transferred to the same schema.

Each new file will be linked to an existing map. Each element in the table structure corresponds to an element in the map. Only one data binding is allowed.

To configure linking options, open the Map Properties tool from the Developer menu.

Possibilities:

1. Each new file will be checked by Excel for compliance with the installed card (if we check the box next to this item).
2. Data may be updated. Or new information will be added to the existing table (makes sense if you need to collect data from similar files).

This is all manual methods import and export of files.

XML for PHP Developers

Part 1. Working with XML in PHP in 15 minutes

PHP5 greatly simplifies working with XML in PHP

Content Series:

The importance of XML in the modern application development environment cannot be overstated. For those who have never worked with XML in PHP or have not yet migrated to PHP5, this quick tutorial will help you get started with the new XML-related functionality of PHP5 and appreciate how easy working with XML can be. This first of three articles introduces the API and demonstrates how SimpleXML, when combined with the DOM, serves the perfect tool for developers dealing with simple, predictable, and relatively compact XML documents. It is precisely these documents that, for example, Ajax applications operate when transmitting information about filling out a form; API responses from Web services such as weather.com are compiled into the same documents.

XML Basics

Brief introduction to XML basics will allow the reader to understand the importance of this language for the PHP developer and learn how to create simple XML documents.

About XML

Extensible Markup Language (XML) can be called both a markup language and a format for storing text data. It is a subset of the Standard Generalized Markup Language (SGML); it provides text tools to describe tree structures and their application to information. XML serves as the basis for a number of languages ​​and formats, such as Really Simple Syndication (RSS), Mozilla XML User Interface Language (XUL), Macromedia Maximum eXperience Markup Language (MXML), Microsoft eXtensible Application Markup Language (XAML) and open source language Java XML UI Markup Language (XAMJ).

XML structure

The basic unit of data in XML is the element. Elements are highlighted with a start tag such as , and an end tag such as. Each start tag must have a corresponding end tag. If any start tag is missing an end tag, the XML document is not formatted correctly and the parser will not be able to parse it properly. Tag names usually reflect the type of element. You might expect the book element to contain the title of the book, such as "The Great American Novel" (see Listing 1). The text contained between tags, including spaces, is called character data.

Listing 1. Example XML document

XML element and attribute names can consist of uppercase (A-Z) and lowercase (a-z) letters, numbers (0-9), some special and non-English characters, and three punctuation characters: hyphen, underscore, and period. Other characters are not allowed in names.

XML is case sensitive. In the given example And describe two different elements. Both names are acceptable. However, describing two different elements by names And cannot be considered a reasonable solution due to the high probability of typos.

Every XML document contains one and only one root element. The root element is the only element in an XML document that does not have a parent element. In the example above, the root element is . Most XML documents contain parent and child elements. Element has one child element . At the element four child elements:

Listing 4 imports a SimpleXMLElement object into a DOMElement object, illustrating how the DOM and SimpleXML interact.

Listing 4. Interop Part 1 - DOM imports SimpleXML

The function in Listing 5 takes a DOM document node and turns it into a SimpleXML node. This new object can then be used as a native SimpleXML element. In case of any error, FALSE is returned.

Listing 5. Interop Part 2 - SimpleXML Imports the DOM

SimpleXML in Action

The SimpleXML extension is the tool of choice for parsing XML. It interacts with the DOM when composing XML files and has built-in XPath support. SimpleXML is easier to code than DOM, as its name suggests.

For those unfamiliar with PHP, Listing 6 formats the test XML file as an include file for readability purposes.

Listing 6. B following examples The test XML file is formatted as a PHP include file named example.php.

In a hypothetical Ajax application, for example, you might want to extract a zip code from an XML document and access a database. Listing 7 extracts the element from the XML file of the previous example

Listing 7. Retrieving a node - how easy is it to get it?

On the other hand, you may need to extract a multi-line address. When a single parent element has multiple instances of a child element, the normal iteration technique is used. This functionality is shown in Listing 8.

Listing 8. Retrieving multiple instances of an element

In addition to reading element names and their values, SimpleXML can also access element attributes. Listing 9 accesses the element's attributes; this is done in exactly the same way as accessing the elements of an array.

Listing 9. Demonstration of SimpleXML accessing element attributes

To compare an element or attribute to a string, or to pass it to a function that requires a string, you must convert it to a string using the (string) operator. Otherwise, by default, PHP treats the element as an object (Listing 10).

Listing 10. Convert to a string or lose

Data in SimpleXML does not have to be immutable. Listing 11 outputs a new XML document that is an exact copy of the original, except that the new one changes the name Cliff to Big Cliff.

Listing 11. Modifying a text node using SimpleXML

Since PHP 5.1.3, SimpleXML has been enhanced with the ability to easily add child elements and attributes. Listing 12 outputs an XML document based on the original one, but with a new character and description added.

Listing 12. Adding child and text nodes using SimpleXML

Conclusion

This first article in a three-part series introduces the API and demonstrates that SimpleXML, when combined with the DOM when needed, is an ideal tool for developers dealing with simple, predictable, and relatively compact XML documents. Version PHP5 has significantly expanded the capabilities of programmers in terms of working with XML in PHP. In part two we will look at more complex methods XML parsing.

Goal of the work: gain practical skills in processing data presented in the Extensible Markup Language format ( XML).

Job objectives:

– study data description technology XML;

– study main library classes FCL, supporting the processing of data presented in the format XM L;

– complete a practical task on developing an application in C#.

Brief theoretical information

Format XML is a way of storing data in a simple text format, which means that the data can be read by almost any computer. This circumstance makes this format very suitable for use when transmitting data over the Internet and can even be read directly by a person.

XML is a markup language that can be used to describe arbitrary data. Based on this language it is possible to organize the storage of information and its exchange, independent of either specific applications or the platform on which they are executed.

XML- documentation. The completed data set is known in XML as XML-document. XML-a document can be a physical file on your computer, or it can be just a line in memory, but it must be complete and follow certain rules. XML-the document consists of several different parts, the most important of which are XML elements that contain the data that actually makes up the document.

Microsoft. NETFramework uses an object data model XML Document Object Model( DOM) to provide access to data in XML-documents, and additional classes for reading, writing and navigating within XML-document. These classes are supported by the namespace System.XML. An example of a description of a book catalog in the DOM is shown in Fig. 8.1.

The description of an XML document includes statements written in accordance with the requirements of its syntax. While creating XML-document, instead of using a limited set of specific elements, it is possible to create your own elements and assign them any names of the user’s choice. This is why XML is extensible. Consequently, this language can be used to describe almost any document: from a musical score to a database.

Catalog

Rice. 8.1. Hierarchical document structure

For example, a book catalog could be described as shown in Listing 8.1 (line numbers are not part of the document XML). For creating XML-document in the environment VisualStudio . NET you should use the command File\ NewFile and select the template name in the drop-down list of templates XMLFile.

Listing 8.1. Text XML-document

Christian Nagel

1560

800

2006

Nikita Kultin

240

350

2007

Line 1 of this listing contains an announcement XML, identifying the text as a document XML.Despite the optionality of the announcement XML, the document must include it to identify the version being used XML, since the document is without declaration XML may subsequently be treated as conforming to the latest version of XML, which may result in errors. Information parameter version indicates version XML, used in the document, parameter encoding– document encoding ( utf-8).

Line 2 contains a comment starting with the characters . Comments can be posted throughout XML-document.

IN XML- the document data is marked using tags(elements), which are names enclosed in angle brackets ( < > ). Tag names in XML-document (such as CATALOG, BOOK,TITLE,AUTHOR,PAGES,PRICE, PDATA in Listing 8.1) are not XML language definitions and are assigned when the document is created. For tags you can choose any correctly specified names, for example INVENTORY instead of CATALOG or ITEM instead of BOOK. Line 3 contains the root tag - CATALOG, which opens the markup of the entire document. When it finishes writing the root tag, the framework automatically inserts the end tag (line 18 of Listing 8.1), marking it with the symbols .

Note. Trying to create more than one root element in XML-document is an error.

The root element can contain an arbitrary number of nested elements. In Listing 8.1 XML-the document has a hierarchical structure in the form of a tree with elements nested within other elements and with one top-level element Document element, or Root element(in our example - CATALOG), which contains all other elements. Root element CATALOG includes descendant elements BOOK. In turn, the element BOOK consists of descendant elements TITLE, AUTHOR,PAGES,PRICE, PDATA.

Well-formed XML documents. The document is called correctly formed(well-formed) if it meets the following minimum set of rules for XML-documents:

– XML-the document must have only one root element - element Document. All other elements must be nested within the root element;

– elements must be nested in an orderly manner. If an element begins inside another element, then it must end inside that element;

– Each element must have a start and end tag. Unlike HTML, XML does not allow the end tag to be omitted even if the browser is able to determine where the element ends;

– the element name in the start tag must exactly match (case sensitive) the name in the corresponding end tag;

– the element name must begin with a letter or an underscore ( _ ), followed by letters, numbers, and symbols: dot ( . ), dash ( - ) or underscore.

These are the basic rules for correct formation XML-document. Other concepts of the XML language (attributes, primitives, relationships) have their own rules that must be followed. We can say that if a document is created correctly and no errors occur when displaying or using it, then this is a correctly formed document. If you make a mistake in any tag HTML-page, then the browser will simply ignore the corresponding tag, and the error is in the tag XML-page will make it impossible to display it. If one of the errors is present, the analyzer built into Internet Explorer (sometimes called XML-processor, or parser) determines its position

FCL Library Classes for reading XML files. Working with XML-documents support the following library classes FCL: XmlTextReader, XmlDocument, XPathNavigator.

Class XmlTextReader is an abstract class that performs reading and provides fast delivery of uncached data. This approach is the least expensive in terms of server resources, but it forces data to be retrieved sequentially, from start to finish.

Class XmlDocument represents an implementation of the model DOM. This class holds data in memory after calling a method Load() to retrieve them from a file or stream, provides a tree view of the document in memory with navigation and editing capabilities, and also allows you to modify the data and save it back to the file.

Class XPathNavigator same as class XmlDocument, keeps in memory XML- entire document. It provides advanced data search capabilities, but does not provide the ability to make changes or save them.

Class XmlTextReader. Let's look at a simple example. Place elements on the form richTextBox And button(Fig. 8.2). When you click a button on an element richTextBox The file whose contents were presented in Listing 8.1 will be loaded. The code for the function called when the button is clicked is shown in Listing 8.2.

Rice. 8.2. Reading results from Xml-document

Listing 8.2. Button click handler code

//Cleaning the element richTextBox1

richTextBox1. Clear();

// Call a static method Create() returning a class object

// File book.xml is in the same location as the executable file

// programs

// Method Read() moves to the next node Xml-document

while (rdr.Read())

if(rdr.NodeType ==XmlNodeType.Text)

richTextBox1.AppendText(rdr.Value + "\r\n");

Class XmlReader can also read strongly typed data. There are several methods ReadElementContentAs, performing reading, among which ReadElementContentAsDecimal(),ReadElementContentAsInt() ,ReadElementContentAsBoolean() and etc.

Listing 8.3 shows how to read values ​​in decimal format and perform mathematical operations on them. In this case, the price of the element increases by 25%. The results of running this code are shown in Fig. 8.3.

Rice. 8.3. Reading results from Xml - document

only titles and prices of books

Listing 8.3. Reading data with strong type checking

// Cleaning the element richTextBox1

richTextBox1. Clear();

// Create a stream to read from a file book. xml

XmlReader rdr = XmlReader.Create("book.xml");

while (rdr.Read())

if (rdr.NodeType == XmlNodeType.Element)

// Checking the element name

if (rdr. Name == " PRICE")

// Method ReadElementContentAsDecimal() performs

// convert element content to type decimal

decimal price = rdr.ReadElementContentAsDecimal();

richTextBox1.AppendText("Currentprice= " + price +

"rub\r\ n");

// Price change by 25%

price += price * (decimal).25;

richTextBox1.AppendText("Newprice= " + price +

" rub\r\n\r\n");

else if (rdr.Name == "TITLE")

richTextBox1.AppendText(rdr.ReadElementContentAsString() + "\r\n");

Class XmlDocument. This class and its derived class XmlDataDocument used in the library .NET to represent the document object model DOM.

Unlike class XmlReader, Class XmlDocument offers not only reading but also writing capabilities, as well as random access to the tree DOM.

Let's look at an example in which an object of a class is created XmlDocument, the document is loaded from disk and a list window with book titles is displayed (Fig. 8.4).

Rice. 8.4. Display book titles in a list.

In the application form class, create a class object XmlDocument:

XmlDocument_doc = new XmlDocument();

The button click handler code is shown in Listing 8.4.

_doc.Load("book.xml");

// Get only those nodes that are needed

XmlNodeListnodeLst = _ doc. GetElementsByTagName(" TITLE");

// View in class loop XmlNodeList

foreach (XmlNodenodeinnodeLst)

listBox1. Items. Add(node. InnerText);

Let's introduce into the application the ability to display information about a book whose title is highlighted in the list, for which we'll add an event handler listBox1_ SelectedIndexChanged as shown in Listing 8.5.

Listing 8.5. List item click handler

private void listBox1_SelectedIndexChanged(object sender, EventArgs e)

// Create a search string

string srch = "KATALOG/BOOK";

// Search for additional data

XmlNode foundNode = _doc.SelectSingleNode(srch);

if (foundNode != null)

MessageBox.Show(foundNode.OuterXml);

MessageBox.Show("Not found");

The results of the application are shown in Fig. 8.5.

Rice. 8.5. Displaying information about the selected list item

Using a class XmlDocument You can also insert nodes into an existing document using the method Create- Element() .

For example, to create a new element BOOK you need to write the following code:

XmlElement newBook ​​= _doc.CreateElement("BOOK");

Create elements nested within an element BOOK, you can use the following code:

// Create a new element AUTOR

XmlElement newAuthor = _doc.CreateElement("AUTOR");

newAuthor.InnerText = "C.Baydachny";

The complete code of the button click handler is shown in Listing 8.6, the results of its operation are shown in Fig. 8.6.

Listing 8.6. Button click handler

privatevoidbutton1_ Click(objectsender, EventArgse)

_doc.Load("book.xml");

XmlElement newBook ​​= _doc.CreateElement("BOOK");

// Create a new element TITLE

XmlElement newTitle = _doc.CreateElement("TITLE");

newTitle.InnerText = ".NET Framework 2.0";

newBook.AppendChild(newTitle);

// Create a new element AUTOR

XmlElement newAuthor = _doc.CreateElement("AUTOR");

newAuthor.InnerText = "C.Baydachny";

newBook.AppendChild(newAuthor);

// Create a new element PAGES

XmlElement newpages = _doc.CreateElement("PAGES");

newpages.InnerText = "498";

newBook.AppendChild(newpages);

// Create a new element PRICE

XmlElement newprice = _doc.CreateElement("PRICE");

newprice.InnerText = "590";

newBook.AppendChild(newprice);

// Create a new element PDATA

XmlElement newpdata = _doc.CreateElement("PDATA");

newpdata.InnerText = "2006";

newBook.AppendChild(newpdata);

// Add to current document

_doc.DocumentElement.AppendChild(newBook);

// Write the document to disk

XmlTextWriter tr = new XmlTextWriter("bookEdit.xml", null);

tr.Formatting = Formatting.Indented;

_doc.WriteContentTo(tr);

tr. Close();

XmlNodeListnodeLst = _ doc. GetElementsByTagName(" TITLE");

// View in class loop XmlNodeList

foreach (XmlNodenodeinnodeLst)

listBox1. Items. Add(node. InnerText);

When using classes XmlDocument And XmlReader The following features must be taken into account. If you need random access to a document, you should use the class XmlDocument, and if you need a threading model, then classes based on the class XmlReader. Class XmlDocument is highly flexible, but its memory requirements are higher than those of the class XmlReader, and performance when reading a document is lower.

Rice. 8.6. Running application window

with added node

Class XPathNavigator. Class XPathNavigator is part of the namespace System. Xml. XPath, designed to increase performance. This space provides only reading of documents, therefore there are no editing tools in it, and classes are built to ensure fast execution on a given XML-document loops and selection operations in cursor style.

The best way to use classes from a namespace System. Xml. XPath is document viewing book. xml in a cycle.

Place elements on the form listBoxAndbutton. The button click handler code is shown in Listing 8.7, the results of executing this code are shown in Fig. 8.7.

Listing 8.7. Button click handler

privatevoidbutton1_ Click(objectsender, EventArgse)

// Create an object with the name doc class XPathDocument and transfer

// in its name constructor XML- file book.xml

XPathDocument doc = new XPathDocument("book.xml");

// Create an object with the name nav class XPathNavigator object based

// XPathDocument. An object nav can be used read-only

XPathNavigator nav = ((IXPathNavigable)doc). CreateNavigator();

// Create an object XPathNodeIterator for directory nodes

// and its child nodes

XPathNodeIterator iter = nav.Select("/KATALOG/BOOK");

while (iter.MoveNext())

// Method SelectDescendants() class XPathNavigator selects everything

// descendant nodes of the current node that meet the selection conditions

XPathNodeIterator newIter =