Microsoft Excel is a convenient 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 XML Tools Add-in from the official Microsoft website. 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).

These are all manual ways to import and export files.

XML is a universal format for working with data. It is supported by many programs, including those from the DBMS field. Therefore, converting information to XML is important precisely from the point of view of interaction and data exchange between various applications. Excel is just one of the programs that work with tables, and can even perform manipulations with databases. Let's figure out how to convert Excel files to XML.

Converting data to XML format is not such a simple process, since during this process a special schema (schema.xml) must be created. However, to convert information into a simple file of this format, it is enough to have the usual saving tools in Excel at hand, but to create a well-structured element you will have to seriously tinker with drawing up a diagram and connecting it to the document.

Method 1: Simple Saving

In Excel, you can save data in XML format simply by using the menu "Save as…". True, there is no guarantee that all programs will then work correctly with a file that was created in this way. And this method does not work in all cases.

Thus, the file conversion from Excel to XML format will be completed.

Method 2: Developer Tools

You can convert the Excel format to XML using the developer tools on the program tab. Moreover, if the user does everything correctly, then at the end he will receive, unlike the previous method, a full-fledged XML file that will be correctly perceived by third-party applications. But it must be said right away that not every beginner may have enough knowledge and skills to immediately learn how to convert data in this way.

1. By default, the Developer Tools tab is disabled. Therefore, first of all, you need to activate it. Go to the tab "File" and click on the item "Options".



2. In the parameters window that opens, move to the subsection "Customize the Ribbon". On the right side of the window, check the box next to the value "Developer". After that, click on the button "OK" located at the bottom of the window. The developer tools tab is now enabled.



3. Next, open the Excel table in the program in any convenient way.



4. Based on it, we have to create a diagram that can be generated in any text editor. For these purposes, you can use regular Windows Notepad, but it is better to use a specialized application for programming and working with markup languages ​​Notepad++. Let's launch this program. We create a diagram in it. In our example, it will look as shown below in the screenshot of the Notepad++ window.

   

   As you can see, the opening and closing tag for the document as a whole is "data-set". The tag plays the same role for each line "record". For the schema it will be enough if we take only two rows of the table, and do not manually translate it all into XML. The name of the opening and closing tag of a column can be arbitrary, but in this case, for convenience, we preferred to simply translate the Russian-language column names into English. After the data is entered, we simply save it through the functionality of a text editor anywhere on the hard drive in XML format under the name "schema".

5. Again we go to Excel with the table already open. Moving to the tab "Developer". On the ribbon in the toolbox "XML" click on the button "Source". In the field that opens on the left side of the window, click on the button "XML Maps...".



6. In the window that opens, click on the button "Add…".



7. The source selection window opens. Go to the directory for the location of the diagram compiled earlier, select it and click on the button "Open".



8. After the elements of the diagram have appeared in the window, drag them using the cursor into the corresponding cells of the table column names.



9. Right-click on the resulting table. In the context menu, go through the items sequentially "XML" And "Export…". After that, save the file in any directory.

As you can see, there are two main ways to convert XLS and XLSX files into XML format using Microsoft Excel. The first of them is extremely simple and consists of an elementary saving procedure with a given extension through the function "Save as…". The simplicity and clarity of this option are undoubtedly advantages. But it has one very serious drawback. The conversion is carried out without taking into account certain standards, and therefore a file converted in this way may simply not be recognized by third-party applications. The second option involves drawing up an XML map. Unlike the first method, a table converted using this schema will meet all XML quality standards. But, unfortunately, not every user will be able to quickly understand the nuances of this procedure.

When working with block data, there is often a need to convert it into other formats, so to speak, for better interaction. This article will provide possible XML to XLS converters. In other words, we will talk about ways to convert one format to another.

Method 1: Using Excel

Since Excel works flawlessly with block format files, it can open the XML extension without problems. So we can say without a twinge of conscience that Excel is an XML to XLS converter. Let's look at how to transform the format using it:

1. Open Excel.
2. On the panel, click File.
3. Click on the “Open” option.
4. In the Explorer window that appears, go to the directory with the XML file and open it.

1. Open the File tab again.
2. Select the "Save As" option.
3. Go to the directory where you want to place the modified document.
4. In the “File type” drop-down list, select “Excel Workbook”.
5. Save the file by clicking the appropriate button.

After all the manipulations described above, the file, which originally had an XML extension, will turn into an Excel workbook, that is, it will have an XLS extension.

Method 2: Import Data Option in Excel

Excel is an XML to XLS converter with two ways to accomplish this task. Therefore, let’s move directly to the second option, which involves using the “Data Import” option. But before you start, you need to enable the “Developer” menu, since this is where the necessary tool is located. Next you need to do the following:

1. Open the program.
2. Go to the "File" menu.
3. Follow the "Options" section.
4. Open the Customize Ribbon menu.
5. Place a check next to the “Developer” line, which is located in the right area of ​​the window.
6. Click OK.

The required toolbar is included in the program interface, which means that we can start converting:

1. Go to the Developer tab.
2. From the toolbar, select Import.
3. A window will open in which you need to select an XML file.
4. A window will appear asking you to create a schema based on the data. Click OK.
5. After this, a second window will appear in which you need to decide on the location of the imported table. Click cell A1 and click OK.

The file is imported into Excel. Now you can save it as XLS. To do this, by analogy with the previous instructions, open the “File” tab, click on “Save as”, specify the directory, select the format and save.

Method 3: Using an Internet service

It may happen that you need an XLS to XML converter for VAT, for example. In this case, Excel will not help, and you will need to turn to another program. In this case, let's talk about the online service Convertio:

1. In any browser, go to the main page of this service.
2. Select a file upload method. If it is located on your disk, click the “From Computer” button and select it in the file manager. If in the “cloud”, then select the storage itself, and then the file.
3. The document has been added and is ready for conversion. To do this, you need to select the XML format from the drop-down list on the right.
4. Click the "Convert" button.

After this, the file will be prepared, and all you have to do is download it. Convertio is not only an XLS to XML file converter. It is capable of working with a much larger number of formats. Simply upload the file, and then choose the format you want to convert it to.

So we looked at XML to XLS converters and vice versa. As you can note, Excel is excellent for converting XML. However, it cannot convert XLS, so it requires the help of other programs. In this case, it is the online service Convertio.

Representing data based on entering a description with tags or program settings. You cannot open them for editing with a regular double click. This is due to the fact that the required application, which is used by default, is not installed to associate with the extension. But if you want a readable, editable table file, you can open the XML file in Excel. In this case, no converters are needed that can convert formats between themselves. The only caveat is that this feature is only available in Office versions 2003 and higher.

How to open XML in Excel: method one

Let's look at importing data based on Excel version 2016. The first and easiest way is to initially launch Excel. When you start the application, instead of a greeting and logo, it will display a special login window, in which there is a line “Open other books” in the left menu.

After this, the browse item is used, and in the new window XML is selected as the opening format. After this, using the usual method, we find the desired file and press the open button. In this case, it is recognized not as a text document containing descriptions and tags, but as a very ordinary table. Naturally, the data can be edited at your discretion, but more on that later.

How to open XML format in Excel: method two

Another proposed method is practically no different from the first. An XML file in Excel can be opened from the file menu or using the shortcut Ctrl + O.

Again, the type of format to be opened is first selected, after which the desired file is found and the corresponding button is pressed.

Opening XML: Method Three

There are several more XML methods in Excel. So, in the 2016 version of the program, you can use the top panel menu, where you select the “Data” section, and then click the button to obtain external data.

In the drop-down menu, you just need to select the “From Other Sources” section and use the line “From XML Import” in the new menu. This is followed by the standard procedure of searching for the desired file and then opening it.

Editing, saving and exporting

When using any of these methods, the user gets the table structure. Editing is done in the same way as with standard XLS files. Sometimes, for ease of editing and saving data, it is advisable to use the developer menu.

In this case, you can import not all the contents of the XML file, but only what is really necessary, inserting information into the appropriate columns and rows, specifying the XML object as the data source. But to do this, you need to log into your account in the program itself using your registration with Microsoft.

You can save the changed file immediately in the original format by selecting the appropriate type from the list. From the file menu, if the object was saved in the “native” Excel format, you can select the export function, click on change file type and set XML as the final format.

If the user is too lazy to do such conversions, or he uses a version of Office lower than version 2003, he will have to use a special converter to open this format as a table. There are quite a lot of such programs now offered. As a last resort, if this is not suitable, you can easily turn to specialized online services, where the format will be changed within a couple of tens of seconds. After completing these steps, all that remains is to download the finished result in XLS format to your hard drive, and then open it in Excel. However, in most cases such actions are not required, since in Office 2003 the ability to directly open (import) the XML format is already provided initially. And it seems that few people today use outdated Microsoft office products.

Those who own the wonderful TextKit program, or at least heard about it (those who haven’t, urgently) should have noticed among its capabilities the parsing of XML documents into any template. That is, if we have an XML document, we can easily create a website using TextKit.

There remains one small problem, how to get this XML. In fact, for the purpose of creating a website, you can take ready-made documents from various affiliate programs, such as MixMarket or Ozone. But about them some other time. I'll tell you how to get XML from a regular XLS table. In fact, it is a MS Excel format.

Possession of this knowledge will allow us to create websites using TextKit from any database in Excel. We will work in the 2007 office.

1) Download the add-on for working with XML from here.

2) Go to the main Excel menu, parameters.

3) In the menu that opens, select “Add-on”. We look for the “Manage” item, select “Excel Add-ins” and click “Go”:

4) In the window that opens, use “Browse” to select the file we need XmlTools.xla, add it to the panel and activate it by checking the box next to it.

By default, this file is stored in the following folder on your hard drive:
\Office Samples\OfficeExcel2003XMLToolsAddin.

5) If you did everything correctly, then in the “Options” in the “Add-ons” section you will see the installed extension:

Now we take our data to create an XML document and work with it.

1) Click on the button on the panel. Select the first item:

2) In the window that appears, select yes/no if our data contains/does not contain a header, in my case it does, so I change the checkbox to yes. Here we hold down the small “pip” button to select cells with data:

3) By dragging, select the data to create an XML document. Press the button and click OK.

If everything was done correctly, the sign will turn blue and white, and the menu will change greatly:

Well, that's almost it. Now go to the document saving options and select XML data there.

Actually, that's all! We received a ready-made XML document as output, which we can now parse in the parser without any problems. But more about that some other time :)